SQL Server 2011 - Автономная база данных - Программные продукты

Источник: habrahabrru

Niladri Biswas

Denali несет в себе множество новых инструментов, а так же расширений функционала для существующих. В этой статье в деталях рассмотрим один из новых инструментов, который, я уверен, придется по душе разработчикам баз данных. Этот инструмент, фича - автономные базы данных (Contained Database). Рассмотрим что они собой представляют, как с ними работать, к чему можно применить и другие вещи.

Что не так с текущими базами?

Перед тем как перейти к описанию сущности независимых баз данных, рассмотрим почему они были придуманы и чем не устраивает разработчиков текущая реализация.

Вот некоторые из ключевых проблем:
Потеря информации во время разворачивания базы или передвижения ее между серверами.
Во время передвижения базы данных между серверами, то такая информация как логины, пароли, задания агента SQL Server и прочее не может быть передвинуто вместе с базой. Потому что эта информация принадлежит серверу баз данных. Пересоздание упомянутых объектов руками не самое приятное занятие, которое к тому же занимает много времени и не гарантирует защиты от ошибок.
Различия между Разработкой и Разворачиванием приложений.
При разворачивании приложения разработчиков могут поджидать самые разные неприятности, начиная от несовпадения тонких настроек окружения, таких как разрешения на создание новых логинов, отключенная командная строка (xp_cmdshell), языковые настройки сервера.
Вопросы безопасности в администрировании приложения.
Весьма сложно администрировать и поддерживать отдельно стоящую базу данных из-за того, что, например, задания агента SQL Server доступны в пределах всего сервера, что влечет за собой повышение привилегий отдельно взятых пользователей. Однако эти повышенные права дают доступ к другим частям сервера, которые не желательно открывать. Все это может вести к серьезным проблемам безопасности.

После того, как обозначили ключевые недостатки существующих баз, перейдем к описанию нового типа.

Автономные базы данных

Уже исходя из названия и описанных недостатков старых баз, я думаю, вы догадываетесь, в чем прелесть нового типа баз. Автономные базы данных хранят всю необходимую для работы и настройки информацию в себе. Такие базы полностью независимы от настроек SQL сервера, не имеют внешних зависимостей и содержат в себе все механизмы аутентификации. Так же не имеет значения, какая настройка языка выставлена у сервера.

Таблицы, функции, процедуры, ограничения, схемы, типы, библиотеки, представления, логины, задания агента SQL Server, системные настройки, связанные сервера (linked servers) - все хранится в базе.

Естественно, что главным преимуществом такой базы будет легкость разворачивания и переноса. Достаточно только развернуть базу и она сразу готова к работе. Больше не будет забытых скриптов для пользователей, их ролей, агентов и тд.

Термины, которые надо запомнить

Application boundary (граница приложения) - граница между экземпляром сервера и кодом приложения. Под кодом приложения понимается вся база со всеми объектами, которые могут понадобиться в процессе работы.

Application Model (модель приложения) - внутри границ приложения есть место, где идет разработка и управление приложением.

Contained (содержащийся) - пользовательская сущность которая полностью содержится в пределах границы приложения.

Uncontained (не содержащийся) - пользовательская сущность которая пересекает границы приложения.

Non-contained database (зависимая база данных) -база, для которой свойство containment = NONE. База зависит от некоторых объектов принадлежащих экземпляру сервера.

Fully contained database (автономная база данных) - база, которая не позволяет каким-либо объектам или функциям пересекать границы приложения.

Partially contained database (частично зависимая база данных) - база, которая позволяет некоторым объектам действовать с пересечением границ приложения. Доступно в CTP 1.

Contained user (автономный пользователь)

Есть два типа таких пользователей:

Пользователь, который авторизуется средствами базы данных.
Пользователь, который использует средства Windows и его данные не содержаться в базе данных.

4 шага для создания автономной базы данных

Я думаю, что на данный момент уже достаточно теоретических знаний и концепций о том, как работает такая база данных, и настало время немного размяться "в поле". Следующие 4 шага описывают как создать автономную базу данных.

Шаг 1. Разрешить использование автономных баз данных на уровне сервера.

Шаг 2. Создать базу данных и выставить режим автономности как частичный. Свойство CONTAINMENT должно быть равно Partial.

Шаг 3. Создать автономного пользователя в новой базе данных.

Шаг 4. Зайти в новую базу данных под учетной записью автономного пользователя.

Теперь рассмотрим каждый шаг подробно и в картинках.

Шаг 1. Разрешить использование автономных баз данных на уровне сервера.

Присоединитесь к экземпляру нового SQL Server 2011 и из Обозревателя объектов (Object Explorer) вызовите контекстное меню для сервера. В контекстном меню необходимо выбрать пункт Properties (Свойства).

Перейдите на страницу Advanced и на ней необходимо выставить значение для свойства Enable Contained Databases равное TRUE.

То же самое может быть достигнуто с помощью скрипта.
--Enabled Advanced options
-- разрешить работать с настройками с закладки Advanced
sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
Go

--Enabled Database Containment
--Разрешить использование автономных баз данных
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
go

Шаг 2. Создать базу данных и выставить режим автономности как частичный

Создадим новую базу и назовем ее TestContainedDB.

После создания открываем ее свойства через контекстное меню

Открываем закладку Options и выбираем для опции Containment type: свойство Partial.

То же самое может быть достигнуто с помощью скрипта.
USE [master]
GO

CREATE DATABASE [TestContainedDB]
CONTAINMENT = PARTIAL
ON PRIMARY
LOG ON
GO

ALTER DATABASE [TestContainedDB] SET COMPATIBILITY_LEVEL = 110
GO

Шаг 3. Создать автономного пользователя в новой базе данных.

В новой базе данных перейдите в узел Security, затем Users и с помощью контекстного меню создаем нового пользователя.

Задаем имя учетной записи и пароль. Пусть для примера это будет testuser\testuser.

Указываем, что пользователь будет владельцем базы. Для этого на странице Membership отмечаем галочкой пункт db_owner.

Эти же действия можно совершить при помощи TSql
USE [TestContainedDB]
GO
CREATE USER [TestUser]
WITH PASSWORD='testuser',
DEFAULT_SCHEMA=[dbo]
GO

Как только описанные действия будут завершены, можно убедиться, что пользователь появился в системе.

Шаг 4. Зайти в новую базу данных под учетной записью автономного пользователя.

Для демонстрации шага надо завершить работу в SSMS, и войти снова следуя описанным шагам.

В полях для имени пользователя и пароля введите ту информацию, которую задавали во время создания пользователя для автономной базы. В нашем случае это testuser \ testuser.

После этого надо нажать на кнопку Options и перейти на закладку Connection Properties.

На этой закладке надо указать к какой базе данных мы собираемся присоедениться. В данном случае это TestContainedDB.

Теперь можно жать на кнопку Connect, и мы окажемся в автономном окружении базы.

Конвертация базы в автономную

Я думаю после описания плюсов автономной базы данных и того, как ее можно создать, вы задумались о том, можно ли перевести существующую базу в автономный режим. Можно. Сейчас будет продемонстрирован такой процесс. Поскольку демонстрация будет вестись на тестовой базе, то для начала ее и создам, воспользовавшись скриптом ниже:
USE [master]
GO

CREATE DATABASE [NonContainedDB]
CONTAINMENT = NONE
ON PRIMARY
GO

ALTER DATABASE [NonContainedDB] SET COMPATIBILITY_LEVEL = 110
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [NonContainedDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

Затем создаем таблицу с данными.
-- Drop the table if it exists
-- удалить таблицу, если она существует
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_Players' AND type = 'U')
DROP TABLE tbl_Players
GO
SET ANSI_NULLS ON
GO
--Create the table
-- создать таблицу
CREATE TABLE tbl_Players (
PlayerID INT IDENTITY,
PlayerName VARCHAR(15),
BelongsTo VARCHAR(15),
MatchPlayed INT,
RunsMade INT,
WicketsTaken INT,
FeePerMatch NUMERIC(16,2)
)

--Insert the records
-- внести записи в таблицу
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Won','India',10,440,10, 1000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Cricket','India',10,50,17, 400000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('B. Dhanman','India',10,650,0,3600000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('C. Barsat','India',10,950,0,5000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Mirza','India',2,3,38, 3600000)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('M. Karol','US',15,44,4, 2000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Hamsa','US',3,580,0, 400)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Loly','US',6,500,12,800000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Summer','US',87,50,8,1230000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.June','US',12,510,9, 4988000)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A.Namaki','Australia',1,4,180, 999999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Samaki','Australia',2,6,147, 888888)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('MS. Kaki','Australia',40,66,0,1234)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Boon','Australia',170,888,10,890)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('DC. Shane','Australia',28,39,338, 4444499)

INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Noami','Singapore',165,484,45, 5678)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Biswas','Singapore',73,51,50, 22222)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Dolly','Singapore',65,59,1,99999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Winter','Singapore',7,50,8,12)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.August','Singapore',9,99,98, 890)

Для полноты картины добавим хранимую процедуру.
If Exists (Select * from sys.objects where name = 'usp_SelectRecordsByPlayerName' and type = 'P')
    Drop Procedure usp_SelectRecordsByPlayerName
Go
-- Create the stored procedure
Create Procedure [dbo].[usp_SelectRecordsByPlayerName]
( @PlayerID int )
As
Begin
Select
  PlayerID
  ,PlayerName
  , BelongsTo
  , MatchPlayed
  ,RunsMade
  ,WicketsTaken
  ,FeePerMatch
From
tbl_Players
Where PlayerId = @PlayerID
End

В итоге у нас должна быть база данных, таблица и хранимая процедура. Сейчас база работает в нормальном, зависимом режиме и нашей целью будет превратить ее в автономную базу данных.

Шаг 1

На первом шаге нужно будет создать нового пользователя на уровне сервера и пользователя для базы. Это можно сделать с помощью такого скрипта:
--Create a login on the server
-- Создаем пользователя на уровне сервера
CREATE LOGIN NonContainedUser
WITH PASSWORD = 'somepassword@123'

--Create a "non-contained" users for the login on the server
-- создаем автономного пользователя
USE NonContainedDB
GO
CREATE USER NonContainedUser FOR LOGIN NonContainedUser
GO

Шаг 2

Теперь надо определить какие объекты принадлежат связанной базе данных. Для этого можно выполнить следующий код
USE NonContainedDB
GO

SELECT
class_desc
,feature_name
,feature_type_name
FROM sys.dm_db_uncontained_entities

Результат должен быть как на скриншоте ниже.

Можно проигнорировать ROUTE. Итак, по данным скрипта, у нас имеется 2 объекта в связанной базе данных.

Для определения пользователей принадлежащих серверу можно запустить такой скрипт:
USE NonContainedDB
GO
SELECT dp.name
FROM sys.database_principals dp
JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.authentication_type = 1
AND sp.is_disabled = 0

Что даст нам в результате выполнения

Шаг 3

На базе NonContainedDB надо вызвать контекстное меню и выбрать Properties. Затем перейти на закладку Options и выбрать для свойства Containment type значение Partial.

Другой способ для описанных действий заключается в написании скрипта
USE master
GO
ALTER DATABASE NonContainedDB SET CONTAINMENT=PARTIAL;
GO

После того как настройка выставлена, необходимо мигрировать пользователей на автономную базу.
USE NonContainedDB
GO
EXEC sp_migrate_user_to_contained @username = N'NonContainedUser',
@rename = N'keep_name',
@disable_login = N'disable_login'

Процедура sp_migrate_user_to_contained нужна для миграции пользователей в автономную базу. Она конвертирует пользователей уровня сервера в пользователей автономной базы. После процедуры можно снова запустить скрипт определяющий зависимых пользователей.
USE NonContainedDB
GO
SELECT dp.name
FROM sys.database_principals dp
JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.authentication_type = 1
AND sp.is_disabled = 0

И результат:

Можно убедиться что NonContainedUser больше не появляется. Это означает что пользователь был изменен и его учетная запись была заблокирована на уровне сервера.

Шаг 4.

Отсоединитесь от текущего сервера и вызовите форму соединения. В полях для имени и пароля введите данные пользователя, которого мы мигрировали на автономную базу. Далее в опциях соединения задайте все как упоминалось выше, в разделе соединения с автономной базой.

После соединения в строке о базе данных должно быть что-то похожее.

Бэкап автономной базы данных.

Осуществляется точно так же как и архивирование обычной базы данных. Так что это процесс можно сделать с интерфейса пользователя или с помощью скрипта.
Интерфейс

Присоединитесь к базе данных с помощью SSMS, в навигаторе объектов (Object Explore) находите желаемую базу. В контекстном меню идете по пунктам Tasks > Backup

Скрипт

Сделать бэкап базы можно с помощью нехитрого скрипта.
BACKUP DATABASE TestContainedDB
TO DISK='<File Path>\TestContainedDB.bak'

Восстановление архивной базы

Опять же есть два пути: через интерфейс пользователя и с помощью скрипта.
Интерфейс

Через интерфейс все делается схожим образом с архивированием

Присоединитесь к базе данных с помощью SSMS, в навигаторе объектов (Object Explore) находите желаемую базу. В контекстном меню идете по пунктам Tasks > Restore

Скрипт

RESTORE DATABASE TestContainedDB
FROM DISK='<File Path>\TestContainedDB.bak'

Во время восстановления базы можно получить сообщение:

Msg 12824, Level 16, State 1, Line 1

The sp_configure value 'contained database authentication' must be set to 1 in order to restore a contained database. You may need to use RECONFIGURE to set the value_in_use.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Из которого становится ясно, что необходимо активировать опцию Contained Database Authentication в настройках SQL Server на уровне экземпляра сервера. Эта настройка по умолчанию выключена. Выполните скрипт ниже для устранения проблемы.
--Enabled Advanced options
-- включаем возможность редактирования опций
sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
go
--Enabled Database Containment
-- включаем возможность аутентификации для автономных баз данных
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
go

Еще немного информации об автономных базах данных

Методы аутентификации поддерживаемые автономными базами остались те же:
SQL Server Authentication
Windows Based Authentication

Изменение базы данных изменилось. CREATE / ALTER DATABASE работают теперь по-другому. Выражение Alter Database <database name> больше не работает. Вместо имени базы надо указывать служебное слово CURRENT.
ALTER DATABASE CURRENT