Lesson Learned #129:Using Data Source External from Azure SQL Database to Azure SQL Managed Instance

Published Jun 05 2020 08:34 AM 2,360 Views

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!

1 Comment
Occasional Visitor

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. 

 

%3CLINGO-SUB%20id%3D%22lingo-sub-1443210%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23129%3AUsing%20Data%20Source%20External%20from%20Azure%20SQL%20Database%20to%20Azure%20SQL%20Managed%20Instance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443210%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20service%20request%20that%20our%20customer%20asked%20about%20how%20to%20connect%20from%20Azure%20SQL%20Database%20to%20Azure%20SQL%20Managed%20Instance.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWell%2C%20the%20first%20thing%20that%20I%20did%20is%20to%20enable%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fmanaged-instance%2Fpublic-endpoint-configure%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EPublic%20Endpoint%3C%2FA%3E%20of%20Azure%20SQL%20Managed%20Instance.%20After%20it%2C%20I%20followed%20up%20these%20steps%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3ECreate%20a%20table%20in%20Azure%20SQL%20Managed%20Instance%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Ecreate%20table%20Table1(id%20int)%0Ainsert%20into%20Table1%20(id)%20values(1))%0A%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EConnect%20to%20Azure%20SQL%20Database%20I%20execute%20the%20following%20command%20to%20create%20the%20External%20Data%20Source%20to%20Azure%20SQL%20Managed%20Instance%3A%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20MASTER%20KEY%20ENCRYPTION%20BY%20PASSWORD%3D'Password'%3B%0A%0ACREATE%20DATABASE%20SCOPED%20CREDENTIAL%20AppCredential%20WITH%20IDENTITY%20%3D%20'username'%2C%20SECRET%20%3D%20'Password'%3B%0A%0ACREATE%20EXTERNAL%20DATA%20SOURCE%20RemoteReferenceData%0AWITH%0A(%0ATYPE%3DRDBMS%2C%0ALOCATION%3D'tcp%3Aservername.public.virtualnetwork.database.windows.net%2C3342'%2C%0ADATABASE_NAME%3D'DatabaseName'%2C%0ACREDENTIAL%3D%20AppCredential%0A)%3B%0A%0ACREATE%20EXTERNAL%20TABLE%20Table1%0A(ID%20int%20)%0AWITH%0A(%0ADATA_SOURCE%20%3D%20RemoteReferenceData%0A)%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EFinally%2C%20running%20a%20SELECT%20*%20FROM%20Table1%20I%20was%20able%20to%20obtain%20the%20information%20from%20Azure%20SQL%20Managed%20Instance.%26nbsp%3B%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlso%2C%20during%20my%20test%2C%20I%20found%20the%20option%20to%20run%20DML%20command%20using%26nbsp%3B-ERR%3AREF-NOT-FOUND-sp_execute_remote%2C%20for%20example%2C%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3EEXEC%20sp_execute_remote%20N'RemoteReferenceData'%2C%20N'INSERT%20INTO%20Table1%20values(2)'%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy!%3C%2FP%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1443210%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20service%20request%20that%20our%20customer%20asked%20about%20how%20to%20connect%20from%20Azure%20SQL%20Database%20to%20Azure%20SQL%20Managed%20Instance.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1929751%22%20slang%3D%22en-US%22%3ERe%3A%20Lesson%20Learned%20%23129%3AUsing%20Data%20Source%20External%20from%20Azure%20SQL%20Database%20to%20Azure%20SQL%20Managed%20Inst%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1929751%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20works%20well%2C%20for%20a%20single%20site%20Managed%20Instance.%20What%20I%20have%20is%20a%20geo-located%20cluster%20and%20would%20like%20to%20ensure%20the%20requests%20coming%20from%20the%20Azure%20SQL%20are%20directed%20to%20the%20read%20only%20secondary%20cluster.%3C%2FP%3E%3CP%3EI%20have%20tried%20various%20things%20within%20the%20CREATE%20EXTERNAL%20DATA%20SOURCE%20STATEMENT.%3C%2FP%3E%3CP%3EExamples%3A%26nbsp%3BCONNECTION_OPTIONS%20%3D%20'ApplicationIntent%3DReadOnly'%3C%2FP%3E%3CP%3EAlternative%20adderess%20directly%20to%20the%20READ-ONLY%20LISTENER%20%3A%26nbsp%3Btcp%3AXXXXXXX.secondary.ZZZZZZZ.database.windows.net%2C3342%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENiether%20of%20these%20have%20been%20successful%20to%20date.%3C%2FP%3E%3CP%3EWorkAround%20is%20to%20reference%20the%20Secondary%20server%20instance%20directly.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Jun 05 2020 08:38 AM
Updated by: