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:
- Master Key
- Protects the private keys of certificates and asymmetric keys in the database
- Database Scoped Credential
- Maps to a SQL Server login/user to access the external database
- External Data Source
- Establishes connectivity to the external database
- External Table
- Special type of local table that matches the structure of the external table we’re querying
- 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:
