SQL Server 資料同步實戰：複寫技術與手動同步方案完整解析

核心概念：SQL Server 複寫架構

複寫將資料從「發行者」複製到多個「訂閱者」，透過「散發者」協調，確保異地資料一致性。三大核心角色：

• 發行者：資料來源，定義發行集合（文章）。
• 散發者：暫存交易記錄，負責分發變更（可與發行者同機）。
• 訂閱者：接收資料的目標伺服器。

三種複寫類型比較

圖形介面設定快照式複寫步驟（入門首選）

1. 設定散發者：SSMS → 右鍵「複寫」→「設定散發」→ 完成精靈（自動建立 distribution 資料庫）。
2. 建立發行集：右鍵「本機發行集」→「新增發行集」→ 選資料庫 → 選「快照式發行集」 → 勾選要同步的資料表（文章）。
3. 建立訂閱：在訂閱端右鍵「本機訂閱」→「新增訂閱」→ 指定發行集 → 設定同步排程（如每分鐘執行）。
4. 驗證同步：查看「複寫監視器」或檢查 `C:\Program Files\Microsoft SQL Server\MSSQL\ReplData\` 下是否按排程產生快照資料夾。

進階：手動同步存儲程序方案（適用特殊需求）

當無法使用複寫（跨版本、異構、頻寬受限）時，可採用「觸發器標記變更 + 連結伺服器 + 排程作業」架構：

-- 1. 本地表加狀態欄位
ALTER TABLE [user] ADD [state] BIT NULL; -- NULL=新增, 1=修改, 0=同步完成

-- 2. 觸發器自動維護 state
CREATE TRIGGER t_state ON [user] AFTER UPDATE AS
UPDATE u SET state = 1
FROM [user] u JOIN inserted i ON u.id = i.id
WHERE u.state IS NOT NULL;

-- 3. 建立連結伺服器
EXEC sp_addlinkedserver 'srv_lnk', '', 'SQLOLEDB', '遠端IP';
EXEC sp_addlinkedsrvlogin 'srv_lnk', 'false', NULL, 'sa', '密碼';

-- 4. 同步存儲程序（刪除/新增/更新三合一）
CREATE PROC p_synchro AS
BEGIN
    -- 刪除遠端已不存在的資料
    DELETE FROM srv_lnk.test.dbo.[user]
    WHERE id NOT IN (SELECT id FROM [user]);

    -- 新增本地標記為 NULL 的資料
    INSERT INTO srv_lnk.test.dbo.[user] (id, number, name)
    SELECT id, number, name FROM [user] WHERE state IS NULL;

    -- 更新本地標記為 1 的資料
    UPDATE r SET number = l.number, name = l.name
    FROM srv_lnk.test.dbo.[user] r JOIN [user] l ON r.id = l.id
    WHERE l.state = 1;

    -- 重置本地標記
    UPDATE [user] SET state = 0 WHERE ISNULL(state,1) = 1;
END;

-- 5. 建立 SQL Agent 作業每日定時執行 p_synchro

方案選擇建議

• 標準企業環境 → 交易式複寫（高可用、低延遲、內建監控）。
• 離線/行動場景 → 合併式複寫（支援衝突解決）。
• 異構/跨版本/精簡需求 → 手動同步程序（完全可控，但需自行處理錯誤重試、監控）。