This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
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:
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:
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:
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:
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)
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
I hope this article was helpful for you, please feel free to share your feedback in the comments section.