Create linked server to readable secondary replica in Managed Instance Business Critical service tier
Published Mar 23 2019 05:53 PM 8,938 Views
First published on MSDN on Aug 27, 2018
Azure SQL Database Managed Instance is a fully managed SQL Server Database Engine hosted in Azure cloud that provides most of the Azure SQL Pass and SQL Server functionalities, such as linked servers and built-in free secondary read-only replicas. In this post you will see how to use linked servers to connect to readable secondary replica.

Business Critical tier in Managed Instance provides free-of-charge read-only replica of the instance with all databases in read-only state that can be used for reporting purpose. In the following figure is shown one Business Critical instance that has one primary instance and three replicas where you can access read-only data in one instance using read-only endpoint:

You can connect to read-only replica using the same connection string that you use to access your primary instance and just add ApplicationIntent=ReadOnly in the connection string.

As an alternative, you can connect from your primary instance to your secondary instance using T-SQL if you create a linked server to the secondary instance. The following script creates a linked server to the secondary replica:
EXEC sp_addlinkedserver
@provstr = N'ApplicationIntent=ReadOnly',
@datasrc= @@SERVERNAME;
@@SERVERNAME will return fully-qualified domain name for Managed Instance and the same name is used to access secondary instance. The only thing that should be added is ApplicationIntent=ReadOnly in the provider string.

Once you create the linked server to secondary, you can query secondary instance using T-SQL. As an example, you can verify that secondary databases are read-only using the following query:

'SELECT DATABASEPROPERTYEX (''master'', ''Updateability'' ) ')

Also, you can use 4-part-name syntax to query any table or view on secondary instance:
FROM SECONDARY.master.sys.databases;
1 Comment
Version history
Last update:
‎Mar 23 2019 05:53 PM
Updated by: