Перенос базы данных на хостинг SQL Server

Лучший способ перенести базу SQL Server к новому хостеру - нативный backup и восстановление: BACKUP DATABASE ... WITH COPY_ONLY, CHECKSUM на старом сервере, копирование .bak, RESTORE DATABASE ... WITH MOVE на новом. Это точно, быстро и сохраняет всё, что внутри базы, - схему, данные, пользователей, права, статистику. Работает это только вверх, с той же или более старой версии SQL Server, и только если база помещается в целевую редакцию. Когда этот путь не подходит - переход на более старую версию, переезд из Azure SQL Database или с MySQL либо PostgreSQL, - альтернативы такие: BACPAC, сгенерированные скрипты или преобразование схемы с последующей загрузкой данных. Это руководство объясняет, как выбрать, какие проверки выполнить до начала, разбирает каждый способ по очереди и перечисляет, что нужно привести в порядок после любой миграции.

Выбор способа#

Решение в основном определяют два вопроса. Источник - SQL Server той же или более старой версии, чем цель? Тогда backup и восстановление. Он новее или это Azure SQL Database, которая вообще не умеет создавать файл .bak? Тогда BACPAC. Сгенерированные скрипты нужны для небольших баз, когда вы хотите прочитать или отредактировать то, что переносится, а инструменты преобразования - чтобы уйти с другого движка.

Проверки до того, как что-то переносить#

Пять минут запросов на источнике избавляют от неудачного восстановления в полночь.

Версия. Выполните SELECT @@VERSION; на источнике. Backup восстанавливается только на ту же или более новую основную версию: 2022 - это версия 16, 2019 - 15, 2017 - 14, 2016 - 13. SQL Server 2022 восстанавливает backup начиная с SQL Server 2008; базе, которая всё ещё на 2005 или старше, нужна промежуточная остановка на версии, которая её примет.

Размер. Если цель - Express, каждая база ограничена 10 ГБ файлов данных (журнал не считается). Проверяйте, сколько реально занято, а не выделенный размер файла:

SELECT name, type_desc,       size / 128.0                              AS allocated_mb,       FILEPROPERTY(name, 'SpaceUsed') / 128.0   AS used_mbFROM sys.database_files;

База с 14 ГБ выделенного места и 6 ГБ занятого нормально восстановится на Express, только если файлы данных сначала сжать до размера меньше 10 ГБ, потому что восстановление пересоздаёт файлы в исходном размере. Сожмите файл данных на источнике (или на восстановленной копии) перед финальным backup, затем перестройте индексы, и учтите, что сжатие сильно фрагментирует индексы. Если вы действительно используете больше 10 ГБ, сначала заархивируйте старые данные или выберите редакцию без этого потолка - где проходит эта граница, разбирает статья хостинг SQL Server Express.

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

SELECT feature_name FROM sys.dm_db_persisted_sku_features;

Начиная с SQL Server 2016 Service Pack 1 большинство возможностей программирования - секционирование, columnstore, сжатие данных, in-memory OLTP - доступны во всех редакциях, так что на современном источнике запрос обычно ничего не возвращает. Если он показывает Transparent Data Encryption, такая база на Express не восстановится; сначала расшифруйте её на источнике.

Зависимости вне базы. Составьте список того, что живёт на старом сервере, а не в базе: задания SQL Server Agent, связанные серверы, логины, профили Database Mail, триггеры уровня сервера и любой код, обращающийся к другим базам по трёхчастному имени (otherdb.dbo.Table). Ничто из этого не переезжает вместе с backup. Заданиям Agent нужно особое внимание, если цель - Express, где Agent нет: каждое задание становится вызовом sqlcmd по расписанию где-то в другом месте, как показывает статья sqlcmd и bcp.

Способ 1: backup и восстановление#

На источнике сделайте backup только для копирования, чтобы не нарушить существующую цепочку backup:

BACKUP DATABASE [shop]TO DISK = N'D:\Backups\shop-migrate.bak'WITH COPY_ONLY, CHECKSUM, INIT, STATS = 10;RESTORE VERIFYONLY FROM DISK = N'D:\Backups\shop-migrate.bak' WITH CHECKSUM;

Скопируйте файл на новый сервер, в каталог, который может читать процесс SQL Server. RESTORE разрешает путь на сервере, а не на вашей рабочей машине, так что загрузка файла - отдельный шаг: SFTP, файловый менеджер хостера или то, что хостер предоставляет. В RE:NODE у каждого сервера есть SFTP и файловый менеджер; если не уверены, какую папку может читать движок, спросите поддержку, прежде чем загружать большой файл.

Затем прочитайте логические имена файлов и восстановите базу, перенеся каждый файл в каталог данных цели. В backup с Windows-источника зашиты пути Windows, и на Linux-цели их все нужно перенести:

RESTORE FILELISTONLY FROM DISK = N'/var/opt/mssql/data/shop-migrate.bak';RESTORE DATABASE [shop]FROM DISK = N'/var/opt/mssql/data/shop-migrate.bak'WITH MOVE N'shop'     TO N'/var/opt/mssql/data/shop.mdf',     MOVE N'shop_log' TO N'/var/opt/mssql/data/shop_log.ldf',     RECOVERY, CHECKSUM, STATS = 10;

Если вы не знаете нужный каталог, его покажет SELECT SERVERPROPERTY('InstanceDefaultDataPath');. После восстановления файл backup можно удалить с сервера - на тарифе с ограниченным диском .bak на 9 ГБ рядом с базой на 9 ГБ - самый быстрый способ этот диск заполнить. Параметры и ошибки восстановления подробнее разбирает статья backup и восстановление SQL Server.

На хостинговом сервере для вас уже может быть создана база. Можно восстановить поверх неё с REPLACE или восстановить под исходным именем и направить всё на неё. Если у вашего логина sa базой по умолчанию стоит заранее созданная база, а вы восстанавливаете под другим именем, смените базу по умолчанию, чтобы инструменты открывали нужную:

ALTER LOGIN [sa] WITH DEFAULT_DATABASE = [shop];

Способ 2: BACPAC через SqlPackage#

BACPAC - это zip-файл, содержащий схему базы в виде модели и данные каждой таблицы в формате массового копирования. Он создаётся подключением к источнику как клиент, поэтому не требует доступа к файлам ни одного из серверов, работает с Azure SQL Database и может перенести базу на более старую версию, если она не использует возможностей, которых в старой версии нет.

$ sqlpackage /Action:Export \    /SourceConnectionString:"Server=old.example.net,1433;Database=shop;User Id=sa;Password=...;TrustServerCertificate=True" \    /TargetFile:shop.bacpac$ sqlpackage /Action:Import \    /SourceFile:shop.bacpac \    /TargetConnectionString:"Server=db.example.net,14330;Database=shop;User Id=sa;Password=...;TrustServerCertificate=True"

Импорт создаёт базу или заполняет существующую, если она полностью пуста. В SSMS те же операции находятся в меню Tasks базы как Export Data-tier Application и в узле Databases как Import Data-tier Application.

Компромиссы вполне реальны:

• Согласованность. Экспорт читает таблицы одну за другой без единого снимка. Если во время экспорта идут записи, BACPAC может содержать заказ без его строк. Остановите приложение или экспортируйте из восстановленной копии backup.
• Скорость. Это в несколько раз медленнее нативного backup и восстановления, а импорт перестраивает каждый индекс. Для нескольких гигабайт нормально, для десятков - утомительно.
• Проверка. Экспорт сначала проверяет схему и отказывается работать с базами, которые не может представить, - чаще всего из-за ссылок на другие базы или объектов, которые больше не компилируются. Ошибка называет объекты; исправьте или удалите их и запустите заново.
• Что не переносится. Статистика, данные Query Store, журнал транзакций и история backup не переезжают. Логины тоже, а вот автономные пользователи и пользователи базы - да.

SqlPackage подключается с теми же умолчаниями шифрования, что и другие современные драйверы Microsoft, поэтому для сервера с самоподписанным сертификатом нужен TrustServerCertificate=True в строке подключения, как выше. Ключевые слова шифрования разбирает статья строки подключения SQL Server.

Способ 3: сгенерированные скрипты и bcp#

Небольшую базу SSMS может целиком выгрузить в виде T-SQL: щёлкните правой кнопкой по базе, Tasks, Generate Scripts. Выберите объекты, затем в Advanced установите «Types of data to script» в «Schema and data», а «Script for Server Version» - в версию цели. Результат - один файл .sql с выражениями CREATE, за которыми следуют INSERT.

Он читаемый, редактируемый и не зависит от версии, поэтому это правильный инструмент для базы в несколько сотен мегабайт, которую вы хотите привести в порядок по дороге. И неправильный для всего крупного: скрипт с миллионами однострочных INSERT выполняется медленно, а SSMS с трудом его даже открывает. Большие скрипты запускайте через sqlcmd -i, а не в окне запросов.

Для баз побольше сочетайте оба подхода: выгрузите в скрипт только схему, выполните её на цели, затем перенесите данные по таблицам через bcp в нативном формате - это быстро и точно. Загружайте родительские таблицы раньше дочерних или отключите внешние ключи на время загрузки и снова включите их WITH CHECK, чтобы они опять стали доверенными.

Переезд с MySQL или PostgreSQL#

Переезд с другого движка - это преобразование, а не копирование. Для MySQL, Oracle, Access, Db2 и SAP ASE бесплатный SQL Server Migration Assistant (SSMA) от Microsoft преобразует схему, помечает то, что преобразовать не может, и копирует данные. Для PostgreSQL SSMA нет; там схему преобразуют вручную или через миграции вашего фреймворка, а затем загружают данные через CSV и bcp или небольшим скриптом, который читает из одного соединения и массово копирует в другое.

Сопоставление типов в основном механическое:

Больше всего внимания требуют текстовые типы, потому что MySQL и PostgreSQL целиком работают в UTF-8, а varchar в SQL Server - нет, если столбец не использует collation UTF-8. Выбор объясняет статья collation и Unicode в SQL Server. Сам SQL тоже придётся переделать: LIMIT превращается в TOP или OFFSET ... FETCH, идентификаторы в обратных апострофах и двойных кавычках - в квадратные скобки, RETURNING - в OUTPUT, а upsert - в MERGE или обновление с последующей вставкой. Болезненные различия собраны в статье основы T-SQL для разработчиков приложений. Если приложение использует ORM, сменить провайдер и сгенерировать схему из миграций обычно быстрее, чем преобразовывать DDL вручную.

Переключение с минимальным простоем#

Для небольшой базы самое простое переключение - честное: переведите приложение в режим обслуживания, сделайте финальный backup, восстановите, смените строку подключения, верните приложение. Для базы в 5 ГБ на приличных каналах это окно в несколько минут.

Если это слишком долго, используйте полный backup плюс разностный. Восстановите полный backup заранее, не выводя его в рабочее состояние, чтобы он ждал продолжения:

-- Days before: restore the full backup, leave it waitingRESTORE DATABASE [shop] FROM DISK = N'/var/opt/mssql/data/shop-full.bak'WITH MOVE N'shop' TO N'/var/opt/mssql/data/shop.mdf',     MOVE N'shop_log' TO N'/var/opt/mssql/data/shop_log.ldf',     NORECOVERY;-- At cutover: stop writes, take a differential on the source, thenRESTORE DATABASE [shop] FROM DISK = N'/var/opt/mssql/data/shop-diff.bak'WITH RECOVERY;

Разностный backup содержит только то, что изменилось после полного, так что простой равен времени, нужному, чтобы его сделать, скопировать и применить. Учтите, что полный backup на источнике для этого не должен быть COPY_ONLY, потому что разностный всегда опирается на последний обычный полный backup. Если источник в модели восстановления full, backup журнала могут ещё сократить разрыв. В любом случае один раз отрепетируйте всю последовательность на тестовой базе до настоящей ночи. Сторону приложения - изменения схемы, с которыми могут жить обе версии кода, - разбирает статья миграции без простоя.

После восстановления: список наведения порядка#

Каждой перенесённой базе до того, как на неё направят приложение, нужно одно и то же:

1. Исправьте осиротевших пользователей. У SQL-логинов на новом сервере новые SID, так что восстановленные пользователи им не соответствуют. Создайте каждый логин, затем выполните ALTER USER [app] WITH LOGIN = [app];. Чтобы перенести логины с паролями и SID без изменений, скрипт Microsoft sp_help_revlogin генерирует выражения CREATE LOGIN на источнике. Модель объясняет статья логины, пользователи и роли SQL Server.
2. Проверьте уровень совместимости. Восстановленная база сохраняет свой уровень, а на сервере 2022 он может оказаться 110 или 130. Сначала проверьте приложение на этом уровне, затем повысьте его: ALTER DATABASE [shop] SET COMPATIBILITY_LEVEL = 160;. С включённым Query Store можно сравнить планы до и после и принудительно закрепить старый план для любого запроса, который стал хуже; как это сделать, показывает статья Query Store и медленные запросы в SQL Server.
3. Обновите статистику. EXEC sp_updatestats; в восстановленной базе, чтобы оптимизатор не работал по цифрам, собранным на другом железе и с оценщиком другой версии.
4. Проверьте целостность. Один раз DBCC CHECKDB WITH NO_INFOMSGS;, чтобы начать с заведомо исправной базы.
5. Настройте проверку страниц. Базы, начинавшие жизнь на очень старых версиях, могут всё ещё использовать TORN_PAGE_DETECTION. Современная настройка - ALTER DATABASE [shop] SET PAGE_VERIFY CHECKSUM;.
6. Проверьте модель восстановления и размер журнала, который мог приехать с источника раздутым.
7. Обновите строки подключения: новый хост, порт после запятой, SQL-аутентификация и параметры шифрования.

FAQ#

Можно ли восстановить backup SQL Server 2022 на SQL Server 2019?

Нет. Backup восстанавливаются только на ту же или более новую версию, и никакой параметр или флаг трассировки этого не меняет. Вместо этого экспортируйте BACPAC или выгрузите схему в скрипт и перенесите данные через bcp, предварительно убедившись, что база не использует ничего, чего нет в 2019.

Как перенести базу из Azure SQL Database на хостинговый SQL Server?

Экспортируйте BACPAC из Azure SQL Database через портал или SqlPackage и импортируйте его на цели. Azure SQL Database не умеет создавать нативный файл .bak, так что backup и восстановление для неё недоступны.

Моя база весит 12 ГБ. Будет ли она работать на SQL Server Express?

В таком виде нет. Express ограничивает данные каждой базы 10 ГБ. Проверьте, сколько реально занято, а не выделено, заархивируйте или удалите старые данные и сожмите файл данных ниже лимита перед backup. Если данных действительно больше 10 ГБ, нужна платная редакция.

Нужно ли переносить файл журнала транзакций?

Вы переносите его через WITH MOVE, как и файл данных, но его содержимое почти не важно: восстановление приводит базу в согласованное состояние, и журнал начинается с этого момента заново. Если журнал на источнике был огромным, сожмите его один раз после восстановления и задайте разумный размер, а не тащите старое раздувание с собой.

Перенесутся ли мои хранимые процедуры и представления?

Да, при backup и восстановлении, через BACPAC или сгенерированные скрипты - они часть базы. Не переносится то, что хранится на уровне сервера: логины, задания Agent, связанные серверы и серверные триггеры. Составьте их список до начала.