淺析SQL Server 2008的Change Data Capture功能

在常見的企業資料平臺管理中有一項任務是一直困擾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的工作原理到底是怎麼樣的呢？下圖可以非常貼切地說明這個功能的原理：


   • 當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後，接下來我們就需要在資料表上啟用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_data_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表上啟用更新跟蹤

EXEC sp_cdc_enable_table_change_data_capture 'dbo', 'Product', @role_name= NULL, @supports_net_changes =1;

   成功提交上述命令後，就可以在資料表change_tables，captured_columns和index_columns表中看到相應的記錄，其中change_table中一條，capture_column中三條，index_columns中一條。同時cdc架構下有增加了一張新表叫做dbo_Product_CT，這張表的結構和Product表的結構有點相似，Product表中的三列在dbo_Product_CT中都有，同時dbo_Product_CT表中還增加了_$start_lsn，_$end_lsn，_$seqval，_$operation和_$update_mask五個新的欄位。
其實在儲存過程sp_cdc_enable_table_change_data_capture中有一系列的引數，在這裡我們為了簡化忽略了一個引數就是@captured_column_list，這個引數可以對錶中特定的某些欄位啟用更新跟蹤。

  5. 在Product表上提交INSERT語句

INSERT INTO dbo.Product VALUES (1, N'ABC', N'A');

    提交完了這條命令後，就會在lsn_time_mapping和dbo_Product_CT中分別看到一條新記錄。

   其中dbo_Product_CT表中的_$operation欄位的值是2，_$update_mask欄位的值是0x07。 _$operation欄位是代表DML操作型別，1是delete，2是insert，3是update的舊值，4是update的新值。

   _$update_mask欄位是表示一個欄位列表的掩碼，那些在DML操作中被更新了的欄位位為1，而沒有更新的欄位位為0。在本例中Product表一共有三列被跟蹤，所以應該是一個三位的二進位制數，右邊低位第一位是第一列ProductID，低位第二位是第二列ProductName，第三位就是Category了。因為這是一次INSERT，所以更新涉及到了所有的三列，所以_$update_mask欄位就應該是0x7了。

   6. 繼續在Product表上提交UPDATE語句

UPDATE dbo.Product SET Category = N'B' WHERE ProductID = 1;

    提交完這條命令後，當然也會在lsn_time_mapping和dbo_Product_CT中看到新記錄了。不過這次lsn_time_mapping中是一條，而dbo_Product_CT中則是兩條。(為什麼會這樣呢？建議大家自己試一下咯，一試就明白了。)
其中dbo_Product_CT表中的_$operation欄位的值是第一條是3，第二條是4，_$update_mask欄位的值兩條都是0x04。
在這次操作中我們更新的是第三列，所以_$update_mask欄位就應該是0x4了。(如果我們更新的是ProductID會發現_$update_mask並非是0x1，而同樣是0x7，這估計是因為ProductID是主鍵，更新主鍵應該視同一條新的記錄。)

   7. 再來一次UPDATE

UPDATE dbo.Product SET Category = N'A' WHERE ProductID = 1;

   提交完這條命令後，在dbo_Product_CT中又看到兩條新記錄了。其中dbo_Product_CT表中的_$operation欄位的值是第一條是3，第二條是4，_$update_mask欄位的值兩條都是0x04。(看來CDC確實會記錄下資料的每次修改。)

8. 繼續在Product表上提交DML語句

DELETE dbo.Product WHERE ProductID = 1;

   提交完了這條命令後，就會在lsn_time_mapping和dbo_Product_CT中分別看到一條新記錄。
   其中dbo_Product_CT表中的_$operation欄位的值是1，_$update_mask欄位的值是0x07。

   9. 提交一個DDL試試看

ALTER TABLE dbo.Product ADD Description nvarchar(100);

   提交完這句命令後，只會在ddl_history表中看到一條新的記錄。
   10. 然後再試試DML

UPDATE dbo.Product SET Description = N'NA';

   提交完這句語句後，所有cdc架構下的表中都沒有看到新記錄。說明新增的列Description不跟蹤更新了......估計有人會說(細心的人哦！)：“這次當然看不到新記錄了，因為在前面第7步我們已經刪除了所有的記錄，因此這次的UPDATE語句沒有影響到任何記錄，當然CDC的表中不會有任何記錄了。”那麼到底對Description更新會不會記錄呢，經過測試確實是不記錄的。

   那麼如果我們想對Description也進行更新跟蹤應該怎麼辦呢？很簡單的，由另外一個儲存過程叫做sp_cdc_disable_table_change_data_capture可以禁用對某張表的更新跟蹤，可以使用這個儲存過程先對Product表禁用更新跟蹤，然後再重新啟用對Product表的更新跟蹤就可以了。

   11. 最後試一下DROP命令

DROP TABLE dbo.Product;

   dbo.Product表消失了，同時cdc.dbo_Product_CT表也消失了。

   12. 評估結束。哦，對了。一定有人問，捕獲到的更新怎麼用呢，還有一堆系統函式和儲存過程可以幫助使用者，但是那段測試的過程就不詳細寫了。

   其中最重要的應該就是cdc.fn_cdc_get_all_changes_和cdc.fn_cdc_get_net_changes_兩個函式了，這兩個函式可以幫助我們獲取dbo_Product_CT表中資料，其中cdc.fn_cdc_get_all_changes_是用於獲取所有更新，而cdc.fn_cdc_get_net_changes_則是用於獲取精簡後的更新，在精簡的更新中有一些重複的更新就會被合併成一條記錄，比如說我們把產品型別由A改為B，然後又改回A，在cdc.fn_cdc_get_all_changes_中應該有3條記錄，而在cdc.fn_cdc_get_net_changes_中則只有1條記錄。兩個函式的範例如下(你會發現精簡結果集的函式運算相當慢，至少在CTP4中是這樣的，不知道以後的版本會不回有改進)：

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Product(0x00000048000001760004, 0x00000048000001F70004, 'all'); 
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Product(0x00000048000001760004, 0x00000048000001F70004, 'all');

CDC功能的IO開銷

    很明顯，CDC功能是會產生一定的IO和儲存開銷的，為了評估CDC功能產生的這些開銷。我又進行了一段評測。
整個評估的思路是這樣的：
1. 建立兩個資料庫
2. 在兩個資料庫中分別建立一張結構完全相同的表，一個資料庫啟用CDC功能，而另外一個禁用CDC功能
3. 向兩張表中寫入相同行數的資料
4. 利用動態管理檢視sys.dm_io_virtual_file_stats來獲得兩個資料庫檔案上的IO
5. 利用sysindexes來獲得兩個資料庫中資料表的儲存消耗情況
    因為是在虛擬機器中進行的測試，所以選取了比較小的資料表(AdventureWorks資料庫中的SalesOrderDetails)，大約有12萬行資料。

    評估的結果如下：

 
    從上面兩張表中可以看到，CDC啟用後日志檔案的讀會顯著增加。原因是CDC在寫更新跟蹤表時，需要讀取日誌。

從上面兩張表中可以看到啟用CDC後資料檔案的寫入和日誌檔案寫入都會顯著增加，不過需要考慮到CDC啟用後會需要多寫一張表，在本例中就是dbo_SalesOrderDetails_CT，所以這種增加是可以理解的。當然在生產環境中並不會對資料表的所有列進行CDC監控，所以啟用CDC對IO寫入的影響還需要針對不同情況進行分析。



    從上面這張圖可以看出，CDC啟用後會生產資料表不會消耗更多的儲存空間，但是更新跟蹤表會需要俄外的儲存空間。另外可以發現的一點是，在本例中dbo_SalesOrderDetail_CT表消耗的空間比SalesOrderDetail表多，這是因為在dbo_SalesOrderDetail_CT表中加入了一些額外的欄位，例如_$start_lsn和_$end_lsn，同時注意觀察dbo_SalesOrderDetail_CT表會發現，SQL Server在這張表上使用_$start_lsn、_$end_lsn和_$seqval三個欄位作為聚簇索引，而SalesOrderDetail表上原來的聚簇索引(SalesOrderID，SalesOrderDetailID)再加上_$start_lsn、_$end_lsn和_$seqval三個欄位則被建立為一個非聚簇索引，所以這就導致了dbo_SalesOrderDetail_CT表需要消耗比原始表更多的空間，不過原始資料表上的非聚簇索引不會在CDC跟蹤表上被建立，這也就說明了原始資料表聚簇索引的大小也會對CDC引發的IO產生影響。

CDC對儲存的消耗

    為了進一步理解CDC功能對儲存的消耗，特別整理了一下CDC的資料開銷。首先CDC功能對資料庫儲存空間產生顯著影響的兩張表是cdc._CT表和cdc.lsn_time_mapping表，這裡簡稱為表1和表2。

下面是對錶1和表2作的一些較為深入地剖析：

   1. 表1和表2的資料
   • 表1主要由3個binary(10)欄位、1個int欄位、1個varbinary(128)欄位以及所有被選定更新跟蹤的原始表欄位構成。因此表1每行資料的尺寸大概是在30 + 4 + 5 (因為通常一張表需要監控的欄位會在16個以內，所以暫定為2bytes的binary然後加上varbinary資料2個bytes的固定開銷)，也就是 39 + x(假定原始表需要監控的欄位鍵總尺寸為x個位元組)個位元組。

   • 表2則有1個binary(10)欄位、2個datetime欄位和1個varbinary(10)欄位構成。因此表2每行資料應該是20 + 16 + 12 = 48個位元組。

    2. 表1和表2的索引(這個不太好估算，因為不同的表聚簇索引的鍵值密度是不一樣的，一般按照1/4的資料尺寸估算，只有多沒有少啦)

    • 表1的3個binary(10)欄位構成了聚簇索引，同時3個binary(10)欄位加上原始資料表的聚簇索引構成一個非聚簇索引，同上面一樣，我們假定原始表聚簇索引鍵是x個位元組，那麼表1的非聚簇索引每行是(30 + y(假定原始表聚簇索引鍵尺寸為y個位元組) + 4(指向聚簇索引的內部指標))個位元組。

    • 而表2中的binary(10)欄位構成了聚簇索引，其中1個datetime欄位構成了非聚簇索引。因此表2的非聚簇索引每行是8 + 4 = 12個位元組。

    3. 對原始資料表的一行資料進行UPDATE操作，會在表1中新增2行資料，而DELETE操作和INSERT操作則會增加1行資料；而對於表2則是每筆事務增加1行資料。 

   因此我們作如下假定，典型的OLTP環境：

    • 原始資料表的聚簇索引為1個整型欄位，同時需要監控的欄位總尺寸為50位元組(約為5個decimal(19)或5個char(10))
    • 對原始表提交100,000個事務
    • 產生1,000,000行次資料操作，其中UPDATE佔60%，INSERT和DELETE佔40%
    • 那麼最終CDC產生的額外資料儲存空間應該為(39 + 4 + 50) * (1000000 * 1.2 + 1000000 * 0.4) + 48 * 100000 = 153,600,000個位元組，約為164MB(假定資料頁填充率為90%)。
    • CDC產生的額外的非聚簇索引儲存空間按照資料尺寸的1/4估算，大概是40MB左右。
    • 因此約合200MB左右。

    經過這樣的對比我們可以知道，CDC在生產環境特別是OLTP環境對儲存空間的影響不算太明顯的，當然這個還要取決於DBA在原始資料表上選取多少欄位進行監控，以及這些欄位的資料尺寸，同時還有原始資料表的聚簇索引鍵值密度。另外需要說明的是表1和表2都是由一個非同步的程式通過讀取日誌來完成的，因此表1和表2的資料重新整理和原始資料表的重新整理會有一定的延時。

對部署CDC的建議

   經過以上測試，我們可以發現以下情況：
   • CDC啟用會顯著增加日誌檔案的讀操作。
   • CDC啟用後更新跟蹤表會產生額外的寫入，並消耗儲存空間。
   • CDC啟用後，原資料表的聚簇索引尺寸會影響到CDC產生的IO資料量，而原始資料表上的非聚簇索引則不會。
   • CDC啟用後，被選定進行更新跟蹤的列鍵值屬性同樣會影響到CDC產生的IO資料量和儲存空間。
   因此如同微軟建議的一樣，在CDC啟用的環境下，應該將更新跟蹤表寫入與原始表不同的檔案組並存放在不同的儲存裝置上，注意控制需要監控的資料列尺寸，同時應該注意為日誌檔案選取可提高讀取效能的儲存硬體上，比如RAID10。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9079672/viewspace-217113/，如需轉載，請註明出處，否則將追究法律責任。