SQL2012和SQL2014永遠只會讀取read-only routing list中第一個可用複本，
所以之前你可以透過Agent job定時修改routing list達到分流效果(非真正load balancing)，
但現在SQL2016可在多個可讀取次要複本，輕鬆實現Round-robin load balancing。
設定AG的Load Balanced Read-Only Routing
--設定次要複本可讀取
alter availability group [ricolabAG]
modify replica on N'win2k12R2B\SQL2K16'
with(secondary_role(allow_connections=read_only))
alter availability group [ricolabAG]
modify replica on N'win2k12R2D\sql2k16'
with(secondary_role(allow_connections=read_only))
--設定read-only routing URL
alter availability group [ricolabAG]
modify replica on N'win2k12R2A\SQL2K16'
with (secondary_role (READ_ONLY_ROUTING_URL = N'TCP://win2k12R2A.RS.com.tw:1433'));
alter availability group [ricolabAG]
modify replica on N'win2k12R2B\SQL2K16'
with (secondary_role (READ_ONLY_ROUTING_URL = N'TCP://win2k12R2B.RS.com.tw:1433'));
alter availability group [ricolabAG]
modify replica on N'win2k12R2D\SQL2K16'
with (secondary_role (READ_ONLY_ROUTING_URL =N'TCP://win2k12R2D.RS.com.tw:1433'));
/*
SQL2016 load balanced lists
將在win2k12R2D\SQL2K16、win2k12R2B\SQL2K16兩個node實現load balancing
我在read-only routing新增了1組list
List1:win2k12R2D\SQL2K16,win2k12R2B\SQL2K16
*/
--When win2k12R2A\SQL2K16 is primary
alter availability group [ricolabAG]
modify replica on 'win2k12R2A\SQL2K16'
with (
primary_role (READ_ONLY_ROUTING_LIST=(('win2k12R2D\SQL2K16', 'win2k12R2B\SQL2K16'))) --注意不要設錯
);
--如果設定如下，所有connections都將使用win2k12R2D\SQL2K16，就沒有load balancing效果了
alter availability group [ricolabAG]
modify replica on 'win2k12R2A\SQL2K16'
with (
primary_role (READ_ONLY_ROUTING_LIST=('win2k12R2D\SQL2K16', 'win2k12R2B\SQL2K16')) --注意不要設錯
);
--When win2k12R2B\SQL2K16 is primary
alter availability group [ricolabAG]
modify replica on 'win2k12R2B\SQL2K16'
with (
primary_role (READ_ONLY_ROUTING_LIST=(('win2k12R2D\SQL2K16', 'win2k12R2A\SQL2K16'))) --注意不要設錯
);
--When win2k12R2D\SQL2K16 is primary
alter availability group [ricolabAG]
modify replica on 'win2k12R2D\SQL2K16'
with (
primary_role (READ_ONLY_ROUTING_LIST=(('win2k12R2B\SQL2K16', 'win2k12R2A\SQL2K16'))) --注意不要設錯
);
select ar_Primary.replica_server_name 'Primary'
,ar_secondary.replica_server_name 'Route_to'
,ror.routing_priority 'Priority'
from sys.availability_read_only_routing_lists ror
join sys.availability_replicas ar_Primary on ror.replica_id = ar_Primary.replica_id
join sys.availability_replicas ar_secondary on ror.read_only_replica_id = ar_secondary.replica_id
join sys.availability_groups ag on ag.group_id = ar_Primary.group_id
where ag.NAME = 'ricolabAG'
order by 1,3
select hars.role_desc,ar.primary_role_allow_connections_desc,ar.read_only_routing_url,ar.replica_server_name
,ar.secondary_role_allow_connections_desc,ar.session_timeout,
ar.availability_mode_desc,ar.endpoint_url,ar.failover_mode_desc
,hars.synchronization_health_desc
from SYS.availability_replicas ar
join sys.dm_hadr_availability_replica_states hars on ar.replica_id = hars.replica_id
Round-robin load balancing機制
第一條connections將使用win2k12R2D\SQL2K16，第二條connections將使用win2k12R2B\SQL2K16，
第三條connections將使用win2k12R2D\SQL2K16，第四條connections將使用win2k12R2B\SQL2K16在第一組List一直循環。
如果第一組List中有某一個複本無法連線存取，則會自動使用其他可用複本。
如win2k12R2D\SQL2K16同步有問題或ALLOW_CONNECTIONS被設定為No，那所有connections都將使用win2k12R2B\SQL2K16。
如果第一組List中所有複本都無法連線存取，則會使用下一組List。
如win2k12R2D\SQL2K16和win2k12R2B\SQL2K16都掛了，那所有read-only connections都將使用win2k12R2A\SQL2K16，
後續假如第一組List中某一個複本可連線存取，那麼新的read-only connections將使用第一組List中的複本(因為高優先權)。
測試Round-robin load balancing
先使用SSMS2016測試存取每個次要複本的可用性資料庫，如果存取可讀取次要複本出現下面錯誤
透過擴充事件診斷Read-Only Routing
CREATE EVENT SESSION [AlwaysOn_ROO] ON SERVER
ADD EVENT sqlserver.hadr_evaluate_readonly_routing_info,
ADD EVENT sqlserver.read_only_route_complete,
ADD EVENT sqlserver.read_only_route_fail
ADD TARGET package0.event_file(SET filename=N'C:\AlwaysOn_ROO\',max_file_size=(5),max_rollover_files=(4))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION AlwaysOn_ROO ON SERVER STATE=START
GO
使用sqlcmd
sqlcmd -S SQLList_ricolab -E -K readonly -d ricolab1
SQLList_ricolab:監聽器 ricolab1:可用性資料庫
查看AlwaysOn_ROO擴充事件資料
win2k12R2D-> win2k12R2B-> win2k12R2D一直循環。
Note:
由於當初我是用VM Clone，所以其他兩個node的servername都和primary重複，透過下面方法更新Servername。
select @@servername regedit location
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130(版本)\Machines\OriginalMachineName'
--Rename a Computer that Hosts a Stand-Alone Instance of SQL Server:
sp_dropserver <old_name\instancename>;
GO
sp_addserver <new_name\instancename>, local;
GO
--最後restart sql server
使用ado.net
透過擴充事件診斷登入登出
CREATE EVENT SESSION [AlwaysOn_Loginout] ON SERVER
ADD EVENT sqlserver.login,
ADD EVENT sqlserver.read_only_route_complete,
ADD EVENT sqlserver.read_only_route_fail,
ADD EVENT sqlserver.logout,
ADD EVENT sqlserver.rpc_completed
ADD TARGET package0.event_file(SET filename=N'C:\loginout\',max_file_size=(5),max_rollover_files=(4))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION AlwaysOn_Loginout ON SERVER STATE=START
GO
win2k12R2D-> win2k12R2B-> win2k12R2D一直循環。
note:由於ado.net預設使用connection pool，所以必須設定Pooling=false才可。
Is_cached=false才能實現load balancing。
const string connectionstring = "Server=tcp:192.167.1.88,1433;database=ricolab2;user id=webuser;password=XXXX;Application Name=LoadBalance;ApplicationIntent=ReadOnly;Timeout=60;Pooling=false;";
int i = 0;
while(i<6)
{
string sqlstatement = @"select 'Current Server:'+@@SERVERNAME as [SERVERNAME]
union all
select top 3 cast(c1 as varchar(10))
from testA";
SqlConnection cn = null;
try
{
using (cn = new SqlConnection(connectionstring))
{
cn.Open();
using (SqlCommand cmd = new SqlCommand(sqlstatement, cn))
{
cmd.CommandType = System.Data.CommandType.Text;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Console.WriteLine(dr[0].ToString());
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("Exception:" + ex.Message);
}
Thread.Sleep(3 * 1000);
i++;
}
參考
Configure Read-Only Routing for an Availability Group (SQL Server)
Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server)
Monitor Availability Groups (Transact-SQL)
SQL Server 2016 AlwaysOn Availability Group Enhancements: Load Balance Read-Only Routing
View Availability Group Listener Properties (SQL Server)
ALTER AVAILABILITY GROUP (Transact-SQL)
Connection Timeouts in Multi-subnet Availability Group
HOW TO CONFIGURE READ-ONLY ROUTING FOR AN AVAILABILITY GROUP
Create or Configure an Availability Group Listener (SQL Server)
AlwaysOn Availability Groups, Listener, Named Instances, Port Numbers, etc.
Troubleshoot Always On Availability Groups Configuration (SQL Server)