SQLServer 事務複製在發佈端（publication）和訂閱端（subscription）對應的資料庫還原之後複製異常問題的解決

書接上文，在一個正常的事務複製環境中，如果發生了資料庫還原，事務複製會不會出問題，出問題之後又如何恢復，如果在不刪除訂閱發佈重建的情況下，如何在現有基礎上修複事務複製的異常，這個問題可以分為兩部分看：

1，如果publisher資料庫發生了還原操作，事務複製會出現什麼異常，該如何恢復？

2，如果是subscription資料庫發生了還原操作，事務複製會不會出現什麼異常？

還原發佈端資料庫的場景

首先看第一種情況，如果publisher資料庫發生了還原操作，由事務複製的架構可以發現，如果出現問題，此時問題一定會出現在publication和distribution之間，因為數據源（publication）發生了變化。

首先是一個正常的事務複製環境（SQL1作為publisher，SQL2作為distrubutor，SQL2，SQL3同時作為Subscriber），此時複製狀態完全正常。

嘗試用備份恢複發布資料庫（SQL1上的DB04庫）

use master
GO
ALTER DATABASE [DB04] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE [DB04] FROM DISK = N'C:\tmp\DB04_full_20240727.bak' 
WITH FILE = 1,REPLACE,  
MOVE N'Db04' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Db04.mdf', 
MOVE N'Db04_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Db04_log.ldf', 
NOUNLOAD, STATS = 5, norecovery;
GO
restore database DB04 from disk = 'C:\tmp\DB04_20240727_log1.trn';
GO

當完成恢復後，實物複製開始出現異常

1，通過複製監控面板看到複製開始異常

2，查看log reader agent的狀態，開始出現異常

3，找到對應的log reader agent對應的job

詳細日誌如下：

2024-07-29 10:13:33.536 OLE DB DISTOLE 'SQL2': select datasource, srvid from master..sysservers where ltrim(substring(reverse(ltrim(substring(reverse(srvname),charindex(',',reverse(srvname))+1,len(srvname)))),charindex(':',srvname)+1,len(srvname))) = N'SQL1' collate Latin1_General_CI_AI
2024-07-29 10:13:33.537 Status: 4096, code: 20024, text: 'Initializing'.
2024-07-29 10:13:33.537 The agent is running. Use Replication Monitor to view the details of this agent session.
2024-07-29 10:13:33.537 OLE DB DISTOLE 'SQL2': sp_MSget_last_transaction @publisher_id = 2, @publisher_db = N'DB04', @for_truncate = 0x0
2024-07-29 10:13:33.538 OLE DB DISTOLE 'SQL2': sp_MSquery_syncstates 2, N'DB04'
2024-07-29 10:13:33.539 OLE DB DISTOLE 'SQL2': sp_MSget_last_transaction @publisher_id = 2, @publisher_db = N'DB04', @for_truncate = 0x1
2024-07-29 10:13:33.539 Publisher: {call sp_repldone ( 0x00000029000002380003, 0x00000029000002380003, 0, 0)}
2024-07-29 10:13:33.556 Status: 0, code: 20011, text: 'The process could not execute 'sp_repldone/sp_replcounters' on 'SQL1'.'.
2024-07-29 10:13:33.556 The process could not execute 'sp_repldone/sp_replcounters' on 'SQL1'.
2024-07-29 10:13:33.556 Status: 0, code: 9003, text: 'The log scan number (41:568:3) passed to log scan in database 'DB04' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.'.
2024-07-29 10:13:33.556 Status: 0, code: 22017, text: 'The process could not set the last distributed transaction.'.
2024-07-29 10:13:33.556 Status: 0, code: 22037, text: 'The process could not execute 'sp_repldone/sp_replcounters' on 'SQL1'.'.
2024-07-29 10:13:33.556 Disconnecting from OLE DB DISTOLE 'SQL2'

錯誤的關鍵是：The process could not execute 'sp_repldone/sp_replcounters' on 'SQL1'。

參考這裡https://repltalk.com/2010/02/19/the-process-could-not-execute-sp_repldonesp_replcounters/
If an old backup was restored on top of published database then use sp_replrestartIf going back to the most recent transaction log backup is not an option then execute sp_replrestart  on publisher in published database. This stored procedure is used when the highest log sequence number (LSN) value at the Distributor does match the highest LSN value at the Publisher.

This stored procedure will insert compensating LSNs (No Operation) in the publisher database log file till one the compensating LSN becomes more than the highest distributed LSN in distribution database for this published database. After this it inserts this new high LSN in the msrepl_transactions table in the distribution database and executes sp_repldone on published database to update the internal structures to mark a new starting point for log reader agent.

Ensure that the log reader agent is stopped and there is no incoming transactions on the published database, when this SP is executed.

由於資料庫被還原之後，相對還原之前的資料庫，還原之後的資料庫的事務日誌號（LSN）發生了“回退”，LSN是一個只讀的遞增序列號，比如還原之間資料庫的LSN到了10000，還原之後可能是8000或者其他小於10000的一個值，當日誌讀取代理器（log reader agent）在讀取日誌時，發現當前讀的日誌LSN比上一次讀的LSN還小（log reader agent在哪裡？其作用是什麼？為什麼要讀取事務日誌？參考上一篇文章），於是就開始報錯了。此時可以使用sp_repl_restart這個SP在發佈資料庫上執行，以修複以上錯誤，sp_replrestart的作用是，當分發伺服器上最高的日誌序列號 （LSN） 值與發佈伺服器上的最高 LSN 值不匹配時，將使用sp_replrestart。sp_replrestart詳細參考這裡

執行sp_replrestart之後，帶log reader agent重試執行之後，實物複製恢復正常。

訂閱端資料庫還原的場景

如果subscription資料庫發生了還原操作，由事務複製的架構可以發現，如果出現問題，此時問題一定會出現在distribution和subscription之間，因為目標資料庫（subscription）發生了變化。

 對於一個正常的實物複製環境，嘗試還原一個訂閱資料庫之後，觀察發生的現象。

1，如圖，當在還原訂閱庫的過程中，複製監控器開始出現retrying failed commend操作，其實不難理解，在訂閱庫還原的過程中，訂閱庫無法訪問，負責分發數據的job distribution agent也就無法正常工作

上述錯誤背後的distribution agent服務錯誤的詳細日誌，該日誌意味著，訂閱庫無法訪問，其實不難理解，訂閱庫在還原的過程中無法被外部訪問，因此distribution agent服務開始報錯。

以上在訂閱資料庫正常還原完成之後，distribution agent服務會自動重試（連接到訂閱庫進行數據傳輸），事務複製開始恢復正常。

此時需要註意，如果還原的資料庫相比未還原之前缺少數據，那麼在以後得複製過程中會出現無法找到匹配的數據錯誤（比如update的時候，主節點上有數據，但是訂閱端沒有對應的數據），類似於MySQL的1062主鍵衝突 ， 或者1032數據未找到錯誤

總結

正常情況下，也就是還原前後資料庫的對象（表）沒有發生變更，對於如下兩種情況

1，如果publisher資料庫發生了還原操作，事務複製會出現什麼異常，該如何恢復？===》需要再發佈端的對應的資料庫上執行sp_replrestart命令來修複因還原資料庫導致的LSN變更

2，如果是subscription資料庫發生了還原操作，事務複製會不會出現什麼異常？===》不許出現特別的異常，只需要確保訂閱庫完成會還原後，對應的distributor agent正常啟動（經過重試錯誤）即可，不需要額外的處理

鑒於現實情況中存在各種複雜的其他因素，即便是事務複製被修複正常之後，也可能資料庫還原前後，數據上的某些差異導致複製無法正確進行，需要基於具體的細節進行診斷和修複。