Readonly Routing List在AlwaysOn功能中不起作用 [英] Readonly Routing List is not working in AlwaysOn feature
问题描述
大家好,
我们正在开始在AlwaysOn设置上加载Readonly操作。
We are exlploring off loading the Readonly operations on AlwaysOn setup.
我们目前有一个AlwaysOn设置on 1 Primary(AlwaysOn1Serv1)& 2个活性只读次级(AlwaysOn2Serv2&安培; AlwaysOn3Serv3)。服务器
We currently have a AlwaysOn set up on 1 Primary(AlwaysOn1Serv1) & 2 active read only secondaries (AlwaysOn2Serv2 & AlwaysOn3Serv3) servers.
在 与应用访问我们AlwaysOn的监听器(ALywasOnAVG_LIstener)意图为只读, 连接不会路由到任何辅助实例。相反,所有只读操作都由主服务器提供。
On accessing our AlwaysOn listener ( ALywasOnAVG_LIstener) with Application intent as Read only, the connection is not routed to none of the secondary instances. Instead, all the read Only opearations are served by Primary server.
请在下面找到配置的详细信息。
Please find the details of the configurations below.
我们定义了Readonly Routing列表如下所示。
We defined the Readonly Routing list as below.
更改可用性组AlwaysOnAVG 修改'ALwaysOn3Serv3'with上的REPIFICA(Secondary_ROLE(READ_ONLY_ROUTING_URL ='TCP://ALwaysOn3Serv3.sqlsolutions.com:5022'))
ALter Availability Group AlwaysOnAVG MODIFY REPLICA ON 'ALwaysOn3Serv3' WITH ( Secondary_ROLE ( READ_ONLY_ROUTING_URL='TCP://ALwaysOn3Serv3.sqlsolutions.com:5022'))
在AlwaysOn2Serv2上执行相同的命令以及相应的值。
Same command executed on AlwaysOn2Serv2 as well with respective values.
现在我们运行以下命令来定义seocndary实例上的只读首选项
Now we ran the below command to define the readonly preference on seocndary instances
更改可用性组ALwaysOnAVG MODIFY REPLICA ON'Á lwaysOn1Serv1'
Alter Availability Group ALwaysOnAVG MODIFY REPLICA ON 'ÁlwaysOn1Serv1'
WITH(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST =('AlwaysOn3Serv3','AlwaysOn2Serv2')))
WITH ( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST=('AlwaysOn3Serv3','AlwaysOn2Serv2') ))
但是当我们尝试使用应用程序意图访问我们的AlwaysOn监听器(ALywasOnAVG_LIstener)时作为只读, 连接不会路由到任何辅助实例。 。相反,所有只读opearations由主服务器提供
But when we tried accesing our AlwaysOn listener ( ALywasOnAVG_LIstener) with Application intent as Read only, the connection is not routed to none of the secondary instances. Instead, all the read Only opearations are served by Primary server.
连接字符串我们使用: -
Connection string we used :-
sqlProvider的= SQLNCLI11.0;数据源= ALwaysOnAVG_Listener; initial Catalog = AlwaysOnDB1; Integrated Security = SSPI; Application Intent = ReadOnly;
SQLPROVIDER=SQLNCLI11.0;Data Source=ALwaysOnAVG_Listener;initial Catalog=AlwaysOnDB1;Integrated Security=SSPI; Application Intent=ReadOnly;
设置deatils: -
可用性组名称:AlwaysOnAVG
数据库:AlwaysOnDB1& ALwaysOnDB2
可用性组侦听名称:AlwaysOnAVG_Listener
Set up deatils:-
Availability Group Name: AlwaysOnAVG
Databases: AlwaysOnDB1 & ALwaysOnDB2
Availability Group Listener Name: AlwaysOnAVG_Listener
服务器           &NBSP ;  当前角色 中学的行为
AlwaysOn1Serv1 PRIMARY 允许ReadOnly Intent COnnections
AlwaysOn2Serv2 Secondary 允许ReadOnly意图连接(同步,自动故障转移模式)
AlwaysOn3Serv3 Secondary 允许只读意向连接(同步,异步,高安全模式)
Servers current Role behaviour in Secondary
AlwaysOn1Serv1 PRIMARY Allow ReadOnly Intent COnnections
AlwaysOn2Serv2 Secondary Allow ReadOnly Intent COnnections (Synchronous, automatic failover mode)
AlwaysOn3Serv3 Secondary Allow ReadOnly Intent COnnections (Synchronous, ASynchronous, High Safety Mode)
请帮助我们解决此问题。
Please help us in fixing this issue.
推荐答案
(READ_ONLY_ROUTING_URL ='TCP://ALwaysOn3Serv3.sqlsolutions.com :5022'))
我认为问题可能是你放在这里的端口,它应该是客户端连接端口(默认情况下它是1433,除非你在安装期间/之后更改它)
I think problem might be the port you put here, it should be the client connection port (by default it's 1433 unless you change it during/after installation)
5022是内部端点主副本实例之间的端口。
5022 is the internal endpoint port between primary and secondary replica instances.
这篇关于Readonly Routing List在AlwaysOn功能中不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!