Data Platform Tips 24 - Azure SQL Database Elastic Query

Elastic Query on Azure SQL Database allow users to run T-SQL statements across multiple Azure SQL Databases. Mainly useful for creating reports using Business Intelligence tools with data spanning across multiple databases.

Elastic Query allows only read only querying of the remote databases and is supported both in Standard and Premium service tiers.

Elastic Query is best suited for reporting scenarios where majority of the processing happens at the external source and not suitable for ETL operations where large amounts of data needs to be pulled from the remote database.

In order to create an elastic query where data needs to be pulled from Database2 from Database1 you need perform the following steps on Database1.

a) Create Master Key on Database1
b) Create Database scoped credential on Database1
c) Create Elastic Query Data Source on Database1
d) Create External Table on Database1 with the schema similar to the table in Database2

Configure Databases for Cross Database Queries on Azure Portal

a) Logon to the Azure Portal.

b) Create a resource group named "ElasticQuery" and a SQL Server named "azuresqlserverdemo13".

c) Create 2 databases named "Database1" and "Database2".

d) Create a table named "Book" in Database1 and insert few records.

e) Create a table named "Author" in Database2 and insert few records.

f) Create master key on Database1.

g) Create Database Scoped Credential on 'Database1' with the credentials to access 'Database2'.

h) Create Elastic Query Source on 'Database1' with the details of 'Database2'.

g) Create External Table on 'Database1' with the schema similar to the table in 'Database2'.

h) Finally run a query joining tables from 'Database1' and 'Database2'.

As you have seen now you have the ability to join tables from 2 different databases using Elastic Query without the need of copying the data from one database to another. Really handy for reporting.