Azure SQL Cross Database Query


By:   |   Updated: 2020-06-23   |   Comments   |   Related: More > 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] 
AmericanCars.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]
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
AmericanCars.dbo.Vehicles combined with JapaneseCars.dbo.Vehicles

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 [email protected]

-- 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 = '[email protected]';                    -- 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.

AmericanCars.dbo.Vehicles combined with JapaneseCars.dbo.Vehicles

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 = '[email protected]';                    -- 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:



Last Updated: 2020-06-23


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips
Related Resources





Comments For This Article





download


Recommended Reading

Adding Users to Azure SQL Databases

Azure Data Factory vs SSIS vs Azure Databricks

Azure Data Factory Pipeline Email Notification Part 1

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Continuous database deployments with Azure DevOps





get free sql tips
agree to terms


Learn more about SQL Server tools