SQL Server 觸發器實現跨伺服器雙向資料同步完整方案

業務場景

兩台獨立 SQL Server (A、B) 部署結構相同的表，雙方均有增刪改操作，需即時雙向同步保持資料一致。

架構前置條件

1. 雙向連結伺服器：A 建立連結至 B，B 建立連結至 A
2. 啟動 MSDTC (分散式交易協調器) 並設為自動啟動
3. 網路互通、防火牆開放 135/1433/動態 RPC 連接埠
4. 同步表擁有主鍵，無觸發遞迴風險 (需在觸發器中判斷來源)

方案一：即時觸發器同步 (低延遲、強一致性)

1. 建立連結伺服器 (於 A 執行)

EXEC sp_addlinkedserver @server = 'SRV_B', @srvproduct = '', 
    @provider = 'SQLNCLI', @datasrc = 'B_IP_or_Instance';
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'SRV_B', @useself = 'FALSE', 
    @locallogin = NULL, @rmtuser = 'sync_user', @rmtpassword = 'StrongPwd';

2. 同步觸發器範本 (於 A 的目標表建立)

-- 新增同步
CREATE TRIGGER tr_Sync_Insert ON dbo.Author
AFTER INSERT
AS
BEGIN
    SET XACT_ABORT ON;
    INSERT SRV_B.TargetDB.dbo.Author (ID, Name, TelPhone)
    SELECT ID, Name, Phone FROM inserted;
END;
GO

-- 修改同步
CREATE TRIGGER tr_Sync_Update ON dbo.Author
AFTER UPDATE
AS
BEGIN
    SET XACT_ABORT ON;
    UPDATE B SET Name = I.Name, TelPhone = I.Phone
    FROM SRV_B.TargetDB.dbo.Author B
    JOIN inserted I ON B.ID = I.ID;
END;
GO

-- 刪除同步
CREATE TRIGGER tr_Sync_Delete ON dbo.Author
AFTER DELETE
AS
BEGIN
    SET XACT_ABORT ON;
    DELETE B
    FROM SRV_B.TargetDB.dbo.Author B
    JOIN deleted D ON B.ID = D.ID;
END;
GO

3. 關鍵防呆機制

• 避免遞迴觸發：在觸發器開頭加入 IF TRIGGER_NESTLEVEL() > 1 RETURN; 或使用 CONTEXT_INFO 標記來源
• 錯誤處理：包裝 TRY...CATCH，記錄失敗至錯誤表，發送告警
• 效能考量：跨網路 RPC 會增加本地交易持有鎖時間，高併發表慎用

方案二：定時批次同步 (高吞吐、最終一致性)

CREATE PROC usp_Sync_Author_Batch
AS
BEGIN
    SET XACT_ABORT ON;
    BEGIN TRAN;
    
    -- 更新已變更
    UPDATE B SET Name = A.Name, TelPhone = A.Phone
    FROM SRV_B.TargetDB.dbo.Author B
    JOIN dbo.Author A ON B.ID = A.ID
    WHERE B.Name <> A.Name OR B.TelPhone <> A.Phone;
    
    -- 插入新增
    INSERT SRV_B.TargetDB.dbo.Author (ID, Name, TelPhone)
    SELECT ID, Name, Phone FROM dbo.Author A
    WHERE NOT EXISTS (SELECT 1 FROM SRV_B.TargetDB.dbo.Author WHERE ID = A.ID);
    
    -- 刪除已移除 (視業務決定是否啟用)
    -- DELETE B FROM SRV_B.TargetDB.dbo.Author B
    -- WHERE NOT EXISTS (SELECT 1 FROM dbo.Author WHERE ID = B.ID);
    
    COMMIT TRAN;
END;

搭配 SQL Agent 作業每 5-15 分鐘執行一次。

方案比較與選型建議

現代替代方案提示

• SQL Server Replication：雙向異動複寫、點對點複寫 (Enterprise 版)
• Always On Availability Groups：可讀次要複本、自動容錯移轉
• Azure SQL Data Sync / Change Feed：雲端混合同步
• CDC + Service Broker / Kafka：事件驅動架構，解耦合

生產環境核心檢查清單

• [ ] 連結伺服器雙向連通、權限最小化
• [ ] MSDTC 正常、防火牆規則驗證
• [ ] 觸發器含遞迴防護、錯誤記錄、效能監控
• [ ] 主鍵衝突測試 (雙邊同時 INSERT 相同 PK)
• [ ] 定期資料一致性比對作業 (CHECKSUM / rowcount)
• [ ] 災難恢復演練：單邊資料庫重建後同步追趕