SQL SERVER 2008--Change Data Capture

問題：前些日子同事問我如何知道資料庫TABLE中資料的異動情形，一個最簡單的方法當然是寫TRIGGER，可是TRIGGER通常都會有一個致命的缺點—效能不佳，為了要達到他的需求而在效能的考量之下選擇Change Data Capture(CDC)是最好的方案。

版本：要SQL SERVER 2008以上的Enterprise、Developer的版本才有Change Data Capture的功能。

什麼是CDC：CDC就是讀取資料庫的LOG檔，藉著讀取LOG檔，把INSERT、UPDATE與DELETE的資料異動紀錄到啟動CDC時建立的TABLE，如此我們就可以藉由查詢CDC建立的TABLE瞭解資料異動的情況。

CDC的運作過程：當我們異動資料時，會把異動記錄到LOG檔中，CDC會使用SQL AGENT排程去執行存取LOG的JOB(該JOB是啟動CDC系統自已建立)，藉由JOB把資料的異動過程紀錄到CDC TABLE(該JOB是啟動CDC系統自已建立)。

執行CDC的步驟：

1. 啟用CDC必須確認可以執行系統的STORED PROCEDURE與SQL AGENT是啟動的。

2. 建立測試資料庫與建立測試資料。

3. 啟動CDC功能，這時候SQL SERVER會建立執行CDC所需要的TABLE、JOB、STORED PROCEDURE與FUNCTION等物件。
3.1 指定要啟用CDC的資料庫(框起來的為增加的物件)

3.2 指定要啟用CDC的TABLE(框起來的為增加的物件)

4. 確認CDC是否已被啟用:1為啟用，0為尚未啟用。

5. 執行DML語法測試CDC。

6. 瀏覽TABLE異動資料紀錄。

__$operation欄位1表示DELETE 2表示INSERT 3表示UPDATE之前 4表示UPDATE之後

7. 使用啟動CDC建立的FUNCTION讀取異動的資料。

7.1. cdc.fn_cdc_get_net_changes_dbo_Employees

使用cdc.fn_cdc_get_net_changes_dbo_Employees讀取資料，使用net_changes會視會EID=2為一個變化群組，取EID=2變化群組最後一次UPDATE之後的值。

LSN是log sequence number的縮寫，LSN是唯一的，用途是資料庫用來辨識log的依據。

7.2. fn_cdc_get_all_changes_dbo_Employees

使用all_changes會列出所有UPDATE的經過。

PS: 若要知道LSN與時間的對應關係可以查詢cdc.lsn_time_mapping這個TABLE。

CDC的用途：

1. 救回誤刪資料：如果有USER誤刪資料，可以用CDC讀取之前異動(包含INSERT、UPDATE與DELETE)過的資料。

2. 可以改善資料倉儲ETL(Extract,Transform,Load)的效能，可以針對異動過的資料作ETL，不必因為異動一筆資料而要更新而做批次大量資料的ETL。

3. 計算資料異動的頻率。