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

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

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

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

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

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

CDC的工作原理

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

　　圖1

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

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

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

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

CDC的配置

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

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

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

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

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

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

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

一段CDC的評(píng)估腳本

為了評(píng)估CDC功能，我特地寫(xiě)了一段腳本如下：

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

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

以下為引用的內(nèi)容： USE TestCDC GO EXEC sp_cdc_enable_db_change_date_capture; GO

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

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

以下為引用的內(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 找出子表樹(shù)
同事在準(zhǔn)備新老系統(tǒng)的切換，清空一個(gè)表的時(shí)候往往發(fā)現(xiàn)這個(gè)表的主鍵被另一個(gè)表用做外鍵，而系統(tǒng)里有太多層次的引用.所以清起來(lái)相當(dāng)麻煩 用下面這個(gè)腳本可以做到找出一個(gè)特定表的引用