Azure SQL Database: Using External Data Sources

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   = '';  
 
CREATE DATABASE   SCOPED CREDENTIAL db_analyst  WITH IDENTITY = 'db_analyst',  
 
SECRET = '';
 
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.