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. – Shooter McGavin May 24 '18 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 '18 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. – Shooter McGavin May 31 '18 at 21:34
  • ServerA is 11.0.6523.0 (SP3-CU2) and ServerY and Z are 12.0.5000.0 (SP2). – AdamM Jun 4 '18 at 4:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.