Create linked server to readable secondary replica in Managed Instance Business Critical service tier

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 
@server=N'SECONDARY', 
@srvproduct=N'', 
@provider=N'SQLNCLI', 
@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 *
FROM OPENQUERY([SECONDARY],
'SELECT DATABASEPROPERTYEX (''master'', ''Updateability'' ) ')

 

Also, you can use 4-part-name syntax to query any table or view on secondary instance:

 SELECT *
FROM SECONDARY.master.sys.databases;