SQL Server 2008 CDC 變更數據捕獲完整指南：原理、配置與 ETL 實戰應用

SQL Server 2008 引入的 Change Data Capture (CDC) 功能，徹底改變了增量數據抽取的傳統模式。通過異步讀取事務日志，CDC 能以極低性能開銷捕獲 DML 操作（INSERT/UPDATE/DELETE）的完整變更詳情，為數據倉庫 ETL、數據同步、審計追蹤提供了原生、高效的解決方案。

CDC 核心架構與工作原理

• 異步日志掃描：獨立捕獲進程（sp_replcmds 衍生）掃描事務日志，不阻塞用戶事務；
• 變更表自動維護：啟用 CDC 後自動創建 `__CT` 變更表，記錄操作類型、變更列掩碼、提交 LSN、時間戳；
  LSN 時間映射：`cdc.lsn_time_mapping` 系統表將 LSN 對應到提交時間，支持基於時間窗口的增量抽取；
  保留與清理：默認保留 3 天（4320 分鐘），SQL Agent 作業定期清理過期變更數據。

  逐步啟用與配置實戰

  1. 數據庫級啟用

  USE AdventureWorksDW;
  GO
  EXEC sys.sp_cdc_enable_db;
  GO

  2. 表級啟用

  EXEC sys.sp_cdc_enable_table
      @source_schema = 'dbo',
      @source_name   = 'FactInternetSales',
      @role_name     = NULL,              -- 無門角色，所有用戶可訪問
      @supports_net_changes = 0;          -- 0=記錄所有變更，1=僅記錄淨變更（需主鍵/唯一索引）

  3. 驗證啟用狀態

  SELECT name, is_tracked_by_cdc FROM sys.tables WHERE name LIKE 'fact%';

  變更數據消費模式

  模式一：基於 LSN 範圍查詢（精確增量）

  DECLARE @begin_lsn binary(10), @end_lsn binary(10);
  SELECT @begin_lsn = sys.fn_cdc_get_min_lsn('dbo_FactInternetSales');
  SELECT @end_lsn   = sys.fn_cdc_get_max_lsn();
  
  SELECT * 
  FROM cdc.fn_cdc_get_all_changes_dbo_FactInternetSales(@begin_lsn, @end_lsn, 'all');
  -- 'all' 返回所有變更行，'all update old' 另包含 UPDATE 前舊值

  模式二：基於時間窗口查詢（業務友好）

  DECLARE @begin_time datetime = DATEADD(HOUR, -1, GETUTCDATE());
  DECLARE @end_time   datetime = GETUTCDATE();
  
  DECLARE @begin_lsn binary(10) = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);
  DECLARE @end_lsn   binary(10) = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);
  
  SELECT * 
  FROM cdc.fn_cdc_get_all_changes_dbo_FactInternetSales(@begin_lsn, @end_lsn, 'all');

  實戰測試：捕獲 INSERT/UPDATE/DELETE

  -- 測試數據操作
  INSERT INTO FactInternetSales VALUES (484,1127,1139,1134,18759,1,100,6,'SO75124',1,1,1,21.9800,21.9800,0,0,8.2205,8.2205,21.9800, 1.7584,0.5495,NULL,NULL);
  INSERT INTO FactInternetSales VALUES (486,1127,1139,1134,18759,1,100,6,'SO75125',1,1,1,21.9800,21.9800,0,0,8.2205,8.2205,21.9800, 1.7584,0.5495,NULL,NULL);
  UPDATE FactInternetSales SET PromotionKey = 2 WHERE SalesOrderNumber = 'SO75124';
  DELETE FROM FactInternetSales WHERE SalesOrderNumber = 'SO75125';
  
  -- 查詢變更表
  SELECT * FROM cdc.dbo_FactInternetSales_CT;

  

  CDC vs 觸發器 vs 變更追蹤

  特性	CDC	觸發器	Change Tracking
  性能影響	極低（異步日志）	高（同步事務內）	極低（同步輕量）
  捕獲內容	完整行變更 + 元數據	自定義邏輯	僅主鍵 + 操作類型
  Schema 變更適應	自動處理（DDL 觸發）	需手動修改	不支持列級變更
  適用場景	ETL 增量抽取、審計、緩存刷新	複雜業務規則、跨表邏輯	應用緩存同步、輕量審計

  生產環境關鍵注意事項

  • 磁盤空間：高頻寫入表變更表增長極快，需獨立文件組、監控空間、調整保留策略；
  • 捕獲作業延遲：默認 5 秒輪詢間隔，高並發下可調整 `sp_cdc_change_job` 的 `@pollinginterval`；
  • 清理作業：確保 SQL Agent 運行，否則變更表無限增長導致性能下降；
  • 備份/還原：還原帶 CDC 數據庫需 `KEEP_CDC` 選項保留捕獲狀態；
  • AlwaysOn 可用性組：僅主副本運行捕獲/清理作業，故障轉移後自動續傳。

  總結

  CDC 是 SQL Server 2008+ 實現低延遲、高保真、零侵入增量數據捕獲的核心利器。正確配置保留策略、監控捕獲延遲、配合 LSN 時間映射 API，即可構建企業級實時數據集成管道，大幅簡化傳統「全量抽取/時間戳輪詢/觸發器」帶來的開發維護負擔。