解析SQL 2008的Change Data Capture功能_Mssql數據庫教程

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

1、在數據表中加入特殊的標志列；

2、 通過在數據表上創建觸發器；

3、通過第三方產品，例如Lumigent的Log Explorer。

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

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

CDC的工作原理

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

　　圖1

◆當DML提交到應用數據庫時，SQL Server必須寫入日志，并在緩存中更新數據，然后在檢查點將內存中的數據刷回數據文件。

◆CDC的內部進程根據CDC的設置，在日志文件中提取更新歷史信息，并將這些個更新信息寫入對應的更新跟蹤表。

◆DBA或開發人員通過調用CDC的函數來訪問更新跟蹤表，提取感興趣的更新歷史信息，并通過ETL應用程序更新數據倉庫。

◆理論上面更新跟蹤表事會無限制增長的，因此CDC內部有一個清理進程，在默認情況下更新跟蹤信息在寫入跟蹤表三天后會被自動清理。

CDC的配置

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

啟用數據庫級別的CDC

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

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

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

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

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

一段CDC的評估腳本

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

1、首先創建一個測試數據庫；

2、然后激活TestCDC數據庫上的更新捕獲功能；

以下為引用的內容： USE TestCDC GO EXEC sp_cdc_enable_db_change_date_capture; GO

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

3、然后在TestCDC數據庫中創建測試表

以下為引用的內容： USE TestCDC GO CREATE TABLE dbo.Product (ProductID int PRIMARY KEY NOT NULL, ProductName nvarchar(100), Category nvarchar(50)) GO

4、在dbo.Product表上激活更新跟蹤

以下為引用的內容： EX EC sp_cdc_enable_table_change_data_capture 'dbo', 'Product', @role_name= NULL, @supports_net_changes =1;

分享：看Sql server 2005 找出子表樹
同事在準備新老系統的切換，清空一個表的時候往往發現這個表的主鍵被另一個表用做外鍵，而系統里有太多層次的引用.所以清起來相當麻煩 用下面這個腳本可以做到找出一個特定表的引用