SQL Azure. Синхронизация данных с on-premise SQL Server. DACPAC
Если база данных достаточно проста, то вместо раздельного переноса объектов схемы, организации заливки данных из таблицы в таблицу, было бы эффективней иметь механизм, позволяющий перенести разом всю базу, с данными или без. Такой механизм дебютировал в SQL Server 2008R2 под названием Data-tier applications. Он позволяет упаковывать структуры всех поддерживаемых им объектов базы в самодостаточный архивный файл DACPAC - Data-tier application component package - сродни msi или манифесту. Технология DAC - некое брендовое обозначение применительно ко всем составляющим ее понятиям: DACPAC, BACPAC, DAC Fx. Наверное,
DAC - не слишком удачная аббревиатура, потому что все уже привыкли, что это dedicated admin connection. С другой стороны, DTA тоже занято под database tuning advisor. Ну да бог с ними, с названиями. Data-tier application - это способ представить базу в виде приложения. DACPAC можно затем импортировать в датабазный проект Visual Studio или развернуть на другом SQL Server или сервере SQL Azure, получив копию исходной базы. Приложения DACPAC интегрируются с Utility Control Point и позволяют устанавливать в себя политики Policy-based Management Framework, но этих возможностей в рамках данного поста мы касаться не будем.
В SSMS в контекстном меню БД можно видеть относящиеся к DAC пункты, которые позволят мигрировать ее в SQL Azure. Перенесем в Облако какую-нибудь простенькую базу, например, бессмертный Northwind.
Рис.1
Выбираем первый пункт секции - Extract Data-tier application. Визард подключается к базе данных, считывает ее объекты и их свойства и создает в памяти модель БД. Подобно тому, как определенные объекты проверяются в процессе сборки сборки, так и здесь происходит проверка на согласованность (если какое-нибудь представление ссылается на несуществующую таблицу, произойдет ошибка, как и в случае Т-SQL) и самодостаточность (все ссылки и зависимости не выходят за пределы текущей базы). Не дозволенные в SQL Azure или не поддерживаемые в in-memory модели DAC объекты блокируются. После валидации построенная в памяти модели DACPAC записывается на диск. Диалог спрашивает полное имя dacpac-файла, в который будет извлечена структура базы:
Рис.2
Процесс проходит успешно, и в указанном месте в назначенное время появляется заказанный файл, который действительно представляет собой архив, который можно распаковать и посмотреть, что там внутри творится.
Рис.3
Рис.4
Основным содержанием являются файлы model.sql и model.xml. Первый есть обычный DDL-скрипт создания объектов, проживающих в БД Northwind:
Рис.5
Второй - его XMLное представление в формате, понимаемом DAC Framework (DAC Fx). 1-я версия Data-tier Application Framework была выпущена с SQL Server 2008R2. В промежутке между ним и SQL Server 2012 (примерно с СТР3) вышла DAC Fx 2.0. Была добавлена поддержка геопространственных типов, упаковкa в архив не только схемы, но и самих данных (import/export) и некоторые другие возможности. Наиболее заметным новшеством стал in-place upgrade. Ранее применялся подход side-by-side. Чтобы доставить изменения на SQL Server, создавалась новая база с временным именем, в которой генерировались объекты из DACPAC, переливались данные из старой базы, старая база дропалась, новая переименовывалась в старую. Это требовало в два раза больше места на сервере и делало бесполезным T-Log. С помощью компоненты ScriptDom теперь генерируются скрипты, которые с учетом версии SQL Server стараются по максимуму обойтись командой ALTER для обновления схемы. Мы увидим in-place upgrade далее на Рис.18-23. C SQL Server 2012 RTM поставляется версия DAC Fx 3.0. Физически это Microsoft.SqlServer.Dac.dll (пространства имен Microsoft.SqlServer.Dac и Microsoft.SqlServer.Dac.Extensions). Отдельно можно скачать в составе Microsoft®SQL Server® 2012 Feature Pack (см. MICROSOFT SQL SERVER 2012 MANAGABILITY FEATURE PACK COMPONENTS, Microsoft® SQL Server® 2012 Data-Tier Application Framework).
Рис.6
Описание формата можно найти здесь - [MS-DACPAC]: Data-Tier Application Schema File Format Structure Specification.
Полученный файл без проблем развертывается на сервере SQL Azure.
Рис.7
Рис.8
Рис.9
Рис.10
Скажите Databases -> Refresh облачному SQL Server и откройте новообразовавшуюся базу Northwind. Обратите внимание, что некоторые таблицы в БД Northwind были кучами, и в таком же виде они переехали в Облако.
Рис.11
Это неудивительно. SQL Azure не запрещает создание таблиц без кластерного ключа:
Рис.12
поэтому Northwind.dacpac благополучно продеплоился на облачный SQL Server (Рис.10). Он даже позволяет из них читать (кстати, видно, что таблицы Northwind пусты, т.е. перенеслись только метаданные), однако при попытке вставить запись мы получим ошибку с жалобой на отсутствие кластерного ключа:
Рис.13
Для исправления ситуации в исходной (on-premise) Northwind я вместо SSMS воспользуюсь SQL Server Data Tools, чтобы заодно проиллюстрировать работу с DAC в датабазных проектах Visual Studio. Открываем SSDT, создаем новый проект типа SQL Server Database Project, который для ясности назовем Northwind:
Рис.14
В Solution Explorer в контекстном меню проекта выбираем Import -> Data-tier Application (*.dacpac)...
Рис.15
и указываем экстрактнутый (см. Рис.1-2) с локального SQL Server файл c:\Temp\Northwind.dacpac. Проект наполняется объектами базы Northwind. Я исправлю индекс на кластерный в таблице Region, а остальные 4 кучи (CustomerCustomerDemo, CustomerDemographics, EmployeeTerritories, Territories) просто поудаляю в Solution Explorer.
Рис.16
Теперь в контекстном меню проекта (см.Рис.15) выбираю 2-й пункт - Snapshot Project - и в папке проекта (...\Northwind\Snapshots, а также в ...\Northwind\bin\Debug) образуются дакпаки, запечатлевшие текущее состояние проекта:
Рис.17
Возвращаемся к облачному варианту Northwind и выбираем из контекстного меню базы Tasks -> Upgrade Data-tier application, чтобы отразить изменения, сделанные в Northwind в SSDT (Рис.16):
Рис.18
Рис.19
Визард предупреждает, что некоторые изменения были сделаны в базу, минуя DAC, и если сейчас его применить, они потеряются. Разве я что-то правил в облачной Northwind напрямую? Ей виднее. При регистрации БД в качестве DAC-приложения (а регистрация автоматически происходит во время деплоймента) в msdb создаются служебные таблицы sysdac_instances_internal, в которой учитываются базы, отмеченные как DAC-приложения, и sysdac_history_internal, в которой ведется история изменений. В SQL Azure они находятся в БД master, т.к. msdb там нет. Апгрейд визард смотрит varbinary(max)-поле type_stream, в котором лежит последний dacpac для данного приложения = базы данных = строки в таблице sysdac_instances_internal, строит в памяти модель для текущего состояния этой базы и, если они различаются, выдает предупреждение Рис.20.
Как бы то ни было, отмечаю галку Proceed despite possible loss of changes. Кнопка Next становится доступной. Двигаемся дальше.
Рис.20
Визард может самостоятельно откатиться к состоянию базы до апгрейда при возникновении ошибки. Неотмеченный чекбокс Rollback on failure означает, что мы сами должны будем выходить из этой ситуации, если вдруг она случится. Предполагается, что у нас есть на этот случай бэкап, поэтому галку сознательно не ставим.
Рис.21
Визард обнаружил исчезновение из базы некоторых таблиц, а также соответствющих им ограничений внешнего ключа и беспокоится о потере данных. Отмечаем галку Proceed despite possible data loss и жмем ставшую доступной кнопку Next.
Рис.22
Апгрейд происходит успешно:
Рис.23
Мы видим, что структура базы пришла в соответствие с правками, внесенными в SSDT (Рис.16). 4 таблицы удалились, а таблица Region из кучи превратилась в дерево, поэтому SQL Azure больше не ругается на отсутствие кластерного ключа и дает с ней нормально работать:
Рис.24
Теперь, когда объекты целевой базы на стороне SQL Azure созданы, для закачки данных можно использовать любой из способов, рассмотренных в двух предыдущих постах.
Алексей Шуленин