8

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?

chad
  • 564
  • 1
  • 4
  • 16

4 Answers4

6

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';
Philip Robinson
  • 61
  • 2
  • I created the linked server. However, when I execute queries against the linked server, I am receiving this error message and I have no idea what it means. The OLE DB provider "SQLNCLI11" for linked server "My Linked Server Name" supplied inconsistent metadata for a column. The column "My Column Name" (compile-time ordinal 1) of object ""Database Name"."Schema Name"."Object Name"" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time. – chad Oct 31 '13 at 20:48
  • I was able to get around the issue above with the assistance of this post - http://stackoverflow.com/questions/1179337/linked-sql-server-database-giving-inconsistent-metadata-error. However, in watching SQL Profiler, SQL statments executed against the linked server are still hitting the primary replica and not the read only secondary. – chad Nov 01 '13 at 19:42
3

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]
Steve Lord
  • 31
  • 2
0

I Don't have an AlwaysOn availability group to test this on, but you can specify a connection string when setting up a linked server via sp_addlinkedserver.

SQL Server 2012 accepts the following and successfully creates a linked server that works, whether it honours the ApplicationIntent property you will have to test, but it should do as it is set to use the native 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'
steoleary
  • 8,968
  • 2
  • 33
  • 47
  • According to this (http://technet.microsoft.com/en-us/library/hh403414.aspx), you can set the provstr parameter for a native SQL Server linked server. – Ben Thul Sep 18 '13 at 17:16
0

https://learn.microsoft.com/en-us/windows-server/networking/core-network-guide/cncg/server-certs/create-an-alias-cname-record-in-dns-for-web1

I have used CNAME for READONLY NODE. sql AO or windows cluster its very useful for readonly servers. EX: Your second node server name is SERVERB, you redirect it to alias name as below. SERVERB ----- >SERVERB.corp.contoso.com.

Then from the SSMS check if you can connect, or ping the alias name from the command promt screen if it works.

ping SERVERB.corp.contoso.com

cunay
  • 61
  • 6