Working with SQL Server in Hybrid Cloud Scripts

    Hybrid Cloud is a rather attractive model for introducing cloud computing into enterprise information systems, since this approach combines the advantages of a public and private cloud. On the one hand, it is possible to flexibly attract external resources as needed and reduce infrastructure costs, on the other hand, full control is maintained over data and applications that the company does not want to give out. However, in such a scenario, we inevitably face the task of integrating data from various sources. Suppose we have a customer table, which we vertically divided into two parts. The depersonalized part was placed in a public cloud, and the information that personified customers remained in the local database. For holistic processing within the application, it is necessary to reconnect both parts by CustomerID. There are various ways to do this. Conventionally, they can be divided into two broad categories: combining data at the on-premise level of the database server, which in this case will act as a single entry point for accessing local and remote data, and inside the business logic. This article will cover the first approach.

    In the case of SQL Server, the mechanism of linked servers is traditionally used to access heterogeneous data sources starting from version 7.0. 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 like a regular database on a regular SQL Server installed on a virtual machine located in Azure - IaaS. The latter case is ideologically no different from connecting to another instance of SQL Server. Creating a linked server on it is not difficult and has long been described in the documentation. Consider the case of creating a linked server in SQL Azure. In fact, connecting to a cloud base is no more difficult than joining a regular SQL Server; you just need to not lose sight of the specifics of SQL Azure:
    - A strictly networked TCP / IP library is used, the port is not assigned dynamically, 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 the addition of Encrypt = True; TrustServerCertificate = true to the connection string. They can be omitted 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 used purely in the case of sharding, which means that it is necessary to connect immediately to the desired base.
    - It is also necessary to consider firewall rules in the configuration of the SQL Azure server, which stipulate a whitelist of the range of addresses from which it is allowed to establish a connection.
    Taking this into account, it remains to choose the application access interface, which can be used as SQL Server Native Client, or ODBC.

    To run the demo, we use the cloud version of the AdventureWorks2012 model database, which you can get here .

    Open SQL Server Management Studio, connect to the local SQL Server and create a linked server on it in SQL Azure 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 Azure SQL Server that was automatically generated when it was created. We test that we can really 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 Fig. 1

    Having the appropriate rights, you can execute not only reading Azure SQL data from the local server, but also data modification requests as well as DDL requests. For example, create a table in the SQL Azure database and paste 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

    Script 3


    Fig. 2

    Using the Azure Management Portal, you can verify that the table has really been created and the entries have been added to it:


    Fig . 3

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


    Fig . 4 Fig.


    5


    Fig. 6


    Fig. 7


    Fig. 8 Fig.


    9

    You can automate the process by importing into the registry (regedit.exe) something like this .REG file: Script 4 Creating a linked server to an ODBC source in this case will look like this:
    [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“





    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

    Check that everything works:

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

    Script 6


    Fig. 10

    Regardless of the method of creating a linked server, the further is obvious. We connect the client table in the database on the local server with the client table in the Azure SQL 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


    Fig. 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 access it by name with 4-private notation, so as not to drag the maximum record to the local server, but to parallelize the work if possible by filtering (joins, groups, etc., if they are on the other side) using remote resources.

    Also popular now: