In my recent interaction with customer, I was asked if linked server can be created to an availability group listener? My immediate response was “Yes”. Next question was immediately thrown at me that if they can redirect this listener to secondary replica? This time my little delayed response was – Yes. Provided we use proper connection string. Here is the linked server creation script

 EXEC sp_addlinkedserver @server = N'MyLinkedServer'
    ,@provider = N'SQLNCLI'
    ,@srvproduct = N'SqlServer'
    ,@datasrc = N'MyListener'
    ,@provstr = N'ApplicationIntent=ReadOnly'

…and it was not working as they expected.

Whenever we want to use routing of read-only connection via listener and connections are not going to secondary replica then below checklist should help.

Here is the checklist which I have created.

• Verify that we are connecting to Listener Name.
• Verify that the Read-Only routing list (READ_ONLY_ROUTING_LIST) is defined.
• Verify that the Routing URL (READ_ONLY_ROUTING_URL) of each instance has a proper FQDN and port combination.
• Verify that ApplicationIntent is specified in the connection string.
• Verify that the Sync_State is SYNCHRONIZED or SYNCHORNIZING for the secondary replica
• Verify that the secondary replicas are set to allow connections
• Verity that the initial catalog is provided in connection string.

If complete checklist is followed, it should resolve most of the routing problems. Most common mistake which I have seen is to miss creating routing URL and routing list. This may be due to the fact that there is no User Interface available and it has to be done by T-SQL.

Coming back to original question – I asked them to use proper connection string while creating listener. They followed the checklist and they have missed last item the initial catalog. Here is my version of the script.

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer'
    ,@srvproduct = N'SQL'
    ,@provider = N'SQLNCLI11'
    ,@datasrc = N'MyListener'
    ,@provstr = N'Integrated Security=SSPI;Initial Catalog=Production;Data Source=MyListener;ApplicationIntent=ReadOnly'
    ,@catalog = N'Production'
GO

How would you test whether the connection is working fine or not? That’s simple.

select * from openquery(MyLinkedServer,'select @@servername')

If Server name is returned as secondary replica then routing is working perfectly fine. If you are getting primary replica then routing has a problem. The best way to troubleshoot further would be to use SQLCMD and follow the checklist provided earlier in this blog.

sqlcmd -S MyListener -E -d Production -K ReadOnly

Hope this helps!

P.S. Checklist is explained in detail in my book SQL Server 2012 AlwaysOn – Paperback, Kindle