SQL Server 複寫技術實現資料同步：從基礎概念到定時同步存儲過程完整實作

複寫核心概念

• 發行者：資料來源伺服器
• 訂閱者：接收資料伺服器
• 散發者：暫存變更的中介伺服器（含散發資料庫）
• 發行集：一組發行項（資料表/檢視）的邏輯集合
• 發行項：單一資料表/檢視

三種複寫類型

1. 快照式：定期完整複製，適合資料量小、變動低、可容忍延遲場景。
2. 交易式：近即時傳遞交易記錄，適合高一致性需求。
3. 合併式：雙向更新，離線/行動端同步首選。

GUI 設定三步驟

1. 設定發行/散發者：工具 → 複寫 → 設定發行/散發者，產生散發資料庫與複寫監視器。
2. 建立發行集：選擇資料庫 → 建立發行集 → 選擇類型（預設快照） → 指定訂閱者類型 → 選擇發行項（資料表） → 完成。
3. 建立訂閱：在訂閱者端 → 要求訂閱 → 確認代理程式啟動 → 完成。

驗證複寫成功

企業管理器 → 複寫 → 發行集 → 右鍵屬性 → 執行代理程式 → 排程設為每分鐘執行。
檢查 C:\Program Files\Microsoft SQL Server\MSSQL\REPLDATA\unc\... 目錄下是否每分鐘產生時間命名資料夾。
或直接查詢訂閱端資料庫確認資料表已同步。

自訂定時同步方案（不使用複寫，適用簡單單向同步）

環境：遠端伺服器 xz (sa/無密碼)，本地同步至遠端。
表結構：本地 [user](id, number, name, state)，state：NULL=新增、1=修改、0=同步済。
觸發器維護 state：UPDATE ... SET state=1 FROM ... JOIN inserted。
建立連結伺服器 srv_lnk 指向 xz。

同步存儲過程 p_synchro

CREATE PROC p_synchro AS
-- BEGIN DISTRIBUTED TRANSACTION (若需跨伺服器交易)
-- 同步刪除：遠端有但本地無
DELETE FROM srv_lnk.test.dbo.[user]
WHERE id NOT IN (SELECT id FROM [user]);

-- 同步新增：本地 state IS NULL
INSERT INTO srv_lnk.test.dbo.[user] (id, number, name)
SELECT id, number, name FROM [user] WHERE state IS NULL;

-- 同步修改：本地 state = 1
UPDATE srv_lnk.test.dbo.[user] SET
    number = b.number,
    name = b.name
FROM srv_lnk.test.dbo.[user] a
JOIN [user] b ON a.id = b.id
WHERE b.state = 1;

-- 重設本地標誌
UPDATE [user] SET state = 0 WHERE ISNULL(state,1) = 1;
-- COMMIT TRAN
GO

SQL Server Agent 作業排程

EXEC msdb..sp_add_job @job_name = N'資料同步';
EXEC msdb..sp_add_jobstep @job_name = N'資料同步', @step_name = N'同步執行',
    @subsystem = N'TSQL', @command = N'EXEC p_synchro',
    @retry_attempts = 5, @retry_interval = 5;
EXEC msdb..sp_add_jobschedule @job_name = N'資料同步', @name = N'每日零點',
    @freq_type = 4, @freq_interval = 1, @active_start_time = 0;

選型建議

• 企業級高可用、雙向、衝突解決 → 合併式複寫
• 單向、近即時、高一致性 → 交易式複寫
• 單向、批次、異構目標 → 快照式複寫 或 自訂同步 SP + Agent 作業