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 table Table1(id int)
insert into Table1 (id) values(1))
CREATE MASTER KEY ENCRYPTION BY PASSWORD='Password';
CREATE DATABASE SCOPED CREDENTIAL AppCredential WITH IDENTITY = 'username', SECRET = 'Password';
CREATE EXTERNAL DATA SOURCE RemoteReferenceData
CREATE EXTERNAL TABLE Table1
(ID int )
DATA_SOURCE = RemoteReferenceData
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)'
This works well, for a single site Managed Instance. What I have is a geo-located cluster and would like to ensure the requests coming from the Azure SQL are directed to the read only secondary cluster.
I have tried various things within the CREATE EXTERNAL DATA SOURCE STATEMENT.
Examples: CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly'
Alternative adderess directly to the READ-ONLY LISTENER : tcp:XXXXXXX.secondary.ZZZZZZZ.database.windows.net,3342
Niether of these have been successful to date.
WorkAround is to reference the Secondary server instance directly.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.