Работа с SQL Server в сценариях гибридного Облака

Гибридное Облако является достаточно привлекательной моделью при внедрении облачных вычислений в информационные системы предприятий, поскольку этот подход сочетает преимущества публичного и частного облака. С одной стороны, достигаются возможности гибкого привлечения внешних ресурсов по мере надобности и сокращения инфраструктурных издержек, с другой - сохраняется полный контроль за данными и приложениями, которые предприятие не хочет отдавать наружу. Однако в подобном сценарии мы неизбежно сталкиваемся с задачей интеграции данных из различных источников. Предположим, имеется таблица клиентов, которую мы вертикально разбили на две части. Обезличенная часть была отнесена в публичное облако, а персонифицирующая клиентов информация осталась в локальной базе. Для целостной обработки внутри приложения необходимо снова соединить обе части по CustomerID. Возможны различные способы это сделать. Условно их можно разбить на две большие категории: объединение данных на уровне on-premise сервера БД, который в этом случае будет выступать единой точкой входа для доступа к локальным и удаленным данным, и внутри бизнес-логики. В этой статье будет рассмотрен первый подход. 

В случае SQL Server для доступа к гетерогенным источникам данных, начиная с версии 7.0, традиционно используется механизм связанных серверов (linked servers). Используя этот механизм, нам нужно получить данные из облачной базы. Как известно, в облаке Windows Azure база данных SQL Server может быть представлена в двух ипостасях: Windows Azure SQL Database (SQL Azure) - это PaaS-решение - и как обычная база данных на обычном SQL Server, установленном на размещенной в Azure виртуальной машине - IaaS. Последний случай идейно ничем не отличается от соединения с другим экземпляром SQL Server. Создание связанного сервера на него не составляет труда и давно <a href="http://technet.microsoft.com/ru-ru/library/ff772782.aspx">описано в документации</a>. Разберем случай создания связанного сервера на SQL Azure. На самом деле, соединение с облачной базой ничуть не сложнее, чем присоединиться к обычному SQL Server; необходимо только не упускать из вида спцифику SQL Azure:

- Используется строго сетевая библиотека TCP/IP, порт не назначается динамически, он всегда 1433.

- В SQL Azure действует только стандартная модель аутентификации.

- Все соединения с SQL Azure в обязательном порядке шифруются на основе TLS (SSL), что соответствует добавлению в строку соединения свойств Encrypt=True;TrustServerCertificate=true. Их можно не указывать в явном виде, они будут добавлены автоматически.

- Имя сервера = <имя сервера SQL Azure>.database.windows.net.

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

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

Принимая сказанное в учет, остается выбрать прикладной интерфейс доступа, в качестве которого может использоваться SQL Server Native Client, либо ODBC.

Для выполнения демонстрационного примера используется облачный вариант модельной базы данных AdventureWorks2012, взять которую можно <a href="http://msftdbprodsamples.codeplex.com/releases/view/37304">здесь</a>.

Открываем SQL Server Management Studio, соединяемся с локальным SQL Server и создаем на нем связанный сервер на SQL Azure, используя нативного клиента:

if exists (select 1 from sys.servers where name = 'SQLAzure_NCli') exec sp_dropserver @server = 'SQLAzure_NCli', @droplogins = 'droplogins'

go

exec sp_addlinkedserver

@server='SQLAzure_NCli',

@srvproduct='',    

@provider='sqlncli',

@datasrc='u1qgtaf85k.database.windows.net',  

@location='',

@provstr='',

@catalog='AdventureWorks2012'

go

exec sp_addlinkedsrvlogin

@rmtsrvname = 'SQLAzure_NCli',

@useself = 'false',

@rmtuser = 'alexejs',            

@rmtpassword = 'Password'

go

Скрипт 1

где u1qgtaf85k- имя сервера SQL Azure, которое было сгенерировано автоматически при его создании. Тестируем, что мы действительно можем с ним соединиться со стороны локального сервера и получить с него данные:

select CustomerID, AccountNumber from SQLAzure_NCli.AdventureWorks2012.Sales.Customer where CustomerID between 1 and 100

Скрипт 2

Результат показан на Рис.1

Рис.1

Обладая соответствующими правами, можно со стороны локального сервера выполнять не только чтение данных SQL Azure, но и запросы на модификацию данных, а также DDL-запросы. Например, создадим в БД SQL Azure таблицу и вставим в нее данные:

exec sp_serveroption 'SQLAzure_NCli', 'rpc out', true

exec ('CREATE TABLE TestTbl(fld1 int not null CONSTRAINT PK_fld1 PRIMARY KEY CLUSTERED (fld1) )') at SQLAzure_Ncli

exec ('INSERT INTO TestTbl VALUES (1), (2), (3)') at SQLAzure_NCli

Скрипт 3

Рис.2

С помощью Azure Management Portal можно убедиться, что таблица действительно создалась и записи в нее добавились:

Рис.3

Создание связанного сервера через ODBC требует предварительного создания DSN. Идем в Control Panel\System and Security\Administrative Tools -> Data Sources (ODBC) или просто запускаем odbcad32.exe и создаем источник данных на SQL Azure, как показано на Рис.4 - .

Рис.4

Рис.5

Рис.6

Рис.7

Рис.8

Рис.9

Можно автоматизировать процесс импортом в реестр (regedit.exe) примерно такого .REG-файла:

<code>[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\SQLAzure]

"Driver"="C:\\Windows\\system32\\sqlncli10.dll"

"Server"="u1qgtaf85k.database.windows.net"

"LastUser"=“alexejs"

"Database"=“AdventureWorks2012"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]

“SQLAzure"="SQL Server Native Client 10.0“</code>

Скрипт 4

Создание связанного сервера на ODBC-источник в этом случае будет выглядеть следующим образом:

if exists (select 1 from sys.servers where name = 'SQLAzure_ODBC') exec sp_dropserver @server = 'SQLAzure_ODBC', @droplogins = 'droplogins‘

go

exec sp_addlinkedserver @server = 'SQLAzure_ODBC', @srvproduct = 'Any', @provider = 'MSDASQL', @datasrc = 'SQLAzure', @catalog='AdventureWorks2012'

go

exec sp_addlinkedsrvlogin @rmtsrvname = 'SQLAzure_ODBC', @useself = 'false', @rmtuser = 'alexejs', @rmtpassword = 'Password'

Скрипт 5

Проверяем, что все работает:

select * from openquery(SQLAzure_ODBC, 'select * from sys.tables')

Скрипт 6

Рис.10

Независимо от способа создания прилинкованного сервера дальнейшее очевидно. Связываем таблицу клиентов в БД на локальном сервере с таблицей клиентов в БД SQL Azure:

select c.CustomerID, c.AccountNumber, p.FirstName, p.LastName from openquery(SQLAzure_NCli, 'select CustomerID, AccountNumber from Sales.Customer where CustomerID between 1 and 100') c join Person.Person p on c.CustomerID = p.BusinessEntityID order by c.CustomerID

Скрипт 7

Рис.11

Следует отметить, что, как всегда в случае связанного сервера, предпочтительней использовать функцию OpenQuery(), чем обращаться к нему по имени с 4-частной нотацией, чтобы не тащить на локальный сервер максимальный рекордсет, а по возможности распараллелить работу, производя фильтрацию (джойны, группировки и т.д., если будут) средствами удаленных ресурсов.