淺析SQL2008的Change Data Capture功能

在常見的企業(yè)數(shù)據(jù)平臺管理中有一項(xiàng)任務(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。

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

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

CDC的工作原理

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

圖1

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

CDC的配置

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

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

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

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

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

一段CDC的評估腳本

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

1、首先創(chuàng)建一個(gè)測試數(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架構(gòu)下。

生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈