Коллеги, добрый день. В этой статье мы с вами поговорим о миграции баз данных между серверами SQL Server. Узнаем подробнее о том, какие подводные камни скрывает этот процесс, как выполнять эту процедуру корректно и без проблем для дальнейшей эксплуатации.
Общее описание статьи
Рано или поздно любой специалист обслуживающий SQL Server сталкивается с вопросом переезда базы данных. Как правило, причиной возникновения подобной задачи является изменение архитектуры проекта или необходимость обновления версий Windows Server и SQL Server.
На первый взгляд может показаться, что процедура не заслуживает отдельного внимания и не содержит никаких нюансов. Для выполнения задачи нам достаточно просто выполнить полную резервную копию базы на сервере откуда мы переезжаем и восстановить ее на новом сервере. Однако, при решении задачи подобным образом, мы обязательно столкнемся с дополнительными трудностями.
Для того, чтобы лучше понять проблематику, мы с вами попробуем смоделировать задачу переезда, развернем необходимую для этого инфраструктуру и пошагово пройдем по каждому из этапов.
Развертывание тестовой лаборатории
Перед тем как продолжить, очень рекомендую ознакомиться с необходимыми для развертывания компонентами. В предыдущих частях описаны действия по установке необходимого ПО на которых будет основана текущая и будущие подобные статьи.
Если у Вас уже есть свой тестовый стенд, можете пропустить этот раздел и перейти к следующему.
Для нашей тестовой лаборатории воспользуемся готовым сценарием. Давайте посмотрим на нашу будущую тестовую инфраструктуру.
Тестовая песочница будет состоять из трех узлов:
- lab-sql1.party.hard (Будут установлены: SQL Server, SQL Studio Management Server, AdventureWorks Test Base)
- lab-sql2.party.hard (Будут установлены: SQL Server)
- lab-witness.party.hard
Несмотря на то, что хостов будет развернуто три, нам будет достаточно первых двух. Предлагаемый сценарий автоматического развертывания подразумевает еще один дополнительный сервер для других случаев тестирования (например для тестирования технологий отказоустойчивости), но он (сценарий) подойдет и для нашей задачи.
Для развертывания тестовой лаборатории клонируем репозиторий проекта при помощи git.
git clone https://github.com/mdcowse/vagrant-sandboxie
Либо скачиваем zip архив проекта.
https://github.com/mdcowse/vagrant-sandboxie/archive/master.zip
Переходим в папку под названием MSSQL_1.1, кликаем правой кнопкой мыши на пустом месте и выбираем пункт Открыть Powershell здесь, либо Открыть командную строку здесь и Выполняем команду:
vagrant up
Теперь занимаемся своими делами, ждем когда завершится процесс, ведь в этом суть автоматизации. 🙂 Напоминаю, что скорость выполнения сильно зависит от производительности вашей системы.
Подготовка тестовой базы данных
После того, как наш тестовый стенд готов, мы можем приступить к подготовке тестовой базы данных. Именно ее мы будем пробовать переносить между серверами.
В ходе автоматического развертывания виртуальных машин, на сервер LAB-SQL1 уже была загружена база данных под названием AdventureWorks. Для тестов будем использовать именно ее.
Перейдем в SQL Server Management Studio на сервере LAB-SQL1 и развернем тестовую базу данных.
База данных находится в корне диска C.
Обязательно убедимся, что восстановление выполнилось успешно.
Следующим шагом мы должны позаботиться об учетных записях. Сначала добавим логин на сервере LAB-SQL1.
Для упрощения назовем его login, выберем тип SQL Server authentication и укажем самый безопасный в мире пароль Qwerty12345
Переместимся на вкладку User Mapping и выполним привязку логина к базе данных AdventureWorks2019 к пользователю user, а также наградим пользователя правами для чтения внутри этой базы.
Убедимся, что пользователь был успешно создан в тестовой базе данных.
В нашей статье мы используем логины типа SQL Server Authentication, поэтому нам необходимо дополнительно включить соответствующий функционал в самом сервере. Для этого перейдем в раздел Properties нашего сервера.
Перейдем на вкладку Security и выберем соответствующую настройку.
Чтобы изменения вступили в силу, потребуется перезапустить SQL Server.
Теперь мы имеем экземпляр SQL Server с рабочей базой данных. Чтобы убедиться в этом, мы можем выполнить подключение к базе и проверить, что запросы к ней действительно выполняются.
Для этого воспользуемся утилитой sqlcmd со следующими параметрами.
sqlcmd -S localhost -U login -P Qwerty12345 USE AdventureWorks2019 go SELECT * FROM Production.Location go
Похоже, что все хорошо 🙂 Переходим к задаче переезда.
Моделирование задачи переезда
Процедура миграции подразумевает переезд уже подготовленной тестовой базы AdventureWorks2019 с сервера LAB-SQL1 на LAB-SQL2. Сперва мы выполним резервную копию базы данных, ведь любой переезд начинается именно с этой процедуры.
Для этого выберем в меню Tasks выберем поле Back Up.
Назовем резервную копию AdventureWorksTransfer.bak и разместим ее в директории C:\tmp.
Теперь нам необходимо передать сформированную копию базы на сервер LAB-SQL2. Самый простой способ — просто передать файл по адресу \\lab-sql2\c$\tmp.
Теперь выполняем подключение к серверу LAB-SQL2 из SQL Server Management Studio.
И запускаем процедуру восстановления базы уже на новом сервере. Для этого выберем поле Restore Database.
Во вкладке General выберем файл базы данных и начнем восстановление.
Обязательно убедимся, что база появилась на сервере LAB-SQL2.
База данных развернута на сервере LAB-SQL2. Однако, при попытке подключения к серверу базы данных мы получим ошибку авторизации.
sqlcmd -S lab-sql2 -u login -P Qwerty12345
На этом этапе мы сталкиваемся с тем, что на новом сервере не существует указанного логина, поэтому мы не можем выполнить вход.
Исправим проблему. Для этого создадим логин с именем login точно также, как мы это делали на первом сервере.
Укажем имя логина, его тип SQL Server authentication и самый безопасный на свете пароль Qwerty12345.
При создании логина мы можем столкнуться с тем, что на новом (LAB-SQL2) сервере недоступен режим аутентификации SQL Server. В таком случае включаем его точно так же, как мы это делали на сервере LAB-SQL1 при подготовке тестовой базы.
В SQL Server Management Studio выбираем раздел Properties сервера LAB-SQL2.
Выбираем нужный тип аутентификации SQL Server.
Перезапускаем SQL Server для применения конфигурации.
Давайте повторно выполним подключение к SQL Server и посмотрим, что изменилось. Для этого воспользуемся утилитой sqlcmd с теми же параметрами.
sqlcmd -S lab-sql2 -u login -P Qwerty12345
USE AdventureWorks2019 go
Теперь мы можем подключиться к SQL Server, но у нас нет доступа к самой базе данных.
Возникшая проблема связана с тем, что между логином на сервере и пользователем в базе данных отсутствует связь. В нашем случае это две несвязанные сущности и нам необходимо их привязать друг к другу.
Попробуем добавить привязку между логином и пользователем в базе данных через созданный нами ранее логин на сервере LAB-SQL2, заодно добавим права этому логину на чтение в тестовой базе данных.
SQL Server Management Studio сообщит нам о невозможности выполнения операции по причине того, что такой пользователь уже существует в базе данных.
В свойствах пользователя в базе данных мы можем увидеть, что поле типа пользователя неактивно и мы не можем повлиять на ситуацию из этого меню.
Самым очевидным решением этой проблемы будет формирование нового пользователя и логина. Как только мы вновь создадим логин и пользователя, проблема будет решена, а вместе с ней, будет решена задача по переносу базы данных на другой сервер.
Уверены? 🙂
Подобный способ действительно может быть решением проблемы, например, для целей тестирования базы данных на другом сервере. Однако для боевой эксплуатации такой вариант имеет несколько неочевидных, но больших недостатков.
Во-первых в нашей статье мы рассматриваем только одного пользователя с достаточно скромным набором доступов. В реальности структура пользовательских прав может быть гораздо больше и сложнее. Связок между логинами и пользователями может быть также несколько. Все эти факторы сильно усложняют процедуру ручного создания логинов и пользователей. Подобное решение практически гарантирует человеческую ошибку.
Во-вторых если у вас планируются или уже используются схемы отказоустойчивости баз данных SQL Server, то создание новых учетных записей будет большой проблемой при переключении на резервные базы данных. Скорее всего вы столкнетесь с тем, что после сбоя основной базы данных, резервная не будет доступна для обращения к ней.
Давайте разбираться с тем, почему проблема с которой мы столкнулись вообще происходит. Ответ на этот вопрос является ключевым для понимания того, почему создавать нового пользователя для дальнейшей эксплуатации боевого экземпляра SQL Server это плохая мысль.
Архитектура SQL Server подразумевает многоуровневое использование идентификаторов безопасности (как на уровне базы — пользователь, так и на уровне сервера — логин). На каждом из этих уровней мы можем управлять правами этих сущностей. В момент создания этих сущностей, для каждой формируется уникальный (в рамках хоста) SID (идентификатор безопасности). Он состоит из строки содержащей несколько частей из букв и цифр (напр. S-1-5-21-1507001333-1204550764-1011284298-1003).
Выдавая какие-либо права в SQL Server, они назначаются учетной записи по SID. Если мы решим создать одну и ту же учетную запись с одинаковым именем на разных хостах, их идентификаторы безопасности будут отличаться. Более того, даже если мы решим удалить уже созданную учетную запись и создать ее снова на одном и том же хосте, то с точки зрения SQL Server, это будет совершенно другая учетная запись, она получит другой идентификатор безопасности.
Давайте посмотрим какие идентификаторы безопасности у созданных нами учетных записях на серверах. Для этого выполним запрос в SQL Server Management Studio на разных серверах.
SELECT @@SERVERNAME SELECT Name, SID FROM sys.Server_Principals WHERE Name = 'login' SELECT Name, SID FROM sys.Database_Principals WHERE Name = 'user'
Для лучшего восприятия разместил слева результаты с сервера LAB-SQL1, а справа с LAB-SQL2.
Обратите внимание, что учетные записи на сервере LAB-SQL1 имеют один и тот же SID. На втором сервере идентификаторы безопасности логина и пользователя отличаются. Учетная запись пользователя с сервера LAB-SQL1 переехала вместе с бекапом базы данных, но в резервной копии отсутствует логин (т.к эта информация не хранится в бекапе тестовой базы данных).
Создавая логин на втором сервере, SQL Server сформировал новый SID, который мы никак не можем привязать к пользователю.
Для правильного решения проблемы нам необходимо создать логин с явным указанием идентификатора безопасности с сервера LAB-SQL1. Для этого давайте удалим логин с сервера LAB-SQL2.
И выполним запрос для создания логина с явным указанием SID (который мы получили чуть ранее).
CREATE LOGIN [login] WITH PASSWORD = 'Qwerty12345' SID, = 0xB26362EEE253B049A3B3B4989128B4BD
Повторно выполним запросы для проверки идентификаторов безопасности.
SELECT @@SERVERNAME SELECT Name, SID FROM sys.Server_Principals WHERE Name = 'login' SELECT Name, SID FROM sys.Database_Principals WHERE Name = 'user'
Выполним подключение к серверу LAB-SQL2 и проверим появившийся доступ к базе данных.
После выполнения этого действия мы смогли обеспечить идентичность идентификаторов безопасности. Таким образом мы решили возникшие проблемы и лишили себя дополнительных проблем при дальнейшей эксплуатации базы данных.
Итоги
Коллеги, мы с вами обсудили нюансы переезда баз данных между серверами. Несмотря на то, что такая процедура выполняется нечасто, она требует от специалиста по эксплуатации хорошего понимания процесса миграции. Очень надеюсь, что информация окажется полезной и вы увидите эту статью до того, как столкнулись с описанными в статье проблемами.