對(duì)于大部分企業(yè)應(yīng)用來(lái)用，有一個(gè)基本的功能必不可少，那就是Audit Trail或者Audit Log，中文翻譯為追蹤檢查、審核檢查或者審核記錄。我們采用Audit Trail記錄每一筆業(yè)務(wù)操作的基本信息，比如操作的基本描述、操作時(shí)間、操作者等。對(duì)于一些安全級(jí)別比較高的應(yīng)用，或者操作一些比較敏感的數(shù)據(jù)，我們甚至需要記錄該筆業(yè)務(wù)操作引起的數(shù)據(jù)的改變。具體來(lái)說(shuō)，這里的"數(shù)據(jù)改變"指的是每一條影響的記錄在操作執(zhí)行前后的變化。對(duì)于添加的記錄，需要記錄下新插入的記錄；對(duì)于刪除的記錄，需要記錄下原來(lái)的記錄；對(duì)于更新的記錄，則需要同時(shí)記錄下更新前后的記錄。

　　說(shuō)到這里，很多人都會(huì)想到采用觸發(fā)器的方式來(lái)實(shí)現(xiàn)對(duì)數(shù)據(jù)改變的捕捉。但是這種實(shí)現(xiàn)方案具有一個(gè)最大的局限：由于觸發(fā)器是在數(shù)據(jù)操作所在事務(wù)范圍內(nèi)執(zhí)行的，所有會(huì)帶來(lái)性能的問(wèn)題，嚴(yán)重的話還會(huì)因?yàn)橛|發(fā)器的執(zhí)行導(dǎo)致事務(wù)超市。所以在這里，我們介紹一種更好的解決方案：SQLCDC。

一、SQLCDC簡(jiǎn)介

　　CDC的全名為Change Data Capture，顧名思義，就是用于追蹤和捕捉數(shù)據(jù)改變。CDC是在SQL Server 2008中才出現(xiàn)的新特性，而這個(gè)特性則在很早之前就出現(xiàn)在了Oracle中。對(duì)于SQL Server之前版本來(lái)說(shuō)，在沒(méi)有CDC的情況下，如果需要記錄基于某個(gè)數(shù)據(jù)表的數(shù)據(jù)改變，我們只能采用觸發(fā)器，具體來(lái)說(shuō)就是通過(guò)手工創(chuàng)建After Insert、After Update和After Delete觸發(fā)器去記錄變化的數(shù)據(jù)。而CDC給了我們一種更為方便、易用和省心的方式去記錄某個(gè)數(shù)據(jù)表的歷史操作。

二、在數(shù)據(jù)庫(kù)級(jí)別開(kāi)啟CDC

　　在默認(rèn)的情況下，數(shù)據(jù)庫(kù)的CDC特性是被關(guān)閉的，你可以通過(guò)系統(tǒng)表sys.databases的is_cdc_enabled字段確定某個(gè)數(shù)據(jù)庫(kù)的CDC是否開(kāi)啟。如果在默認(rèn)的情況下，我執(zhí)行如下的SQL語(yǔ)句查看數(shù)據(jù)庫(kù)TestDb的CDC是否開(kāi)啟，你將會(huì)看到該字段的值為0。

　　你可以通過(guò)執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程sys.sp_cdc_enable_db為當(dāng)前數(shù)據(jù)庫(kù)開(kāi)啟CDC特性。下面的T-SQL代碼片斷中，我們通過(guò)執(zhí)行該存儲(chǔ)過(guò)程為T(mén)estDb打開(kāi)了CDC特性。

　　Use TestDbGoExec sys.sp_cdc_enable_dbGo

三、為某個(gè)數(shù)據(jù)表開(kāi)啟CDC

　　由于CDC用于記錄基于某個(gè)數(shù)據(jù)表的數(shù)據(jù)改變，所以在當(dāng)前數(shù)據(jù)庫(kù)CDC開(kāi)啟的情況下，你還需要顯式地為某個(gè)數(shù)據(jù)表開(kāi)啟CDC特性。作為演示，我們通過(guò)如下T-SQL在TestDb下創(chuàng)建了一個(gè)簡(jiǎn)單的Users表，它僅僅具有三個(gè)字段：Id、Name和Birthday。

　　CREATE TABLE [dbo].[Users](
[Id] [varchar](50) PRIMARY KEY,
[Name] [nvarchar](50) NOT NULL,
[Birthday] [date] NOT NULL)

　　數(shù)據(jù)表的CDC特性的開(kāi)啟通過(guò)執(zhí)行sys.sp_cdc_enable_table存儲(chǔ)過(guò)程實(shí)現(xiàn)。調(diào)用該存儲(chǔ)過(guò)程的最簡(jiǎn)的方式就是指定數(shù)據(jù)表的Schema、名稱(chēng)和用于提取改變數(shù)據(jù)必須具有的權(quán)限（角色）。我通過(guò)執(zhí)行下面的T-SQL將我們創(chuàng)建的Users表的CDC特性打開(kāi)，其中@role_name參數(shù)被設(shè)置成NULL，表明我不對(duì)讀取改變數(shù)據(jù)操作進(jìn)行授權(quán)。sys.sp_cdc_enable_table具有很多參數(shù)，至于相應(yīng)參數(shù)所影響的CDC行為，可以參考SQL Server 2008在線文檔。

　　Use TestDb
Go
Exec sys.sp_cdc_enable_table 'dbo', 'Users', @role_name = NULL
Go

　　需要注意的是，CDC實(shí)際上建立在SQL Server Agent之上的，所以在執(zhí)行上述T-SQL之前需要啟動(dòng)SQL Server Agent。當(dāng)某個(gè)數(shù)據(jù)表的CDC特性被開(kāi)啟之后，系統(tǒng)會(huì)為創(chuàng)建一個(gè)用于保存數(shù)據(jù)變化的追蹤表（Tracking Table）。該表的Schema為cdc，命名方式為被追蹤表的表名后加"CT"后綴。執(zhí)行上面一段T-SQL之后，會(huì)有如下一個(gè)系統(tǒng)表被創(chuàng)建出來(lái)，我們發(fā)現(xiàn)Users表的三個(gè)字段也在該表中。此外。該表還具有5個(gè)額外字段：__$start_lsn、__$end_lsn、__$seqval、__$operation 和__$update_mask，表示日志系列號(hào)（Log Sequence Number）、操作（刪除、插入、修改前和修改后）信息。

四、記錄添加記錄的數(shù)據(jù)改變

　　現(xiàn)在我們就可以來(lái)試驗(yàn)CDC針對(duì)某個(gè)數(shù)據(jù)表的數(shù)據(jù)改變的捕捉功能了，我們先來(lái)試試記錄的添加操作。為此，我們執(zhí)行如下一段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')

　　然后通過(guò)如下的T-SQL查看cdc.dbo_Users_CT表的數(shù)據(jù)是否將添加操作涉及到的數(shù)據(jù)改變保存起來(lái)。從查詢(xún)結(jié)果我們清晰地看到，上面添加的兩筆記錄已經(jīng)被記錄下來(lái)，而__$operation字段為2表示的是"插入"操作。

五、記錄更新數(shù)據(jù)的數(shù)據(jù)改變

　　接下來(lái)我們來(lái)CDC對(duì)更新操作的追蹤記錄，為此我們通過(guò)下面的T-SQL改變了用戶(hù)Foo的Birthday。

　　Update Users
Set Birthday = '1982-7-10'
Where Name = 'Foo'

　　再次執(zhí)行對(duì)于cdc.dbo_Users_CT的全表查詢(xún)，你會(huì)看到這次多了兩筆記錄。其中第3條記錄的是修改之前的數(shù)據(jù)，而第四條則是修改之后的數(shù)據(jù)，它們的__$operation字段德值分別為3和4。

　　在這里值得一提的是__$update_mask字段的值，它表示的記錄更新操作改變的字段。這是一個(gè)以16進(jìn)制表示的數(shù)字，在進(jìn)行對(duì)修改字段進(jìn)行判斷的時(shí)候需要將其轉(zhuǎn)換成2進(jìn)制。上述的更新操作對(duì)應(yīng)的__$update_mask值為0x04，轉(zhuǎn)化成2進(jìn)制就是100，這三位分別代表3個(gè)字段。不過(guò)這里的順序是從右到左，所以100這三位表示的字段為Birthday、Name和Id。1表示改變，0則表示保持不變。由于在上面的T-SQL中，我們只改動(dòng)了Birthday，這個(gè)和100這個(gè)值是吻合的。

六、記錄刪除記錄的數(shù)據(jù)改變

　　我們最后來(lái)演示當(dāng)我們對(duì)記錄實(shí)施刪除操作的時(shí)候，CDC會(huì)為我們記錄下怎樣的數(shù)據(jù)。現(xiàn)在我們執(zhí)行如下的T-SQL將Users表中所有的記錄均刪除。

　　Delete From Users

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

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