Elastic querying on Azure SQL Database

Author – Moulshree S. D., Sr. Data Engineer

On-premises SQL Server does support three- or four-part names, but the Azure SQL Database does not. The Elastic Database query feature enables you to run a Transact-SQL query that spans multiple Databases in Azure SQL Database. The Database migration Assessment throwing error as “Cross-Database queries using three- or four-part names not supported in Azure SQL Database” can be eliminated using Elastic querying on Azure SQL Database.
In this document, we will resolve Cross-Database Queries in Azure SQL Database. An example of an on-premises Cross-Database query is given below:

In Azure SQL Database we will execute Cross Database queries. Pre-requisites are as below:

·        Login to Master Database.

·        User on the Database from which you need to query from.

·        Master Key on the Database on which External Tables will be created.

·        Grant Select access on the Database from which you need to query from on the table.

NOTE: Grant can be given by the command given below:

GRANT SELECT ON [SCHEMA_NAME]. [TABLE_NAME]TO USER_NAME;

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=azuresqldb-current#permissions-1

Execution of Cross Database queries:

Step 1: Create Database Scoped Credential

·        The Database Scoped Credential is used to access external location. External location could be your another Database. It is not mapped to any Server login or Database User.

STEP 2: Create External data source

·        The External Data Source will be pointed to the Database from which you will be querying the table.

·        The External data source TYPE will be RDBMS for Azure SQL Database.

·        Location points to the server location.

·        Credential contains the Database scoped credential which was created before.

·        Database name points to the Database from which the data will be queried from.

STEP 3: Create External Table

·        Identity columns are not supported while creating External Table.

STEP 4: Create two-part stored procedure to load data

·        The Stored procedure given below inserts into base table test13 and fetches data from the External Tables.

·        Meanwhile, External Tables fetch data from another Database using its data source as connection to the other Database.

·        This step involves any transformation or statements using data fetched from External Tables.

STEP 5: If source schema is altered.

·        If the source table is altered to add a column, then the External Table and Destination table also needs to be altered accordingly.

·        If any schema changes are been made, then the External Tables are not altered. However, they could be remade.

·        Destination and source tables are base tables therefore they can be altered.

·        First, we alter source and add a column as shown below:

·        Next, we script External Table as “Create to” and add the column to it as shown below:

·        Next, we will delete the External Table and run the SQL query to create External Table.

·        We will then alter the base table and add a column on the Database where we need to insert data into and run the stored procedure to load the base table.

This resolves the error Cross Database query using three- or four-part names. Now you can use three or four part names in Elastic query on Azure SQL Database.