Working with SQL Server in Hybrid Cloud Scripts

Hybrid Cloud is a fairly attractive model when cloud computing is introduced into enterprise information systems, since this approach combines the benefits of a public and private cloud. On the one hand, the possibility of flexible attraction of external resources as needed and reduction of infrastructure costs are achieved, on the other hand, full control over data and applications that the company does not want to give to the outside is retained. However, in such a scenario, we inevitably face the challenge of integrating data from various sources. Suppose there is a table of customers, which we vertically divided into two parts. The impersonal part was assigned to the public cloud, and the customer-personalizing information remained in the local database. For complete processing within the application, it is necessary to connect both parts again using the CustomerID. There are various ways to do this. Conventionally, they can be divided into two broad categories: data consolidation at the on-premise level of the database server, which in this case will act as a single entry point for access to local and remote data, and within business logic. This article will look at the first approach.

In the case of SQL Server, access to heterogeneous data sources, starting from version 7.0, has traditionally used linked servers. Using this mechanism, we need to get data from the cloud base. As you know, in the Windows Azure cloud, a SQL Server database can be represented in two ways: Windows Azure SQL Database (SQL Azure) is a PaaS solution - and just like a regular database on a normal SQL Server installed on an Azure hosted virtual machine - IaaS. The latter case is ideologically no different from connecting to another instance of SQL Server. Creating a linked server to it is easy and has long been described in the documentation . Let's consider the case of creating a linked server in SQL Azure. In fact, connecting to a cloud base is just as easy as joining a regular SQL Server; it is only necessary not to overlook the specifics of SQL Azure:
- A strictly TCP / IP network library is used, the port is not dynamically assigned, it is always 1433.
- In SQL Azure, only the standard authentication model is valid.
- All connections to SQL Azure are necessarily encrypted based on TLS (SSL), which corresponds to adding properties Encrypt = True to the connection string; TrustServerCertificate = true. They can not be specified explicitly, they will be added automatically.
- Server Name = <Azure SQL Server Name> .database.windows.net.
- There is no USE command in SQL Azure. Strictly speaking, it exists, but it is applied exclusively in the case of sharding, which means that it is necessary to connect immediately with the necessary base.
- It is also necessary to take into account the firewall rules in the configuration of the SQL Azure server, which stipulate a white list of the range of addresses from which to establish a connection.
Taking this into account, it remains to choose an application access interface, which can be used as a SQL Server Native Client, or ODBC.

To perform the demo, the cloud version of the AdventureWorks2012 model database is used, which can be taken here .

Open the SQL Server Management Studio, connect to the local SQL Server and create a linked SQL Azure server on it using the native client:
')

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 

Script 1

where u1qgtaf85k is the name of the SQL Azure server that was generated automatically when it was created. We are testing that we can actually connect to it from the local server and get data from it:

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

Script 2

The result is shown in Fig.1


Pic1

With the appropriate permissions, it is possible on the local server side not only to read SQL Azure data, but also to modify data as well as DDL queries. For example, create a table in SQL Azure DB and insert data into it:

 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 

Script 3


Pic2

With the help of Azure Management Portal, you can make sure that the table was really created and records were added to it:


Pic.3

Creating a linked server through ODBC requires the prior creation of a DSN. Go to Control Panel \ System and Security \ Administrative Tools -> Data Sources (ODBC) or simply run odbcad32.exe and create a data source in SQL Azure, as shown in Fig.4 - 9.


Pic.4


Pic.5


Pic.6


Fig.7


Fig.8


Fig.9

You can automate the process of importing into the registry (regedit.exe) something like this .REG file:
[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“
Script 4

Creating a linked server on an ODBC source in this case will look like this:

 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' 

Script 5

We check that everything works:

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

Script 6


Pic.10

Regardless of how you create a linked server, the further is obvious. We link the client table to the database on the local server with the client table in the SQL Azure database:

 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 

Script 7


Figure 11

It should be noted that, as always in the case of a connected server, it is preferable to use the OpenQuery () function than to call it by name with 4-part notation, so as not to drag the maximum recordset to the local server, and if possible to parallelize the work, producing filtering (joins , groupings, etc., if they are on that side) by means of remote resources.