Перенос всех баз данных MS SQL Server на другую машину
Недавно возникла необходимость переноса всех БД (>50 на одном экземпляре SQL Server) из dev-окружения на другой экземпляр SQL Server, который располагался на другом железе. Хотелось минимизировать ручной труд и сделать всё как можно быстрее.
Disclaimer
Скрипты написаны для одной конкретной ситуации: это dev-окружение, все базы в простой модели восстановления, файлы данных и журналы транзакций лежат в одной куче.
Всё, что написано дальше относится только к этой ситуации, но вы можете без особых усилий допилить их под себя (свои условия).
В скриптах не используются новомодные STRING_AGG и прочие приятные штуки, поэтому работать всё должно начиная с SQL Server 2008 (или 2008 R2, не помню где появилось сжатие бэкапов). Для более старых версий нужно убрать WITH COMPRESSION из команды бэкапа, но тогда разницы по времени с копированием файлов может уже и не быть.
Это не инструкция — «как надо» делать такой перенос. Это демонстрация того, как можно использовать метаданные в dynamic SQL.
Конечно, самым быстрым способом было бы просто переподключить полку с дисками к новому серверу, но это был не наш вариант. Detach — копирование — Attach рассматривался, но не подошёл, поскольку канал был довольно узким и перенос БД в несжатом виде занял бы довольно большой промежуток времени.
В итоге, решили, что будем делать бэкап с компрессией на шару на новом сервере, а там уже восстанавливать. Железо и на старой, и на новой локации неплохое, бэкап жмётся неплохо, выигрыш по времени тоже неплохой.
Так был написан «генератор скриптов»:
На выходе получаем готовые команды для создания бэкапов в нужное место, перевода БД в offline, чтобы их пользователи не могли с ними работать на старом сервере и скрипты для восстановления полученных бэкапов на новом сервере (с автоматическим перемещением всех файлов данных и журналов транзакций в указанное место).
Проблема с этим такая — либо кто-то должен сидеть и по очереди выполнять все скрипты (бэкап-офлайн-восстановление), либо кто-то должен сначала запустить все бэкапы, потом отключить все базы, потом всё восстановить — действий меньше, но нужно сидеть и отслеживать.
Хотелось автоматизировать все эти операции. С одной стороны, всё просто — уже есть готовые команды, заворачивай в курсор и выполняй. И, в принципе, я так и сделал, добавил новый сервер как linked server на старом и запустил. На локальном сервере команда выполнялась через EXECUTE (@sql_text);, на linked server — EXECUTE (@sql_text) AT [linkedServerName].
Таким образом, последовательно выполнялись операции — бэкап локально, перевод локальной БД в офлайн, восстановление на Linked server. Всё завелось, ура, но мне показалось, что можно немного ускорить процесс, если бэкапы и восстановления выполнять независимо друг от друга.
Тогда придуманный курсор был разделён на две части — на старом сервере в курсоре каждая база бэкапится и переводится в офлайн, после чего второй сервер-таки должен понять, что появилось новое задание и выполнить восстановление БД. Для реализации этого механизма я использовал запись в таблицу на linked server и бесконечный цикл (мне было лень придумывать критерий остановки), который смотрит не появилось ли новых записей и пытается восстановить что-нибудь, если появились.
Решение
На старом сервере создаётся и заполняется глобальная временная таблица ##CommandList, в которой собираются все команды и там же можно будет отслеживать статус выполнения бэкапов. Таблица глобальная, чтобы в любой момент из другой сессии можно было посмотреть — что там сейчас происходит.
Посмотрим что там оказалось (SELECT * FROM ##CommandList):
Отлично, там собираются все команды для бэкапа/восстановления всех нужных БД.
На новом сервере была создана БД Maintenance и в ней таблица CommandList, которая будет содержать в себе информацию о восстановлении баз:
На старом сервере был настроен linked server, смотрящий на новый экземпляр SQL Server. Скрипты, которые приведены в этом посте, я писал дома и не заморачивался с новым экземпляром, использовал один и его же подключил как linked server сам к себе. Поэтому тут у меня и пути одинаковые и unc-path локальный.
Теперь можно объявлять курсор, в котором бэкапить базы, отключать их и писать на linked server команду для восстановления:
Каждое действие «логируется» на вкладке Messages в SSMS — там можно наблюдать за текущим действием. Если использовать WITH LOG в RAISERROR, в принципе, можно засунуть это всё в какой-нибудь job и потом смотреть логи.
Во время выполнения курсора можно обращаться к ##CommandList и смотреть в табличном виде что и как происходит.
На новом сервере, параллельно, крутился бесконечный цикл:
Всё что он делает — смотрит в таблицу CommandList, если там есть хотя бы одна необработанная запись — берёт имя БД и команду для восстановления и пытается выполнить с помощью EXEC (@sql_text);. Если записей нет, ждёт 30 секунд и пробует снова.
И курсор, и цикл обрабатывают каждую запись только один раз. Не получилось? Пишем сообщение об ошибке в таблицу и больше сюда не возвращаемся.
Про условие остановки — мне на самом деле было лень. Пока набирал текст, придумал минимум три решения — как вариант — добавление флагов «Готов к восстановлению \ Не готов к восстановлению \ Завершён», заполнение списка БД и команд сразу, при заполнении ##CommandList на старом сервере и обновление флага внутри курсора. Останавливаемся, когда не осталось «готовых к восстановлению» записей, так как нам сразу известен весь объём работ.
Выводы
А нет никаких выводов. Подумал, что кому-то может быть полезно/интересно посмотреть как использовать метаданные для формирования и выполнения dynamic sql. Приведённые в посте скрипты в том виде, как есть, мало пригодны для использования на проде, однако, их можно немного допилить под себя и использовать, например, для массовой настройки log shipping / database mirroring / availability groups.
При выполнении бэкапа на шару, у учётной записи, под которой запущен SQL Server, должны быть права для записи туда.
В посте не раскрыто создание Linked Server’a (мышкой в GUI интуитивно настраивается за пару минут) и перенос логинов на новый сервер. Те, кто сталкивался с переносом пользователей знают, что простое пересоздание sql-логинов не очень помогает, поскольку у них есть sid’ы, с которыми и связаны пользователи БД. Скрипты для генерации sql-логинов с текущими паролями и корректными sid’ами есть на msdn.
5 способов миграции вашей локальной базы данных SQL в Azure
В настоящее время существует три варианта развертывания базы данных SQL Server в облаке. Что ж, четыре, если вы посчитаете базы данных SQL Azure в эластичном пуле. Но SQL-база данных Azure в эластичном пуле — это, по сути, просто базы данных SQL Azure, сгруппированные в эластичный пул, поэтому вы можете максимизировать то, за что платите, и совместно использовать ресурсы.
Прямо сейчас вы можете поместить свою базу данных SQL Server на SQL Server в виртуальную машину Azure, в управляемый экземпляр или в базу данных SQL Azure. Теперь, есть разные соображения по выбору одного над другим, но это совершенно другой пост в блоге. В этом посте мы сосредоточимся на том, как вы можете переместить локальную базу данных SQL Server в Azure. Вот аккуратная таблица, сравнивающая возможные пути для трех:
SQL Server в Azure VM
Управляемый экземпляр
Azure SQL Database (Синглтон/эластичный пул)
Резервное копирование и восстановление
Зеркальное отображение/группы доступности
Почти каждая стратегия, используемая для миграции локального SQL Server, будет работать для SQL Server в виртуальной машине Azure. Это наиболее распространенный способ миграции в облако. Это просто, поскольку по сути это просто перемещение вашего старого сервера на новый сервер. Управляемый экземпляр также позволяет восстанавливать резервные копии собственного SQL Server из локального SQL Server, поэтому он имеет достаточно хорошую совместимость, а перемещение происходит более или менее плавно. Проблема возникает при перемещении существующих баз данных SQL Server в базу данных SQL Azure. Из-за соображений безопасности и различий в инфраструктуре вы не можете восстановить резервную копию SQL Server в Azure. Таким образом, многие люди часто ломают голову над тем, как перенести свои существующие базы данных в базу данных SQL Azure. Вот пять способов перенести вашу локальную базу данных SQL в Azure:
1. Помощник по миграции данных ( DMA) / Служба миграции данных ( DMS)
Это сервис / инструмент, специально созданный Azure для этой задачи. Этот инструмент также может выполнять оценки для определения четности функций и проблем совместимости базы данных при перемещении вашего SQL Server к любому из доступных вариантов развертывания SQL Server из локального SQL Server в базу данных SQL Azure. Он предоставит вам отчет с указанием всех этих проблем, а также возможные исправления для различных уровней совместимости баз данных, доступных для выбранной вами цели. Для миграции в DMA/DMS предусмотрены автономные и онлайн-опции. Автономный режим позволяет переносить только схему или схему и данные за один раз. Вероятно, вы будете использовать этот метод для тестирования / POC, для небольших баз данных или для тех приложений, которые могут позволить более длительное время простоя при перемещении данных. Опция онлайн позволяет поддерживать непрерывную синхронизацию локальной базы данных и базы данных SQL Azure после перемещения исходной схемы/данных. Таким образом, у вас будет достаточно времени, чтобы база данных SQL Azure могла быть захвачена, а затем решить, когда следует завершить работу и начать указывать свое приложение на базе данных SQL Azure. В документе Microsoft есть подробное пошаговое руководство по автономной и онлайн-миграции на базу данных SQL Azure.
2. Репликация
Вы можете добавить базу данных SQL Azure в качестве подписчика для репликации транзакций. Это может позволить вам перенести данные и объекты с локального SQL Server в базу данных SQL Azure. Поскольку вы не можете восстановить резервные копии, вы можете инициировать эту репликацию только из снимка. Как и в случае онлайн-миграции для DMS, вы можете начать синхронизацию задолго до запланированной миграции, а когда все будет синхронизировано, решите, когда вы сможете выполнить обрезку, и переназначить свои приложения в базу данных Azure SQL. Стоит отметить, что вы можете копировать таблицы только с первичным ключом. Поэтому, если у вас есть несколько таблиц без них, вам нужно проявить творческий подход или объединить их с другими методами для перемещения этих таблиц.
3. DACPAC
DACPAC — это отдельный файл, содержащий определения для вашей схемы базы данных — таблицы, представления, хранимые процедуры, функции и другие объекты базы данных. Это что-то вроде схемы, только резервная копия. Вы можете создать и восстановить базу данных SQL Azure, используя SSDT, SSMS или SQLpackage.exe. Так как это переносит только вашу схему, вам потребуется связать ее с другими методами, чтобы перенести ваши данные в базу данных SQL Azure. Скорее всего, вы используете это для POC/test или для новых приложений, которые не требуют каких-либо данных для переноса. Вы можете создать файл dacpac и развернуть его, используя SSDT, sqlpackage.exe или SSMS.
4. BACPAC
BACPAC похож на DACPAC, но инкапсулирует как схему, так и данные. Таким образом, вы можете перенести всю базу данных в базу данных SQL Azure. Этот единственный файл хранит информацию в формате JSON. Эта стратегия применима к небольшим базам данных. Вы можете создать файл BACPAC и импортировать его, используя SSDT, sqlpackage.exe или SSMS.
5. Генерация SQL-скриптов
Sql Server Management Studio имеет пользовательский интерфейс для создания сценариев SQL. Вы можете щелкнуть правой кнопкой мыши базу данных->Tasks -> Generate SQL Scripts. Если вы работали с SQL Server, вы, вероятно, использовали это раньше. То, что вы, возможно, раньше не замечали, — это когда вы нажимаете «Advanced», вы можете фактически контролировать то, что предоставляет вам скрипт. В частности, вы можете изменить тип Engined из автономной базы данных или базы данных SQL Azure. Таким образом, предоставленный скрипт совместим с ним. Вы также можете указать тип данных для сценария: выводить только схему или схему вместе с данными. Итак, чего бы это ни стоило, это ваша TSQL-версия DACPAC или BACPAC. Преимущество этого заключается в том, что вы можете добавлять/изменять полученный скрипт TSQL по мере необходимости. Подобно BACPAC, если вы пишете сценарии для данных, это применимо для небольших баз данных, но не для больших баз данных. Если вы просматриваете полученный скрипт, он фактически записывает ваши данные в виде отдельных команд вставки для каждой строки.
Azure предоставляет очень полезный инструмент для планирования миграции, Руководство по миграции базы данных Azure . Вы можете изменить источник и назначение, и он предоставит вам руководство по выполнению задач перед миграцией, миграцией и после миграции, о которых вам необходимо знать, а также ссылки на ресурсы и инструменты для каждого шага. Если вы планируете миграцию, обязательно изучите страницу заранее, чтобы упростить планирование.
Копировании базы данных SQL Server средствами пакетов службы Integration Services
В этой статье я рассмотрю один из вариантов копирования базы данных, расположенной на SQL Server — копирование через пакет служб Integration Services.
Данный механизм копирования является встроенной функцией Management Studio и предлагает 2 варианта работы: с отключением базы источника и без отключения. Разница между этими вариантами приведена ниже:
| Метод | Описание |
|---|---|
| С отключением базы данных | Работа с базой невозможна пока выполняется копирование. При этом копия базы является точной, а сама процедура копирования происходит быстро. |
| Без отключением базы данных | Возможна работа с базой в процессе копирования. Копия базы может содержать несогласованные данные, а процедура копирования происходит медленно. |
Ввиду наличия в обоих вариантах серьезных недостатков: «с отключением» — невозможна работа пользователей, а «без отключения» могут быть несогласованные данные, я считаю данный механизм копирования неподходящим для реальной жизни, тем не менее я продолжу его рассмотрение в общеобразовательных целях.
Выполнение копирования
В целом, настройка данного механизма не составит проблем. Для того чтобы выполнить копирование, необходимо зайти в Management Studio. Далее, щелкнув правой клавишей мыши на копируемой базе данных, в контекстном меню перейти «Задачи» (Tasks) → Скопировать базу данных (Copy database), после чего откроется окно помощника.
Management Studio: Копирование базы данных
Далее, помощник попросит указать сервер с которого необходимо копировать базу данных, а так же способ авторизации и реквизиты доступа на этом сервере; после чего помощник запросит аналогичную информацию о сервере-приемнике (куда будет скопирована база).
Выбор сервера-источника 
Выбор сервера-приемника
На следующем этапе мастер настройки предлагает вариант копирования: «метод отсоединения и присоединения» (use the detach and attach method) — т.е. копирование с отключением базы данных; или же использовать метод объектов SMO (use the SQL Management Object Method) — т.е. без отключения базы данных. Выше были описаны плюсы и минусы этих вариантов. Помимо этого, для варианта с отключением БД, необходимо выбрать вариант действий в случае возникновения ошибки (if a failure occurs…): если флаг установлен — подключение в БД будет восстановлено, в противном случае — нет.
Выбор варианта копирования
Далее необходимо выбрать копируемую базу и действие с ней (копирование или перемещение). Данные флаги уже будут расставлены, поскольку мастер настройки мы вызвали из контекстного меню.
Выбор баз данных
На следующем шаге помощник предлагает указать имя базы-приемника, а так же расположение файлов базы данных и журнала транзакций. Помимо этого, необходимо выбрать вариант действия когда база-приемник существует: прекратить передачу (stop the transfer) или удалить целевую базу (drop database)
Настройка базы приемника
Далее необходимо указать имя пакета и параметры ведения журнала, на этом этапе можно все оставить по умолчанию, после чего можно указать будет ли задание выполнено немедленно или указать расписание для задания.
Настройка пакета заданий 
Установка расписания пакета заданий
На последнем шаге приводится сводная информация о задании, затем, если все корректно, необходимо нажать «Готово» (Finish), после чего будет создано задание и, если было указано, немедленно выполнен созданный пакет.
Завершение настройки
В результате будет создано задание в дереве «Обозревателя объектов», которое можно выполнить в любой момент времени, вызвав контекстное меню правой клавишей мыши и выбрав «Запустить задание на шаге» (Start job at step)
Management Studio. Задание пакета IS
igorsmolin
Правильный перенос базы данных Microsoft SQL Server на другой сервер
Коллеги, добрый день. В этой статье мы с вами поговорим о миграции баз данных между серверами 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
Переходим в папку под названием MSSQL_1.1, кликаем правой кнопкой мыши на пустом месте и выбираем пункт Открыть Powershell здесь, либо Открыть командную строку здесь и Выполняем команду:
Теперь занимаемся своими делами, ждем когда завершится процесс, ведь в этом суть автоматизации. �� Напоминаю, что скорость выполнения сильно зависит от производительности вашей системы.
Подготовка тестовой базы данных
После того, как наш тестовый стенд готов, мы можем приступить к подготовке тестовой базы данных. Именно ее мы будем пробовать переносить между серверами.
В ходе автоматического развертывания виртуальных машин, на сервер 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 со следующими параметрами.
Похоже, что все хорошо �� Переходим к задаче переезда.
Моделирование задачи переезда
Процедура миграции подразумевает переезд уже подготовленной тестовой базы 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. Однако, при попытке подключения к серверу базы данных мы получим ошибку авторизации.
На этом этапе мы сталкиваемся с тем, что на новом сервере не существует указанного логина, поэтому мы не можем выполнить вход.
Исправим проблему. Для этого создадим логин с именем 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 с теми же параметрами.
Теперь мы можем подключиться к 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 на разных серверах.
Для лучшего восприятия разместил слева результаты с сервера LAB-SQL1, а справа с LAB-SQL2.
Обратите внимание, что учетные записи на сервере LAB-SQL1 имеют один и тот же SID. На втором сервере идентификаторы безопасности логина и пользователя отличаются. Учетная запись пользователя с сервера LAB-SQL1 переехала вместе с бекапом базы данных, но в резервной копии отсутствует логин (т.к эта информация не хранится в бекапе тестовой базы данных).
Создавая логин на втором сервере, SQL Server сформировал новый SID, который мы никак не можем привязать к пользователю.
Для правильного решения проблемы нам необходимо создать логин с явным указанием идентификатора безопасности с сервера LAB-SQL1. Для этого давайте удалим логин с сервера LAB-SQL2.
И выполним запрос для создания логина с явным указанием SID (который мы получили чуть ранее).
Повторно выполним запросы для проверки идентификаторов безопасности.
Выполним подключение к серверу LAB-SQL2 и проверим появившийся доступ к базе данных.
После выполнения этого действия мы смогли обеспечить идентичность идентификаторов безопасности. Таким образом мы решили возникшие проблемы и лишили себя дополнительных проблем при дальнейшей эксплуатации базы данных.
Итоги
Коллеги, мы с вами обсудили нюансы переезда баз данных между серверами. Несмотря на то, что такая процедура выполняется нечасто, она требует от специалиста по эксплуатации хорошего понимания процесса миграции. Очень надеюсь, что информация окажется полезной и вы увидите эту статью до того, как столкнулись с описанными в статье проблемами.