MSSQL 資料庫同步完整指南：從複寫技術到自訂同步方案

MSSQL 資料同步概述

MSSQL 資料同步主要透過資料庫複寫技術實現，該技術能將一組資料從單一來源複製到多個目標，確保分散在不同位置的資料自動同步更新，維持資料一致性。複寫架構包含五大核心元素：發行伺服器、訂閱伺服器、散發伺服器、發行集與發行項。

SQL Server 複寫運作原理

採用「發行/訂閱」模式：資料來源為發行伺服器，負責發布資料變更；散發伺服器內含散發資料庫，接收並暫存所有變更，再分發給訂閱伺服器。SQL Server 提供三種複寫類型：

• 快照複寫：定期完整複製資料，適合資料量小、變動少的情境
• 交易複寫：近乎即時傳遞交易記錄，適合高一致性需求
• 合併複寫：允許多端離線修改再合併，適合行動/分散式應用

逐步設定複寫環境

1. 設定發行伺服器

1. 在企業管理員中選取伺服器節點
2. 工具 → 複寫 → 設定發行、訂閱伺服器與散發
3. 依嚮導完成設定，系統將自動建立散發資料庫與複寫監視器

2. 建立發行集

1. 工具 → 複寫 → 建立與管理發行集
2. 選取資料庫 → 建立發行集 → 選擇快照式發行
3. 指定訂閱者類型（SQL Server 2000）
4. 選取要發行的資料表（發行項）
5. 完成後該資料庫成為共享資料庫

3. 設定訂閱

1. 在訂閱伺服器選取工具 → 複寫 → 要求訂閱
2. 確認 SQL Server Agent 服務已啟動（必要條件）
3. 完成訂閱設定

驗證複寫是否成功

展開發行伺服器 → 複寫 → 發行集 → 右鍵屬性 → 狀態 → 立即執行代理程式。排程設為每分鐘執行一次。檢查 C:\Program Files\Microsoft SQL Server\MSSQL\REPLDATA\unc\ 目錄下是否每分鐘產生以時間命名的資料夾，並確認訂閱端資料庫出現已發行的資料表。

手動同步替代方案：觸發器 + 連結伺服器 + 排程作業

適用於需精細控制同步邏輯或跨版本環境。核心步驟：

1. 本地資料表設計

CREATE TABLE [user] (
    id INT IDENTITY(1,1),
    number VARCHAR(4),
    name VARCHAR(10),
    state BIT  -- NULL=新增, 1=修改, 0=已同步
)

2. 觸發器維護狀態旗標

CREATE TRIGGER t_state ON [user]
AFTER UPDATE
AS
UPDATE [user] SET state = 1
FROM [user] a JOIN inserted b ON a.id = b.id
WHERE a.state IS NOT NULL

3. 建立連結伺服器

EXEC sp_addlinkedserver 'srv_lnk', '', 'SQLOLEDB', 'xz'
EXEC sp_addlinkedsrvlogin 'srv_lnk', 'false', NULL, 'sa', ''

4. 同步儲存程序

CREATE PROC p_synchro
AS
-- 同步刪除
DELETE FROM srv_lnk.test.dbo.[user]
WHERE id NOT IN (SELECT id FROM [user])

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

-- 同步修改
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

5. 建立排程作業

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

此方案透過 state 欄位追蹤變更，配合連結伺服器跨伺服器操作，適合無法使用原生複寫或需自訂衝突解決邏輯的場景。