SQL Server 變更資料捕獲(CDC)

Hehuyi_In發表於2020-10-24

一、簡介

在2008版本之前，通常使用DML觸發器監控對錶資料庫的變更，但是觸發器的維護比較困難，效能也不高。2008推出了新功能變更資料捕獲（Change Data Capture，CDC）可以用捕獲對錶的DML操作，常用於ETL，同步至其他（型別）資料庫。

當在一個表上啟用CDC 時，SQL Server 會建立一個系統更改表，更改表包含後設資料列及與被跟蹤表相同的列。CDC 的資料來源為 SQL Server 事務日誌，在將DML應用於跟蹤的源表時，捕獲程式讀取日誌，將記錄發生的更改記錄到更改表中。系統還將提供一些變更資料查詢函式，通過指定範圍訪問更改的資料，並以過濾結果集的形式返回。

對於啟用CDC的表DDL操作不會被阻止，但新增列也不會被對映；如果是刪除一列，目標庫該表對應列將返回null值而不是被刪除。可以為表建立另一個捕獲例項對映新架構，每個表最多可以有2個跟蹤例項。

變更資料捕獲資料流

二、實現過程（Realization）

1. 啟用CDC

例如我們的測試庫名為CDC_DB

--啟用資料庫CDC 
USE CDC_DB 
GO 
EXECUTE sys.sp_cdc_enable_db; 
GO 
--檢查啟用是否成功 
SELECT is_cdc_enabled,CASE WHEN is_cdc_enabled=0 THEN 'CDC功能禁用' ELSE 'CDC功能啟用' END 描述 FROM sys.databases WHERE NAME = 'CDC_DB';

啟動之後會自動建立一些系統表、新使用者和架構

2. 建立測試表，對錶變更啟用捕獲

/******* Step3:對錶啟用變更捕獲*******/
--建立測試表
USE CDC_DB
GO
CREATE TABLE [dbo].[Department](
    [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](200) NULL,
    [GroupName] [nvarchar](50) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
    [AddName] [nvarchar](120) NULL,
 CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED 
(
    [DepartmentID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

--對錶啟用捕獲
EXEC sys.sp_cdc_enable_table 
    @source_schema= 'dbo',
       @source_name = 'Department',
       @role_name = N'cdc_Admin',
       @capture_instance = DEFAULT,
       @supports_net_changes = 1,
    @index_name = NULL,
    @captured_column_list = NULL,
    @filegroup_name = DEFAULT

--檢查是否成功
SELECT name, is_tracked_by_cdc ,
    CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用' ELSE 'CDC功能啟用' END 描述
FROM sys.tables
WHERE OBJECT_ID= OBJECT_ID('dbo.Department');

--返回某個表的變更捕獲配置資訊
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'Department'

Department表啟用CDC後，首先會建立cdc.dbo_Department_CT系統更改表（CT代表Capture Table，表名為：使用者.架構_表_CT）。對於每一個表啟用CDC的表，都會生成一個對應的更改表。

另外會建立兩個作業 cdc.CDC_DB_capture和cdc.CDC_DB_cleanup（捕獲和清理作業），清理作業預設凌晨２點執行，清除72小時以上的資料。如果同一資料庫的表已經啟用CDC，不會重建job。需要開啟SQL Server Agent服務，不然會報錯。

多了個資料庫角色

3. DML測試

測試DML操作，觀察cdc.dbo_Department_CT幫我們記錄些什麼。

/******* Step4:測試DML變更捕獲*******/
--測試插入資料
INSERT  INTO dbo.Department(
    Name ,
    GroupName ,
    ModifiedDate
)VALUES('Marketing','Sales and Marketing',GETDATE())

--測試更新資料
UPDATE dbo.Department SET Name = 'Marketing Group',ModifiedDate = GETDATE()
WHERE Name = 'Marketing'

--測試刪除資料
DELETE FROM dbo.Department WHERE Name='Marketing Group'

--查詢捕獲資料
SELECT * FROM cdc.dbo_Department_CT

對於insert/delete操作，會有對應的一行記錄，而對於update，會有兩行記錄。

__$operation列：1 = 刪除、2= 插入、3= 更新（舊值）、4= 更新（新值）；後面幾列與表原有列相同。

4. 獲取更改資料

/******* Step6:使用LSN 檢視CDC記錄*******/
--http://msdn.microsoft.com/zh-cn/library/bb500137%28v=sql.100%29.aspx
SELECT sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal', '2013-07-24 09:00:30') AS BeginLSN

SELECT sys.fn_cdc_map_time_to_lsn
('largest less than or equal', '2013-07-24 23:59:59') AS EndLSN

/******* 檢視某時間段所有CDC記錄*******/
DECLARE @FromLSN binary(10) =
sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal' , '2013-06-23 09:00:30')

DECLARE @ToLSN binary(10) =
sys.fn_cdc_map_time_to_lsn
('largest less than or equal' , '2013-07-26 23:59:59')

SELECT CASE [__$operation]
WHEN 1 THEN 'DELETE'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'Before UPDATE'
WHEN 4 THEN 'After UPDATE'
END Operation,[__$operation],[__$update_mask],DepartmentId,Name,GroupName,ModifiedDate,AddName
FROM [cdc].[fn_cdc_get_all_changes_dbo_Department]
(@FromLSN, @ToLSN,  N'all update old')
/*
all 其中的update，只包含新值
all update old 包含新值和舊值
*/

三、 CDC的維護

1. 獲取配置資訊

--返回所有表的變更捕獲配置資訊
EXECUTE sys.sp_cdc_help_change_data_capture;

--返回某個表的變更捕獲配置資訊
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'Department'

--檢視對某個表的哪些列做了捕獲監控,使用上面返回的capture_instance列值
EXEC sys.sp_cdc_get_captured_columns
@capture_instance = 'dbo_Department'

由於前面 sys.sp_cdc_enable_table 的引數 @captured_column_list = NULL，所以dbo.Department表的所有欄位都進行監控了，如果你只關心某些欄位，可以在建立捕獲時指定。

2. 獲取job資訊

--所有資料庫CDC Job資訊
SELECT B.name,A.* FROM msdb.dbo.cdc_jobs AS A
LEFT JOIN sys.databases AS B
ON A.database_id = B.database_id

--當前資料庫CDC Job資訊
EXEC sp_cdc_help_jobs