SQL Server 2008 變更資料擷取 (CDC) 捕獲 DDL 變更完整實作

CDC 簡介

Change Data Capture (CDC) 為 SQL Server 2008 新功能，可捕獲資料表的 DDL 與 DML 變更，將變更資訊寫入對應的變更資料表。預設於資料庫層級關閉，需 sysadmin 啟用資料庫，dbo 角色成員可啟用資料表。

啟用資料庫級 CDC

USE master;
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'ChangeDataCapture')
    DROP DATABASE ChangeDataCapture;
GO
CREATE DATABASE ChangeDataCapture;
GO

USE ChangeDataCapture;
GO
EXEC sys.sp_cdc_enable_db;
GO

-- 驗證
SELECT name AS DBName, is_cdc_enabled FROM sys.databases;
-- is_cdc_enabled = 1 表示已啟用

建立測試資料表並啟用資料表級 CDC

USE ChangeDataCapture;
GO
CREATE TABLE Currency (
    CurrencyKey INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    CurrencyAlternateKey VARCHAR(5)
);
GO

-- 確認 SQL Server Agent 服務正在執行（CDC 依賴 Agent 讀取交易記錄）

EXEC sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'Currency',
    @role_name = NULL,
    @supports_net_changes = 1;
GO

-- 驗證
SELECT name, is_tracked_by_cdc FROM sys.tables;
-- is_tracked_by_cdc = 1 表示已啟用

CDC 產生的系統物件

• 新結構描述：cdc
• 新使用者：cdc
• 變更資料表：cdc.dbo_Currency_CT（記錄 DML 變更）
• 中繼資料表：cdc.ddl_history（記錄 DDL 更）

⚠️ 啟用前資料庫不可有既存 cdc 結構描述或使用者，否則設定失敗。

捕獲 DDL 變更示範

-- 修改資料表結構
ALTER TABLE Currency ADD CurrencyName VARCHAR(25);
GO

-- 查詢 DDL 變更歷程
USE ChangeDataCapture;
GO
SELECT
    OBJECT_NAME(source_object_id) AS [Table Name],
    OBJECT_NAME(object_id) AS [CDC Table Name],
    ddl_command AS [DDL Command],
    ddl_lsn AS [Log Sequence Number],
    ddl_time AS [DateModified]
FROM cdc.ddl_history;
GO

結果解讀

查詢會回傳 ALTER TABLE Currency ADD CurrencyName VARCHAR(25) 等 DDL 指令、LSN、執行時間，方便審計追蹤。

總結

CDC 讓 DBA 無需自建觸發器即可完整捕獲結構描述變更，配合 cdc.ddl_history 即時掌握 DDL 操作，強化資料治理與合規稽核能力。