create linked server in azure sql database

Question

Want to create linked server in azure sql. I want to use local DB views in sql azure using linked server. Is it possible or is there any alternate way. All suggestions are welcome.

Answer 1

yes you can create linked servers in SQLAZURE...Assuming you have local on premises server A and database in azure say AZ_b..you can create a linked server for azure on your local on premise instance...

since you want to do this I want to use local DB views in sql azure using linked server. after creating linked server you need to run queries from server A which is your local onpremises server and this is the only way name resolution of linked server can happen and you can't do the other way

below are the steps

-- Supporse your database on Azure is named 'Azure_Test_DB' 
EXEC sp_addlinkedserver   
@server='myLinkedAzureServer', -- specify the name of the linked server   
@srvproduct='',        
@provider='sqlncli', 
@datasrc='azure-test-db.database.windows.net',   -- add here your server name   
@location='',   
@provstr='',   
--------Change it by your need ------------------------------------------------------------------ 
@catalog='Azure_Test_DB'  -- specify the name of database on your Azure DB you want to link 
------------------------------------------------------------------------------------------------- 

-- Configure credentials for Azure linked server 
EXEC sp_addlinkedsrvlogin   
@rmtsrvname = 'myLinkedAzureServer',   
@useself = 'false',   
--------Change it by your need ------------------------------------------------------------------ 
@rmtuser = 'yourLoginName',   -- add here your login on Azure DB   
@rmtpassword = 'yourPassword' -- add here your password on Azure DB   
------------------------------------------------------------------------------------------------- 

-- Configure options for Azure linked server 
EXEC sp_serveroption 'myLinkedAzureServer', 'rpc out', true;   


-- Now you can query the data using 4-part names   
select * from myLinkedAzureServer.[Azure_Test_DB].[dbo].[Students]; 

once you create linked server , you can connect to server A and can run below queries

select * from 
myLinkedAzureServer.[Azure_Test_DB].[dbo].[Students] az
join
localdb.dbo.table1 tbl
on tbl.somecol=az.somecol

References:
https://gallery.technet.microsoft.com/scriptcenter/How-to-create-linked-cb98fa7d
https://www.mssqltips.com/sqlservertip/3630/connect-an-azure-sql-database-to-an-onpremises-sql-server/

The above steps works for most of machines..incase it doesn't work, you need to setup a ODBC DSN by following the steps here..

https://blogs.msdn.microsoft.com/sqlcat/2011/03/07/linked-servers-to-sql-azure/

Answer 2

You cannot create a linked server in Azure SQL Database.

Microsoft does have a preview available of a new Instance as a Service offering that will allow you to perform cross database queries. It may also allow for linked servers. It's not yet widely available.

Now, you can use Elastic Query to set up something pretty similar. Here's a blog post I wrote on it. However, it's not the same as a linked server.