SQL Server 數據同步全方案：復制技術原理、配置步驟與手工同步存儲過程

SQL Server 復制架構核心概念

三大復制類型選型指南

圖形化配置全流程 (企業管理器 / SSMS)

1. 配置分發服務器：工具 → 復制 → 配置發布/訂閱/分發 → 選擇分發服務器 (可自為) → 完成，生成 `distribution` 數據庫與復制監視器。
2. 創建發布：工具 → 復制 → 創建和管理發布 → 選擇發布數據庫 → 創建發布向導 → 選擇類型 (建議事務復制) → 指定訂閱者類型 (SQL Server) → 勾選文章 (表/視圖/函數) → 設置快照代理排程 → 完成。
3. 創建訂閱：在訂閱服務器 → 工具 → 復制 → 請求訂閱 → 選擇發布服務器/發布 → 選擇訂閱數據庫 → 設置分發代理排程 (持續運行/定時) → 完成。

驗證復制健康度

• 復制監視器 → 發布 → 右鍵 → 查看詳細信息 → 狀態：運行中、最後同步時間、未分發命令數、延遲 (ms)。
• 文件系統檢查：`C:\Program Files\Microsoft SQL Server\MSSQL\ReplData\unc\__\` 下每分鐘生成新快照/事務文件夾。
• 數據抽樣：訂閱端執行 `SELECT COUNT(*) FROM dbo.KeyTable` 對比發布端。

手工同步方案：異構/跨域/無復制權限場景

適用於：無法建立復制拓撲、跨防火牆、版本差異大、需自定義衝突邏輯。

核心機制：鏈接服務器 + 觸發器標記 + 定時作業

-- 1. 創建鏈接服務器 (遠程目標)
IF EXISTS (SELECT 1 FROM master..sysservers WHERE srvname = 'SRV_LNK')
    EXEC sp_dropserver 'SRV_LNK', 'droplogins';
GO
EXEC sp_addlinkedserver 'SRV_LNK', '', 'SQLOLEDB', 'RemoteServerIP';
EXEC sp_addlinkedsrvlogin 'SRV_LNK', 'false', NULL, 'sync_user', 'StrongP@ss';
GO

-- 2. 本地表增加狀態列 (NULL=新增, 1=修改, 0=同步済)
ALTER TABLE dbo.[User] ADD SyncState BIT NULL;
GO

-- 3. 觸發器維護狀態
CREATE TRIGGER trg_User_SyncState ON dbo.[User]
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE u SET SyncState = 1
    FROM dbo.[User] u
    JOIN inserted i ON u.ID = i.ID
    WHERE u.SyncState IS NOT NULL; -- 僅標記已同步過的行，首次插入保持 NULL
END;
GO

-- 4. 同步存儲過程 (分布式事務保證一致性)
CREATE PROC dbo.sp_Sync_User
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON; -- 確保分布式事務原子性

    BEGIN DISTRIBUTED TRANSACTION;

    -- 4.1 同步刪除 (遠程有、本地無)
    DELETE FROM SRV_LNK.TargetDB.dbo.[User]
    WHERE ID NOT IN (SELECT ID FROM dbo.[User]);

    -- 4.2 同步新增 (本地 SyncState IS NULL)
    INSERT INTO SRV_LNK.TargetDB.dbo.[User] (ID, Number, Name)
    SELECT ID, Number, Name FROM dbo.[User] WHERE SyncState IS NULL;

    -- 4.3 同步修改 (本地 SyncState = 1)
    UPDATE r SET
        r.Number = l.Number,
        r.Name   = l.Name
    FROM SRV_LNK.TargetDB.dbo.[User] r
    JOIN dbo.[User] l ON r.ID = l.ID
    WHERE l.SyncState = 1;

    -- 4.4 重置本地狀態
    UPDATE dbo.[User] SET SyncState = 0 WHERE SyncState IN (1, NULL);

    COMMIT TRANSACTION;
END;
GO

-- 5. Agent 作業定時調用 (每 5 分鐘/業務定義)
EXEC msdb.dbo.sp_add_job @job_name = N'Sync_User_Data';
EXEC msdb.dbo.sp_add_jobstep @job_name = N'Sync_User_Data', @step_name = N'RunSync', @subsystem = N'TSQL', @command = N'EXEC dbo.sp_Sync_User;';
EXEC msdb.dbo.sp_add_jobschedule @job_name = N'Sync_User_Data', @name = N'Every5Min', @freq_type = 4, @freq_subday_type = 8, @freq_subday_interval = 5;

手工同步關鍵注意點

1. 主鍵不可變：同步邏輯依賴 PK 關聯，業務禁止更新 PK。
2. 分布式事務 (MSDTC) 必須雙向通信：防火牆開放 135 (RPC Endpoint Mapper) + 動態端口範圍，或配置固定端口。
3. 大批量優化：分批次 (如 5000 行) 循環同步，避免長事務鎖升級、日誌暴漲、MSDTC 超時。
4. 衝突檢測：若雙向寫入，需引入 `RowVersion`/`Timestamp` 列，比較版本號決定勝者或記錄衝突表人工介入。
5. 監控告警：作業歷史失敗、同步行數異常波動、遠程鏈接失敗 → 郵件/短信告警。

方案選型建議