Rock的SQL筆記本

首先微軟文件中建議另建一個filegroup來存放CDC相關物件，因此如下圖所示我在資料庫中加了一個名為fgCDC的filegroup。

 

接下來我們用系統預存程序sp_cdc_enable_db來啟動CDC功能。

 

啟動後我們可以在該資料庫的系統資料表中看見多出了幾張schema為cdc的資料表物件。

 

我們先建立一張名為tbCDC的資料表來做接下來之測試。其中注意一下phone這一個欄位的資料長度為char(10)。

接下來就我們就對tbCDC這一張資料表做啟動CDC功能的動作，一樣是透過預存程序來做。注意該預存程序有兩個特別參數。

@role_name這一參數我填入一個值叫cdcRole，DB就會建一個角色叫cdcRole，屆時所有CDC物件都會授予權限給cdcRole。此時我們只要將欲使用CDC物件的User加入到該cdcRole角色後，該User就有相關存取CDC物件的權限了。

@filegroup_name這一個參數就是指定此tbCDC資料表的相關CDC物件欲存放在哪一個filegroup中，下圖中我將該物件放在fgCDC的filegroup上，而不是Primary上，此一設定主要是用來分散IO。

完成上步驟後就會在DB中產出相關CDC物件。

cdc.dbo_tbCDC_CT的資料表物件，是用來存放tbCDC資料表增刪修相關紀錄。

fn_cdc_get_all_changes_dbo_tbCDC針對在指定之記錄序號 (LSN) 範圍內套用至來源資料表的每個變更，各傳回一個資料列。 如果來源資料列在間隔期間具有多個變更，就會在傳回的結果集中表示每個變更。

fn_cdc_get_net_changes_dbo_tbCDC傳回一個淨變更資料列變更指定的記錄順序編號 (LSN) 範圍內的每個來源資料列。

cdcRole則是DB建立出來可以存取CDC物件的角色。

 

我們透過下面圖中的語法，可以看見cdcRole角色對剛剛的兩個Function都有Select權限。

下圖中是比對原資料表tbCDC跟該資料表相對應CDC物件cdc.dbo_tbCDC_CT的欄位差異，可以看得出來cdc.dbo_tbCDC_CT多了一些開頭是 _$ 的欄位。兩張表其他的欄位型態則是一樣。

 

接下來我對tbCDC資料表做增刪修的DML動作，如下圖所示，我新增一筆id=1的資料然後做兩次的update後再將該筆資料刪除。

 

做完增刪修後再來檢查一下cdc.dbo_tbCDC_CT中紀錄了什麼。我們剛剛上一步驟中做了4個動作，但cdc.dbo_tbCDC_CT卻有六筆資料，那是因為該資料表在資料做Update時會記錄修改前及修改後的資料內容，因此做一次update會有兩筆紀錄。

_$operation中值是

1 = 刪除
2 = 插入
3 = 更新 (舊的值)執行更新陳述式之前，資料行資料具有資料列值。
4 = 更新 (新的值)執行更新陳述式之後，資料行資料具有資料列值。

 

而我們用fn_cdc_get_all_changes_dbo_tbCDC可以針對LSN的區段檢視當下的資料變化。如下圖所示我去搜尋當下Max LSN及tbCDC最小的LSN之間的資料變化。

以上是CDC功能簡易介紹，但在LAB的過程中發現一個需要注意的地方，如果沒注意到可是會導致LDF爆炸的災難。這部分我在下一篇(MS SQL CDC(Change Data Capture)功能初體驗之有雷勿踩)再來LAB給大家參考囉。

我是ROCK

rockchang@mails.fju.edu.tw