摘要:[SQL SERVER] AlwaysOn ReadOnlyRouting分流 (Load Balancing)
有長官來詢問AlwaysOn 可否Load Balance的問題
不過AlwaysOn只有使用ReadOnly來讀寫分離，沒有Load Balance的功能。
研究一下，在官方論壇發現有人提到一個不錯的方法
雖然不是真的LoadBalance，不過也能參考一下
利用Agent Job排程每15秒改變一次ReadOnlyRouting的內容
實作結果如下:
1. 檢視read_only_routing_url，順序為 1 > 2 > 3
2. 15秒後檢視，順序為 2 > 3 > 1
3. 15秒後檢視，順序為 3 > 1 > 2
用SQLCMD建立 六個 連線測試，都是同一連線字串，確認每15秒連進去的機器都是不同
以上方法可以使AlwaysOn的ReadOnly連線字串達到分流效果，但非真正的Load Balance
JOB語法，在所有節點上執行
While 1=1
Begin
If (
SELECT ARS.role_desc
FROM SYS.availability_replicas AR
join sys.dm_hadr_availability_replica_states ARS on AR.replica_id = ARS.replica_id
where AR.replica_server_name = (select @@SERVERNAME)
) = 'PRIMARY' and (select count(*) from sys.availability_read_only_routing_lists) > 1
Begin
ALTER AVAILABILITY GROUP [AG AdventureWorks]
Modify Replica on
N'SP-DENALI1\SQL1' with
(Primary_Role (READ_ONLY_ROUTING_LIST =('SP-Denali3\SQL1','SP-Denali4\SQL1')))
ALTER AVAILABILITY GROUP [AG AdventureWorks]
Modify Replica on
N'SP-DENALI3\SQL1' with
(Primary_Role (READ_ONLY_ROUTING_LIST =('SP-Denali1\SQL1','SP-Denali4\SQL1')))
ALTER AVAILABILITY GROUP [AG AdventureWorks]
Modify Replica on
N'SP-DENALI4\SQL1' with
(Primary_Role (READ_ONLY_ROUTING_LIST =('SP-Denali3\SQL1', 'SP-Denali1\SQL1')))
--print 'changing ROR URL in 15 seconds...'
WAITFOR DELAY '00:00:15'
--print 'Changing ROR URL'
--Run every 15 seconds
ALTER AVAILABILITY GROUP [AG AdventureWorks]
Modify Replica on
N'SP-DENALI1\SQL1' with
(Primary_Role (READ_ONLY_ROUTING_LIST =('SP-Denali4\SQL1','SP-Denali3\SQL1')))
ALTER AVAILABILITY GROUP [AG AdventureWorks]
Modify Replica on
N'SP-DENALI3\SQL1' with
(Primary_Role (READ_ONLY_ROUTING_LIST =('SP-Denali4\SQL1','SP-Denali1\SQL1')))
ALTER AVAILABILITY GROUP [AG AdventureWorks]
Modify Replica on
N'SP-DENALI4\SQL1' with
(Primary_Role (READ_ONLY_ROUTING_LIST =('SP-Denali1\SQL1', 'SP-Denali3\SQL1')))
end
WAITFOR DELAY '00:00:15'
End