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.
@srvproduct=N’Azure SQL Db’,
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
@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 *
Example 2. Update data
UPDATE top(5000) A
SET A.Salary = A.salary * 1.1
FROM [LinkedServerName].[AzureDatabaseName].[dbo].[AzureTableName] A
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