[SQL Server] 如何追蹤資料異動記錄 (1) : Change Data Capture

前言

以往應用系統的使用者, 常會問說, 我的那筆客戶資料, 到底是誰動過的, 跟我當初所輸入的怎麼會不相同.

如果應用系統作得稍微好一點, 會留下最後那筆資料的異動時間及異動人員; 但這只是留下該筆資料最後一個版本, 並不能找出異動資料的元兇.

如果應用系統作得還不錯, 可能會利用 Trigger, 自行撰寫一段程式碼, 把 inserted 及 deleted 的資料, 寫到一個 Log 記錄檔, 這樣就可以找出異動資料的元兇.

自 SQL Server 2008 開始, 微軟提供了 Change Data Capture 及 Change Data Tracker.

本文重點在 Change Data Capture. 下一篇會著重在 SQL Server 2016 開始提供的 System-Versioned Temporal Table.

茲分為以下幾個實作步驟進行說明:

1.. 建立測試資料庫

2.. 啟用資料庫 Change Data Caputre 的功能

3.. 建立測試資料表

4.. 針對特定資料表, 啟用 Change Data Capture

5.. 增加 2 筆資料試試

6.. 修改 2 筆資料試試

7.. 刪除 1 筆資料試試

8.. SQL Server 如何清除 Change Data Capture 的資料

9.. 另一個應用情境: ETL 整合

10.. 參考文件

1.. 建立測試資料庫

DROP DATABASE IF EXISTS CDCSample;
GO
CREATE DATABASE CDCSample;
GO

2.. 啟用資料庫 Change Data Caputre 的功能

USE CDCSample
GO
EXEC sys.sp_cdc_enable_db
GO

3.. 建立測試資料表

DROP TABLE IF EXISTS Employee;
GO
CREATE TABLE Employee 
( [ID] int NOT NULL PRIMARY KEY CLUSTERED 
, [Name] nvarchar(100) NOT NULL
, [Position] varchar(100) NOT NULL 
, [Department] varchar(100) NOT NULL
, [Address] nvarchar(1024) NOT NULL
, [AnnualSalary] decimal (10,2) NOT NULL
);
GO

4.. 針對特定資料表, 啟用 Change Data Capture

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'Employee',
@role_name     = NULL
GO

執行結果:
(1) 會產生 1 個 cdc.dbo_Employee_CT 的系統資料表. 所有針對 dbo.Employee 的資枓改變記錄, 都會留在這裡.

(2) 在 SQL Server Agent 上會有 2 個 job : dbo.CDCSample_capture , dbo.CDCSample_cleanup

作業 'cdc.CDCSample_capture' 已成功啟動。

作業 'cdc.CDCSample_cleanup' 已成功啟動。

留意上述 $operation 欄位的定義

Delete Statement = 1
Insert Statement = 2
Value before Update Statement = 3
Value after Update Statement = 4

留意上述 $update_mask 欄位的定義

它代表那個欄位被異動過 (每個 bit 代表 1 個欄位)
如果是 insert or delete, 每個 bit 都會是 1. 例如: 總共有 6 個欄位, 則會是 0x3F (0011 1111)
如果是 update, 只有被異動的資料會是 1. 例如: 異動第 6 個欄位, 則會是 0x20 (0010 0000)

SELECT [name], is_tracked_by_cdc 
FROM sys.tables
GO

查詢一下資料庫各個資料表是否已啟用 Change Data Capture

5.. 增加 2 筆資料試試

INSERT INTO [dbo].[Employee]
VALUES (1, 'jasper', 'engineer', 'vx', 'taipei', 1500)
,      (2, 'polaris', 'senior engineer', 'vx', 'taipei', 2000)
;        
GO

查一下 dbo_Employee_CT

SELECT *
FROM cdc.dbo_Employee_CT;
GO

6.. 修改 2 筆資料試試

UPDATE A
SET A.AnnualSalary = 2500
FROM dbo.Employee A
GO

查一下 dbo_Employee_CT, 因為異動了 AnnualSalary 欄位 (第 6 個), 所以 $update_mask 的值是 0x20 (0010 0000)

SELECT *
FROM cdc.dbo_Employee_CT;
GO

7.. 刪除 1 筆資料試試

DELETE A
FROM dbo.Employee A
WHERE A.ID = 2
GO

查一下 dbo_Employee_CT

SELECT *
FROM cdc.dbo_Employee_CT;
GO

8.. SQL Server 清除 Change Data Capture 的資料

(1) Change Data Capture 的貟料, 是由掛在 SQL Server Agent 的 cdc.CDCSample_cleanup 開始執行的 (每天凌晨 02:00 執行, 呼叫 sys.sp_MScdc_cleanup_job ).

(2) sys.sp_MScdc_cleanup_job 的內容, 摘要如下. 它會再呼叫其它 function 或 stored procedure, 但黃底字都查不到它的程式內容.
主要的重點, 在於呼叫 sp_cdc_get_cleanup_retention 取回 @retention 及 @threshold ; 再把取得的 @retention 及 @threshold 傳入到 sp_cdc_cleanup_job_internal 清除資料.

ALTER procedure [sys].[sp_MScdc_cleanup_job]
as
begin
    declare @retcode int
          , @db_name sysname
          , @retention bigint
          , @threshold bigint
    ....       

    -- Verify database is enabled for change data capture
    if ([sys].[fn_cdc_is_db_enabled]() != 1)
    begin
              raiserror(22910, 16, -1, @db_name)
        return(1)
    end
    -- get cleanup retention and threshold from msdb
    exec @retcode = sp_cdc_get_cleanup_retention @retention output, @threshold output
    if @retcode <> 0 or @@error <> 0
              return(1)
    .... 

    -- Call internal stored procedure to do the work here.
    -- Switch to database 'cdc' user to mitigate against malicious DML triggers.
    execute as user = 'cdc'
       
       exec @retcode = sys.sp_cdc_cleanup_job_internal @retention, @threshold
    if @retcode <> 0 or @@error <> 0
    begin
        revert
        return(1)
    end
       
    revert 
   
    return(0)
end

並附上截圖如下, 以標示主要的重點.

(3) 依參考文件 7.. 的說明, 看來資料會保留 3 天; 以參考文件 8.. 實測, 確實是保留 4320 minutes = 3 days.

select retention, threshold
    from msdb.dbo.cdc_jobs
    where database_id = db_id()
    and job_type = N'cleanup'
;

關於 retention 及 threshold 的欄位說明

9.. 另一個應用情境: ETL 整合

茲以下圖表示, 一個整合至 ETL 的情境. 可以把異動的資料, 由 change table 取出, 不用再去原來的 source table 去讀取了.

10.. 參考文件

1.. Microsoft Docs, "Tracking Data Changes (SQL Server)"

2.. redgate Hub, "Introduction to Change Data Capture (CDC) in SQL Server 2008"

--> 這篇有蠻完整的範例可以參考

3.. Technet, "About Change Data Capture (SQL Server)"

--> 可以選擇各個不同版本查看說明 (2012 / 2014 / 2016 and later)

4.. SQLShack, "How to track changes in SQL Server"

5.. Microsoft Docs, "Editions and supported features of SQL Server 2016"

--> 這篇有提到各個版本間功能的差異, 但關於 Change Data Capture, 它是列在 Data Warehouse 的部份 (有點怪 ...)
--> SQL Server 2016 Enterprise Edition 有支援; 自 SQL Server 2016 SP1 起, Standard Edition 也有支援

--> 筆者手邊只有 Developer Edition, 沒有 Standard Edition, 無法作驗證

6.. StackOverflow, "What SQL Server edition supports CDC?"

--> 這篇是提到, 只有 Enterprise 及 Developer Edition 有此功能

7.. StackOverflow, "How the cdc retention value can be changed for the cleanup job?"

--> 這篇有提到, @retention 是 4320 minutes = 72 hrs = 3 days

8.. G-Productions, "sys.sp_cdc_get_cleanup_retention"

--> 這篇有提供 sp_cdc_get_cleanup_retention 在 SQL 2008 SP1 的內容

9.. MSSQL Tips, "SQL Server Temporal Tables vs Change Data Capture vs Change Tracking - part 1"

10.. MSSQL Tips, "SQL Server Temporal Tables vs Change Data Capture vs Change Tracking - part 2"

11.. MSSQL Tips, "SQL Server Temporal Tables vs Change Data Capture vs Change Tracking - part 3"

--> 這3篇, 有將 System-Versioned Temporal Tables 與 Change Data Capture / Change Tracking 作了比較

傑士伯的IT學習之路

2018年1月1日星期一