SQL Server 2012 Linked Server Application Purpose

Question

I need to create a linked server with SQL Server 2012 Availability Group, and I want all requests to be redirected to a read-only replica. However, I could not determine how I can specify the ReadOnly Application Intent to ensure that the request is redirected to the correct copy.

Has anyone successfully configured the linked server this way?

Answer 1

I tried both methods and below (from Tech Tech website) 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 database connection, I found that I still hit the main database even with ApplicationIntent = ReadOnly in the connection settings.

After further research, I found that the main reason for this was that the default database associated with this login was set to "master". This can be tested by running the following query:

  sp_helplogins 

To avoid this problem, I now use the following connection options to make sure that I connect to the database replica:

 ApplicationIntent=ReadOnly;Database=database-db 

In addition, when connecting to the database through a linked server, please use the following query format:

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

Answer 3

I do not have an AlwaysOn availability group to verify this, but you can specify the connection string when setting up the linked server via sp_addlinkedserver.

SQL Server 2012 accepts the following and successfully creates a linked server that works, does it distinguish the ApplicationIntent property, which you will need to test, but it should do since it is configured to use its own client as a provider:

 sp_addlinkedserver @srvproduct = 'DB', --Don't use 'SQL Server' otherwise it won't let you set any properties @server = 'LINKED-SERVER-NAME', @provider = 'SQLNCLI', @provstr = 'Data Source=SERVER\INSTANCE;Initial Catalog = Database;ApplicationIntent=ReadOnly'