SQL Server 2005 交易式複寫 可將資料變更近即時傳遞至訂閱端，適用於報表伺服器卸載、異地災備、多站點同步等場景。本文涵蓋前置需求 (帳戶、連通性、共享資料夾、SQL Agent 設定、防火牆、網路協議)、發布/訂閱精靈操作步驟、快照代理設定、常見錯誤排查 (命名管道/TCP/IP、IP 連線失敗) 及快速驗證腳本。
前置準備 (雙端皆需)
1. 建立專用 Windows 帳戶
- 帳戶名：
repl_agent (範例)
- 加入本機 Administrators** 群組
- 設定強式密碼，密碼永不過期
2. 網路連通性
- 雙向 Ping 通、
telnet IP 1433 通
- 防火牆開放 1433 (TCP)**、1434 (UDP)**、445 (SMB/共享資料夾)**
- SQL Server Configuration Manager → 網路設定 → 啟用 TCP/IP、Named Pipes**，重啟服務
3. 快照共享資料夾 (發布端)
- 建立資料夾：
D:\ReplSnapshot
- 共享權限：
Everyone: 讀取、repl_agent: 讀取/寫入
- NTFS 權限：同步給
repl_agent 完整控制
- UNC 路徑：
\\PublisherServer\ReplSnapshot
4. SQL Server Agent 服務帳戶設定
- 服務管理器 →
SQL Server Agent → 內容 → 登入
- 選「此帳戶」 → 輸入
repl_agent 及密碼
- 雙端 (發布/訂閱) 同步設定
5. 驗證模式設為混合模式
- SSMS 右鍵實例 → 內容 → 安全性 → SQL Server 和 Windows 驗證模式**
- 重啟 SQL Server 服務
6. 建立 SQL 登入對應 Windows 帳戶
CREATE LOGIN [DOMAIN\repl_agent] FROM WINDOWS;
-- 或 SQL 登入
CREATE LOGIN repl_agent WITH PASSWORD = 'StrongPass123!';
-- 授予發布資料庫 db_owner
USE PublishDB;
CREATE USER repl_agent FOR LOGIN repl_agent;
EXEC sp_addrolemember 'db_owner', 'repl_agent';
7. 雙向註冊伺服器 (SSMS 註冊伺服器)
- 檢視 → 註冊的伺服器 → 右鍵資料庫引擎 → 新增伺服器註冊
- 驗證：SQL Server 驗證 /
sa / 密碼
8. IP 連線無法解析主機名時：建立伺服器別名
- SQL Server Configuration Manager → SQL Native Client 設定 → 別名 → 新增別名
- 別名：
PublisherAlias，伺服器：IP 位址，通訊協定：TCP/IP，連接埠：1433
發布端設定 (發布 + 訂閱一次完成)
1. 設定散發者
- SSMS → 複寫 → 右鍵 本機發布** → 設定散發**
- 散發者：本機 (或遠端專用散發者)
- 快照資料夾：
\\PublisherServer\ReplSnapshot
- 散發資料庫：預設
distribution
- 完成精靈
2. 建立發布
- 右鍵 本機發布** → 新增發布**
- 發布資料庫：選擇目標資料庫
- 發布類型：交易式發布** (Transactional Publication)
- 發行項：勾選需同步的資料表 (需有主鍵)
- 篩選：視需求加入列/列篩選
- 快照代理：立即建立快照**、排程依需求 (如每日 02:00)
- 代理安全性：使用 SQL Server Agent 服務帳戶**、連接發布者用
repl_agent SQL 登入
- 發布名稱：
Pub_Transactional
3. 建立訂閱 (推送訂閱 - 發布端主動推送)
- 右鍵 本機訂閱** → 新增訂閱**
- 發布者：選擇發布伺服器
- 發布：選擇
Pub_Transactional
- 訂閱類型：推送訂閱** (發布端執行散發代理)
- 訂閱者：輸入訂閱伺服器名稱/IP
- 訂閱資料庫：選擇/建立目標資料庫
- 代理排程：連續執行** (近即時) 或 定期 (如每 5 分鐘)
- 初始化：立即** (使用快照)
- 完成
常見錯誤與解決
錯誤：無法連接到 IP，命名管道提供者錯誤 233
無法連接到 10.114.x.x
已成功建立連接，但是在登入過程中發生錯誤。
(provider: 命名管道提供者, error: 0 - 管道的另一端上無任何進程。) (Microsoft SQL Server, 錯誤: 233)
解決
- SQL Server Configuration Manager → SQL Server 網路設定 → MSSQLSERVER 協定
- 啟用 Named Pipes 和 TCP/IP**
- 重啟 SQL Server 服務
錯誤：快照代理失敗、權限不足
- 確認
repl_agent 對共享資料夾有讀寫權限
- 檢查散發代理作業歷程記錄詳細錯誤訊息
錯誤：主鍵衝突、資料類型不符
- 發行項資料表 必須有主鍵
- 發布/訂閱端結構完全一致 (含觸發器、條件約束、索引)
驗證複寫運作
-- 發布端插入測試資料
INSERT INTO PublishDB.dbo.TestTable (Col1, Col2) VALUES ('ReplTest', GETDATE());
-- 訂閱端查詢 (應在幾秒內出現)
SELECT * FROM SubscribeDB.dbo.TestTable WHERE Col1 = 'ReplTest';
-- 複寫監視器 (SSMS → 複寫 → 右鍵啟動複寫監視器)
-- 檢查：未散發命令數、延遲時間、代理狀態
複寫類型快速選擇
| 類型 | 延遲 | 適用場景 | 雙向寫入 |
|---|
| 交易式 | 秒級 | 報表卸載、災備、單向同步 | 否 (可用雙向交易式) |
| 合併式 | 排程 | 離線用戶端、雙向同步、衝突解決 | 是 |
| 快照式 | 批次 | 小資料量、定期重整、唯讀報表 | 否 |
| 點對點 | 秒級 | 多主機雙向寫入、高可用 | 是 |
維護建議
- 定期監控 複寫監視器：延遲 < 30 秒、無錯誤、代理執行中
- 設定警示：
sp_add_alert 監控「複寫: 未散發命令數 > 閾值」
- 定期重建索引、更新統計 (發布/訂閱端)
- 大量異動 (如 ETL 載入) 時暫停散發代理，完成後再啟動
- 備份散發資料庫
distribution、發布資料庫、訂閱資料庫
