How to create linked server between On-premise and Azure SQL data warehouse

In my previous post, l wrote about how to create linked server between On-premise and Azure SQL database.

In this post, let us see how to create linked server between On-premise and Azure SQL data warehouse and after establishing linked server, how we can query Azure SQL data warehouse tables from local On-premise SQL Server.

I have created Azure SQL data warehouse in my previous post and for this example I have created table "dwtable1" within azure sql dw.

Below are the steps to publish linked server connection between On-premise and azure sql dw.




From SSMS, connect to On-premise SQL Server. Goto Object Explorer -> Server Objects -> Linked servers

Linked Server - Linked server name. I have provided the linked server name as AZSQLDW.
Provider - SQL native client 11.0
Data source - Azure SQL Server detail
Catalog - Azure SQL data warehouse. I have created azure sql dw with name trnazsqldw




Provide Remote login & password (Azure SQL server authentication):



Now we can query the Azure SQL database from local On-premise SQL Server using four part naming convention as shown below:

SELECT * FROM Linkedservername.databasename.schemaname.tablename





--Select data in azure sql dw from on-premise sql server using four part naming convention
SELECT * FROM AZSQLDW.[trnazsqldw].dbo.dwtable1

--Insert data into azure sql dw from on-premise sql server using EXEC command
 EXEC ( 'INSERT [trnazsqldw].dbo.dwtable1 (Id)
         VALUES(1)' ) AT AZSQLDW;

--Update data in azure sql dw from on-premise sql server using four part naming convention
UPDATE AZSQLDW.[trnazsqldw].dbo.dwtable1 SET Id = 100
WHERE Id = 1

--Delete data in azure sql dw from on-premise sql server using four part naming convention
DELETE FROM AZSQLDW.[trnazsqldw].dbo.dwtable1
WHERE Id = 1


See Also:

No comments: