Overview

Azure SQL Database does not support Linked Server, however there is something similar called External Data Source that works in a very similar fashion, allowing to easily query other Azure SQL Databases.

Querying Remote Azure SQL Databases

Elastic database query provides easy access to tables in remote Azure SQL Databases, in order to be included in your queries. First we need to create and store the remote credentials as follows:

 

CREATE MASTER KEY ENCRYPTION BY PASSWORD  = 'p@$$w0rd'
 
CREATE DATABASE  SCOPED CREDENTIAL db_analyst  WITH IDENTITY = 'db_analyst'
 
SECRET = 'p@$$w0rd';
 
Now we can create the External Data Source, that will use the previous credential to access a specific database in a remote Azure SQL Database server:
 
 
 
 
CREATE EXTERNAL DATA SOURCE RemoteData
 
WITH
 
(
 
 TYPE=RDBMS,
 
 LOCATION='remoteserver.database.windows.net',
 
 DATABASE_NAME='RemoteDB',
 
 CREDENTIAL= db_analyst
 
);
 
Finally we can use the external Data Source to define a new External Table as follows:
 
 
 
CREATE EXTERNAL TABLE [dbo].[country](
 
  
 
 [country_code] int  NOT NULL
 
 [country_name] nvarchar(256) NULL,
 
 [country_continent] nvarchar(20) NULL
 
 )
 
 WITH
 
 (
 
 DATA_SOURCE = RemoteData
 
 );
 
And now the new external table can be queried as it were a local table or view, and eventually be joined / inserted into local tables:
 
 
 
SELECT *
 
FROM  [dbo].[country]

Flexible Naming

As you may have noticed, there is a risk to create an external table with the same name of a local table. For cases likes this, or just if you want to use a different name for the external table, flexible naming option is available.

In the example below the remote table [dbo].[country] will now be created as [dbo].[remote_country], avoiding any name clash, or just to rename as desired:


CREATE EXTERNAL TABLE [dbo].[remote_country](
 
  
 
 [country_code] int  NOT NULL, 
 
 [country_name] nvarchar(256) NULL,
 
 [country_continent] nvarchar(20) NULL
 
 )
 
 WITH
 
 (
 
 DATA_SOURCE = RemoteData
 
 ,SCHEMA_NAME = 'dbo'
 
 ,OBJECT_NAME = 'country'
 
 );

Summary

External Data Sources feature allows to easily query remote Azure SQL Server Databases, in a similar way than the on-premise Linked Server feature. I will love to hear if it works for you as well.