SQL Server 2012: Иногда часть лучше целого

Частично автономные базы данных решают серьезные проблемы с консолидацией и миграцией, которые существуют в базах данных SQL Server, начиная с версии 4.2. К счастью, это будет одна из многих новых функций в SQL Server 2012.

Первая проблема, решаемая такими базами данных, - перемещение базы данных с одного экземпляра SQL Server на другой без необходимости указывать имена входа SQL Server, используемые в этой БД. Также решается проблема создания необходимых имен входа на целевом экземпляре.

В SQL Server 2012 это реализуется за счет использования автономного имени входа SQL Server. Это пользователь SQL Server, созданный в автономной БД, причем у этого пользователя нет соответствующего ему имени входа в SQL Server. Пароль этого пользователя в автономной БД хранится в этой же базе данных, поэтому проверка подлинности проходит без проблем.

Вторая разрешенная проблема - конфликты сортировки между таблицами в автономной БД и временными таблицами. SQL Server автоматически создает временные таблицы в контексте автономной базы данных при ее сортировке. Это позволяет размещать на одном экземпляре SQL Serverбазы данных с разными сортировками без необходимости менять инструкцию CREATE TABLE для определения порядка сортировки или использовать COLLATE в синтаксисе инструкции JOIN.

В отличие от некоторых новых функций, которые требуют переводить базы данных на новый уровень совместимости, частично автономные БД поддерживаются вплоть до версии SQL Server 2005. С другой стороны, функциональность автономных баз данных пока не совсем готова. Она будет завершена где-то между выпуском SQL Server 2012 CTP3 и готовой версией в начале или середине 2012 года.

Создание частично автономной БД

Прежде чем приступить к превращению обычной базы данных в частично автономную, нужно изменить параметры настройки сервера с помощью системной хранимой процедуры sp_configure. Используйте ее для изменения значения параметра "contained database authentication" с 0 на 1, а затем примените инструкцию RECONFIGURE для активации нового параметра:

EXEC sp_configure 'contained database authentication', 1
RECONFIGURE
GO

После включения автономной проверки подлинности базу данных можно перевести в разряд частично автономных. Для этого можно задействовать SQL Server Management Studio, как показано на рис. 1, использовать инструкцию ALTER DATABASE или создать новую базу данных как автономную:

ALTER DATABASE:
USE [master]
GO
ALTER DATABASE [Cont]
 SET CONTAINMENT=PARTIAL
GO
CREATE DATABASE:
CREATE DATABASE [Cont1]
 CONTAINMENT=PARTIAL
GO

Чтобы создать новую частично автономную БД или превратить существующую базу данных в автономную, подключитесь к SQL Server в средстве просмотра объектов. Выберите существующую базу данных или щелкните правой кнопкой, чтобы создать новую БД. Откройте страницы свойств (новой или существующей БД). На вкладке Options в поле со списком Containment type вместо None выберите Partial.

Рис. 1. В окне свойств базы данных можно изменить тип автономности

Чтобы превратить обычную БД в автономную, используйте T/SQL и инструкцию ALTER DATABASE. Чтобы успешно изменить тип базы данных, у инструкции ALTER DATABASE должна быть возможность получить монопольную блокировку всей базы данных. Поэтому надо запланировать небольшой перерыв в работе этой БД.
Избегайте ошибок сортировки во временных таблицах.

Новые частично автономные БД позволяют поддерживать различные параметры сортировки в одном экземпляре SQL Server. Не нужно беспокоиться о проблемах с сортировкой при соединении временных таблиц. Проверить это легко: надо взять экземпляр SQL Server и создать частично автономную БД с другими параметрами сортировки. После этого создайте физическую таблицу и временную таблицу в частично автономной БД. Загрузите данные в эти таблицы и попытайтесь выполнить их соединение.

В листинге 1 показан запрос, создающий базу данных с параметром сортировки Albanian_100_CI_AI_KS_WS, тогда как в экземпляре используется параметр SQL_Latin1_General_CP1_CI_AS. В новой БД создается таблица по имени dbo.Employee, в которую загружаются три строки. Также создается временная таблица по имени #emp, в которую вставляется одна строка. При соединении таблиц в конце запроса возвращается одна строка. Если бы база данных не была сконфигурирована как частично автономная, возникла бы ошибка.

Листинг. 1. Создание и использование автономной базы данных

use master
GO
CREATE DATABASE [Cont]
CONTAINMENT = PARTIAL
ONPRIMARY
(NAME = N'Cont', FILENAME =
  N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Cont.mdf',
  SIZE = 4096KB, FILEGROWTH= 1024KB)
LOGON
(NAME = N'Cont_log',FILENAME =
  N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Cont_log.ldf',
  SIZE = 1024KB, FILEGROWTH = 10%)
COLLATE Albanian_100_CI_AI_KS_WS
GO
Use Cont
GO
Create table Employee
(EmployeeId INTPRIMARYKEY,
LastName nvarchar(100),
FirstName nvarchar(100))
GO
Insert into Employee
(EmployeeId,LastName,FirstName)
values
(1,'last1','first1'), (2,'last2','first2'),(3,'last3','first3')
GO
Create table #emp
(LastName nvarchar(100))
GO
Insert into #emp
(LastName)
values
('last1')
GO
select*
from Employee
join #emp on Employee.LastName = #emp.LastName

Автономные пользователи

Автономные пользователи похожи на традиционные имена входа SQL Server за исключением того, что у них нет имен входа, соответствующих пользователям автономной БД. Автономные пользователи создаются средствами консоли SQL Server Management Studio или с применением инструкции CREATE USER T/SQL.

Чтобы сделать это с помощью SQL Server Management Studio, в обозревателе объектов (object explorer) подключитесь к экземпляру, на котором расположена автономная база данных. Перейдите к папке Databases/{Your Contained Database}/Security/Users. Щелкните правой кнопкой папку Users и в контекстном меню выберите New User. В списке User type выберите SQL user with password. Заполните поля для имени пользователя и пароля (рис. 2), а также укажите схему по умолчанию и принадлежность к нужным ролям в базе данных.

Рис. 2. Создание автономного пользователя SQL Server в консоли SQL Server Management Studio

После создания автономного пользователя можно просмотреть представление каталога sys.database_principals в автономной БД. Набор записей, возвращенный в результате запроса представления каталога, будет содержать новый столбец по имени authentication_type, в котором будет указано значение 2. Будет также столбец authentication_type_desc со значением DATABASE у пользователей, являющихся автономными.

Создайте пользователя автономной БД Create средствами T/SQL с использованием инструкции CREATE USER. SQL Server узнает, что создается автономный пользователь, а тот, которому соответствует имя входа на уроне экземпляра, по инструкции WITH PASSWORD:

CREATE USER MyContainedUser WITHPASSWORD = 'MyContainedUserPassword'
GO

Можно создавать не только автономных пользователей SQL Server, но и автономные имена входа Windows. Измените значение в поле со списком User Type (рис. 3) на Windows user и задайте имя пользователя, не указывая имя входа.

Рис. 3. Создание автономного имени входа Windows в консоли SQL Server Management Studio

Войдите в автономную БД с использованием автономного имени входа - точно так же, как это делается с применением обычного (не автономного) пользователя. Особенность автономного пользователя в том, что в строке подключения надо указывать имя базы данных. Если этого не сделать, SQL Serverбудет предполагать, что подключение происходить под обычным именем входа SQL Server. Попытка входа потерпит сбой, если на уровне экземпляра не окажется указанного пользователя с паролем.

В SQL Server Management Studio для этого надо щелкнуть кнопку Options внизу окна подключения. На вкладке Connection Properties имя автономной базы данных можно указать в поле со списком Connect to database (рис. 4).

Как автономный пользователь на вкладке Login вы не сможете увидеть список баз данных экземпляра SQL Server. У автономного пользователя нет права на доступ к главной базе данных без предварительной проверки подлинности в автономной базе данных, в которой хранится его имя входа и пароль. По этой причине нужно знать имя базы данных и указать ее в соответствующем поле вручную.