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

1、在數(shù)據(jù)表中加入特殊的標(biāo)志列；

2、 通過在數(shù)據(jù)表上創(chuàng)建觸發(fā)器；

3、通過第三方產(chǎn)品，例如Lumigent的Log Explorer。

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

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

CDC的工作原理

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

　　圖1

◆當(dāng)DML提交到應(yīng)用數(shù)據(jù)庫時，SQL Server必須寫入日志，并在緩存中更新數(shù)據(jù)，然后在檢查點將內(nèi)存中的數(shù)據(jù)刷回數(shù)據(jù)文件。

◆CDC的內(nèi)部進(jìn)程根據(jù)CDC的設(shè)置，在日志文件中提取更新歷史信息，并將這些個更新信息寫入對應(yīng)的更新跟蹤表。

◆DBA或開發(fā)人員通過調(diào)用CDC的函數(shù)來訪問更新跟蹤表，提取感興趣的更新歷史信息，并通過ETL應(yīng)用程序更新數(shù)據(jù)倉庫。

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

CDC的配置

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

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

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

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

CDC功能啟用后，SQL Server會首先在cdc架構(gòu)下創(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è)置兩個跟蹤實例。每個跟蹤實例中可以設(shè)置對原始數(shù)據(jù)表的所有列或部分列進(jìn)行更新跟蹤。如果想知道數(shù)據(jù)表是否進(jìn)行了更新跟蹤，DBA可以查詢sys.tables系統(tǒng)目錄的is_tracked_by_cdc字段。

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

一段CDC的評估腳本

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

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

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

以下為引用的內(nèi)容： 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架構(gòu)下。

3、然后在TestCDC數(shù)據(jù)庫中創(chuàng)建測試表

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

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

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

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