Connect an on-Premises SQL Server Database with an Azure SQL Database Using a Linked Server

Azure SQL database, also known as a platform as a service (Paas) or a database as a service (DBaas), is designed for software-as-a-service application development. Although you can move all your data into the Azure SQL database, there are times that one wants to connect a Azure SQL database to an on-Premises SQL server database.

Linked servers provide a powerful tool to access data from another instance of SQL server database. In this blog, I want to show you that you can also use a linked server to access data reside in Azure SQL database from your on-premises SQL server database.

Create a Linked Server

There are two steps to establish a connection with Azure SQL database.

Step 1. Create a linked server.  We can create a linked server using sp_addlinkedserver stored procedure.  To execute this stored procedure, you essentially need to give four parameters.

  • LinkedServerName. You can give any name as long as it is legal and unique.
  • AzureServerName. You can easily find it on your azure portal. An Azure server name has four parts ending with ‘.database.windows.net’.
  • PortNumber, usually is 1433.
  • AzureDatabaseName

EXEC sp_addlinkedserver
@server=N’LinkedServerName’,
@srvproduct=N’Azure SQL Db’,
@provider=N’SQLNCLI’,
@datasrc=N’AzureServerName, PortNumber’,
@catalog=’AzureDatabaseName’;

Step 2. Set up login mapping. We can use sp_addlinkedsrvlogin stored procedure. To execute this stored procedure, you need to give three parameters.

  • LinkedServerName. The one you just created in step 1
  • UserName.
  • Password.

EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘LinkedServerName’,
@useself = ‘FALSE’,
@locallogin = NULL,
@rmtuser = ‘UserName’,
@rmtpassword = ‘Password’

Test a Linked Server

After executing the above two steps, you can test your linked server by executing sp_testlinkedserver stored procedure. This stored procedure require one parameter which is the LinkedServerName you just created. If success, you can see “command(s) completed successfully” message in the SQL Server Management Studio.

EXEC sp_testlinkedserver LinkedServerName;

Use a Linked Server 

Example 1. Read data

SELECT TOP 10 *
FROM [LinkedServerName].[AzureDatabaseName].[dbo].[AzureTableName]

Example 2. Update data

UPDATE top(5000) A
SET A.Salary = A.salary * 1.1
FROM [LinkedServerName].[AzureDatabaseName].[dbo].[AzureTableName] A

Summary

In this blog, I have shown you how to create a linked server which connects to an Azure SQL database. Basically, we need to execute two stored procedures: sp_addlinkedserver and sp_addlinkedsrvlogin. We can also test the connection with another stored procedure called sp_testlinkedserver. I have also included two simple examples which show you how to use a linked server to read and update an Azure SQL database table. For more information, please check Microsoft Azure Documents

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s