Lesson Learned #156: Creating a Linked Server connecting to Read-Scale Managed Instance Replica

Published 01-19-2021 09:26 AM 939 Views

Today, a customer asked how to run a query against the read-scale replica using SQL Server Agent in a Azure SQL Managed Instance.

 

In the following example, I would like to share with you how to achieve this. 

 

In order to instruct how to use the read-scale feature I added the following parameter values for:

 

  • Data Source will be the name of the Managed Instance.
  • Provider String we added the value ApplicationIntent=Readonly
  • Catalog may be master or any database.

Capture.PNG

Finally, using, for example, the option OPENQUERY or running the query I was able to run my queries to the read-scale replica. 

 

 

SELECT * FROM MyExample.UserDatabaseName.SchemaName.Table WHERE Id=1

 

 

Enjoy!

 

 

%3CLINGO-SUB%20id%3D%22lingo-sub-2075326%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23156%3A%20Creating%20a%20Linked%20Server%20connecting%20to%20Read-Scale%20Managed%20Instance%20Replica%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2075326%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EToday%2C%20a%20customer%20asked%20how%20to%20run%20a%20query%20against%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fdatabase%2Fread-scale-out%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Eread-scale%20replica%3C%2FA%3E%20using%20SQL%20Server%20Agent%20in%20a%20Azure%20SQL%20Managed%20Instance.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EIn%20the%20following%20example%2C%20I%20would%20like%20to%20share%20with%20you%20how%20to%20achieve%20this.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EI%3CSTRONG%3En%20order%20to%20instruct%20how%20to%20use%20the%20read-scale%20feature%20I%20added%20the%20following%20parameter%20values%20for%3A%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSPAN%3E%3CSTRONG%3EData%20Source%26nbsp%3B%3C%2FSTRONG%3Ewill%20be%20the%20name%20of%20the%20Managed%20Instance.%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3E%3CSPAN%3E%3CSTRONG%3EProvider%20String%3C%2FSTRONG%3E%20we%20added%20the%20value%20%3CSTRONG%3EApplicationIntent%3DReadonly%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3E%3CSPAN%3E%3CSTRONG%3ECatalog%3C%2FSTRONG%3E%20may%20be%20master%20or%20any%20database.%20%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247501iD674CD6E858DEB96%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Capture.PNG%22%20alt%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EFinally%2C%20using%2C%20for%20example%2C%20the%20option%20OPENQUERY%20or%20running%20the%20query%20I%20was%20able%20to%20run%20my%20queries%20to%20the%20read-scale%20replica.%26nbsp%3B%3C%2FSPAN%3E%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%3ESELECT%20*%20FROM%20MyExample.UserDatabaseName.SchemaName.Table%20WHERE%20Id%3D1%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2075326%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20a%20customer%20asked%20how%20to%20run%20a%20query%20against%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fdatabase%2Fread-scale-out%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Eread-scale%20replica%3C%2FA%3E%20using%20SQL%20Server%20Agent%20in%20a%20Azure%20SQL%20Managed%20Instance.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20following%20example%2C%20I%20would%20like%20to%20share%20with%20you%20how%20to%20achieve%20this.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Version history
Last update:
‎Jan 19 2021 09:30 AM
Updated by: