Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Connect an Azure SQL Database to an On-Premises SQL Server


By:   |   Read Comments (3)   |   Related Tips: More > Azure

Problem

Microsoft released the preview version of Azure SQL Database V12 in December of 2014. The main purpose of this version is to narrow the syntactical differences between the on premise and in-cloud database engines. The hope is that more companies will migrate their data to this software as a service platform.

In the future, we will be living in a hybrid world in which data resides not only on premise in our local data center, but in the cloud at a Microsoft installation. I cannot help but think about asking Weird Al Yankovic to re-write Madonna's 1984 hit song Material Girl to fit our situation?

As a database administrator, one might wonder: How can I easily transfer data back and forth between On Premise and Azure SQL databases?

Solution

Sometimes solutions to new problems lie in the answers to old ones. Microsoft has had the concept of a linked server since version 7.0 of the database engine. Many other database providers have similar concepts. For instance, Oracle has the idea of a Database Link to access external data sources.

To solve our hybrid data center problem, we will configure a linked server to enable the SQL Server Database Engine to execute commands against data sources outside of the local instance of SQL Server.

The following MSDN diagram shows the basic configuration of a linked server.



Linked Server Architecture

Business Problem

I can image in the future that a small publishing company might buy an on-line ordering system which was built with Microsoft Azure Technologies. Since the company has been in business for many years, they might have an on premise ERP system that runs on top of a SQL Server database. Your manager wants to have a daily feed of orders downloaded from the cloud into ERP system.

What is an easy way to accomplish this task without having to know a Extract, Transform, and Load (ETL) tool such as SQL Server Integration Services (SSIS)?

Windows Azure Logo

Custom Create Wizard

The article assumes that you have an Azure account with credits to spend.

The first step is to launch the custom create wizard to create a new database. This can be found by clicking the new button on the bottom left corner. Choose the data services, SQL Database and custom create options. You should see the pop up window below.

Custom Create Wizard - Page 1

For this demonstration, we are going to create a pubs database using my MSDN subscription. The default performance level is a standard database with a service level agreement of S0. This choice gives us 10 DTUs of processing power. Also, we can choose the collation of the database and whether or not we want a new database server. Every database server, which is a logical concept, has a maximum capacity of 2000 DTUS.

So what does this all mean to you?

A database in the cloud can live on any hardware rack or physical server in the Azure Data Center. You are charged by performance level which equates to how many DTU's you can consume. Database Throughput Units (DTU) are based on a blended measure of computer processing unit, memory, reads, and writes. It provides a way to describe the relative capacity and performance of the various database tiers (Basic, Standard, and Premium databases).

Each service tier and performance level combination has certain maximum values assigned. For instance, a S0 database has to be size less than or equal to 250 GB size, 60 concurrent logins, and 600 active sessions. The bench marking of this particular combination is 521 transactions per minute on average. At the time of writing this article, you will be charge $15 per month for a database of this caliber.

See books on line for more service tier information.

The second step is supply key information to create a new database server. The following pop up window is displayed if you choose the right arrow on the previous window.

Custom Create Wizard - Page 2

The login name and password are the very important. This account is equivalent to the [sa] account for a on premise mix security installation. The region determines which Azure data center the server and database are deployed to. Yes, leave the next two defaults. Select the check box to create the database and optional server right now.

Azure Management Portal

Important server or database level settings are managed via the portal. We can select the servers page to show our newly created server.

SQL Databases - Servers

The dashboard page for our server named o6s7przin7 has some interesting information. It tells us how many databases have been deployed and the remaining DTUs. If you forget your administrative login or password, this page can help. The name of the login is on this page. Also, there is a link to launch the reset password pop up window.

SQL Databases - Server Dashboard

The configure page controls the firewall for the Azure SQL database. If your development laptop is not white listed as a allowed IP, you will not be able to access your pubs database using SQL Server Management Studio. A date and time stamp are saved with the IP when granting access.

SQL Databases - Configure Server

Pubs Azure SQL Database

When SQL Server 2000 shipped to the public, two sample databases named northwind and pubs were included for developers to play with. You can still download the sample database scripts from the Microsoft site.

I am going to re-create the pubs database schema in Azure. Because Azure SQL databases do not support file commands, the source code had to be slightly changed to remove those statements. Also, the USE statement is not supported. But do not allow that to stop us. Just open a new connection to the correct database.

The below download file can be used to create your own pubs database on your Azure SQL Server.

So why did I do this?

I wanted to show that older, legacy databases can be moved to the cloud with little to no changes. If we were not using the V12 preview, the sample code would not work since heaps, tables without indexes, were not supported.

Legacy databases that stayed away from file base features are the easiest to migrate. Features like FILESTREAM, FILETABLE or FILEGROUP are not supported in Azure SQL Database. Please see this MSDN page for supported, partially supported and not supported features.

The database dashboard page shown below has some interesting information. It keeps track of deadlocks, failed/successful connections, storage usage and percent DTU over time.

SQL Databases - Database Dashboard

Enabling Distributed Queries in SQL Server

Unfortunately, we are only half done with this tip. All the information above was designed to set the background for the main event. We now have our on-line ordering system in the form of the pubs database.

Since linked servers can use ad hoc distributed queries, we should turn on that advance server level option.

Execute the code below to accomplish this task.

/*
    Turn on ad hoc distributed queries
*/
 
-- Just shows standard options
sp_configure;
GO
 
-- Turn on advance options
sp_configure 'show advanced options', 1;
GO
 
-- Reconfigure the server
reconfigure;
GO
 
-- Turn on ad hoc dist queries
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
 
-- Reconfigure the server
reconfigure;
GO

Remove an Existing SQL Server Linked Server

Many of the system stored procedures that deal with Linked Servers return tables as output. I took the liberty of creating table variables to store the output of such stored procedures. The next step determines if our linked server named MyAzureDb exists. If it does, we should drop any logins and the linked server definition.

The sp_linkedservers system stored procedure returns a listing of servers. The results are stored in a table variable named @LinkServers. The code searches through the table variable for our linked server name. If it exists, we drop the definition using the sp_dropserver system stored procedure.

Execute the code below to accomplish this task.

/*
    Remove existing linked server
*/

-- Table to hold server info
DECLARE @LinkServers TABLE 
(
    SRV_NAME sysname NULL,
    SRV_PROVIDERNAME nvarchar(128) NULL,
    SRV_PRODUCT nvarchar(128) NULL,
    SRV_DATASOURCE nvarchar(4000) NULL,
    SRV_PROVIDERSTRING nvarchar(4000) NULL,
    SRV_LOCATION nvarchar(4000) NULL,
    SRV_CAT sysname NULL
);

-- Are there any existing linked servers
INSERT INTO @LinkServers
    EXEC sp_linkedservers;

-- Show servers
SELECT * FROM @LinkServers;

-- Remove servers / logins
IF EXISTS (SELECT * FROM @LinkServers WHERE SRV_NAME like 'MyAzureDb')
    EXEC sp_dropserver 'MyAzureDb', 'droplogins';
GO

SQL Server Linked Server Creation

There are many OLE DB providers that can be called to connect to various data sources. Usually this involves the installation of a driver on the server. Since we want to connect to a SQL Server database in the cloud, we are going to use the Native Client driver named SQLNCLI as the @provider. This driver is installed as part of a normal on premise installation. Please see this MSDN page for more information.

The @server parameter is how we refer to the server and the @catalog parameter is the default database. Last but not least, we need to know the fully qualified DNS address of our Azure Server and the TCP/IP port number. This information is stored as the @datasrc parameter.

Calling the sp_addlinkedserver system stored procedure creates a definition for our cloud database. However, if we try to reference any tables in the pubs database using a four part notation, the call will fail. This is because the default security context of linked server is "make the connection using the security context of the current connection".

What does Azure know about my AD user account?

The missing piece of the puzzle is to change the security context to "make connection using known remote user and password". Calling the sp_addlinkedsrvlogin system stored procedure with a valid @rmtuser and @rmtpassword parameters fixes this issue. The sp_testlinkedserver system stored procedure can even be used to test the validity of our connection.

Execute the code below to create our linked server.

/*
    Create new linked server
*/

-- Make a link to the cloud
EXEC sp_addlinkedserver   
   @server=N'MyAzureDb', 
   @srvproduct=N'Azure SQL Db',
   @provider=N'SQLNCLI', 
   @datasrc=N'o6s7przin7.database.windows.net,1433',
   @catalog='pubs';
GO

--Set up login mapping
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = 'MyAzureDb', 
    @useself = 'FALSE', 
    @locallogin=NULL,
    @rmtuser = 'jminer',
    @rmtpassword = 'SQLtip$2015'
GO

-- Test the connection
sp_testlinkedserver MyAzureDb;
GO

Test the SQL Server Linked Server

Referring back to our business requirements, our boss wants to download sales data from the on-line ordering system. We know the data exists in the sales table in the pubs database.

The following SELECT query using our new linked server in a four part notation should do the trick. The output from the test is shown below.

SQL Databases - IP White List

The called failed to execute correctly. It is very import to remember that Azure SQL database has a built in firewall.

We developed the solution at work. But started our testing after we connected to the internet at home. Because we our using our home provider, we have a new TCP/IP address. If we go back to the Azure Server configuration page and white list this new address, we should be ready to go.

The output below shows the 21 sample records in the sales table.

SQL Databases - Select Sales Data

Our MyAzureDb linked server definition is located under server objects in SQL Server Management Studio. We can drill into this object and see the available providers. If we drill into our defined server, we can see all the tables and views that are defined in the pubs database.

SQL Databases - Define linked server

Local Staging Database

To round out this example, we need to create a local staging table in our on premise ERP database. The code below creates a local database using default file locations and growth/size options. A stage schema is created with a sales table. After executing the code, we are all ready to move data from the cloud to our on premise system.

/*
    Create local ERP database 
*/

-- Which database to use.
USE [master]
GO
 
-- Delete existing databases.
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'ERP')
    DROP DATABASE [ERP]
GO
 
-- Add new databases.
CREATE DATABASE [ERP] 
GO


/*
    Create stage Schema
*/

-- Which database to use.
USE [ERP]
GO
 
-- Delete existing schema.
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'STAGE')
    DROP SCHEMA [STAGE]
GO
 
-- Add new schema.
CREATE SCHEMA [STAGE] AUTHORIZATION [dbo]
GO

 
/*
    Create sales Table
*/

-- Delete existing table
IF  EXISTS (
    SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[STAGE].[SALES]') AND 
    type in (N'U'))
DROP TABLE [STAGE].[SALES]
GO

CREATE TABLE [STAGE].[SALES]
(
   stor_id char(4) NOT NULL,
   ord_num varchar(20) NOT NULL,
   ord_date datetime NOT NULL,
   qty smallint NOT NULL,
   payterms varchar(12) NOT NULL,
   title_id varchar(6) NOT NULL,
   CONSTRAINT PK_SALES PRIMARY KEY CLUSTERED (stor_id, ord_num, title_id)
)
GO

Manipulating Data

The key to using linked servers is to reference the table using a four part notation: server, database, schema and table names. The code below solves our initial problem. It moves all the sales data from the cloud to the on premise database. If this was a production system, we would use a mechanism such as a flag column to indicate new data to download.

/*
    Move data (cloud 2 local)
*/

-- Move data from cloud to on premise
INSERT INTO [ERP].[STAGE].[SALES]
SELECT * 
FROM [MyAzureDb].[pubs].[dbo].[sales];
GO

-- Show local copy of data
SELECT * FROM [ERP].[STAGE].[SALES]

To round out our discussion, Linked Servers are a two way street. The code below executes SELECT, UPDATE, DELETE and INSERT statements against the data in our pubs Azure SQL database. In summary, if you do not have time to create a SSIS package to move data from Azure to your local data mart, consider using a Linked Server.

/*
    CRUD statements against cloud data
*/

-- Read record
SELECT * FROM [MyAzureDb].[pubs].[dbo].[sales];

-- Update record
UPDATE S
SET ord_date = getdate()
FROM [MyAzureDb].[pubs].[dbo].[sales] S
WHERE 
  stor_id = 8042 and 
  ord_num = 'P723' and
  title_id = 'BU1111'

-- Delete record
DELETE
FROM [MyAzureDb].[pubs].[dbo].[sales] 
WHERE 
  stor_id = 8042 and 
  ord_num = 'P723' and
  title_id = 'BU1111'

-- Insert record
INSERT INTO [MyAzureDb].[pubs].[dbo].[sales] 
SELECT * 
FROM [ERP].[STAGE].[SALES]
WHERE 
  stor_id = 8042 and 
  ord_num = 'P723' and
  title_id = 'BU1111';

Next Steps
  • There are many more system stored procedures that retrieve meta data for linked servers.
    Since a linked server is a OLE DB provider, the target platform does not have to be SQL Server.
    How do I use the system stored procedures to understand my linked server data source?
  • One nice feature about Azure SQL database is that any client systems have to be white listed in the firewall.
    However, we did pass our credentials to the server in an unsecured format.
    What are the different ways in which a secured connection can be established with Azure SQL Database?


Last Update:






About the author
MSSQLTips author John Miner John Miner is currently a Microsoft Technology Solutions Professional (TSP) advising North East District (NED) corporations.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, September 08, 2015 - 9:23:09 AM - Dusnul Back To Top

Many thanks for the guidelines, I'll explore what you suggested :)

 


Monday, September 07, 2015 - 9:00:53 PM - John Miner Back To Top

Hi Dusnul,

Here is what is out there.

1 - SQL Server On premise supports linked servers.

2 - Azure SQL database does not support linked servers.

3 - The elastic stretch for Azure SQL database is just a implementation of sharding.

4 - The new stretch tables in SQL Server 2016 CTP 2.x allow you to have hot data on premise and cold data in Azure.

It handles queries on the on premise engine that select data from both instances (onsite & cloud).

It mashes up the results to present records to the user.

 

In short, none of these technologies will solve your problem.  Do not fret, there are two ways you can do this.

 

A - Duplicate the on-premise data to the cloud.  Have a SQL Agent job (TSQL Code) move the data in chunks up to Azure SQL Database.

Now, you just have another database to query from your Azure Web Application.

 

B - Use infrastructure as a service (IAAS).  Create a instance of SQL Server in a Azure VM.  This means linked servers are now available.

You can start down the path your were initially going.  Tie the IAAS server to the ON PREMISE one using a linked server.

 

One thing to remember, if you are querying small amounts of data, a linked server might give you the performance that is required by your application.  Larger queries might not perform well.

Also, a dedicated path way like Azure Express route will reduce some of the internet traffic competition that your request might encounter.

 

In short, good luck on the development.

 

Sincerely

John Miner

(The Crafty DBA)

 

 

 

 

 


Monday, September 07, 2015 - 4:26:36 AM - Dusnul Back To Top

Hi, thanks for this nice post.

but the title suggests that the connection is made from Azure to the on premises database. In fact this is what I need :)

I have an Azure SQL Server and an on premises SQL Server. I would like to select data in the on premises db from an app running in Azure.

I thought that I could create an Azure SQL Server and define a link to the on premises db, then the azure app would connect the Azure SQL Server and do queries in tables on the op remises db.

Do you know how to do it and if it's possible ?

Thanks

 


Learn more about SQL Server tools