AG + Read-only Routing
環境設定
| SQL Node | SQL1 SQL2 |
| AG Listener | SQL1001 |
| Read-only Routing | USE [master] GO ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SQL1' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://SQL1.ellie.com:1433')) GO ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SQL2' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://SQL2.ellie.com:1433')) GO
USE [master] GO ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SQL2' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'SQL1'))) GO ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SQL1' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'SQL2'))) GO |
| Client | SQL4 建立兩個 Linked Server 連到 SQL1001分別連到Primary 及Secondary 
USE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N'AG_Primary', @srvproduct=N'', @provider=N'SQLNCLI11', @datasrc=N'SQL1001' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AG_Primary',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AG_READONLY',@useself=N'False',@locallogin=NULL,@rmtuser=N'User01',@rmtpassword='########' GO
EXEC master.dbo.sp_addlinkedserver @server = N'AG_READONLY', @srvproduct=N'', @provider=N'SQLNCLI11', @datasrc=N'SQL1001', @provstr=N'ApplicationIntent=ReadOnly;', @catalog=N'DB_SQL1' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AG_READONLY',@useself=N'False',@locallogin=NULL,@rmtuser=N'User01',@rmtpassword='########' GO |
測試結果
Primary : SQL1, Readable Secondary =Y Secondary: SQL2, Readable Secondary =Y 
| 
|
執行failover後， Primary : SQL2, Readable Secondary =Y Secondary: SQL1, Readable Secondary =Y  |
 |
將Secondary改為Read-intent only Primary : SQL2, Readable Secondary = Y Secondary: SQL1, Readable Secondary =Read-intent only  |
 |
再次執行Failover, 並將新的Secondary改為Read-intent only Primary : SQL1, Readable Secondary =Read-intent only Secondary: SQL2 Readable Secondary = Read-intent only  |  |
Delegation 问题?
前端Server (SQL6) 使用域账号-> 存取 -> 第一台 SQL Server (SQL4) ServicesAccount: ellie\sqlsrvuser -> linked server存取 -> 第二台 SQL server, ServicesAccount: ellie\sqlsrvuser
可能会有如下的Error :
需执行setspn执行如下(当前执行setspn的账号在AD上需要有write service principle name的权限。):
setspn -A MSSQLSvc/SQL1001.ellie.com:1433 ellie\sqlsrvuser
setspn -A MSSQLSvc/SQL1001.ellie.com ellie\sqlsrvuser
(SQL1001.ellie.com 为AG listener Name, ellie\sqlsrvuser 为SQL service 启动账号)
并在AD设定SQL service account的delegation权限:
