利用SQLCDC改善每筆數據追蹤審核和記錄
對于大部分企業應用來用，有一個基本的功能必不可少，那就是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在線文檔。
原文鏈接：http://www.cnblogs.com/artech/archive/2010/11/20/cdc.html
【編輯推薦】
