A hybrid cloud is a fairly attractive model when implementing cloud computing in enterprise information systems since this approach combines the advantages of public and private clouds. On the one hand, it is possible to flexibly attract external resources when needed and reduce infrastructure costs. On the other hand, full control over data and applications that the enterprise does not want to outsource remains. However, in such a scenario, we inevitably face the task of integrating data from various sources. Suppose there is a table with customers, which is vertically divided into two parts. The depersonalized part was allocated in a public cloud, and the information personalizing the customers remained in a local database. For holistic processing inside the application, you need to combine both parts by CustomerID. There are various ways to do this. Conventionally, they can be divided into two large categories: data aggregation at the on-premise database server level which, in this case, will be a single sign on for accessing local and remote data, and data aggregation within the business logic. This article will consider the first approach.
Suppose there is a table with customers, which is vertically divided into two parts. The depersonalized part was allocated in a public cloud, and the information personalizing the customers remained in a local database. For holistic processing inside the application, you need to combine both parts by CustomerID. There are various ways to do this. Conventionally, they can be divided into two large categories: data aggregation at the on-premise database server level which, in this case, will be a single sign on for accessing local and remote data, and data aggregation within the business logic. This article will consider the first approach.
In the case of SQL Server, access to heterogeneous data sources starting from version 7.0 has traditionally been based on the mechanism of linked servers. Using this mechanism, we need to get data from the cloud database. As you know, in Windows Azure cloud, the SQL Server database can be represented in two ways: Windows Azure SQL Database (SQL Azure), which is a PaaS solution, and as a regular database on a traditional SQL Server installed on a virtual machine hosted in Azure – IaaS. The latter case is conceptually the same as the connection with another instance of SQL Server. Creating a linked server for it is easy and has been described in the documentation. Let’s consider the case of creating a linked server on SQL Azure. In fact, connecting to a cloud database is not more complicated than connecting to a traditional SQL Server; it is necessary to consider the specifics of SQL Azure:
– Only TCP/IP network library must be used, the port must not be assigned dynamically, it is always 1433.
– Only the standard authentication model is valid in SQL Azure.
– All connections to SQL Azure must be encrypted with TLS (SSL), which means adding Encrypt=True; TrustServerCertificate=true properties to the connection string. They may be not explicitly specified, they will be added automatically.
– Server name = <SQL Azure server name>.database.windows.net.
– There is no USE command in SQL Azure. Strictly speaking, it exists, but it is used purely in the case of sharding, which means that it is necessary to connect to the required base at once.
– You also need to consider the firewall rules in the SQL Azure server configuration that specify the white list of the range of addresses from which you are allowed to connect.
In view of the foregoing, all that is lacking is to select an application access interface. SQL Server Native Client or ODBC can take on this role.
To run the demo, use the cloud version of the AdventureWorks2012 model database, which you can download here.
Open SQL Server Management Studio, connect to local SQL Server and create an SQL Azure linked 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
where u1qgtaf85k is the name of the SQL Azure server that was generated automatically when the server was created. We need to test that we really can connect to the server from the local server and get data from it:
select CustomerID, AccountNumber from SQLAzure_NCli.AdventureWorks2012.Sales.Customer where CustomerID between 1 and 100
The result is shown in Fig.1.
Having the appropriate rights, it is possible not only to read SQL Azure data from the local server but also requests for data modification, as well as DDL-requests. For example, create a table in the SQL Azure DB and insert the 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
Using Azure Management Portal, you can verify that the table has actually been created and that entries have been added to the table:
Creating a linked server using ODBC requires a DSN to be first created. Go to Control Panel\System and Security\Administrative Tools -> Data Sources (ODBC) or simply run odbcad32.exe and create a data source on SQL Azure, as shown in Figures 4-9.
You can automate the process by importing the following.REG file into the registry (regedit.exe):
[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“
Creating a linked server on the 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'
Check that everything works:
select * from openquery(SQLAzure_ODBC, 'select * from sys.tables')
Regardless of the method of creating a linked server, the following is obvious. Link the client table on the local server database with the client table on 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
Note that, as always in the case of a linked server, it is preferable to use the OpenQuery() function, rather than refer to it by name with a 4-part notation, so as not to drag the maximum recordset to the local server, and, if possible, parallelize the work by filtering (joins, groupings, etc., if they are on that side) by means of remote resources.