Today, I worked on a service request that our customer asked about how to connect from Azure SQL Database to Azure SQL Managed Instance. 

 

Well, the first thing that I did is to enable the Public Endpoint of Azure SQL Managed Instance. After it, I followed up these steps:

 

  • Create a table in Azure SQL Managed Instance

 

 

 

create table Table1(id int)
insert into Table1 (id) values(1))

 

 

 

  • Connect to Azure SQL Database I execute the following command to create the External Data Source to Azure SQL Managed Instance:

 

 

 

CREATE MASTER KEY ENCRYPTION BY PASSWORD='Password';

CREATE DATABASE SCOPED CREDENTIAL AppCredential WITH IDENTITY = 'username', SECRET = 'Password';

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH
(
TYPE=RDBMS,
LOCATION='tcp:servername.public.virtualnetwork.database.windows.net,3342',
DATABASE_NAME='DatabaseName',
CREDENTIAL= AppCredential
);

CREATE EXTERNAL TABLE Table1
(ID int )
WITH
(
DATA_SOURCE = RemoteReferenceData
);

 

 

 

  • Finally, running a SELECT * FROM Table1 I was able to obtain the information from Azure SQL Managed Instance. 

 

Also, during my test, I found the option to run DML command using sp_execute_remote, for example, 

 
EXEC sp_execute_remote N'RemoteReferenceData', N'INSERT INTO Table1 values(2)'

 

Enjoy!