MS SQL 2011 (Denali) – Автономная база данных

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

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

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

Вот некоторые из ключевых проблем:

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

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

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

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

Таблицы, функции, процедуры, ограничения, схемы, типы, библиотеки, представления, логины, агенты задач, системные настройки, соединенные сервера (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.

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

 

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

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

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

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

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

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

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

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

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

Эти же действия можно совершить при помощи TSql

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

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

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

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

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

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

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

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

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

Затем создаем таблицу с данными.

 

Для полноты картины добавим хранимую процедуру.

 

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

Шаг 1

На первом шаге нужно будет создать нового пользователя на уровне сервера и пользователя для базы. Это можно сделать с помощью такого скрипта:

 

Шаг 2

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

 

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

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

Для определения пользователей принадлежащих серверу можно запустить такой скрипт:

 

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

Шаг 3

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

Другой способ для описанных действий заключается в написании скрипта

 

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

 

Процедура sp_migrate_user_to_contained нужна для миграции пользователей в автономную базу. Она конвертирует пользователей уровня сервера в пользователей автономной базы. После процедуры можно снова запустить скрипт определяющий зависимых пользователей.

 

И результат:

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

Шаг 4.

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

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

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

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

Интерфейс

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

Скрипт

Сделать бэкап базы можно с помощью нехитрого скрипта.

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

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

Интерфейс

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

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

 

Скрипт

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

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 на уровне экземпляра сервера. Эта настройка по умолчанию выключена.  Выполните скрипт ниже для устранения проблемы.

 

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

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

  • SQL Server Authentication
  • Windows Based Authentication

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

 

Дополнительно по автономным базам данных можно почитать здесь.

Hard’n’heavy!

 

Оставить комментарий