- PVSM.RU - https://www.pvsm.ru -

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

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

Работа с SQL Server в сценариях гибридного Облака
Рис.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

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

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

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

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

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

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

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

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

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

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

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

[HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INISQLAzure]
"Driver"="C:\Windows\system32\sqlncli10.dll"
"Server"="u1qgtaf85k.database.windows.net"
"LastUser"=“alexejs"
"Database"=“AdventureWorks2012"

[HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INIODBC 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

Работа с SQL Server в сценариях гибридного Облака
Рис.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

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

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

Автор: alexejs

Источник [3]


Сайт-источник PVSM.RU: https://www.pvsm.ru

Путь до страницы источника: https://www.pvsm.ru/sql-azure/47035

Ссылки в тексте:

[1] описано в документации: http://technet.microsoft.com/ru-ru/library/ff772782.aspx

[2] здесь: http://msftdbprodsamples.codeplex.com/releases/view/37304

[3] Источник: http://habrahabr.ru/post/200168/