Advanced Data Solutions : Cross database queries between Azure SQL database and Managed instance using external table

In this article we will cover how to perform cross queries between Azure SQL database and Azure SQL Managed Instance. This article will mainly focus on retrieving data from Azure SQL Managed Instance to Azure SQL database.

Since Azure SQL database does not support cross-database and cross-instance queries using three- or four-part names (Check this link for more information about unsupported T-SQL). Using cross queries with external tables can be an option for Azure SQL database to query data from another Azure SQL Managed Instance database and in this article you will find the required setup and steps.

Before we start, please take into consideration the below points:

Source database will be Azure SQL managed instance: we will be retrieving the database from Azure SQL managed database.
The destination database will be Azure SQL database: the Azure SQL database will contain the external table that will be connected to the Azure SQL managed instance using an external data source.
Please review the comments added to the below sample scripts as some of the queries needs to be executed on the master database and some on the user database.

Required step on destination database (Azure SQL database)

1) To start with the setup, we will create the required login, user, and credentials on Azure SQL database:

--master database
create login LgTest with password = 'STRONGP@123' 

-- user database 
create user User1 for login LgTest         
alter role [db_owner] add Member [User1]

-- user database 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'STRONGP@123'; 
GO

-- user database 
CREATE DATABASE SCOPED CREDENTIAL User1
WITH IDENTITY = 'LgTest', SECRET = 'STRONGP@123' -- this user needs to  have sufficient rights on your Azure SQL MI. 
GO

2) Create an external data source that will contain the connection to connect to your Azure SQL managed instance, and you can create it using the below script after adding your location (FQDN), database name, as well as the created CREDENTIAL previously:

CREATE EXTERNAL DATA SOURCE RemoteReferenceData    -- create the external data
WITH
( TYPE=RDBMS,
LOCATION='tcp:******.public.******.database.windows.net,3342',
DATABASE_NAME='*****',
CREDENTIAL= User1, -- the comma is important to work 
);

3) Create your external table as below, and please note that the external table structure should match with the original table in your Azure SQL managed instance:

-- the external table should be same as the table in your Azure SQL managed database. 

CREATE EXTERNAL TABLE [dbo].[DBtable](
[col1] [nvarchar](max) NULL,
[col2] [nvarchar](max) NULL,
[col3] [nvarchar](max) NULL) 
WITH
(
DATA_SOURCE= RemoteReferenceData
);

After these steps you should be able to see the created external table and external data source on your Azure SQL database as below:

Required step on source database (Azure SQL Managed instance)

The below scripts is required to create and allow the required login and user to access your Azure SQL managed instance:

-- Master database 
create login LgTest with password = 'STRONGP@123' 

-- user database 
create user User1 for login LgTest 
alter role [db_owner] add Member [User1]

Retrieving data using an external table

After completing the above steps, you will be able to retrieve data using the created external table.

Run the below script on your Azure SQL database (destination database)