追蹤記錄每筆業務操作資料改變的利器——SQLCDC

標籤：資料改變   參數   部分   實驗   示範   into   角色   系統   系統資料表   

對於大部分公司專屬應用程式來用，有一個基本的功能必不可少，那就是Audit Trail或者Audit Log，中文翻譯為追蹤檢查、審核檢查或者審核記錄。我們採用Audit Trail記錄每一筆業務操作的基本資料，比如操作的基本描述、操作時間、操作者等。對於一些安全層級比較高的應用，或者操作一些比較敏感的資料，我們甚至需要記錄該筆業務操作引起的資料的改變。具體來說，這裡的“資料改變”指的是每一條影響的記錄在操作執行前後的變化。對於添加的記錄，需要記錄下新插入的記錄；對於刪除的記錄，需要記錄下原來的記錄；對於更新的記錄，則需要同時記錄下更新前後的記錄。

說到這裡，很多人都會想到採用觸發器的方式來實現對資料改變的捕捉。但是這種實現方案具有一個最大的局限：由於觸發器是在資料操作所在事務範圍內執行的，所有會帶來效能的問題，嚴重的話還會因為觸發器的執行導致事務超市。所以在這裡，我們介紹一種更好的解決方案：SQLCDC。

目錄 
一、SQLCDC簡介 
二、在資料庫層級開啟CDC 
三、為某個資料表開啟CDC 
四、記錄添加記錄的資料改變 
五、記錄刪除資料的資料改變 
六、記錄更新記錄的資料改變

一、SQLCDC簡介

CDC的全名為Change Data Capture，顧名思義，就是用於追蹤和捕捉資料改變。CDC是在SQL Server 2008中才出現的新特性，而這個特性則在很早之前就出現在了Oracle中。對於SQL Server之前版本來說，在沒有CDC的情況下，如果需要記錄基於某個資料表的資料改變，我們只能採用觸發器，具體來說就是通過手工建立After Insert、After Update和After Delete觸發器去記錄變化的資料。而CDC給了我們一種更為方便、易用和省心的方式去記錄某個資料錶的歷史操作。

二、在資料庫層級開啟CDC

在預設的情況下，資料庫的CDC特性是被關閉的，你可以通過系統資料表sys.databases的is_cdc_enabled欄位確定某個資料庫的CDC是否開啟。如果在預設的情況下，我執行如下的SQL語句查看資料庫TestDb的CDC是否開啟，你將會看到該欄位的值為0。

你可以通過執行系統預存程序sys.sp_cdc_enable_db為當前資料庫開啟CDC特性。下面的T-SQL代碼片斷中，我們通過執行該預存程序為TestDb開啟了CDC特性。

Use TestDb

Go

Exec sys.sp_cdc_enable_db

Go

 

三、為某個資料表開啟CDC

由於CDC用於記錄基於某個資料表的資料改變，所以在當前資料庫CDC開啟的情況下，你還需要顯式地為某個資料表開啟CDC特性。作為示範，我們通過如下T-SQL在TestDb下建立了一個簡單的Users表，它僅僅具有三個欄位：Id、Name和Birthday。

CREATE TABLE [dbo].[Users](

    [Id] [varchar](50) PRIMARY KEY,

    [Name] [nvarchar](50) NOT NULL,

    [Birthday] [date] NOT NULL)

資料表的CDC特性的開啟通過執行sys.sp_cdc_enable_table預存程序實現。調用該預存程序的最簡的方式就是指定資料表的Schema、名稱和用於提取改變資料必須具有的許可權（角色）。我通過執行下面的T-SQL將我們建立的Users表的CDC特性開啟，其中@role_name參數被設定成NULL，表明我不對讀取改變資料操作進行授權。sys.sp_cdc_enable_table具有很多參數，至於相應參數所影響的CDC行為，可以參考SQL Server 2008線上文檔。

Use TestDb

Go

Exec sys.sp_cdc_enable_table ‘dbo‘, ‘Users‘, @role_name = NULL

Go

需要注意的是，CDC實際上建立在SQL Server Agent之上的，所以在執行上述T-SQL之前需要啟動SQL Server Agent。當某個資料表的CDC特性被開啟之後，系統會為建立一個用於儲存資料變化的追蹤表（Tracking Table）。該表的Schema為cdc，命名方式為被追蹤表的表名後加“CT”尾碼。執行上面一段T-SQL之後，會有如下一個系統資料表被建立出來，我們發現Users表的三個欄位也在該表中。此外。該表還具有5個額外欄位：__$start_lsn、__$end_lsn、__$seqval、__$operation 和__$update_mask，表示日誌系列號（Log Sequence Number）、操作（刪除、插入、修改前和修改後）資訊。

四、記錄添加記錄的資料改變

現在我們就可以來實驗CDC針對某個資料表的資料改變的捕捉功能了，我們先來試試記錄的添加操作。為此，我們執行如下一段T-SQL，插入兩筆User記錄。

Insert Into Users(Id, Name, Birthday)

Values (‘001‘,‘Foo‘,‘1981-08-24‘)

Insert Into Users(Id, Name, Birthday)

Values (‘002‘,‘Bar‘,‘1981-08-24‘)

然後通過如下的T-SQL查看cdc.dbo_Users_CT表的資料是否將添加操作涉及到的資料改變儲存起來。從查詢結果我們清晰地看到，上面添加的兩筆記錄已經被記錄下來，而__$operation欄位為2表示的是“插入”操作。

五、記錄更新資料的資料改變

接下來我們來CDC對更新操作的追蹤記錄，為此我們通過下面的T-SQL改變了使用者Foo的Birthday。

Update Users 

Set Birthday = ‘1982-7-10‘ 

Where Name = ‘Foo‘

再次執行對於cdc.dbo_Users_CT的全表查詢，你會看到這次多了兩筆記錄。其中第3條記錄的是修改之前的資料，而第四條則是修改之後的資料，它們的__$operation欄位德值分別為3和4。

在這裡值得一提的是__$update_mask欄位的值，它表示的記錄更新操作改變的欄位。這是一個以16進位表示的數字，在進行對修改欄位進行判斷的時候需要將其轉換成2進位。上述的更新操作對應的__$update_mask值為0x04，轉化成2進位就是100，這三位分別代表3個欄位。不過這裡的順序是從右至左，所以100這三位表示的欄位為Birthday、Name和Id。1表示改變，0則表示保持不變。由於在上面的T-SQL中，我們只改動了Birthday，這個和100這個值是吻合的。

六、記錄刪除記錄的資料改變

我們最後來示範當我們對記錄實施刪除操作的時候，CDC會為我們記錄下怎樣的資料。現在我們執行如下的T-SQL將Users表中所有的記錄均刪除。

Delete From Users

查看cdc.dbo_Users_CT的記錄，多出的兩筆記錄正式我們刪除的User記錄，__$operation欄位的值為1表示“刪除”操作。

本篇文章僅僅是簡單介紹SQLCDC的基本原理和大體上的使用方式，這篇文章《Introduction to Change Data Capture (CDC) in SQL Server 2008[轉]》會給你更加詳盡的介紹。如果你想深入研究SQLCDC，還是參考SQL Server 2008線上文檔。

追蹤記錄每筆業務操作資料改變的利器——SQLCDC