Tips and Tricks : Creating Linked Server to an Availability Group Listener with ReadOnly routing
Posted by blakhani on September 30, 2014
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
Help: SQL Server 
Aman Ankit said
I feel like if we are making any changes in Linked Server. SQL Server instance needs to be restarted to make effect to the changes. Am i right?
blakhani said
Nope. Restart is not needed for linked servers change.
Aman Ankit said
Thanks.
Shivram said
Hi,
I have gone through the checklist and verified all the details.
However, I get the following error when I use my Availability group listener name in the @datasrc and corresponding @provstr. My listener is configured as
Name – AG1LISTENER
Port – 5022
I connect to it through SSMS as AG1LISTENER,5022
How do I pass this in the script?
Thank you,
Regards
VvK said
My LinkedServer that uses ReadOnly Replica stops working correctly AFTER the AlwaysOn Failover occurred, is this a bug? (If I Re-create the LinkedServer once the Failover happened , it start returning the correct Node/Replica again, but of course do not wont to be re-creating the LinkedServer after each Failover….
Coach James said
Sorry for the late response however, cannot help reply:
Linked Servers are independent server objects and therefore do not follow meta-data transaction updates like procedures and tables. Therefore, you must create the Linked Server on each secondary so that when the secondary becomes a primary you already have the linked server available on this server. VvK created a new linked server on the secondary after it become the primary on failover… It’s best to create the linked server on all secondaries at the same time it was created on the primary.
Consider using Master/Targets to accomplish this on hundreds of secondaries (exec one time) as needed. see https://docs.microsoft.com/en-us/sql/ssms/agent/make-a-master-server
pelya said
I had the same problem.
I decided so.
I use MSDASQL and System DSN or MSDASQL and Provider string when creating LinkedServer
For example:
–with System DSN (Before create LinkedServer create System DSN in OS)
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N’MyLinkedServer’
, @srvproduct=N”
, @provider=N’MSDASQL’
, @datasrc=N’MySystemDSN’
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’MyLinkedServer’,@useself=N’False’,@locallogin=NULL,@rmtuser=N’login’,@rmtpassword=’Password’
EXEC master.dbo.sp_serveroption @server=N’MyLinkedServer’, @optname=N’rpc’, @optvalue=N’true’
EXEC master.dbo.sp_serveroption @server=N’MyLinkedServer’, @optname=N’rpc out’, @optvalue=N’true’
GO
–without System DSN
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N’MyLinkedServer’
, @srvproduct=N”
, @provider=N’MSDASQL’
, @provstr=N’Driver={SQL Server Native Client 11.0};SERVER=MyListener;DATABASE=MyDatabase;ApplicationIntent=READONLY;MultiSubnetFailover=Yes’
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’MyLinkedServer’,@useself=N’False’,@locallogin=NULL,@rmtuser=N’login’,@rmtpassword=’Password’
EXEC master.dbo.sp_serveroption @server=N’MyLinkedServer’, @optname=N’rpc’, @optvalue=N’true’
EXEC master.dbo.sp_serveroption @server=N’MyLinkedServer’, @optname=N’rpc out’, @optvalue=N’true’
GO
blakhani said
Thanks for sharing this.
Jithendra said
I have created a linked server to AlwaysOn listener it was working fine. Failover happened last week, after that unable to see user databases under linked server catalog folder, system databases are visible.
Could you please help me to fix this.
GK said
It worked for me. Need to mention port number also along with listener name for @datasrc and an existing database name for Catalog.
blakhani said
Sure. Thanks for sharing this.
Ravinder said
Hi What is the impact of Linked Server from Primary SQL server having Aways On replica to Remote Oracle Database. How will it behave if the failover happens to replica ?
blakhani said
I am not sure if I understood your question. Where is the linked server created?
VKalenyk said
Hi,
Thanks a lot, very good and useful explication and script.
With you script the I’ve created successfully the linked server to listener with ApplicationIntent=ReadOnly, but when I test from developer station (not the same as server/instance where linked server was created) connected via RDP, I had the error :
Msg 18456, Level 14, State 1, Line 45
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.
If I test from server/instance where linked server was created
Could you help please? Is this error linked with double hop? What is the solution in this case?
VKalenyk said
Hi,
Thanks a lot, very good and useful explication and script.
With you script the I’ve created successfully the linked server to listener with ApplicationIntent=ReadOnly, but when I test from developer station (not the same as server/instance where linked server was created) connected via RDP, I had the error :
Msg 18456, Level 14, State 1, Line 45
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.
If I test from server/instance where linked server was created, it’s successful with the name of secondary readable node.
Could you help please? Is this error linked with double hop? What is the solution in this case?