Работа с 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. Создание связанного сервера на него не составляет труда и давно описано в документации. Разберем случай создания связанного сервера на 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, взять которую можно здесь.

Открываем 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 - 9.

Рис.4

Рис.5

Рис.6

Рис.7

Рис.8

Рис.9

Можно автоматизировать процесс импортом в реестр (regedit.exe) примерно такого .REG-файла:
[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"
Скрипт 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-частной нотацией, чтобы не тащить на локальный сервер максимальный рекордсет, а по возможности распараллелить работу, производя фильтрацию (джойны, группировки и т.д., если будут на той стороне) средствами удаленных ресурсов.

Ссылки по теме

• Microsoft: Серверные программы (SQL Server)