SQL Server 2008 CDC 變更資料捕獲完整實戰：從啟用到查詢、監控與維運

什麼是 CDC (Change Data Capture)？

CDC 是 SQL Server 2008 企業版/開發版引入的功能，透過非同步讀取交易記錄，自動捕獲資料表的 INSERT/UPDATE/DELETE 變更，並持久化至系統表，供下游系統（ETL、快取同步、稽核）消費。優於觸發器：低延遲、低衝突、不侵入業務邏輯。

前置條件與常見阻礙

• 版本：Enterprise / Developer Edition（Standard 不支援）。
• 權限：需 sysadmin 執行 sp_cdc_enable_db。
• 資料庫擁有者必須為有效登入（常見錯誤 15517）：
  若資料庫還原後擁有者為孤立帳號，啟用 CDC 會失敗。
  解法：ALTER AUTHORIZATION ON DATABASE::[目標資料庫] TO [sa];
• SQL Server Agent 服務必須啟動（捕獲與清理作業依賴 Agent）。

兩步驟啟用 CDC

步驟 1：資料庫層級啟用

USE GPOSDB;  -- 目標資料庫
GO
EXEC sys.sp_cdc_enable_db;
GO

-- 驗證
SELECT is_cdc_enabled,
       CASE is_cdc_enabled WHEN 1 THEN '已啟用' ELSE '未啟用' END AS 狀態
FROM sys.databases WHERE name = 'GPOSDB';

成功後，資料庫新增 cdc 結構描述、cdc 使用者、cdc_jobs 系統表，Agent 新增兩項作業：
cdc.GPOSDB_capture（捕獲）、cdc.GPOSDB_cleanup（清理）。

步驟 2：資料表層級啟用

USE GPOSDB;
GO
EXEC sys.sp_cdc_enable_table
    @source_schema = 'dbo',
    @source_name   = 'SYSTEMPARA',
    @role_name     = NULL,          -- 不限制存取角色
    @capture_instance = DEFAULT;    -- 預設捕獲執行個體名稱
GO

-- 驗證
SELECT name, is_tracked_by_cdc,
       CASE is_tracked_by_cdc WHEN 1 THEN '已啟用' ELSE '未啟用' END AS 狀態
FROM sys.tables WHERE object_id = OBJECT_ID('dbo.SYSTEMPARA');

啟用後自動產生變更表 cdc.dbo_SYSTEMPARA_CT 及查詢函數 cdc.fn_cdc_get_all_changes_dbo_SYSTEMPARA、cdc.fn_cdc_get_net_changes_dbo_SYSTEMPARA。

實測：變更捕獲與查詢

-- 1. 插入測試
INSERT INTO dbo.SystemPara (ParaValue, Name, Description) VALUES ('中國', '中國', '中國');
-- 2. 更新測試
UPDATE dbo.SystemPara SET ParaValue = '德國' WHERE Description = '中國';
-- 3. 刪除測試
DELETE FROM dbo.SystemPara WHERE Description = '中國';

直接查詢變更表（僅供除錯，生產請用函數）

SELECT * FROM cdc.dbo_SYSTEMPARA_CT;

關鍵欄位解讀：

• __$operation：1=DELETE、2=INSERT、3=UPDATE(舊值)、4=UPDATE(新值) → UPDATE 產生兩筆。
• __$start_lsn：交易記錄序號 (LSN)，可用於排序、增量讀取。

官方建議：使用查詢函數

-- 取得完整變更歷程
DECLARE @from_lsn BINARY(10) = sys.fn_cdc_get_min_lsn('dbo_SYSTEMPARA');
DECLARE @to_lsn   BINARY(10) = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_SYSTEMPARA(@from_lsn, @to_lsn, 'all update old');
GO

-- 取得淨變更（同一鍵值最終狀態）
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_SYSTEMPARA(@from_lsn, @to_lsn, 'all');
GO

進階操作：時間範圍查詢、DDL 捕獲、保留期調整

依時間範圍查詢變更

DECLARE @begin_lsn BINARY(10) = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', '2024-01-01 00:00:00');
DECLARE @end_lsn   BINARY(10) = sys.fn_cdc_map_time_to_lsn('largest less than or equal', GETDATE());

SELECT __$operation, __$start_lsn, sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS 變更時間, *
FROM cdc.dbo_SYSTEMPARA_CT
WHERE __$start_lsn BETWEEN @begin_lsn AND @end_lsn;

DDL 變更追蹤

CDC 自動記錄結構異動至 cdc.ddl_history：

ALTER TABLE dbo.SystemPara ALTER COLUMN ParaValue VARCHAR(120);
SELECT * FROM cdc.ddl_history;

調整清理保留期（預設 3 天 = 4320 分鐘）

-- 查看目前設定
EXEC sys.sp_cdc_help_jobs;

-- 修改保留期為 100 分鐘（示範）
EXEC sys.sp_cdc_change_job @job_type = 'cleanup', @retention = 100;
GO

-- 重啟作業生效
EXEC sys.sp_cdc_stop_job 'cleanup';  EXEC sys.sp_cdc_start_job 'cleanup';
GO

常用系統物件速查表

關鍵限制與避坑指南

• 僅限 Enterprise/Developer Edition，Standard/Express 無法使用。
• 依賴 SQL Server Agent，Agent 停止 → 捕獲/清理停擺。
• 記錄檔不可截斷：捕獲進程未讀取的 LSN 區段會阻止記錄截斷，長時間未處理會導致記錄檔暴增。
• 資料保留非永久：預設 3 天自動清理，需依業務調整 retention。
• 避免直接查詢 _CT 表：結構可能隨版本變更，請一律使用官方函數。
• 啟用前檢查擁有者：ALTER AUTHORIZATION ON DATABASE::[DB] TO [sa]; 可預防 15517 錯誤。

結語

CDC 為 SQL Server 提供了原生、低侵入、高可靠的變更捕獲機制，極大簡化 ETL、快取同步、稽核軌跡等場景的開發。掌握「啟用 → 驗證 → 查詢函數 → 維運調整」完整流程，即可在生產環境穩健落地。記住：Agent 必須跑、擁有者要對、保留期要配、函數要用對，CDC 就能成為你資料整合管線的基石。