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)
select * from [dbo].[DBtable]
sample result
More information
CREATE EXTERNAL TABLE (Transact-SQL) - SQL Server | Microsoft Docs
CREATE EXTERNAL DATA SOURCE (Transact-SQL) - SQL Server | Microsoft Docs
Cross-Database Queries in Azure SQL Database | Azure Blog and Updates | Microsoft Azure
Additional note
This blog is focusing on retrieving data from Azure SQL Managed Instance to Azure SQL database. In case your goal is to retrieve data from Azure SQL database to Azure SQL managed instance, please consider using linked servers and you can refer to this blog article for more information: Lesson Learned #63: It is possible to create Linked Server in Azure SQL Managed Instance? - Microsof...
I hope this article was helpful for you, please feel free to share your feedback in the comments section.