SQL Server 2000 同步複寫技術實作與替代方案 (連結伺服器 + 作業排程)

前言

SQL Server 2000 複寫功能雖成熟，但設定繁瑣、跨網段/防火牆部署困難。許多實務案例改用「連結伺服器 + 分散式交易 (MSDTC) + Agent 作業排程」實現輕量級同步。本文詳細記錄複寫標準部署步驟、連結伺服器同步替代方案、作業排程設定、MSDTC 啟用與常見問題排解。

一、標準複寫部署前置作業 (雙向同步必備)

1. 同名 Windows 帳號/密碼：發佈者/訂閱者建立同名帳號 (如 SynUser) 加入 Administrators 群組，作為快照資料夾存取、Agent 服務登入身分。
2. 共用快照資料夾：發佈者建立共用目錄 (如 D:\PUB)，授予 SynUser 完整控制權限。
3. SQL Server Agent 服務登入身分：雙邊將 Agent 服務改為 SynUser 啟動。
4. 驗證模式 = 混合模式：雙邊執行個體屬性 → 安全性 → SQL Server 和 Windows。
5. 互相註冊伺服器：SSMS 註冊對方伺服器，**使用電腦名稱** (非 IP)，驗證選「SQL Server 驗證」，帳號 SynUser。
6. 跨網段 Hosts 對應：C:\Windows\System32\drivers\etc\hosts 加入對方 IP 與電腦名稱對應。

二、標準複寫精靈部署步驟

1. 設定發佈者/散發者

• 工具 → 複寫 → 設定發佈、訂閱伺服器和散發。
• 散發者選「本身做散發者」。
• 快照資料夾：UNC 路徑 \\Publisher\PUB。
• 散發資料庫：預設 distribution。
• 啟用發佈者：勾選本機。

2. 建立發行集

• 工具 → 複寫 → 建立和管理發行集 → 建立發行集。
• 選擇資料庫、複寫類型 (快照/交易/合併)。
• 訂閱者類型：SQL Server 2000。
• 選擇發行文章 (資料表/檢視/預存程序)。
• 發行集名稱/描述。
• 自訂屬性：建議選「是」，可設定篩選、匿名訂閱、快照排程、代理安全性。
• 完成。

3. 建立訂閱 (推式/提取式)

• 推式：發佈者端「建立推式訂閱」→ 指定訂閱者/資料庫/散發代理排程/初始化。
• 提取式：訂閱者端「建立提取式訂閱」→ 指定發佈者/發行集。
• 注意：匿名訂閱需在發行集屬性 → 訂閱選項啟用。

三、替代方案：連結伺服器 + 作業排程同步 (輕量、彈性)

適用場景：僅需單向/雙向資料表同步、不需完整複寫功能、跨網段/雲端、欲自訂同步邏輯 (衝突處理、條件同步)。

1. 建立連結伺服器

-- 來源伺服器 (SRC) 執行，連結目標伺服器 (DST)
EXEC sp_addlinkedserver 'DST', '', 'SQLOLEDB', 'DST_IP_or_Name';
EXEC sp_addlinkedsrvlogin 'DST', 'false', NULL, 'sync_user', 'sync_pwd';
GO

2. 啟用 MSDTC (分散式交易協調器) - 雙邊必做

• 控制台 → 管理工具 → 服務 → Distributed Transaction Coordinator → 啟動類型：自動 → 啟動。
• 元件服務 → 電腦 → 我的電腦 → 屬性 → MSDTC → 安全性設定：
  勾選「網路 DTC 存取」、「允許遠端用戶端」、「允許輸入/輸出」、「不需要驗證」、「啟用 TIP」。

3. 同步預存程序範例 (增量 UPSERT + 刪除同步)

CREATE PROCEDURE dbo.Sync_Table_Data
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN DISTRIBUTED TRANSACTION;

    -- 1. 更新已存在且異動的列
    UPDATE DST
    SET    Col1 = SRC.Col1, Col2 = SRC.Col2, ..., ModTime = GETDATE()
    FROM   DST_DB.dbo.TargetTable DST
    JOIN   SRC_DB.dbo.SourceTable SRC ON DST.PK = SRC.PK
    WHERE  DST.Col1 <> SRC.Col1
        OR DST.Col2 <> SRC.Col2
        -- ... 其他需比對欄位
        OR DST.ModTime <> SRC.ModTime;

    -- 2. 插入新增列
    INSERT INTO DST_DB.dbo.TargetTable (PK, Col1, Col2, ..., CreateTime)
    SELECT PK, Col1, Col2, ..., GETDATE()
    FROM   SRC_DB.dbo.SourceTable SRC
    WHERE  NOT EXISTS (SELECT 1 FROM DST_DB.dbo.TargetTable DST WHERE DST.PK = SRC.PK);

    -- 3. 刪除來源已刪除的列 (視業務決定是否啟用)
    -- DELETE DST
    -- FROM   DST_DB.dbo.TargetTable DST
    -- WHERE  NOT EXISTS (SELECT 1 FROM SRC_DB.dbo.SourceTable SRC WHERE SRC.PK = DST.PK);

    COMMIT TRANSACTION;
END;
GO

4. 建立 Agent 作業排程執行

1. SSMS → SQL Server Agent → 作業 → 右鍵「新增作業」。
2. 一般：名稱 Sync_TargetTable，啟用。
3. 步驟：新增 → 類型 T-SQL → 命令 EXEC dbo.Sync_Table_Data。
4. 排程：新增 → 週期性 → 如每 15 分鐘/每小時/每日。
5. 通知：失敗寫入事件記錄/郵件。

關鍵：確保 SQL Server Agent 服務已啟動且設為自動。

四、常見問題排解

五、現代替代方案評估

結語

SQL Server 2000 時代的標準複寫與連結伺服器排程同步，解決了許多跨伺服器資料一致性需求。隨著版本演進，AlwaysOn、CDC、雲端同步服務提供更強大、易維護的替代方案。建議依據「資料量、延遲容忍度、網路拓撲、團隊技能、授權成本」綜合評估，選擇最適合當前與未來 3-5 年的架構。