利用SQLCDC改善每筆數(shù)據(jù)追蹤審核和記錄
對于大部分企業(yè)應(yīng)用來用，有一個基本的功能必不可少，那就是Audit Trail或者Audit Log，中文翻譯為追蹤檢查、審核檢查或者審核記錄。我們采用Audit Trail記錄每一筆業(yè)務(wù)操作的基本信息，比如操作的基本描述、操作時間、操作者等。對于一些安全級別比較高的應(yīng)用，或者操作一些比較敏感的數(shù)據(jù)，我們甚至需要記錄該筆業(yè)務(wù)操作引起的數(shù)據(jù)的改變。具體來說，這里的“數(shù)據(jù)改變”指的是每一條影響的記錄在操作執(zhí)行前后的變化。對于添加的記錄，需要記錄下新插入的記錄；對于刪除的記錄，需要記錄下原來的記錄；對于更新的記錄，則需要同時記錄下更新前后的記錄。
說到這里，很多人都會想到采用觸發(fā)器的方式來實現(xiàn)對數(shù)據(jù)改變的捕捉。但是這種實現(xiàn)方案具有一個***的局限：由于觸發(fā)器是在數(shù)據(jù)操作所在事務(wù)范圍內(nèi)執(zhí)行的，所有會帶來性能的問題，嚴(yán)重的話還會因為觸發(fā)器的執(zhí)行導(dǎo)致事務(wù)超時。所以在這里，我們介紹一種更好的解決方案：SQLCDC。
目錄
一、SQLCDC簡介
二、在數(shù)據(jù)庫級別開啟CDC
三、為某個數(shù)據(jù)表開啟CDC
四、記錄添加記錄的數(shù)據(jù)改變
五、記錄刪除數(shù)據(jù)的數(shù)據(jù)改變
六、記錄更新記錄的數(shù)據(jù)改變
一、SQLCDC簡介
CDC的全名為Change Data Capture，顧名思義，就是用于追蹤和捕捉數(shù)據(jù)改變。CDC是在SQL Server 2008中才出現(xiàn)的新特性，而這個特性則在很早之前就出現(xiàn)在了Oracle中。對于SQL Server之前版本來說，在沒有CDC的情況下，如果需要記錄基于某個數(shù)據(jù)表的數(shù)據(jù)改變，我們只能采用觸發(fā)器，具體來說就是通過手工創(chuàng)建After Insert、After Update和After Delete觸發(fā)器去記錄變化的數(shù)據(jù)。而CDC給了我們一種更為方便、易用和省心的方式去記錄某個數(shù)據(jù)表的歷史操作。
二、在數(shù)據(jù)庫級別開啟CDC
在默認(rèn)的情況下，數(shù)據(jù)庫的CDC特性是被關(guān)閉的，你可以通過系統(tǒng)表sys.databases的is_cdc_enabled字段確定某個數(shù)據(jù)庫的CDC是否開啟。如果在默認(rèn)的情況下，我執(zhí)行如下的SQL語句查看數(shù)據(jù)庫TestDb的CDC是否開啟，你將會看到該字段的值為0。
你可以通過執(zhí)行系統(tǒng)存儲過程sys.sp_cdc_enable_db為當(dāng)前數(shù)據(jù)庫開啟CDC特性。下面的T-SQL代碼片斷中，我們通過執(zhí)行該存儲過程為TestDb打開了CDC特性。
- Use TestDb
- Go
- Exec sys.sp_cdc_enable_db
- Go
三、為某個數(shù)據(jù)表開啟CDC
由于CDC用于記錄基于某個數(shù)據(jù)表的數(shù)據(jù)改變，所以在當(dāng)前數(shù)據(jù)庫CDC開啟的情況下，你還需要顯式地為某個數(shù)據(jù)表開啟CDC特性。作為演示，我們通過如下T-SQL在TestDb下創(chuàng)建了一個簡單的Users表，它僅僅具有三個字段：Id、Name和Birthday。
- CREATE TABLE [dbo].[Users](
- [Id] [varchar](50) PRIMARY KEY,
- [Name] [nvarchar](50) NOT NULL,
- [Birthday] [date] NOT NULL)
數(shù)據(jù)表的CDC特性的開啟通過執(zhí)行sys.sp_cdc_enable_table存儲過程實現(xiàn)。調(diào)用該存儲過程的最簡的方式就是指定數(shù)據(jù)表的Schema、名稱和用于提取改變數(shù)據(jù)必須具有的權(quán)限（角色）。我通過執(zhí)行下面的T-SQL將我們創(chuàng)建的Users表的CDC特性打開，其中@role_name參數(shù)被設(shè)置成NULL，表明我不對讀取改變數(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實際上建立在SQL Server Agent之上的，所以在執(zhí)行上述T-SQL之前需要啟動SQL Server Agent。當(dāng)某個數(shù)據(jù)表的CDC特性被開啟之后，系統(tǒng)會為創(chuàng)建一個用于保存數(shù)據(jù)變化的追蹤表（Tracking Table）。該表的Schema為cdc，命名方式為被追蹤表的表名后加“CT”后綴。執(zhí)行上面一段T-SQL之后，會有如下一個系統(tǒng)表被創(chuàng)建出來，我們發(fā)現(xiàn)Users表的三個字段也在該表中。此外。該表還具有5個額外字段：__$start_lsn、__$end_lsn、__$seqval、__$operation 和__$update_mask，表示日志系列號（Log Sequence Number）、操作（刪除、插入、修改前和修改后）信息。
四、記錄添加記錄的數(shù)據(jù)改變
現(xiàn)在我們就可以來試驗CDC針對某個數(shù)據(jù)表的數(shù)據(jù)改變的捕捉功能了，我們先來試試記錄的添加操作。為此，我們執(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')
然后通過如下的T-SQL查看cdc.dbo_Users_CT表的數(shù)據(jù)是否將添加操作涉及到的數(shù)據(jù)改變保存起來。從查詢結(jié)果我們清晰地看到，上面添加的兩筆記錄已經(jīng)被記錄下來，而__$operation字段為2表示的是“插入”操作。
五、記錄更新數(shù)據(jù)的數(shù)據(jù)改變
接下來我們來CDC對更新操作的追蹤記錄，為此我們通過下面的T-SQL改變了用戶Foo的Birthday。
- Update Users
- Set Birthday = '1982-7-10'
- Where Name = 'Foo'
再次執(zhí)行對于cdc.dbo_Users_CT的全表查詢，你會看到這次多了兩筆記錄。其中第3條記錄的是修改之前的數(shù)據(jù)，而第四條則是修改之后的數(shù)據(jù)，它們的__$operation字段德值分別為3和4。
在這里值得一提的是__$update_mask字段的值，它表示的記錄更新操作改變的字段。這是一個以16進(jìn)制表示的數(shù)字，在進(jìn)行對修改字段進(jìn)行判斷的時候需要將其轉(zhuǎn)換成2進(jìn)制。上述的更新操作對應(yīng)的__$update_mask值為0x04，轉(zhuǎn)化成2進(jìn)制就是100，這三位分別代表3個字段。不過這里的順序是從右到左，所以100這三位表示的字段為Birthday、Name和Id。1表示改變，0則表示保持不變。由于在上面的T-SQL中，我們只改動了Birthday，這個和100這個值是吻合的。
六、記錄刪除記錄的數(shù)據(jù)改變
我們***來演示當(dāng)我們對記錄實施刪除操作的時候，CDC會為我們記錄下怎樣的數(shù)據(jù)?，F(xiàn)在我們執(zhí)行如下的T-SQL將Users表中所有的記錄均刪除。
- Delete From Users
查看cdc.dbo_Users_CT的記錄，多出的兩筆記錄正式我們刪除的User記錄，__$operation字段的值為1表示“刪除”操作。
本篇文章僅僅是簡單介紹SQLCDC的基本原理和大體上的使用方式，這篇文章《Introduction to Change Data Capture (CDC) in SQL Server 2008[轉(zhuǎn)]》會給你更加詳盡的介紹。如果你想深入研究SQLCDC，還是參考SQL Server 2008在線文檔。
原文鏈接：http://www.cnblogs.com/artech/archive/2010/11/20/cdc.html
【編輯推薦】
