Ellie's blog

2022-09-21

AG 設定replication後加入新節點

  • 171
  • 0

若replica是在Replication設定後才加入AG，那麼該replica可能無法即時看見Local Publications。若該replica開放readable 或是當AG failover到該replica時，便可看見。

測試環境

SQL node      

SQL3  - Primary

SQL4  - 2nd replica 

SQL11  - 3rd replica ,尚未加入AG

SQL listenerSQL1002
AG Name

AG2

DistributorSQL5
PublisherSQL1002
SubscriberSQL6

 

測試步驟

  • Replication 設定完成後，將第三個節點SQL11加入AG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  • 確定第三個節點SQL11已成功加入AG

 

 

 

 

 

 

 

 

 

 

  •  在第三個節點SQL11上設定遠端Distribution至SQL5，並新建Linked Server

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

** 建立Linked Server到Distributor SQL5

EXEC sys.sp_addlinkedserver @server = 'SQL5';

 

** 建立Linked Server到Subscriber SQL6

EXEC sys.sp_addlinkedserver @server = 'SQL6'; 

 

 

  • 在這個SQL11輔助副本下，尚無法看見Local Publications

 

 

 

 

 

 

 

 

 

 

 

 

 

  • 若將SQL 11輔助副本改為允許Readable或是將AG failover 到SQL11輔助副本，便可看見Local Publications了!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

**若replica不允許readonly，在distriubtor，SQL5上執行 sp_validate_replica_hosts_as_publishers會回報Error 

USE distribution;

GO

DECLARE @redirected_publisher sysname;

EXEC sys.sp_validate_replica_hosts_as_publishers

@original_publisher = 'SQL3',

@publisher_db = 'DB1',

@redirected_publisher = 'SQL1002';

 

/* -- 因為SQL11沒有開放readonly

OLE DB provider "SQLNCLI11" for linked server "[6A7A29E1-AEE9-4E92-A15B-5603DC2286AA]" returned message "Deferred prepare could not be completed.".

Msg 21899, Level 11, State 1, Procedure sp_hadr_verify_subscribers_at_publisher, Line 109 [Batch Start Line 15]

The query at the redirected publisher 'SQL11' to determine whether there were sysserver entries for the subscribers of the original publisher 'SQL3' failed with error '976', error message 'Error 976, Level 14, State 1, Message: The target database, 'DB1', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.'.

One or more publisher validation errors were encountered for replica host 'SQL11'.

*/