Федор Самородов,
преподаватель учебного центра «Специалист» при МГТУ им. Н.Э. Баумана»
Приходилось ли вам переносить базу данных с одного SQL-сервера на другой? Это примерно, как перемещать файлы между дисками. Кажется, действие само по себе простое, но даже такая незатейливая сущность, как файл, может переместиться на новое место не полностью. Не забыли проверить, переместились ли вместе с файлом разрешения, выданные на него? А как насчёт ярлыков и ссылок? База данных – гораздо более сложный объект, чем файл, и проблем, вызванных наличием внешних зависимостей, при переносе будет гораздо больше.
База данных в SQL Server живёт не сама по себе, а в контексте сервера, постоянно пользуясь разнообразными услугами, которые предоставляет ей сервер. Аутентификацией, которая работает на для всего сервера, назначенными заданиями, которые тоже работают на уровне сервера и другими функциями и настройками, которые реализованы именно для всего сервера. То есть база данных от своего сервера серьёзно зависит. И чтобы перенести базу на другой сервер (или, как вариант, выполнить развёртывание приложения, использующего базу данных), вам придётся восстановить все эти зависимости.
Вот было бы здорово, если бы база данных могла быть сама себе сервером и существовать совершенно независимо от остальных баз и от настроек всего сервера. Тогда, развёртывая сложное приложение на новом сервере, мы могли бы обойтись без помощи местного администратора и сохранить все нужные нашей базе данных права и настройки.
История проблемы
Одна из архитектурных особенностей SQL Server – серьёзная зависимость базы данных от настроек сервера – хорошо известна всем администраторам. С одной стороны, такая зависимость бывает полезна, но чаще администраторы говорят об этом, как об источнике проблем и дополнительных усложнений в административных сценариях.
К примеру, задания планировщика хранятся и управляются в системной базе MSDB. А среди этих заданий могут находиться не только административные сценарии, но и часть бизнес-логики, необходимой для корректной работы приложения.
Или – другой пример – база TempDB. Она одна на весь сервер, то есть все базы данных используют её для временных вычислений. Это тоже проблема – если одна база начинает активно использовать TempDB, то от этого может упасть производительность и всех остальных баз тоже. Попробуйте, к примеру, включить шифрование в какой-то одной базе данных. Операции над временными объектами (курсорами, временными таблицами, версиями строк), которые как раз производятся в TempDB, во всех остальных базах тоже замедлятся.
А ещё есть настройки, которые устанавливаются только на уровне всего сервера, и их невозможно переопределить для какой-то одной базы. Как быть, если для одного из приложений именно эти настройки критичны? Покупать и устанавливать ради одной базы данных новый экземпляр SQL-сервера?
Получается, что нельзя просто взять и перенести базу с одного сервера на другой. Например, через создание-восстановление резервной копии, или через механизм отсоединения-подключения (detach-attach). Множество параметров и механизмов при перемещении базы нужно согласовать между самой базой и новым сервером, на который она переезжает.
Есть хорошая новость: Микрософт озадачился стратегической целью – дать возможность администраторам и разработчикам создавать полностью автономные базы данных. Такие базы, для которых совершенно ничего не нужно дополнительно настраивать и развёртывать на уровне сервера. Подняли базу данных на новом сервере – и всё. Приложение сразу же может эту базу использовать.
Это значит, что автономная база данных должна внутри себя содержать абсолютно все настройки, объекты и программные модули, необходимые для её работы. У неё не должно быть никаких зависимостей от сервера.
Но в 2012-й версии SQL-сервера полной автономности добиться не удалось. Поэтому новинку, которую Микрософт называет Contained Databases, правильнее было бы назвать «частично автономными» базами данных. Часть объектов теперь действительно можно отвязать от сервера и переместить внутрь базы, но много серверных зависимостей всё ещё осталось. С этим придётся смириться до выхода следующих версий SQL-сервера.
Аутентификация
В плане зависимостей базы от сервера для администратора самый главный кошмар – это, конечно, аутентификация. Не все администраторы сталкиваются с проблемами зависимостей от TempDB, настроек сервера, заданий планировщика. Возможно, для вашей базы данных это всё не критично. А вот имена входа (logins) и пользователи (users) вас наверняка касаются. Это самая «популярная» проблема при переносе базы с сервера на сервер. И именно с ней в первую очередь сталкиваются администраторы, когда пытаются «отвязать» базу от сервера.
А проблема заключается в том, что в SQL-сервере аутентификация (проверка пароля) пользователя выполняется на «верхнем уровне» - на уровне всего сервера. То есть, когда пользователь подключается к базе данных, он, на самом деле подключается к SQL-серверу и именно SQL-сервер проверяет пароль пользователя и принимает решение об успешной аутентификации. Только после этого пользователь может войти в контекст базы данных и начать работу.
В SQL-сервере процесс аутентификации организован таким образом: на сервере есть объект «login» (имя входа) – этот объект содержит имя, пароль и всё остальное, что нужно для аутентификации. А внутри базы тоже есть объект, который представляет пользователя – «user». Оба объекта – login и user представляют одного и того же человека, но объект login находится на сервере и с этого сервера он никуда не денется, а объект user хранится внутри базы и вместе с базой путешествует между серверами: включается в резервную копию или отсоединяется вместе с файлами базы. Но между этими двумя объектами администратор обязательно должен поддерживать связь, иначе пользователь на сервере успешно аутентифицируется, но в базу войти не сможет.
Это проблема. Значит, необходимо не только базу перенести на другой сервер, но вслед за ней корректно перенести имена входа, а они содержат пароли, а пароли – это чувствительная информация, которая требует особого обращения.
Поэтому администратору часто хочется эту связь между уровнем сервера и уровнем базы данных устранить. Например, так чтобы база сама содержала всю необходимую информацию для аутентификации своих пользователей. Тогда она сможет сразу начать работать при восстановлении на новом SQL-сервере.
Как раз такую возможность нам и даёт автономный режим работы базы. В этом случае на сервере не нужно создавать никаких объектов. Пользователь сразу подключается напрямую к базе, минуя серверный уровень, и база сама проверяет его пароль и сама принимает решение аутентифицировать его или нет.
Автономный режим работы в 2012-й версии распространяется на:
1. Аутентификацию
2. Некоторые настройки, которые раньше можно было задать только для всего сервера
Классический режим работы базы данных
При традиционном для SQL-сервера режиме работы для решения вопроса с аутентификацией мы должны сделать два действия:
1. Завести на сервере объект «имя входа» (login).
2. Создать в базе объект «пользователь» (user), причём обязательно связанный с серверным именем входа.
Хорошо, если мы можем использовать Windows-режим аутентификации – в этом случае проблем будет чуть меньше. Однако, это часто бывает невозможно, поэтому приходится заводить учётную запись в SQL-режиме. В этом случае важно понимать, что логин и пароль будут храниться не в базе данных, а в самом SQL-сервере.
И, чтобы пользователь мог не только подключиться к серверу, но и войти в базу данных, нужно связать его с пользователем внутри базы. Обычно это удобнее сделать сразу при заведении имени входа на закладке «Сопоставление пользователей» (User mapping).
Теперь попробуем перенести эту базу на другой сервер. К сожалению, при таком переносе связь между login’ом и user’ом придётся разорвать. Восстановив на другом сервере эту базу из резервной копии (либо присоединив файлы), мы обнаружим, что наш пользователь никуда не делся, но на новом сервере для него нет соответствующего имени входа. А если это имя входа создать заново, то оно не привяжется к нашему пользователю. Придётся восстанавливать привязку вручную.
Но сделать это будет не просто. В графическом интерфейсе нет возможности восстановить галочку, обозначающую связь между двумя этими объектами. Создавать в базе нового пользователя? А как же разрешения и настройки, которые были привязаны к старому пользователю?
Проблема эта не нова, поэтому, разумеется, существуют способы её решения. Связь между именем входа и пользователем устроена не очевидным образом. Эти объекты связаны по специальным идентификаторам – SID (Security ID). Эти идентификаторы через интерфейс не видны и, чтобы их узнать, нужно заглянуть в системные таблицы.
SELECT Principal_ID, SID, Name
FROM Sys.Server_Principals
WHERE Name = 'Самородов Фёдор Анатольевич'
SELECT Principal_ID, SID, Name
FROM Sys.Database_Principals
WHERE Name = 'Самородов Фёдор Анатольевич'
Так на эти идентификаторы можно посмотреть, но не изменить их – системные таблицы предназначены только для чтения. А чтобы заново связать имя входа с пользователем, SID’ы придётся поменять. Ведь на новом сервере SID пользователя не изменился, он перемещается вместе с базой, а имя входа создалось с каким-то новым SID’ом, отличным от SID’а пользователя. И тут у нас есть два способа.
Начинающие администраторы обычно поступают так. Чтобы восстановить синхронизацию SID’ов пользователей и имён входа, нужно либо выполнить специально для этого предназначенную процедуру sp_Change_Users_Logins, либо воспользоваться более удобной инструкцией ALTER USER. Такой способ действительно решает проблему, SID’ы у имени входа и у пользователя снова становятся идентичными. Но у этого подхода есть недостаток – побочные деструктивные действия в базе. Инструкция ALTER USER и процедура sp_Change_Users_Logins меняют SID пользователя, а не имени входа. То есть, как только вы это сделали, с этого момента базы на первом и втором серверах различаются. Это значит, что вы не сможете перенести базу обратно на первый сервер, не произведя повторно ручную замену SID’ов. Кроме того, не сработает механизм подхвата (failover) при зеркалировании баз данных.
Опытные администраторы поступают иначе. Они меняют SID не у пользователя, а у серверного имени входа. Тогда базы остаются идентичными, и вы можете легко вернуть базу на первый сервер. Задать желаемый SID можно при создании имени входа. Разумеется, предварительно нужно заглянуть в системные таблицы на первом сервере, чтобы узнать оригинальное значение идентификатора безопасности.
CREATE LOGIN [Самородов Фёдор Анатольевич]
WITH SID = 0x4B201934F8C24613BB9AB980C5F5764D,
PASSWORD = 'Pa$$w0rd'
В общем, проблема решаема, но с большими трудозатратами. Хорошо, если у нас большинство пользователей заходят в SQL-сервер с учётными данными Windows. В этом случае используется SID windows-пользователя и он не изменяется. А если у нас много пользователей с SQL-логинами, придётся либо писать сценарий для автоматизации переноса имён входа (не забудьте про пароли), либо пользоваться какими-то другими инструментами.
К счастью, в 2012-й версии можно объявить базу данных частично автономной.
Автономные базы данных в SQL Server 2012
Автономные базы позволяют вам вообще отказаться от имён входа на сервере. Теперь пользователь имеет возможность подключаться к базе напрямую, минуя проверки на уровне сервера. Пользователь сразу заходит в базу данных, и там хранится всё, что нужно для аутентификации, включая пароль. Таким образом, база данных сама проверяет подлинность пользователя, и при переносе базы на другой сервер никаких дополнительных серверных объектов, связанных с этим пользователем создавать не нужно.
Прежде чем мы сможем воспользоваться преимуществами автономных баз, администратор SQL-сервера должен дать согласие на их использование. Это важный шаг, так как, выражая такое согласие, администратор должен понимать, что это в некотором роде ослабление безопасности. Зона внимания администратора теперь размывается и он уже не может контролировать аутентификацию, заглядывая только в серверный контейнер «Security/Logins». Он обязан понимать, что кроме серверных имён входа есть и другие возможности подключения и, чтобы их все проконтролировать, придётся заглянуть в контейнер «Security/Users» в каждой автономной базе данных. Поэтому без ведома администратора всего SQL-сервера мы не можем перевести свою базу в автономный режим.
Если администратора это не пугает, то он может разрешить использование автономных баз данных в настройках SQL-сервера:
Теперь в свойствах базы данных можно перевести её в автономный режим.
После переведения базы данных в автономный режим администратору открываются два инструмента.
Первый – группа заблокированных ранее настроек «Включение» (Containment). Раньше эти параметры мог настраивать только администратор всего сервера. Это могло создать трудности при развёртывании проекта, так как на уровне базы переопределить их было невозможно. Теперь для каждой автономной базы можно индивидуально определять, к примеру, поведение вложенных триггеров или границу отсечения столетия для дат.
Второй инструмент – это имена входа. Теперь их тоже можно отвязать от сервера. В автономной базе при заведении пользователя появился режим «Пользователь SQL с паролем». Обратите внимание, что этот пользователь вместе с паролем заводится и в дальнейшем хранится не на уровне сервера, а внутри базы данных. И на сервере не появляется никакого связанного с ним имени входа (login). Это действительно автономный пользователь!
Теперь если эту базу перенести на другой сервер (на новом сервере должны быть разрешены автономные базы данных), ничего с SID’ами и Login’ами делать не нужно. Автономный пользователь сразу может подключиться и приступить к работе в своей базе данных.
Однако при подключении теперь нужно обязательно указать имя базы – либо в интерфейсе, либо в строке соединения. Иначе сервер будет искать указанного пользователя среди серверных имён входа.
Миграция базы в автономный режим
Если вас заинтересовали преимущества автономных баз данных, то можно осуществить несложную миграцию для существующих пользователей, связанных с серверными именами входа. Для этого нужно выполнить четыре шага.
Во-первых, необходимо разрешить автономию баз данных. Это настройка для всего сервера, и установить такое разрешение можно либо через интерфейс студии, либо при помощи хранимой процедуры sp_Configure.
Далее необходимо изменить режим работы нашей базы на полуавтономный. Либо в окне управления свойствами базы, либо при помощи команды ALTER DATABASE.
ALTER DATABASE ИмяБазыДанных
SET CONTAINMENT = PARTIAL
Теперь можно приступить к миграции пользователей. Самый простой вариант – использовать предназначенную специально для этого процедуру sp_Migrate_User_to_Contained. За один вызов она переводит в автономный режим только одного пользователя, так что в реальных ситуациях для циклического вызова этой процедуры придётся использовать курсор, перебирающий список пользователей.
После переноса пользователей на всякий случай следует проверить, всех ли пользователей удалось перенести. Специально для такой проверки имеется системное представление sys.DM_DB_Uncontained_Entities. Сквозь это представление видны все зависящие от сервера объекты (не только пользователи). Если в списке нет ни одного пользователя, значит миграция удалась.
Преимущества и недостатки автономных баз данных
Автономный режим работы базы – это лишь опция. И, прежде, чем её включать и начинать миграцию пользователей, имеет смысл оценить преимущества и недостатки автономии по сравнению с централизацией.
Преимущества автономии очевидны. По большому счёту их два:
1. При переносе базы или развёртывании проекта нет необходимости вслед за базой копировать и синхронизировать серверные объекты.
2. Не нужно согласовывать с администратором сервера настройки работы базы данных.
Но у автономных баз данных есть и серьёзный минус. Автономия – это ослабление безопасности. Администратор почувствует это в двух местах:
1. У администратора теперь нет единого списка всех субъектов, которые могут подключиться к серверу. Чтобы увидеть полную картину, администратор вынужден не только проверить список серверных имён входа, но и заглянуть в каждую автономную базу и проверить список её пользователей. Такое положение дел размывает фокус внимания администратора и увеличивает риск срабатывания человеческого фактора. Администратор, серьёзно относящийся к обеспечению безопасности, должен как следует подумать, прежде чем разрешить автономию баз данных.
2. Парольные хэши теперь хранятся в самой базе и, следовательно, вместе с базой попадают в резервную копию. То есть, если злоумышленник получит доступ к MDF-файлу базы или её резервную копию, то хэш пароля будет скомпрометирован.
Так что используйте автономные базы данных только в том случае, если готовы взять на себя ответственность за действия пользователей в этой базе и при этом уверены в надёжности хранения файлов базы и резервных копий.
