SQL Server雙機資料同步方案：復制技術與手工存儲過程實現

SQL Server復制核心概念

• 發行者：資料來源伺服器。
• 散發者：暫存變更的散發資料庫（distribution）。
• 訂閱者：接收資料的目標伺服器。
• 發行集/發行項：要同步的表/檢視/預存程序。

三種復制類型

1. 快照復制：定期發送完整資料快照，適合資料量小、變動低、容忍延遲場景。
2. 交易復制：近即時傳播交易日志，適合高一致性、低延遲需求。
3. 合併復制：雙向同步，支援離線客戶端，衝突解決機制複雜。

圖形化設定快照復制步驟

1. EM → 工具 → 復制 → 發布、訂閱伺服器和散發者 → 完成（自動建立distribution DB與監視器）。
2. 工具 → 復制 → 建立和管理發行集 → 選資料庫 → 建立發行集 → 選快照發行 → 選訂閱者類型（SQL Server 2000） → 選發行項（表） → 完成。
3. 訂閱者端 → 工具 → 復制 → 請求訂閱 → 完成（需SQL Server Agent已啟動）。
4. 驗證：發行者右鍵發行項 → 屬性 → 狀態 → 立即執行代理 → 排程設每分鐘 → 查看C:\Program Files\Microsoft SQL Server\MSSQL\REPLDATA\unc\...下每分鐘產生時間命名資料夾，或訂閱端查看表是否出現。

手工同步存儲過程方案（適用異構/特殊邏輯）

-- 環境：遠端伺服器 xz (sa/無密碼)，本地建立連結伺服器 srv_lnk
EXEC sp_addlinkedserver 'srv_lnk', '', 'SQLOLEDB', 'xz'
EXEC sp_addlinkedsrvlogin 'srv_lnk', 'false', NULL, 'sa'

-- 本地表含state旗標：NULL=新增, 1=修改, 0=同步完成
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

-- 同步存儲過程
CREATE PROC p_synchro AS
BEGIN
  -- 啟動MSDTC（分散式交易需雙端啟動）
  -- 同步刪除
  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
END

-- 建立每日0點執行的SQL Agent作業
EXEC msdb..sp_add_job @job_name = N'資料同步'
EXEC msdb..sp_add_jobstep @job_name = N'資料同步', @step_name = N'同步執行',
  @subsystem = 'TSQL', @database_name = DB_NAME(), @command = 'EXEC p_synchro',
  @retry_attempts = 5, @retry_interval = 5
EXEC msdb..sp_add_jobschedule @job_name = N'資料同步', @name = N'每日零點',
  @freq_type = 4, @freq_interval = 1, @active_start_time = 0

方案選擇建議

• 標準同步、支援復制拓撲 → **內建復制**（快照/交易/合併）。
• 需自訂衝突邏輯、異構資料庫、特殊過濾/轉換 → **手工SP + 連結伺服器 + Agent排程**。