Querying Cross Databases in Azure SQL

Querying Cross-Databases in Azure SQL Database

From past few days I am getting this question again and again that how to get data from multiple databases in Azure SQL Database. In other words, how to set up cross-database queries in Azure SQL Database. I am quite familiar with Cross-Database Queries in SQL Server as it is required in most of my projects, mostly for reporting purpose but the same process is not allowed in Azure SQL. Let’s have a look what we already have SQL Server:

Here I have two databases TestDBFirst and TestDBSecond within same SQL Server instance and both of them are accessible by same SQL user. In TestDBFirst, I have Employee and Salary tables but information about their Department is in TestDBSecond. However, I have DepartmentID in Employee table for Employee-Department mapping. Now have a look at below stored procedure definition

In this procedure I am using an INNER JOIN on two tables which are in different databases and execution of this procedure will give you a result like 

Now if you try the same thing in Azure SQL then you will get an error message like 

For achieving this in Azure SQL we need to use Elastic Query.

Evolution of Elastic Queries

The elastic query is a new feature which enables us to run a Transact-SQL query that spans multiple databases in Azure SQL Database. It allows us to perform cross-database queries to access remote tables. We need to use “sp_execute_remote” for the execution of external stored procedures or user defined functions or TSQL statements. Elastic queries can be of two types

       ·         Horizontal Queries: These are the queries that are executed across a set of databases which are of exactly same schema but can contain different data. Below diagram is for Horizontal Elastic Query

       ·         Vertical Queries: These are the queries which are executed across databases of different schema and different datasets. These queries can be executed across any two Azure SQL Database instances. Below diagram is for Vertical Elastic Query

Setting up Vertical Elastic Queries in Azure SQL Database

Let’s set up an Elastic Query that queries different tables in two different Azure SQL Database instance. In my example, I have same databases (TestDBFirst and TestDBSecond) which are on the same Azure SQL Server (They can be on different server also. However that does not make any effect in Elastic Queries). Now we need to follow these 5 steps for creating an elastic query

        ·         Create a Database User.

        ·         Create a Master Key.

        ·         Create a Database Scoped Credential.

        ·         Create an External Data Source.

        ·         Create an External Table.

Create a Database User

Create a database user and assign the role of “db_owner” or “db_datareader”.

You can also assign Execution access to any object (Stored Procedure, User Defined Function) to this resource like this 

Now, this user can execute “DecodingEncryptionKey” which is a UDF.

Create a Master Key

The master key will be used for protecting the Database Scoped Credential. You need to define a password for creating a master key which can be a combination of alpha-numeric with special characters. See below query

Create a Database Scoped Credential

This credential will contain the credentials which will be required for connecting to our external data source. Just pass you created a new user in below query 

Create an External Data Source

Create an external data source by using below query where

    o   TestData is External Data Source Name.

    o   Location is my server.

    o   TestDB is the name of my database.

    o   The credential is which we have created in the previous step.

Create an External Table

This statement specifies the external table which you want to query. In my case, I have created Department table in TestDBFirst.

After creating an external table, you should see the tables listed under the External Tables folder in SSMS. Now we are done with setting up Elastic Queries. Now we are ready to start running some cross-database queries.

Querying External Tables

Basically, there are two ways to query external tables in Azure SQL Database

          ·   Using SELECT statement

          ·   Using “sp_execute_remote” for executing SQL statement or external stored procedures.

Using SELECT statement

We can write a basic select statement using the external table just like we do with normal tables

Here Department is an external table.

Using “sp_execute_remote”

We can use “sp_execute_remote” for executing TSQL or stored procedure of remote database.

Use this for executing a stored procedure

Use this for execution of a TSQL statement

Using the same process we can execute cross-database queries from one Azure SQL Database to another.

Resources

For the documentation of Elastic Query visit this link https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-overview

For information on pre-requisites for vertical queries visit this link https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-vertical-partitioning