Linked server to readable secondary replica AG AON

Как прилинковать кластер AG к своему серверу.

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/08/27/create-linked-server-to-readable-secondary-replica-in-managed-instance-business-critical-service-tier/

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;