Azure SQL Cross Database Query

By: Joe Gavin   |   Comments (9)   |   Related: > Azure

Problem

You're running a query in one database and need to incorporate data from another database, but you're using Azure SQL Database that doesn't allow cross database querying. The requirement is to be able to pull data from a table in one database and combine it into a list pulled from a table in another database into one result set.

Solution

We can use the Azure SQL Database Elastic Query feature to run read only queries against one or more external databases.

Versions used in this tip:

• SQL Server Management Studio 18.5
• Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) - 14.0.3294.2 (X64) Mar 13 2020 14:53:45 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 17763: ) (Hypervisor)
• Microsoft SQL Azure (RTM) - 12.0.2000.8 Apr 9 2020 16:39:55 Copyright (C) 2019 Microsoft Corporation

Querying Cross Databases in On Premises SQL Server

To get started, let's first look at a simple example of a cross database query on a traditional on-premises SQL Server.

We have two databases, one called AmericanCars and another called JapaneseCars that, as the names imply, hold data on American and Japanese Cars respectively. Both have a table called Vehicles that hold the year, make, and model of the vehicles.

We are working in the AmericanCars database and need to incorporate data from the Vehicles table in the JapaneseCars database.

From the AmericanCars database we query the Vehicles table.

-- select from AmericanCars 
SELECT [Year]
     , [Make]
     , [Model] 
FROM [dbo].[Vehicles] 

Now, while still in the AmericanCars database, we query the Vehicles tables in JapaneseCars by preceding [dbo].[Vehicles] with the external database name [JapaneseCars] so our FROM looks like this: [JapaneseCars] .[dbo].[Vehicles].

-- select from external database
SELECT [Year]
     , [Make]
     , [Model] 
FROM [JapaneseCars].[dbo].[Vehicles]

Now, to get a list of Vehicles from both tables, we can combine the output of both with a UNION ALL and we get one list.

-- select from AmericanCars 
SELECT [Year]
     , [Make]
    , [Model] 
FROM [dbo].[Vehicles] 
 
UNION ALL 
 
-- select from external database
SELECT [Year]
     , [Make]
    , [Model] 
FROM [JapaneseCars].[dbo].[Vehicles] – preceded with external database name

Querying Cross Databases in Azure SQL Database

But, if we attempt the same query with the same two database names and the same table names in Azure SQL Database we get the message “Msg 40515, Level 15, State 1, Line 16 Reference to database and/or server name in 'JapaneseCars.dbo.Vehicles' is not supported in this version of SQL Server.”.

Configure Azure Elastic Query

Now, it's time to configure Elastic Query in the following five steps in order as each one builds on the prior steps:

1. Master Key
   • Protects the private keys of certificates and asymmetric keys in the database
2. Database Scoped Credential
   • Maps to a SQL Server login/user to access the external database
3. External Data Source
   • Establishes connectivity to the external database
4. External Table
   • Special type of local table that matches the structure of the external table we're querying
5. Test
   • This is how we know if we did it all correctly

Step #1 - Create Master Key

CREATE MASTER KEY;
GO

Step #2 - Create a Database Scoped Credential

Create a credential called CrossDb_cred that will map to the SQL Server login CrossDb, that maps to a user with read permissions in our external database and it has a password of Str0ngP@ssword.

-- credential maps to a login or contained user used to connect to remote database 
CREATE DATABASE SCOPED CREDENTIAL CrossDbCred -- credential name
WITH IDENTITY = 'CrossDb',                    -- login or contained user name
SECRET = 'Str0ngP@ssword';                    -- login or contained user password
GO

Step #3 - Create an External Data Source

Create a data source that will use the Credential we just created to connect to the external database on Azure SQL Database server called server.database.windows.net.

-- data source to remote Azure SQL Database server and database
CREATE EXTERNAL DATA SOURCE JapaneseCars
WITH
(
    TYPE=RDBMS,                             -- data source type
    LOCATION='server.database.windows.net', -- Azure SQL Database server name
    DATABASE_NAME='JapaneseCars',           -- database name
    CREDENTIAL=CrossDbCred                  -- credential used to connect to server / database  
);
GO

Step #4 - Create an External Table

Create a table that maps to the table in the external database we want to query.

-- external table points to table in an external database with the identical structure
CREATE EXTERNAL TABLE [dbo].[JapaneseCars_Vehicles]
(
   [Year] [varchar](4),
   [Make] [varchar](20),
   [Model] [varchar](20)
)
WITH (DATA_SOURCE = [JapaneseCars],  -- data source 
      SCHEMA_NAME = 'dbo',           -- external table schema
      OBJECT_NAME = 'Vehicles'       -- name of table in external database
     );
GO

Step #5 – Test

Now we can test our new Elastic Query by making one change to our original query.

-- test 
SELECT [Year]
     , [Make]
    , [Model] 
FROM [dbo].[Vehicles] 
 
UNION ALL 
 
SELECT [Year]
     , [Make]
    , [Model] 
FROM [dbo].[JapaneseCars_Vehicles] -- external table name 

And here is the combined output of both our internal and external tables.

Complete Script to Configure Azure Elastic Query

This is the complete script with all five steps.

-- protects private keys
CREATE MASTER KEY; -- create master key
GO
 
-- credential maps to a login or contained user used to connect to remote database 
CREATE DATABASE SCOPED CREDENTIAL CrossDbCred -- credential name
WITH IDENTITY = 'CrossDb',                    -- login or contained user name
SECRET = 'Str0ngP@ssword';                    -- login or contained user password
GO
 
-- data source to remote Azure SQL Database server and database
CREATE EXTERNAL DATA SOURCE JapaneseCars
WITH
(
    TYPE=RDBMS,                           -- data source type
    LOCATION='server.database.windows.net', -- Azure SQL Database server name
    DATABASE_NAME='JapaneseCars',         -- database name
    CREDENTIAL=CrossDbCred                -- credential used to connect to server / database  
);
GO
 
-- external table points to table in an external database with the identical structure
CREATE EXTERNAL TABLE [dbo].[JapaneseCars_Vehicles]
(
    [Year] [varchar](4),
   [Make] [varchar](20),
   [Model] [varchar](20)
)
WITH (DATA_SOURCE = [JapaneseCars],  -- data source 
      SCHEMA_NAME = 'dbo',           -- external table schema
      OBJECT_NAME = 'Vehicles'       -- name of table in external database
    );
GO
 
-- test 
SELECT [Year]
     , [Make]
    , [Model] 
FROM [dbo].[Vehicles] 
 
UNION ALL 
 
SELECT [Year]
     , [Make]
    , [Model] 
FROM [dbo].[JapaneseCars_Vehicles] -- external table name 
GO

Next Steps

Here are some links with further information:

• SQL Azure Cross Database Querying
• Cross-database query performance in Azure SQL Database
• CREATE MASTER KEY (Transact-SQL)
• CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
• CREATE EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips