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
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
1 comment:
Very descriptive post, I enjoyed that a lot. Will there be a part 2?
Post a Comment