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