I have been given the task of setting up a read-only linked server on ServerA to Availability Group listener ListnerX. ListnerX points to ServerY or ServerZ (ServerY is usually the primary). Read-only routing URLs and priority lists have been set up for ServerY and ServerZ. When I create the linked server on ServerA using this command...

Exec master.dbo.sp_addlinkedserver
@server = N'ListnerX',
@srvproduct = N'SQL',
@provider = N'SQLNCLI11',
@datasrc = N'ListenerX,1433',
@provstr = N'ApplicationIntent=ReadOnly',
@catalog = N'TestDatabase'

... and then run...

select * from openquery([ListenerX], 'select @@servername')

...I get...


...which is not what I'm expecting since it is the primary replica.

However, when I create the same ListenerX linked server on ServerY itself using the above command, I get...


...which is what I'm expecting since it is the readable secondary replica.

ServerA lives in a separate zone of our network from ServerY and ServerZ. From ServerA I can definitely connect to ListenerX, but I don't know what network restrictions exist between the two zones.

Any ideas why the read-only linked server on ServerA to ListenerX doesn't route correctly, but the one on ServerY does?

  • What provider are you using for the Linked Server? As far as I know, the only one that supports the application intent connection switch is SQL Native Client 11.0. Earlier versions won't work either. May 24, 2018 at 22:33
  • I'm using the SQL Server Native Client 11.0 provider. If it makes any difference, ServerA is SQL Server 2012, but SQL ServerY and Z are SQL Server 2014.
    – AdamM
    May 29, 2018 at 0:59
  • The client provider version definitely matters. It might help to include the SQL Serever patch level for each server as well. There could be some issues that might be resolved with an update. May 31, 2018 at 21:34
  • ServerA is 11.0.6523.0 (SP3-CU2) and ServerY and Z are 12.0.5000.0 (SP2).
    – AdamM
    Jun 4, 2018 at 4:09


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.