SQL Server – Is @CATALOG Argument Required for Linked Server to Connect to Read-Only Secondary?

availability-groupslinked-serversql server

I have 1 primary and two secondaries. Node 3 is READ-ONLY. I created a linked server as follows (as described in Microsoft Docs):

EXEC sp_addlinkedserver 
@server=N'Node3', 
@srvproduct=N'', 
@provider=N'SQLNCLI', 
@provstr = N'ApplicationIntent=ReadOnly', 
@datasrc= 'AGListenerName';

When using this linked server it will always connect to Node 1 (primary). I verify my connection as follows:

SELECT *
FROM OPENQUERY([Node3],
SELECT DATABASEPROPERTYEX (''AnyDatabase'', ''Updateability'' ) ')

This returns: READ_WRITE

If I specify a database (catalog) in the linked server as follows:

EXEC sp_addlinkedserver 
@server=N'Node3', 
@srvproduct=N'', 
@provider=N'SQLNCLI', 
@provstr = N'ApplicationIntent=ReadOnly', 
@datasrc= 'AGListenerName',
@catalog= 'database';

The connection is made to the correct READ-ONLY secondary (Node 3). I verify in the same manner as above.

It returns: READ_ONLY

This is the same behavior in find in SSMS when creating a connection to a READ-ONLY instance.

Am I configuring the linked server incorrectly or is the @CATALOG argument required? Thanks.

Best Answer

Am I configuring the linked server incorrectly or is the @CATALOG argument required?

Regardless of the style in which you connect (SSMS, Linked Server, SQLCMD, JDBC Client, Whatever), there are a few things required in the connection string in order for Read Only Routing to properly work:

  • TCP/IP must be the protocol used
  • The listener name must be used (depending on version)
  • A database belonging to the AG must be in the connection string
  • Application Intent must be set to ReadOnly

Any deviation from the above will immediately result in read only routing not working. Thus, to answer your question, you need to somehow specify the default database as part of the connection string.

Related Question