SQL Server 2008 變更資料擷取 (CDC) 實作完整指南：啟用、擷取、清理與停用

CDC 核心原理

Change Data Capture (CDC) 在每次 INSERT/UPDATE/DELETE 時擷取變更資料。交易記錄檔記錄 DML 變更，擷取程序將來源表變更寫入變更表，ETL 工具透過 CDC 查詢函數抽取至資料倉庫。優於觸發器：對來源交易效能影響小，可取得變更中繼資料。

實作步驟 (基於 CTP3)

1. 啟用 SQL Server Agent

2. 建立資料庫

3. 啟用資料庫層級 CDC

USE [DatabaseForCDC];
EXECUTE [sys].[sp_cdc_enable_db_change_data_capture];

系統自動建立 cdc 結構描述、使用者與系统表 (captured_columns, change_tables, ddl_history, index_columns, lsn_time_mapping)。

4. 建立來源表與唯一索引

CREATE TABLE Source_Table (column_key int PRIMARY KEY, column_1 varchar(50), column_2 varchar(50));

5. 啟用資料表層級 CDC

EXEC sys.sp_cdc_enable_table_change_data_capture
    @source_schema = 'dbo',
    @source_name = 'Source_Table',
    @role_name = 'change_table_user1',
    @index_name = 'IX_Source_Table',
    @capture_instance = 'ST_Instance',
    @supports_net_changes = 1,
    @captured_column_list = 'column_key, column_1, column_2',
    @filegroup_name = 'PRIMARY';

參數說明：@supports_net_changes=1 需主鍵或唯一索引；@capture_instance 一張表最多兩個實例；系統自動建立變更表 ST_Instance_CT。

變更資料儲存格式

變更表包含來源欄位外，額外 5 個中繼欄位 (以 __$ 開頭)：

• __$start_lsn：記錄序號 (LSN)
• __$end_lsn：保留
• __$seqval：交易內列序號
• __$operation：1=DELETE, 2=INSERT, 3=UPDATE(前), 4=UPDATE(後)
• __$update_mask：二進位標識變更欄位

查詢變更資料

LSN 與時間轉換

• sys.fn_cdc_map_lsn_to_time、sys.fn_cdc_map_time_to_lsn

取得最小/最大 LSN

• sys.fn_cdc_get_min_lsn、sys.fn_cdc_get_max_lsn

擷取所有變更 / 淨變更

• cdc.fn_cdc_get_all_changes_<instance>：回傳範圍內所有變更記錄。
• cdc.fn_cdc_get_net_changes_<instance>：僅回傳最終狀態 (淨變更)。

識別變更欄位

• sys.fn_cdc_is_bit_set(ordinal, __$update_mask) 判斷特定欄位是否變更。
• sys.fn_cdc_get_column_ordinal 取得欄位序數。

DDL 變更歷程

• sys.sp_cdc_get_ddl_history 查詢來源表 DDL 變更，同步調整變更表。

清理與停用

• sys.sp_cdc_cleanup_change_table 週期性清除已載入資料倉庫的變更資料，Agent 自動建立每日清理作業。
• sys.sp_cdc_disable_table_change_data_capture 停用資料表 CDC。
• sys.sp_cdc_disable_db_change_data_capture 停用資料庫 CDC。

結語

CDC 於 CTP3 引入，提供低衝擊、高可靠的變更擷取機制，雖較 Oracle 成熟度仍有提升空間，但為 SQL Server 資料倉庫更新帶來原生解決方案。