Linked Server / 連到 AG readonly node

2022-10-07

200
0
MSSQL

Linked Server 連到 AG readonly node

Q1. 如何使用Alias別名建立Linked Server?

A: 本機端 : SQL04
遠端SQL SQL06\SQL2019, 1455
Step1 : 在Sql Server Configuration Manager為遠端SQL建立別名 SQLDest (請在32位及64位的SQL Native Client 11.0 Configuration下都建立別名)

Step2 : 在本機端SQL4即可以別名建立linked Server.

OPENQUERY語法：
select * from OPENQUERY ( [SQL6\SQL2019,1455] ,'select top 1 * from T1.dbo.Tab1' )
go
select * from OPENQUERY ( [SQLDEST] ,'select top 1 * from T1.dbo.Tab1' )
go

Q2. 如何建立Linked Server連到AG readonly node?
A: 請使用下列語法建立Linked Server後，再開啟Property設定安全性：
EXEC master.dbo.sp_addlinkedserver
@server = N'AG_READONLY', --Linked Server 名稱
@srvproduct=N'',
@provider=N'SQLNCLI11',
@datasrc=N'SQL1001', --AG listener
@provstr = N'ApplicationIntent=ReadOnly;',
@catalog=N'DB_SQL1'--Database Name

即可透過Linked Server正確連到Readonly節點：

Q3. Delegation 問題?
A: 前端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許可權:

Q4. 假若發生了failover, 下面兩種選擇有什麼不同？
1) Add application intent = read only at application connection string
2) DBA change the readable secondary to read-only

A: Failover後，透過Linked Server 依舊可以連到新的Primary 或Secondary，並沒有差別。
環境設定

測試結果

Q5. 使用sqlcmd –S<AG_Listener> –K readonly做測試，它並沒有連接到secondary instance。測試的方法不對？
A: 必需是AG裡的database才能連接到secondary replica，若在connection string裡不指定DB預設值會是master，而master不在AG裡，因此無法導到Secondary replica.
Workaround :
Connection string裡先指定DB name，連接成功後再執行use database 切換到其它DB
或是在SQL 的安全性裡將SQL login的default database 改為AG裡的DB，這樣連接字串就不需指定db name了：

回首頁

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