SQL Server 2008 變更資料擷取 (CDC) 深度解析：系統表、擷取作業與安全角色

CDC 架構總覽

CDC 讀取啟用資料表的交易記錄，將變更寫入同資料庫的系統表，可透過直接查詢或系統函數存取。

逐步建置與觀察物件變化

1. 建立測試資料庫

CREATE DATABASE TestCdc

2. 資料庫層級啟用 CDC

USE TestCdc
EXEC sys.sp_cdc_enable_db

系統表新增（均屬 cdc 結構描述）：

• cdc.captured_columns
• cdc.change_tables
• cdc.ddl_history
• cdc.index_columns
• cdc.lsn_time_mapping
• dbo.systranschemas

資料表值函數（含內嵌空白，需特殊查詢）：

• fn_cdc_get_all_changes_...
• fn_cdc_get_net_changes_...

系統預存程序新增 4 支（批次插入 LSN 對應等）。

3. 建立使用者結構描述與資料表

CREATE SCHEMA MySchema
CREATE TABLE MySchema.Person (
    PersonID INT IDENTITY PRIMARY KEY,
    FirstName VARCHAR(32) NOT NULL,
    LastName VARCHAR(32) NOT NULL,
    UpdateCt INT NOT NULL DEFAULT 0
)

此步驟不影響上述系統物件。

4. 資料表層級啟用 CDC

EXEC sys.sp_cdc_enable_table
    @source_schema = N'MySchema',
    @source_name = N'Person',
    @role_name = N'ChangeDataAccessRole',
    @supports_net_changes = 1

新增物件：

• 系統表：cdc.MySchema_Person_CT（含 __$start_lsn, __$operation, PersonID, FirstName...）
• Agent 作業：cdc.TestCdc_capture（擷取）、cdc.TestCdc_cleanup（清除）
• 資料庫角色：ChangeDataAccessRole（隸屬 cdc，僅有兩個函數的 SELECT 權限）
• 資料表值函數：fn_cdc_get_all_changes_MySchema_Person, fn_cdc_get_net_changes_MySchema_Person
• 三支專用預存程序：sp_batchinsert_..., sp_insdel_..., sp_upd_...

關鍵元件深度剖析

擷取作業運作機制

• 屬於 sa，分類 REPL-LogReader，描述 "CDC Log Scan Job"
• 兩步驟：sys.sp_MScdc_capture_job（安全檢查後呼叫 sys.sp_cdc_scan）
• sys.sp_cdc_scan 每 5 秒一循環，呼叫 sp_replcmds 讀取待發送交易
• 記錄歷程至 sys.dm_cdc_log_scan_sessions

安全角色設計

ChangeDataAccessRole 僅授予查詢變更函數權限，遵循最小權限原則。

結語

CDC 將交易記錄非同步轉換為結構化變更資料，配合專用擷取作業與細粒度權限控管，提供可靠、低延遲的變更資料饋送機制。