SQL Server 2012链接服务器应用程序意图

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.

我需要针对SQL Server 2012可用性组创建链接服务器,并且我希望将所有请求路由到只读副本。但是,我无法确定如何指定ReadOnly Application Intent以确保将请求路由到正确的副本。

Has anyone sucessfully configured a linked server in this manner?

有没有人以这种方式成功配置链接服务器?

3 个解决方案

#1

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

我已尝试过两种方法,下面(来自Microsoft技术网站)可以使用

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';

#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.

在测试与数据库的链接服务器连接时,我发现即使在连接参数中指定ApplicationIntent = ReadOnly,我仍然在访问主数据库。

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:

经过进一步调查后,我发现造成这种情况的根本原因是因为与该登录相关联的默认数据库设置为“master”。这可以通过运行以下查询来测试:

 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]

#3

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.

我没有AlwaysOn可用性组来测试它,但您可以在通过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:

SQL Server 2012接受以下内容并成功创建一个可以工作的链接服务器,无论它是否支持您必须测试的ApplicationIntent属性,但它应该这样做,因为它设置为使用本机客户端作为提供者:

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'

#1