SQL Server 複寫原理與三大類型深度解析：快照、交易、合併式複寫的優缺點與限制

雙向複寫模型

以伺服器 A、B 雙向複寫為例說明。

三大複寫類型

1. 快照複寫

機制：定期將發佈端完整資料複製至訂閱端（先刪除再插入）。

缺點：資料量大時耗時長、鎖定久、頻寬佔用高、無增量能力。不適合高頻變動、大資料量、低延遲需求場景。

2. 交易式複寫

機制：發佈端交易記錄掃描代理讀取符合條件的交易，透過散發者以三個預存程序（sp_MSins_, sp_MSupd_, sp_MSdel_）應用至訂閱端。可自訂這三個程序實現業務邏輯。

致命缺陷：迴路複寫。若 A→B 與 B→A 篩選條件相同，A 的交易複寫到 B，B 再複寫回 A，形成無限迴路（踢皮球）。

解決：使用 @loopback_detection、設定不同篩選條件、或採用合併式複寫。

3. 合併式複寫

機制：定期雙向合併變更，最終雙端資料一致。衝突依優先級解決（保留一方）。

關鍵點：SQL Server 自動在每張發行表新增 rowguid (uniqueidentifier) 與 msrepl_tran_version 欄位追蹤變更，開發時需注意：

• INSERT 必須提供 rowguid 或讓預設值 NEWID() 產生。
• 不得任意修改/刪除追蹤欄位。
• Schema 變更需透過 sp_repladdcolumn 等複寫專用程序。

SQL Server 複寫通用限制與避坑

1. 連線失敗自動停用：預設 10 次重試失敗後，複寫代理程式停止，需人工重啟。建議監控代理程式狀態並設定警示。
2. 既有資料初始化困難：建立複寫前表已有資料，SQL Server 無內建解決方案，需手動同步（備份/還原、BCP、SSIS）後再啟用複寫。
3. 結構變更受限：發行表結構頻繁變更會導致複寫中斷，變更需嚴格走複寫專用程序流程。
4. 頻寬與延遲：跨資料中心複寫需評估網路品質，考慮壓縮、排程離峰時段。
5. 監控盲區：必須部署 複寫監視器 或自訂巡檢作業，監控未散發命令數、延遲秒數、代理程式狀態。

選型建議