在常見的企業(yè)數(shù)據(jù)平臺管理中有一項任務是一直困擾SQL Server DBA們的，這就是對數(shù)據(jù)更新的監(jiān)控。很多數(shù)據(jù)應用都需要捕獲對業(yè)務數(shù)據(jù)表的更新。筆者見過幾種解決方案：

1、在數(shù)據(jù)表中加入特殊的標志列；
2、 通過在數(shù)據(jù)表上創(chuàng)建觸發(fā)器；
3、通過第三方產品，例如Lumigent的Log Explorer。

其實第1種和第2中方案都不好，因為第1種方法需要在應用程序編碼的時候尤為小心，如果有一段數(shù)據(jù)訪問邏輯忘了更新標志位就會導致遺漏某些數(shù)據(jù)更新，而第2種方法對性能影響過于明顯，因為觸發(fā)器的性能開銷是眾所周知的。第3種方法其實屬于一種叫做Log Audit的方案體系。因為SQL Server同其他關系型數(shù)據(jù)庫一樣，所有數(shù)據(jù)操作都會在日志中記錄，因此通過分析日志就可以獲得完整的數(shù)據(jù)操作歷史。SQL Server其實早就有內部的API可供ISV開發(fā)者中Log Audit的方案，不過微軟對這套API控制比較嚴格，只有簽署了一堆協(xié)議的核心級合作伙伴才能了解這套API。

因此，現(xiàn)對業(yè)務數(shù)據(jù)更新的跟蹤在SQL Server平臺上一直是一件非常頭疼的事情，用戶需要在投入大量開發(fā)精力和投入額外采購成本之間做出選擇。幸運的事，微軟終于在SQL Server 2008中提供了一套半公開的Log Audit機制，就是我們所說的Change Data Capture，我們后面簡稱CDC。

CDC的工作原理

我們前面說過CDC是通過分析日志獲得數(shù)據(jù)操作歷史信息的，那么CDC的工作原理到底是怎么樣的呢？下圖可以非常貼切地說明這個功能的原理：

圖1

◆當DML提交到應用數(shù)據(jù)庫時，SQL Server必須寫入日志，并在緩存中更新數(shù)據(jù)，然后在檢查點將內存中的數(shù)據(jù)刷回數(shù)據(jù)文件。
◆CDC的內部進程根據(jù)CDC的設置，在日志文件中提取更新歷史信息，并將這些個更新信息寫入對應的更新跟蹤表。
◆DBA或開發(fā)人員通過調用CDC的函數(shù)來訪問更新跟蹤表，提取感興趣的更新歷史信息，并通過ETL應用程序更新數(shù)據(jù)倉庫。
◆理論上面更新跟蹤表事會無限制增長的，因此CDC內部有一個清理進程，在默認情況下更新跟蹤信息在寫入跟蹤表三天后會被自動清理。

CDC的配置

由于CDC是一項比較高端的功能，因此只有在SQL Server 2008的企業(yè)版、開發(fā)版和評估版中才能找到CDC功能。

啟用數(shù)據(jù)庫級別的CDC

要啟用CDC功能，首先需要一個sysadmin服務器角色的成員用戶激活數(shù)據(jù)庫級別的CDC，這個過程可以通過sys.sp_cdc_enable_db_change_data_capture存儲過程來完成。如果想知道一個數(shù)據(jù)庫是否啟用了CDC功能，可以通過查詢sys.databases系統(tǒng)目錄的is_cdc_enabled字段。

當一個數(shù)據(jù)庫啟用CDC功能后，SQL Server會自動在這個數(shù)據(jù)庫中創(chuàng)建cdc架構和cdc用戶，所有CDC相關的數(shù)據(jù)表和用戶函數(shù)都會存放在cdc架構下。

CDC功能啟用后，SQL Server會首先在cdc架構下創(chuàng)建五張表用于記錄一些CDC的原數(shù)據(jù)，分別是ddl_history，change_tables，captured_columns，index_columns和lsn_time_mapping。

在數(shù)據(jù)庫啟用了CDC后，接下來我們就需要在數(shù)據(jù)表上啟用CDC了。屬于db_owner角色的用戶可以通過存儲過程sys.sp_cdc_enable_table_change_data_capture來啟用對某張數(shù)據(jù)表的更新跟蹤，一張數(shù)據(jù)表最多可以設置兩個跟蹤實例。每個跟蹤實例中可以設置對原始數(shù)據(jù)表的所有列或部分列進行更新跟蹤。如果想知道數(shù)據(jù)表是否進行了更新跟蹤，DBA可以查詢sys.tables系統(tǒng)目錄的is_tracked_by_cdc字段。

對一張數(shù)據(jù)表啟用CDC跟蹤實例后，SQL Server會在cdc架構下創(chuàng)建一張數(shù)據(jù)表用于記錄從日志中解析出來的更新歷史信息。

一段CDC的評估腳本

為了評估CDC功能，我特地寫了一段腳本如下：

1、首先創(chuàng)建一個測試數(shù)據(jù)庫；

2、然后激活TestCDC數(shù)據(jù)庫上的更新捕獲功能；

USE TestCDC
GO
EXEC sp_cdc_enable_db_change_date_capture;
GO

執(zhí)行了存儲過程sp_cdc_enable_db_change_data_capture后，就會在數(shù)據(jù)庫TestCDC中看到有一些新的表被創(chuàng)建了，分別是ddl_history，change_tables，captured_columns，index_columns和lsn_time_mapping，并且這5張表都是在cdc架構下。