什麼是 CDC？
Change Data Capture (CDC) 非同步讀取交易記錄檔，擷取資料表的 INSERT/UPDATE/DELETE 變更，寫入系統變更表，供應用程式（ETL、快取同步、稽核）以低延遲、低衝擊方式取得增量資料。SQL Server 2008 Enterprise/Developer/Datacenter 版支援。
啟用流程與產生的系統物件
1. 資料庫層級啟用
CREATE DATABASE TestCdc;
GO
USE TestCdc;
GO
EXEC sys.sp_cdc_enable_db;
產生 6 張系統表（`cdc` schema）：`captured_columns`、`change_tables`、`ddl_history`、`index_columns`、`lsn_time_mapping`、`systranschemas`；4 個內嵌資料表值函數（`fn_cdc_get_all_changes_...`、`fn_cdc_get_net_changes_...`）；多個系統預存程序（`sp_cdc_*`）。
2. 建立使用者資料表
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
);
3. 資料表層級啟用 CDC
EXEC sys.sp_cdc_enable_table
@source_schema = N'MySchema',
@source_name = N'Person',
@role_name = N'ChangeDataAccessRole', -- 控制存取權限的角色
@supports_net_changes = 1; -- 支援 net changes 函數
新增物件：
- 變更表：`cdc.MySchema_Person_CT`（含 `__$start_lsn`、`__$operation`、`__$update_mask` 等中繼欄位 + 業務欄位）
- 兩個 Agent 作業：`cdc.TestCdc_capture`（掃描記錄檔）、`cdc.TestCdc_cleanup`（清理過期變更）
- 資料庫角色：`ChangeDataAccessRole`
- 兩個查詢函數：`fn_cdc_get_all_changes_MySchema_Person`、`fn_cdc_get_net_changes_MySchema_Person`
- 三個專用預存程序：`sp_batchinsert_...`、`sp_insdel_...`、`sp_upd_...`（捕獲邏輯）
核心查詢函數
| 函數 | 用途 | 回傳內容 |
|---|
fn_cdc_get_all_changes_<capture_instance> | 指定 LSN 範圍內的所有變更（含中間更新） | 每筆變更一列，`__$operation`：1=Delete、2=Insert、3=Update(前)、4=Update(後) |
fn_cdc_get_net_changes_<capture_instance> | 指定 LSN 範圍內的淨變更（每 PK 只回最終狀態） | 需 `@supports_net_changes=1` 且 PK 唯一；`__$operation`：1=Delete、2=Insert/Update(合併) |
查詢範例：取得昨天至今的淨變更
DECLARE @begin_lsn binary(10), @end_lsn binary(10);
SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', DATEADD(day,-1,GETDATE()));
SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', GETDATE());
SELECT * FROM cdc.fn_cdc_get_net_changes_MySchema_Person(@begin_lsn, @end_lsn, 'all');
-- 'all' 顯示最終狀態；'all update old' 顯示更新前後值
捕獲作業運作機制
`cdc._capture` 作業由 SQL Server Agent 執行，屬 `REPL-LogReader` 類別，實際呼叫 `sp_replcmds` 掃描記錄檔。預設輪詢間隔 5 秒，每次最多處理 500 筆交易。可透過 `sp_cdc_change_job` 調整。
清理作業與保留原則
`cdc._cleanup` 每天執行一次，預設保留 3 天（4320 分鐘），最多每次刪 5000 筆。調整：
EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 10080; -- 7 天
安全性模型
- `sysadmin` / `db_owner` 具完整存取權。
- 其他使用者需被加入 `@role_name` 指定的角色（如 `ChangeDataAccessRole`）才能查詢變更函數。
- 若 `@role_name = NULL`，則無角色限制（不建議）。
常見應用場景與注意事項
- ETL 增量載入：資料倉庫夜間批次改為近即時 CDC 串流。
- 快取同步/搜尋索引重建：變更事件觸發應用層刷新。
- 稽核追蹤：保留完整變更歷史，滿足合規。
- 限制：不支援記憶體最佳化表、不支援資料庫鏡像/AlwaysOn 可讀次要複本（需主複本啟用）、DDL 變更需手動處理（`sp_cdc_ddl_event`）。
- 效能：捕獲作業讀取記錄檔屬順序 I/O，影響極小；但大量變更時清理作業可能造成鎖爭用，建議調整保留期與批次大小。
結語
CDC 是 SQL Server 內建、低延遲、無需程式碼侵入的變更擷取方案。掌握「啟用層級 → 系統物件 → 查詢函數 → 作業調校 → 安全性」五大面向，即能穩健落地。
- 上一篇文章： SQL Server 基礎教學：SELECT 與 DISTINCT 查詢完整指南
- 下一篇文章： Oracle 遷移至 SQL Server：主鍵約束自動化腳本實作（T-SQL 版）
Copyright ©
程式碼之家 All Rights Reserved
