Перенос базы данных ms sql на другой сервер linux
Обновлено: 08.07.2024
Недавно возникла необходимость переноса всех БД (>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 и бесконечный цикл (мне было лень придумывать критерий остановки), который смотрит не появилось ли новых записей и пытается восстановить что-нибудь, если появились.
Решение
Отлично, там собираются все команды для бэкапа/восстановления всех нужных БД.
На новом сервере была создана БД Maintenance и в ней таблица CommandList, которая будет содержать в себе информацию о восстановлении баз:
На старом сервере был настроен linked server, смотрящий на новый экземпляр SQL Server. Скрипты, которые приведены в этом посте, я писал дома и не заморачивался с новым экземпляром, использовал один и его же подключил как linked server сам к себе. Поэтому тут у меня и пути одинаковые и unc-path локальный.
Теперь можно объявлять курсор, в котором бэкапить базы, отключать их и писать на linked server команду для восстановления:
Каждое действие «логируется» на вкладке Messages в SSMS — там можно наблюдать за текущим действием. Если использовать WITH LOG в RAISERROR, в принципе, можно засунуть это всё в какой-нибудь job и потом смотреть логи.
На новом сервере, параллельно, крутился бесконечный цикл:
Всё что он делает — смотрит в таблицу CommandList, если там есть хотя бы одна необработанная запись — берёт имя БД и команду для восстановления и пытается выполнить с помощью EXEC (@sql_text);. Если записей нет, ждёт 30 секунд и пробует снова.
Выводы
А нет никаких выводов. Подумал, что кому-то может быть полезно/интересно посмотреть как использовать метаданные для формирования и выполнения dynamic sql. Приведённые в посте скрипты в том виде, как есть, мало пригодны для использования на проде, однако, их можно немного допилить под себя и использовать, например, для массовой настройки log shipping / database mirroring / availability groups.
При выполнении бэкапа на шару, у учётной записи, под которой запущен SQL Server, должны быть права для записи туда.
В посте не раскрыто создание Linked Server'a (мышкой в GUI интуитивно настраивается за пару минут) и перенос логинов на новый сервер. Те, кто сталкивался с переносом пользователей знают, что простое пересоздание sql-логинов не очень помогает, поскольку у них есть sid'ы, с которыми и связаны пользователи БД. Скрипты для генерации sql-логинов с текущими паролями и корректными sid'ами есть на msdn.
Правильный перенос базы данных 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
Несмотря на то, что хостов будет развернуто три, нам будет достаточно первых двух. Предлагаемый сценарий автоматического развертывания подразумевает еще один дополнительный сервер для других случаев тестирования (например для тестирования технологий отказоустойчивости), но он (сценарий) подойдет и для нашей задачи.
Переходим в папку под названием 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. Если мы решим создать одну и ту же учетную запись с одинаковым именем на разных хостах, их идентификаторы безопасности будут отличаться. Более того, даже если мы решим удалить уже созданную учетную запись и создать ее снова на одном и том же хосте, то с точки зрения 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 и проверим появившийся доступ к базе данных.
После выполнения этого действия мы смогли обеспечить идентичность идентификаторов безопасности. Таким образом мы решили возникшие проблемы и лишили себя дополнительных проблем при дальнейшей эксплуатации базы данных.
Итоги
Коллеги, мы с вами обсудили нюансы переезда баз данных между серверами. Несмотря на то, что такая процедура выполняется нечасто, она требует от специалиста по эксплуатации хорошего понимания процесса миграции. Очень надеюсь, что информация окажется полезной и вы увидите эту статью до того, как столкнулись с описанными в статье проблемами.
Вы можете перенести базы данных и данные на сервер SQL Server, работающий в Linux. Выбор способа переноса зависит от исходных данных и ваших условий. В следующих разделах приводятся рекомендации по различным сценариям переноса.
Перенос из SQL Server в Windows
Если нужно перенести базы данных с сервера SQL Server в Windows на сервер SQL Server на Linux, рекомендуется использовать резервное копирование и восстановление SQL Server.
- Создайте резервную копию базы данных на компьютере Windows.
- Перенесите файл резервной копии на конечный компьютер SQL Server на Linux.
- Восстановите резервную копию на компьютере Linux.
Руководство по переносу базы данных с помощью резервного копирования и восстановления см. в следующей статье:
Кроме того, можно экспортировать базу данных в файл BACPAC (сжатый файл, содержащий схему базы данных и данные). Если у вас есть файл BACPAC, его можно передать на компьютер Linux, а затем импортировать в SQL Server. Дополнительные сведения см. в следующих разделах:
Перенос с других серверов баз данных
В SQL Server на Linux можно переносить базы данных из других систем баз данных. К ним относятся Microsoft Access, DB2, MySQL, Oracle и Sybase. В этом случае используйте Помощник по миграции SQL Server (SSMA) для автоматизации переноса. Дополнительные сведения см. в статье Перенос баз данных в SQL Server на Linux с помощью SSMA.
Перенос структурированных данных
Существуют также способы импорта необработанных данных. У вас могут быть файлы со структурированными данными, экспортированные из других баз данных или источников данных. В этом случае вы можете выполнить массовую вставку данных с помощью программы bcp. Кроме того, вы можете запустить службы SQL Server Integration Services в Windows, чтобы импортировать данные в базу данных SQL Server на Linux. Службы SQL Server Integration Services позволяют выполнять более сложные преобразования данных во время импорта.
Первоначально действия производятся на SQL-сервере C которого мы переносим данные.
На базе данных, которую необходимо перенести, нажмите правую кнопку маши и выберите пункт "Задачи" и в выпадающем пункте меню "Создать резервную копию. ":
Далее ничего сверхестественного - все оставляем по умолчанию примерно так:
Можем заглянуть на вторую закладку/страницу диалогового окна:
Нажимаем кнопку "ОК" и ждем.
Резервная копия сделана:
Любым удобным для нас способом переносим *.bak файл резервной копии нашей базы данных на другой SQL-сервер.
Дальнейшие действия производятся на SQL-сервере НА который мы переносим данные.
В среде MS SQL Server Management Studio нажимаем правую кнопку мыши на пункте "Базы данных" и далее выбираем мункт "Восстановить базу данных. ":
Указываем из чего будем восстанавливать базу данных:
Далее необходимо указать устройство с которого будет производиться восстановление базы данных. Нажимаем кнопку с тремя точками и, в открывшемся окне нажимаем кнопку "Добавить":
Указываем путь к *.bak-файлу резервной копии нашей базы данных.
Здесь просто нажимаем кнопку "ОК":
Поставьте галочку в поле "Восстановить".
Из выпадающего списка выберите базу данных в которую будет производиться восстановление данных из резервной *.bak-копии.
Все настройки сделаны - нажимаем "ОК":
Пойдет процесс восстановления данных в базу:
Удачное восстановление выглядит так:
Обязательно нужно проверить владельца базы данных после восстановления. (Нажмите правую кнопку мыши на имени базы и далее выберите пункт "Свойства"):
Переходим на закладку/страницу "Файлы" и смотрим на поле "Владелец". Если оно пустое, то необходимо указать "Владельца" базы.
В конце поля "Владелец" нажмите кнопку с тремя точками и, в появившемся окне нажмите кнопку "Обзор. ":
В открывшемся окне установите "галочку" напротив нужного владельца базы и нажмите кнопку "OK":
Здесь тоже просто нажимаем "OK":
Указав "Владельца" базы нижимаем "OK" чтобы сохранить изменения. Перенос базы завершён!
Читайте также: