SQL Server 異地資料庫同步複寫完整架設指南：交易式與合併式複寫實戰

前言

SQL Server 複寫 是實現資料庫高可用性、異地災備、報表卸載的核心技術。本文系統整理從環境前置作業、發佈/訂閱/散發者設定、複寫類型選擇、常見故障排查到緊急切換流程的完整實戰指南，基於 SQL Server 2000/2005 架構但核心邏輯適用於後續版本。

一、 環境前置作業 (雙向同步基礎)

1. 建立同步服務帳號 (雙邊同名同密)

在發佈端與訂閱端各建立一個隸屬 Administrators 群組的 Windows 本地帳號 (如 repl_user)，密碼完全一致。此帳號用於：

• SQL Server (MSSQLSERVER) 服務啟動帳號
• SQL Server Agent (SQLSERVERAGENT) 服務啟動帳號
• 快照資料夾共享存取身分

2. 設定快照共享資料夾權限

發佈端建立資料夾 (如 D:\PUB) → 右鍵共享 → 權限 → 加入 repl_user → 給予 完全控制。訂閱端需能以此帳號存取 `\\Publisher\PUB`。

3. 設定 SQL 服務啟動身分

服務管理工具 → MSSQLSERVER / SQLSERVERAGENT → 內容 → 登入 → 選「此帳號」 → 輸入 repl_user 及密碼 → 重啟服務。

4. 驗證模式設為混合模式

企業管理器/SSMS → 伺服器屬性 → 安全性 → 選 SQL Server 和 Windows 驗證模式。

5. 伺服器互相註冊與別名設定

雙向在企業管理器註冊對方 (用 SQL 驗證，帳號 sa 或專用帳號)。若無法解析電腦名，需在 用戶端網路公用程式 設定 TCP/IP 別名，指向對方 IP。

二、 建立發佈與散發者架構

1. 設定散發者 (通常佈署在發佈端或獨立伺服器)

• 企業管理器 → 複寫 → 右鍵 設定發佈、訂閱伺服器和散發者。
• 選「使自己成為散發者」→ 快照資料夾指向步驟 2 的共享路徑 → 完成。系統會建立 distribution 資料庫與 distributor_admin 使用者。

2. 加入遠端發佈者 (若散發者獨立)

散發者屬性 → 發佈者 → 新增 → 輸入遠端發佈者名稱、連線驗證 (用 distributor_admin 密碼)。

3. 建立發佈

• 複寫 → 發佈 → 右鍵 新增發佈。
• 選發佈資料庫 → 發佈類型：
  交易式複寫：低延遲、高一致性，需主鍵，適合 OLTP 同步。
  合併式複寫：允許雙端寫入、離線同步，會加 rowguid 欄位，適合行動/分散式場景。
• 選發佈文章 (資料表/檢視/預存程序) → 可設定資料行/列篩選 (如 `WHERE region='TW'`)。
• 設定快照代理程式排程 (立即或定時)。

三、 建立訂閱 (Pull 訂閱為例)

1. 訂閱端企業管理器 → 複寫 → 訂閱 → 右鍵 新增提取訂閱。
2. 尋找發佈 → 選目標發佈 → 同步代理程式登入用 distributor_admin。
3. 選目的資料庫 (可新建) → 允許匿名訂閱 → 初始化結構與資料 → 快照資料夾用發佈端預設共享。
4. 散發代理程式排程：如 每 5 分鐘 執行一次 (交易式) 或依業務需求。
5. 完成後訂閱端會建立 REPL-散發 類別作業。

四、 複寫監控與常見異常處理

關鍵作業說明

斷網/重啟情境分析

• 發佈端斷網/重啟：影響最小，恢復後 LogReader 續傳未送交易。
• 散發端斷網/重啟：發佈端交易堆積於記錄檔 (可能暴增)，訂閱端重試。恢復後需時間追趕堆積命令 (實測 10 萬命令約 7 分鐘)。
• 訂閱端斷網/重啟：風險最大，可能需重新初始化 (重新套用快照)。若設定「保留過期訂閱」時間較長，可能自動追趕。

錯誤 3724: 無法刪除複寫使用的資料表

-- 解除複寫標記 (謹慎使用，建議先備份)
USE master;
GO
sp_configure 'allow updates', 1; RECONFIGURE WITH OVERRIDE;
GO
BEGIN TRAN;
UPDATE sysobjects SET replinfo = 0 WHERE replinfo > 0;
COMMIT TRAN;
GO
sp_configure 'allow updates', 0; RECONFIGURE WITH OVERRIDE;
GO

五、 進階應用：日誌傳送 實現暖備援 (Log Shipping)

原理：主庫 BACKUP LOG → 共享資料夾 → 備庫 RESTORE LOG ... WITH STANDBY (唯讀可查詢)。

-- 主庫建立同步作業 (每分鐘)
EXEC msdb..sp_add_job @job_name = N'LogShipping_Sync';
EXEC msdb..sp_add_jobstep @job_name = N'LogShipping_Sync', @step_name = N'BackupLog',
    @command = N'BACKUP LOG Db_test TO DISK=\\Shared\Logs\test_log.bak WITH FORMAT';
EXEC msdb..sp_add_jobstep @job_name = N'LogShipping_Sync', @step_name = N'RestoreLog',
    @command = N'RESTORE LOG Db_test_bak FROM DISK=\\Shared\Logs\test_log.bak WITH STANDBY=\\Shared\Logs\test_log.ldf';
-- 加入排程每分鐘執行

主庫災難切換至備庫

1. 若主庫仍可備份尾部記錄：BACKUP LOG Db_test TO ... → 備庫 RESTORE LOG Db_test_bak FROM ... WITH RECOVERY。
2. 若主庫損毀無法備份：備庫直接 RESTORE LOG Db_test_bak WITH RECOVERY (損失尾部交易)。

六、 總結與建議

• 複寫 適合雙向同步、報表卸載、異地多活；日誌傳送 適合單向暖備援、成本低。
• 生產環境務必啟用 複寫監視器警示 (代理程式失敗、延遲閾值、過期訂閱)。
• 定期演練 容錯移轉 與 重新初始化 流程，文件化 SOP。
• SQL Server 2012+ 建議改用 AlwaysOn 可用性群組 取代傳統複寫/日誌傳送，獲得自動容錯移轉與讀取延展。