SQL Azure. Синхронизация данных с on-premise SQL Server. SSIS
Хотя функциональность SQL Azure имеет большую долю пересечения с привычным SQL Server, тождественный знак равенства между ними поставить нельзя. Например, все таблицы облачной базы обязаны иметь кластерный ключ. В Облаке можно размазывать базу по федерации SQL Serverов, но, с другой стороны, в нем не поддерживаются партиционирование, сжатие, файлстрим, FTS, TDE, CDC, CLR (я знаю еще много умных аббревиатур). Поэтому перенос схемы данных в Облако (и обратно) есть преимущественно ручной процесс подобно тому, как мы это проделывали в предыдущем посте: получили DDL-скрипт создания таблицы, доработали его напильником, выполнили, создав таблицу в БД SQL Azure, и залили в нее данные при помощи bcp. После того, как вопрос со схемами решен, данные льются отлично. Что туда, что обратно. Конечно, хотелось бы иметь какую-нибудь полуавтоматическую тулу наподобие Migration Assistant, которая бы ускоряла процесс переноса схем за счет рекомендаций и подсказок. Например, эй, мужик, в этой таблице у тебя отсутствует кластерный индекс. Ты уж создай, будь добр, а то ведь она не перенесется. Или вот это поле у тебя объявлено как типизированный XML, который в SQL Azure не поддерживается. Мы с ним что делаем: вычеркиваем из списка колонок, отмеченных к переносу, или отбрасываем у него namespace? Как-то так. Возможно, со временем такой интеллектуальный подсказчик появится в составе штатного комплекта SQL Server/SQL Azure.
После того, как схемы на обоих концах (SQL Azure и on-premise SQL Server) приведены к согласию, между ними можно воткнуть любой шланг для перекачки данных. В предыдущем посте в качестве шланга использовался bcp. В этом для разнообразия воспользуемся SSIS, для чего открываем SSDT (я же говорил про умные аббревиатуры) и заводим новый проект на основе шаблона Integration Services Project:
Рис.1
Перетаскиваем слева из SSIS Toolbox задачу Data Flow Task на рабочую поверхность пакета. Это будет шланг.
Рис.2
Один конец шланга нужно подключить к источнику данных, другой - к назначению. Для источника и назначения нужно создать соединения. Кликаем правой кнопкой на среднюю панель, которая называется Connection Managers (на ней написано Right-click here to add a new connection manager to the SSIS package). Говорим, что это будет New ADO.NET Connection. В открывшемся окне Configure ADO.NET Connection Manager кликаем на кнопку New, чтобы создать новое соединение.
Рис.3
Открывается привычное окно, где нужно выбрать провайдера, имя сервера, БД и т.д. Источником данных, как и в предыдущем примере, будет выступать таблица Production.Product в БД AdventureWorks2012, расположенной на локальном SQL Server, экземпляре по умолчанию:
Рис.4
Нажмите ОК и еще раз ОК. Аналогично создаем соединение к SQL Azure - для назначения. Оно будет тянуться к облачному SQL Server по имени fxv4koqar4, база TestDB, таблица dbo.Product. Мы с ней работали в прошлом посте. Это соединение понадобится нам впоследствии еще в одной задаче, поэтому не след хардкодить строку соединения в Connection Manager. По этой причине сохраните строку соединения в переменной пакета. Это единственная причина, т.к. соединения, переменные, вообще теоретически все засвеченные свойства задач можно задавать при запуске пакета через dtexec/dtexecui.
Встаньте на панель свойств (по умолчанию - справа внизу) свежесозданного Connection Manager.
Рис.5
При необходимости окно свойств соединения, подобное Рис.4, можно снова вызвать, кликнув на троеточие справа от значения свойства ConnectionString.
Скопируйте строку значения свойства ConnectionString.
Теперь создайте переменную пакета. Нажмите на пункт SSIS в верхней строке меню SSDT, из распахнувшегося pop-up меню выберите пункт Variables. Перед вами откроется окно с переменными пакета или докнутая панель Variables посредине внизу. Предварительно уйдите из панели свойств. Встаньте хотя бы на Connection Manager, иначе пункта Variables в меню не будет.
Рис.6
Кликните на значок создания новой переменной. Назовите ее, скажем, DstCnn. Задайте ей тип String, в качестве значения вставьте скопированную строку соединения. Свойство Application Name в строке соединения можно опустить, а свойство Password нужно добавить: Data Source=fxv4koqar4.database.windows.net;User ID=alexejs;Password=Tiwanaku;Initial Catalog=TestDB.
Рис.7
Вернитесь в панель Properties для Connection Manager (Рис.5). Встаньте на свойство Expressions. Кликните кнопку с троеточием, появившуюся справа в строке свойства. В открывшемся окне Property Expressions Editor встаньте на первую ячейку в колонке Property, распахните комбобокс и выберите ConnectionString из списка. Нажмите справа на троеточие справа от колонки Expression в той же строке таблицы. Откроется окно Expression Builder. Раскройте строчку Variables and Parameters, нажав на плюсик слева. Перетащите созданную на Рис.7 переменную User::SrcCnn (по умолчанию все переменные, для которых при создании не оговаривалось пространство имен, получают пространство имен User) в текстбокс Expression ниже. Нажмите ОК. Свойство ConnectionString у Connection Manager по имени localhost.AdventureWorks2012 стало равняться переменной @[User::DstCnn], значение которой (см.Рис.7) - это строка Data Source=fxv4koqar4.database.windows.net;User ID=alexejs;Password=Tiwanaku;Initial Catalog=TestDB. Можете теперь убрать это значение из свойства ConnectionString в панели свойств, где оно было задано явно в результате Рис.4, сделав его для ясности пустой строкой.
Рис.8
Отлично. Переходим в Data Flow Task, кликнув по ней два раза или кликнув на закладку Data Flow вверху рабочей поверхности дизайнера пакета, и перетаскиваем в нее из левого тулбокса ADO.NET Source и ADO.NET Destination.
Рис.9
Дважды кликаем по ADO.NET Source и в открывшемся окне редактора задаем созданное на Рис.4 соединение localhost.AdventureWorks2012, в качестве Data access mode - что это будет таблица, а не SQL-запрос и в Name of the table or the view выбираем из комбобокса таблицу Production.Product.
Рис.10
Зацепляем торчащую из прямоугольника с надписью ADO.NET Source синюю стрелку (Рис.9) и тащим ее в прямоугольник ADO.NET Destination, чтобы она в него уперлась. Дважды кликаем по прямоугольнику ADO.NET Destination. Устанавливаем для него соединение аналогично Рис.10, только в качестве Connection Manager здесь будет использоваться, понятно, соединение назначения - fxv4koqar4.database.windows.net.TestDB.alexejs. В панели слева редактора ADO.NET Destination Editor выбираем пункт Mappings и при необходимости корректируем, какие поля источника переносятся в какие поля таблицы-назначения. Здесь она самостоятельно определила парные поля в источнике и назначении и протянула между ними стрелочки, а также что в таблице-назначении отсутствует поле rowguid и его никуда копировать не нужно. Молодец.
Рис.11
Осталось нанести последний штрих - очистку таблицы-назначения перед заливкой в нее данных из источника. Для этого переходим на закладку Control Flow и добавляем перед задачей Data Flow Task задачу Execute T-SQL Statement, также перетаскивая ее из SSIS Toolbox. Зацепляем торчащую из нее зеленую стрелочку и протягиваем к Data Flow Task, задавая таким образом последовательность выполнения. Дважды кликаем по прямоугольнику Execute T-SQL Statement Task. В качестве T-SQL statement задаем оператор очистки truncate table dbo.Product. В качестве соединения кликаем кнопку New.
Рис.12
Задаем свойства соединения, на котором должна выполняться эта команда, потому что без этого нас отсюда не выпустят:
Рис.13
Теперь ровно так же, как мы действовали на Рис.8, открываем Expressions в свойствах новосозданного Connection Manager aaa и устанавливаем для него ConnectionString равной переменной пакета @[User::DstCnn]. Ради этого она и создавалась. Поскольку ConnectionString в нашем случае является одинаковой для ADO NET Destination и Execute T-SQL Statement Task, грамотнее его не хардкодить и там, и там, а брать из одного места.
Собственно, все. Запускаем (F5) пакет на выполнение, он успешно выполняется:
Рис.14
и мы видим, что в облачную таблицу TestDB.dbo.Product перенеслось 504 записи из on-premise таблицы AdventureWorks2012.Production.Product:
Рис.15
Перекачка данных в обратном направлении предоставляется читателям в качестве самостоятельного упражнения.
Алексей Шуленин