Миграция баз данных в SQL Azure.

Привет, всем ! Сегодня затрону очень актуальную тему переноса существующих MSSQL баз данных в облачную службу SQL Azure. Будет довольно таки много скриншотов, поэтому осторожно трафик :). Как нам всем известно SQL Azure построена на базе Microsoft SQL Server, но несмотря на это у большинства разработчиков возникают определенные трудности с процессом миграции существующей базы данных в облако. В сегодняшней публикации я расскажу как это можно сделать при помощи довольно таки грубой силы, а именно командной утилиты BCP.

Итак, для начала нам естественно нужна пустая база данных в облаке. Для этого мы перейдем на портал разработчика и в разделе Database кликнем на проекте, который ассоциирован с активной подпиской, после чего создадим на выбранном сервере новую бд.

В открывшемся диалоге введем название новой базы и ее максимальный размер (мне для поста хватило с головой 1 Гб версии). После того как пустая база будет создана нам надо добавить исключение в правила файрволла, для того чтобы он пропускал входящие запросы. Для этого перейдем на портале в раздел конфигурации файрволла и добавим исключение (я для простоты разрешил подключения для всего возможного спектра входящих IP-адресов).

Генерация DDL-скриптов.

Как только мы закончим все приготовления на портале нам необходимо будет сгенерировать DDL-скрипты для переноса (напомню DDL расшифровывается как “Data Definition Language” и является семейством  языков, используемых в компьютерных программах для описания структуры баз данных). Для генерации сначала необходимо подключиться в SSMS к выбранной базе данных и в контекстном меню выбрать пункт Tasks > Generate Scripts после чего выбрать первые три пункта как показано на скриншоте.

* К сожалению на данный момент SQL Azure не поддерживает DDL триггеры.

Дальше нам необходимо раскрыть в этом же окошке подпункт Tables и выбрать там все необходимые таблицы, имеющие кластерные индексы (это требование SQL Azure). После того, как все необходимые таблицы выбраны, переходим к следующему шагу диалога и там нажимаем кнопочку Advanced


По клику откроется еще одно окошко с расширенными настройками, в котором опцию General > Script for database установим в значение “SQL Azure Database”, а опцию Table/View Options > Script Indexes в значение True.

Последним шагом будет выбор места хранения результирующего SQL-кода и пересмотр Summary страницы. Как только наши скрипты на создание объектов базы данных будут готовы нам необходимо будет сменить соединение в SSMS и подключиться уже к облачной базе.

В новом соединении выполним заранее сгенерированные скрипты и по завершению получим облачную базу данных, содержащую все необходимые объекты.

Создание вспомогательных процедур для миграции данных

Как только наша структура будет восстановлена, создадим 2 вспомогательные хранимые процедуры для упрощения миграции данных. Хранимка SetForeignKeyEnabledStatus будет использоваться для отключения ограничений внешнего ключа во время переноса данных. Это позволит нам загружать данные таблиц в произвольном порядке без учета primary/foreign key ограничений. Эта процедура будет вызвана как перед началом (для отключения проверок) так и после переноса данных (для возврата всех констрейнтов). Хранимка SetIndexEnabledStatus будет использоваться для отключения всех не кластерных индексов во время переноса данных. Это ускорит процесс загрузки данных. Данную процедуру также будем запускать до и после переноса. Код вышеназванных процедур представлен листингом ниже:

IF OBJECT_ID('[dbo].[SetForeignKeyEnabledStatus]','P') IS NOT NULL 
    DROP PROCEDURE [dbo].[SetForeignKeyEnabledStatus]; 
GO
CREATE PROCEDURE [dbo].[SetForeignKeyEnabledStatus] 
( 
    @enabled bit 
) 
AS 
BEGIN 
    DECLARE 
        @schema_name sysname, 
        @table_name sysname, 
        @fk_constraint_name sysname, 
        @cmd_txt varchar(8000);
DECLARE fk_cursor CURSOR FOR 
        SELECT 
            sc.name AS schema_name, 
            so.name AS table_name, 
            so2.name AS fk_constraint_name 
        FROM 
            sys.objects so 
            inner join sys.schemas sc ON so.schema_id = sc.schema_id 
            inner join sys.foreign_key_columns fk ON so.object_id = fk.parent_object_id 
            inner join sys.objects so2 ON so2.object_id = fk.constraint_object_id 
        WHERE 
            so.type = 'U';
OPEN fk_cursor;
FETCH NEXT FROM fk_cursor INTO 
        @schema_name, 
        @table_name, 
        @fk_constraint_name;
WHILE @@FETCH_STATUS = 0 
    BEGIN 
        IF @enabled = 0 
            SET @cmd_txt = 'ALTER TABLE [' + @schema_name + '].[' + @table_name + '] NOCHECK CONSTRAINT [' + @fk_constraint_name + '];' 
        ELSE 
            SET @cmd_txt = 'ALTER TABLE [' + @schema_name + '].[' + @table_name + '] WITH CHECK CHECK CONSTRAINT [' + @fk_constraint_name + '];';                     
        PRINT @cmd_txt; 
        EXECUTE (@cmd_txt); 
        FETCH NEXT FROM fk_cursor INTO 
            @schema_name, 
            @table_name, 
            @fk_constraint_name; 
    END;
CLOSE fk_cursor; 
    DEALLOCATE fk_cursor; 
END; 
GO
IF OBJECT_ID('[dbo].[SetIndexEnabledStatus]','P') IS NOT NULL 
    DROP PROCEDURE [dbo].[SetIndexEnabledStatus]; 
GO
CREATE PROCEDURE [dbo].[SetIndexEnabledStatus] 
( 
    @enabled bit 
) 
AS 
BEGIN 
    DECLARE 
        @schema_name sysname, 
        @table_name sysname, 
        @index_name sysname, 
        @cmd_txt varchar(8000);
DECLARE idx_cursor CURSOR FOR 
        SELECT 
            sc.name AS schema_name, 
            so.name AS table_name, 
            si.name AS index_name 
        FROM 
            sys.objects so 
            inner join sys.schemas sc ON so.schema_id = sc.schema_id 
            inner join sys.indexes si ON so.object_id = si.object_id 
        WHERE 
            so.type = 'U' 
            AND si.index_id > 1; 
    OPEN idx_cursor;
FETCH NEXT FROM idx_cursor INTO 
        @schema_name, 
        @table_name, 
        @index_name;
WHILE @@FETCH_STATUS = 0 
    BEGIN 
        IF @enabled = 0        
            SET @cmd_txt = 'ALTER INDEX [' + @index_name + '] ON [' + @schema_name + '].[' + @table_name + '] DISABLE;' 
        ELSE 
            SET @cmd_txt = 'ALTER INDEX [' + @index_name + '] ON [' + @schema_name + '].[' + @table_name + '] REBUILD;' 
        PRINT @cmd_txt; 
        EXECUTE (@cmd_txt); 
        FETCH NEXT FROM idx_cursor INTO 
            @schema_name, 
            @table_name, 
            @index_name; 
    END;
CLOSE idx_cursor; 
    DEALLOCATE idx_cursor; 
END; 
GO

Эти две хранимые процедуры перебирают объекты (внешние ключи и индексы) из системного представления. Для каждого объекта динамически формируется команда для его включения или отключения.

Используем BCP для экспорта/импорта данных исходной базы

Для экспорта данных воспользуемся командной утилитой “Bulk copy program”, по окончании работы которой у нас будут сформированы “нативные” файлы для каждой таблицы исходной базы данных. Давайте посмотрим на формат используемой команды:

@ECHO OFF

SET SourceSqlServerName=[REPLACE_SERVER]
SET SourceSqlDbName=[MY_SOURCE_DB_NAME]


SET TableName=[dbo.MY_FIRST_TABLE]

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %SourceSqlDbName%.%TableName% out %TableName%.dat -n -S %SourceSqlServerName% -T
IF ERRORLEVEL 1 GOTO ABORT

Как мы видим нам необходимо прописать в данном командном файле адрес сервера, имя исходной базы данных и по очереди перегнать все таблицы (я для экономии места экспортировал лишь таблицу [dbo.MY_FIRST_TABLE]). По окончании работы скрипта будут созданы необходимые dat-файлы для каждой из прописанных в скрипте таблиц. Все, что нам осталось – это загрузить наши данные в целевую базу данных опять же при помощи BCP. Давайте посмотрим на скрипт, помогающий в этой несложной задаче:

@ECHO OFF

SET DestSqlServerName=[REPLACE_SQLAZURE_SERVER]
SET DestSqlDbName=[REPLACE_AZURE_DB_NAME]
SET DestSqlUserName=[REPLACE_USERNAME]
SET DestSqlUserPassword=[REPLACE_PASSWORD]


SET TableName=[dbo.MY_FIRST_TABLE]

ECHO ******************************
ECHO Migrating data for %TableName%
ECHO ******************************

bcp %DestSqlDbName%.%TableName% in %TableName%.dat -n -S %DestSqlServerName% -U%DestSqlUserName% -P%DestSqlUserPassword% -E
IF ERRORLEVEL 1 GOTO ABORT

Как видим процедура импорта использует заранее подготовленные dat-файлы. Будьте очень внимательны с параметром -E. Если забыть этот параметр, то для IDENTITY-полей будут сгенерированы новые значения вместо значений из dat-файла. В принципе на этом процесс миграции базы данных в SQL Azure можно считать завершенным. И не забывайте запускать созданные ранее вспомогательные процедуры. Перед импортом данных необходимо запустить хранимки вот таким образом:

EXECUTE [dbo].[SetForeignKeyEnabledStatus] 0
GO
EXECUTE [dbo].[SetIndexEnabledStatus] 0
GO

После импорта вот таким:

EXECUTE [dbo].[SetForeignKeyEnabledStatus] 1
GO
EXECUTE [dbo].[SetIndexEnabledStatus] 1
GO

Спасибо за внимание ! Надеюсь процесс миграции Ваших баз данных пройдет гладко и беспроблемно :)
Blogpost sources: http://msdn.microsoft.com/en-us/gg282151, http://msdn.microsoft.com/en-us/library/windowsazure/ee730904.aspx, http://www.sqlazureperformance.com/2010/migrating-sql-server-database-to-sql-azure/