SQL Azure. Синхронизация данных с on-premise SQL Server. BCP

Artikel
05/01/2012

До сих пор в этой серии постов рассматривалась автономная работа c облачной базой данных. Однако SQL Azure (в нынешнем виде) появилась на свет спустя почти 22 года после первой версии обычного SQL Server, который за это время успел распространиться необычайно широко и сделаться одной из наиболее популярных СУБД. Поэтому когда вы проникнитесь идеей Облака (а вы никуда от этого не денетесь), вы, скорее всего, не будете создавать базу SQL Azure с нуля, а станете наполнять ее на основе данных, хранящихся в традиционных on-premise базах. Как уже говорилось, одним из преимуществ облака выступает эластичность - возможность быстрого привлечения вычислительных ресурсов, а также оплата по факту потребления, что означает, что нам не нужно покупать дорогущий сервак, который будет использоваться неделю в году, пока бухгалтерия будет этот год закрывать. Очевидный сценарий - обойтись более скромным оборудованием, которого достаточно 95% времени в году. В остальные 5% загрузить обезличенные данные в SQL Azure (трафик туда - бесплатный), посчитать (стоимость SQL Azure не зависит от потраченного процессорного времени - только от размера базы) и вернуть результаты счета. Имеет смысл. Чтобы его реализовать, надо научиться передавать данные (и схемы данных) между SQL Azure и on-premise SQL Server. Нет ничего проще. Выпишите на бумажке известные вам способы синхронизировать экземпляры человеческого SQL Server. Вычеркните из них то, что не поддерживается SQL Azure. Оставшиеся мы тут же коротенечко рассмотрим.

Синхронизация не на уровне баз, а на уровне экземпляров SQL Server обеспечивается при помощи компонента Windows Server Failover Clustering. Кластеризацию смело вычеркиваем.

Синхронизация на уровне отдельных баз с минимальным временем задержки достигается при помощи зеркалирования. Вычеркиваем.

По этой же причине вычеркиваем новую прогрессивную функциональность, обеспечивающую зеркалирование в несколько реплик на уровне группы баз, появившуюся в SQL Server 2012 под названием Always On.

Лог шиппинг - аналогично.

В принципе, резервное копирование/восстановление можно тоже рассматривать как некоторую кондовую синхронизацию базы - забэкапили в одном месте, подняли в другом. Логично? Логично, вычеркиваем. Команды BACKUP/RESTORE в SQL Azure не поддерживаются.

Вышеперечисленные способы были не столько синхронизацией, сколько средством обеспечения отказоустойчивости, позволяя подстелить соломку на случай, если рабочая база неожиданно навернулась. В SQL Azure в них нет необходимости, т.к. отказоустойчивость по определению обеспечивается компонентом SQL Azure Fabric, который автоматически изготавливает и поддерживает реплики баз.

Кстати, о репликах. В привычной жизни привычным способом обмена данными между привычными SQL Serverами является репликация. При помощи BCP на подписчике создается начальный снимок данных издателя, на который затем применяются изменения из журнала транзакций издателя. Логично? Вычеркиваем. Репликация тоже не поддерживается в SQL Azure. Вместо репликации для облачных баз применяется утилита SQL Azure Data Sync на основе Microsoft Sync Framework, но о ней в следующий раз.

Кстати, о BCP. А вот это попробуйте (с). Возьмем какую-нибудь несложную табличку из модельной базы AdventureWorks2012, например, Production.Product и перенесем ее при помощи утилиты командной строки Bulk Copy в какую-нибудь БД на сервере SQL Azure, например, TestDB, созданную в одном из предыдущих упражнений. Берем таблицу Product, находящуюся в схеме Production базы данных AdventureWorks2012 на локальном SQL Server (localhost), c которым устанавливаем доверительное (-Т) соединение на основе Windows-аутентификации, и экспортируем (out) ее в текстовый файл, используя символы Юникода (-w):

bcp AdventureWorks2012.Production.Product out c:\Temp\AdventureWorks2012.Production.Product.dat -Slocalhost -T -w

Рис.1

В соответствии с документацией практикой хорошего тона является тут же сделать файл формата, описывающий результаты экспорта, т.к. AdventureWorks2012.Production.Product.dat не включает сведения о схеме. При удалении таблицы восстановить ее структуру будет проблематично, если отсутствует файл форматирования. Следующая команда изготавливает форматный файл в виде XML, подобный тому, с которым мы работали в посте BULK INSERT юникодного файла.

bcp AdventureWorks2012.Production.Product format nul -Slocalhost -T -w -x -f c:\Temp\Product_fmt.xml

<?xml version="1.0"?>

</RECORD>

<ROW>

</ROW>

</BCPFORMAT>

Скрипт 1

Oсталось импортировать AdventureWorks2012.Production.Product.dat на SQL Azure. К сожалению, несмотря на наличие такого замечательного форматного файла ни bcp, ни BULK INSERT не умеют создавать на его основе таблицу. Они умеют только заливать на его основе в нее данные. Таблица должна быть как-либо создана перед этим:

bcp TempDB..Product in c:\Temp\AdventureWorks2012.Production.Product.dat -Slocalhost -T -f c:\Temp\Product_fmt.xml

Рис.2

Создать в облачной базе таблицу идентичной структуры проще всего с помощью SSMS. В Object Explorer можно получить код создания объекта из его контекстного меню

Рис.3

либо сразу нескольких объектов из контекстного меню базы данных:

Рис.4

Рис.5

В принципе, вместе со схемой таблицы можно заскриптовать и содержащиеся в ней данные. Если на следующем экране визарда

Рис.6

нажать кнопку Advanced, видно, что опция Types of data to script выставлена в Schema only

Рис.7

но при желании можно сказать, чтобы скриптовались данные или структура вместе с данными:

Рис.8

Таблица заскриптовалась как традиционный оператор CREATE TABLE (в опциях Рис.7 можно предварить его DROP), данные - как INSERT каждой строки таблицы. Остается соединиться с БД SQL Azure, как показывалось в одном из предыдущих постов

Рис.9

слегка подкорректировать автосгенеренный скрипт: заменить пользовательский тип dbo.Name в поле Name на nvarchar(50), а тип dbo.Flag - на bit, несуществующую в TestDB схему Production - на, допустим, dbo; убрать команду USE, поле [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL, настройку индекса WITH (PAD_INDEX = OFF ...), файл-группы как неподдерживаемые в SQL Azure. Кое-что из этого можно пометить в опциях Рис.7, например, поставить Script USE DATABASE = false. Модифицированный скрипт выполняется на сервере SQL Azure:

CREATE TABLE dbo.[Product](

[ProductID] [int] IDENTITY(1,1) NOT NULL,

[Name] nvarchar(50) NOT NULL,

[ProductNumber] [nvarchar](25) NOT NULL,

[MakeFlag] bit NOT NULL,

[FinishedGoodsFlag] bit NOT NULL,

[Color] [nvarchar](15) NULL,

[SafetyStockLevel] [smallint] NOT NULL,

[ReorderPoint] [smallint] NOT NULL,

[StandardCost] [money] NOT NULL,

[ListPrice] [money] NOT NULL,

[Size] [nvarchar](5) NULL,

[SizeUnitMeasureCode] [nchar](3) NULL,

[WeightUnitMeasureCode] [nchar](3) NULL,

[Weight] [decimal](8, 2) NULL,

[DaysToManufacture] [int] NOT NULL,

[ProductLine] [nchar](2) NULL,

[Class] [nchar](2) NULL,

[Style] [nchar](2) NULL,

[ProductSubcategoryID] [int] NULL,

[ProductModelID] [int] NULL,

[SellStartDate] [datetime] NOT NULL,

[SellEndDate] [datetime] NULL,

[DiscontinuedDate] [datetime] NULL,

[ModifiedDate] [datetime] NOT NULL,

CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED ([ProductID] ASC)

)

Рис.10

Выполнять вставку данных таким образом можно, но не нужно - получается слишком накладно вставлять каждую запись таблицы по отдельности. Лучше генерировать только скрипт создания структуры таблицы без данных (Рис.7) c последующей адаптацией под особенности SQL Azure. Заливку данных производить методом массированного копирования (bcp), которым мы уже начали было пользоваться. Препятствием выступило отсутствие таблицы-приемника в назначении. Теперь такая таблица есть. Нужно изменить файл данных (Рис.1) и форматный файл (Скрипт 1) в соответствии с адаптированной к SQL Azure структурой таблицы. В данном случае из нее выпало поле rowguid, т.к. признак ROWGUIDCOL не поддерживается в SQL Azure. Выборку остальных полей записываем в виде запроса

select [ProductID], [Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], [Color], [SafetyStockLevel], [ReorderPoint], [StandardCost], [ListPrice], [Size], [SizeUnitMeasureCode], [WeightUnitMeasureCode], [Weight], [DaysToManufacture], [ProductLine], [Class], [Style], [ProductSubcategoryID], [ProductModelID], [SellStartDate], [SellEndDate], [DiscontinuedDate], [ModifiedDate] from [Production].[Product]

Скрипт 2

и выполняем его аналогично Рис.1:

declare @qry varchar(8000) = 'select [ProductID], [Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], [Color], [SafetyStockLevel], [ReorderPoint], [StandardCost], [ListPrice], [Size], [SizeUnitMeasureCode], [WeightUnitMeasureCode], [Weight], [DaysToManufacture], [ProductLine], [Class], [Style], [ProductSubcategoryID], [ProductModelID], [SellStartDate], [SellEndDate], [DiscontinuedDate], [ModifiedDate] from [Production].[Product]'

declare @cmd varchar(8000) = 'bcp "' + @qry + '" queryout c:\Temp\AdventureWorks2012.Production.Product.dat -Slocalhost -T -dAdventureWorks2012 -w'

exec xp_cmdshell @cmd

Рис.11

Изменить подобным образом (Скрипт 1) форматный файл не удастся, потому что создание форматного файла возможно только когда bcp делается из таблицы, а не из запроса: A valid table name is required for in, out, or format options. Поэтому в XML Скрипт 1 просто руками удаляем строки

<FIELD ID="24" xsi:type="NCharTerm" TERMINATOR="\t\0" MAX_LENGTH="74"/> и <COLUMN SOURCE="24" NAME="rowguid" xsi:type="SQLUNIQUEID"/>. 25-й номер поля можно не менять.

Выполняем импорт:

bcp TestDB.dbo.Product in c:\Temp\AdventureWorks2012.Production.Product.dat -Sfxv4koqar4.database.windows.net -Ualexejs -PTiwanaku -f c:\Temp\Product_fmt.xml

Рис.12

Говорит, что все 504 записи из таблицы Product успешно переехали в ее облачную копию. Проверяем. Открываем SQL Server Data Tools. Меню Data -> Data Compare -> New Data Comparison.

Рис.13

Создаем два соединения: одно с локальным SQL Server, БД AdventureWorks2012; другое - с БД TestDB в SQL Azure, как это делалось в SSMS.

Рис.14

Ну, извините. Остается визуальный контроль, согласно которому все перенеслось, вроде, правильно.

Что касается обратного направления: из SQL Azure в on-premise SQL Server, попытка заскриптовать таблицу из SSMS аналогично Рис.3-7 на SQL Azure завершается ошибкой Microsoft.SqlServer.Management.Sdk.Sfc.InvalidVersionEnumeratorException: Operation not supported on version 11.0. Сам процесс массированного импорта из SQL Azure работает нормально

bcp TestDB.dbo.Product out c:\Temp\AdventureWorks2012.Production.Product.dat -Sfxv4koqar4.database.windows.net -Ualexejs -PTiwanaku -w

Рис.15

Продолжение следует.

Алексей Шуленин

Freigeben über

SQL Azure. Синхронизация данных с on-premise SQL Server. BCP

Zusätzliche Ressourcen