SQL Server 2012 linked server Application Intent

Question

I need to create a linked server against a SQL Server 2012 Availability Group and I want to have all requests routed to the read only replica. However, I have been unable to determine how I can specify the ReadOnly Application Intent in order to ensure that the request is routed to the correct replica.

Has anyone sucessfully configured a linked server in this manner?

Answer 1

I have tried both methods and the below (from the Microsoft tech site) works

EXEC sp_addlinkedserver 
@server = N'linked_svr', 
@srvproduct=N'SqlServer',
@provider=N'SQLNCLI11', 
@datasrc=N'AG_Listener_Name', 
@provstr=N'ApplicationIntent=ReadOnly', 
@catalog=N'MY_DB_NAME';

Answer 2

When testing a Linked Server connection to the database I found that I was still hitting the primary database even when specifying ApplicationIntent=ReadOnly in the connection parameters.

After further investigations I found that the root cause for this was because the default database associated with that login was set to "master". This can be tested by running the following query:

 sp_helplogins

To avoid this issue I now use the following connection parameters to ensure I am connecting to the database replica:

ApplicationIntent=ReadOnly;Database=database-db

Also, when connecting to a database via a linked server, please be sure to use the following query format:

 SELECT * FROM [server].[database].[scheme].[table]