Azure SQL Database cross-database queries

In Azure SQL Database, you need to use Elastic Queries to run a query that access data from other databases, which is called cross-database queries. You probably have done it in SQL Server on-premises or IaaS, where you access tables from other databases by typing <database name>.<schema name>.<table name>.

In this post, I will show how to configure Elastic database query, please follow steps below:

  • Create a MASTER KEY:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
  • Create a DATABASE SCOPE CREDENTIAL (don’t include @servername):
CREATE DATABASE SCOPED CREDENTIAL <CREDENTIAL>
WITH IDENTITY = '<User Name>',
SECRET = '<Password>';
  • Create an EXTERNAL DATA SOURCE:
    1. In Location enter your Azure Server Name.

E.g.: ‘db.database.windows.net’

CREATE EXTERNAL DATA SOURCE <DATA SOURCE NAME>
WITH (
TYPE = RDBMS,
LOCATION = N'<Azure SQL DB Server Name>',
DATABASE_NAME = N'<Database Name>',
CREDENTIAL= <Credential - from Previous Step>);
  • Create an EXTERNAL DATA SOURCE:
CREATE EXTERNAL TABLE [dbo].[Supplier](
    [id] int NOT NULL,
    [name] nvarchar(200) NULL,
    [contact_name] nvarchar(100) NOT NULL,
    [contact_phone] nvarchar(20) NOT NULL,
    [street] nvarchar(200) NOT NULL,
    [city] nvarchar(50) NOT NULL,
    [state] nvarchar(20) NULL,
    [country] nvarchar(50) NOT NULL
)
WITH
(
     DATA_SOURCE = <EXTERNAL DATA SOURCE NAME>,
     SCHEMA_NAME = N'<SCHEMA NAME IN THE SOURCE DATABASE>',
     OBJECT_NAME = N'<TABLE NAME IN THE SOURCE DATABASE>'
);

SCHEMA_NAME and OBJECT_NAME clauses are optional, as it provides the ability to map the external table to a table in a different schema or table name.

Please see table below, for the first record, Schema_Name and Object_Name are not required as Schema and Table name are the same. For the second record Schema_Name and Object_Name are required as Schema and Table names are different.

Source Schema Source Table External Table Schema External Table Name Schema_Name Object_Name
dbo Location dbo Location Not Required Not Required
Sales Location dbo Sales_Location Sales Location

You can use the query below to retrieve a list of all external data sources:

select * from sys.external_data_sources;

Reference: https://azure.microsoft.com/en-au/blog/querying-remote-databases-in-azure-sql-db/

Thanks!

Leave a Reply

%d bloggers like this: