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)'
Note about private endpoint:
You can also connect to your destination database using private link, when configured as Private Endpoint on your source VNET connection will be made using the private endpoint.
if you want to validate your connection was made using the private endpoing , you may query sys.dm_exec_connections and sys.dm_exec_sessions to find the session from the remote server the application name in use is "Microsoft SQL Server" and you can confirm the client_net_address.
Enjoy!
Microsoft