[SQL]SQL Server 異動資料擷取 ( CDC ) 測試與 SSIS 搭配使用相關心得
SQL Server 從 2008 版本開始，提供了異動資料擷取 ( Change data capture ) 的功能，但該功能僅限於企業版和開發版才有提供，一般常使用的標準版和 Express 版本則無法使用該功能。而從 SQL Server 2012 版本，微軟則是購買 Attunity CDC 相關元件，並且將它放到 SSIS 內來做使用，因此使用起來相對方便得多了。
原本沒有特別注意這項功能，主要原因都是使用標準版，因此當有類似需求的時候，多半都是透過 TRIIGER 或者是 REPLICATION 來做到類似的功能，但剛好有同事有遇到使用上有出錯的狀況，因此在找問題的過程中，順便整理一下相關使用紀錄，免得以後又忘記了。
如同前面所言，如果要啟用 CDC 的功能，首先你必須先確定您的 SQL Server 版本是企業版 ( 或開發版 )，接著就可以使用 sp_cdc_enable_db 這個預存程序，來將資料庫 CDC 功能給開啟。如果我們要查看資料庫的 CDC 功能是否有開啟，也可以透過 sys.databases 裡面著 is_cdc_enabled 欄位值來做判斷。
當開啟之後，我們透過 SSMS 來查看這個資料庫裏面的系統資料表，會增加五個是 cdc 這個 schema 下的資料表，這幾個就是用來存放 CDC 相關的資訊了。
因為 CDC 的單位是按照 table 來管理的，因此這個時候我們還需要使用 sp_cdc_enable_table 這個預存程序來設定我們藥管理的資料表，此時我們的資料庫內有一個之前已經建立好的資料表 SourceTable，透過預存程序設定之後，如果是該資料庫第一次設定的話，會發現在 SQL Agent 排程內多兩個 JOB，分別是 cdc.<database name>_capture 和 cdc.<database name>_cleanup，因為我們測試的資料庫名稱是 CDCSample，所以這兩個新增的排程名稱就是 cdc.CDCSample_capture 和 cdc.CDCSample_cleanup。前者是當 SQL Agent 一啟動就執行，然後在背景利用非同步的方式透過 sp_replcmds 的工具將交易紀錄給取出並存放到指定的資料表內；後者預設是每天執行一次，用來清除 CDC 的歷史資料，預設會刪除最後一筆交易時間往前回推三天前的資料。
除了上述的 JOB 外，也對針對你所指定的資料表，多了一個在 cdc 這個 schema 下的系統資料表，後面加上 CT 的對應的 Table 來存放異動紀錄；還有對應的兩個資料表值函數，用這兩個來取得異動紀錄。
完成前面的設定之後，接下來我們可以來做一點測試，了解一下要怎麼來使用這兩個資料表值函數，因此在測試之前，我們先看一下原始資料表內有哪些資料，在這個資料表內我們之前就先預備好了五筆資料
接著我們下指令做一些變化，並且看看會發生甚麼樣的變化。我先刪除兩筆資料，然後新增兩筆紀錄，再將剛剛中間的三筆紀錄做了一些更新。如下圖我們可以看出資料表的前、後變化。
此時我們直接查看剛剛設定 CDC 所產生的系統資料表，可以看到除了原本資料表的欄位外，又增加了五個 metadata 的欄位，這裡面可以看到十筆紀錄
1. 前面兩筆紀錄，它的 _$operation 欄位值是 1，用來表示被刪除的資料，後面欄位紀錄刪除前原本的資料
2. 中間兩筆紀錄，它的 _$operation 欄位值是 2，用來表示被新增的資料，後面欄位紀錄此次新增加的資料
3. 後面六筆紀錄，它的 _$operation 欄位值是 3 和 4，3 表示異動前的舊資料，4 表示新資料
我們可以用 _$start_lsn 看出來相同的是同一個交易，而 _$seqval 相同則表示同一筆紀錄。比較特數的是 _$update_mask，你要把它用二進位然後由右向左看，比方說最後一塊有異動資料的值是 0x04，那麼二進位之後本來是 0100，那我們由右向左數的話，1 是位在第三個位置，就表示是第三個欄位有異動。
接下來我們來看那我們透過那兩個資料表值函數，會看到甚麼樣的資料呢 ? 這裡我們先用兩個函數 sys.fn_cdc_get_min_lsn 和 sys.fn_cdc_get_max_lsn 來分別取得在變更資料表內最小和最大的 lsn ( 記錄序號 )。取得這兩個值之後，就可以來測試 cdc.fn_cdc_get_all_changes<capture_instance> 函數，在我們的環境就是 cdc.fn_cdc_get_all_changes_dbo_SourceTable。這個資料表值函式的前兩個參數分別是起始的 lsn 和結束的 lsn，第三個參數則有兩種 all 和 all update old。當如果是 all 的時候，則會有刪除、新增和修改後的資料；而使用 all update old 則是除了原本 all 的結果之外，還加入了修改前的資料。
測試完之後，接著我們可以測試 cdc.fn_cdc_get_net_changes<capture_instance> 函數，在我們的環境就是 cdc.fn_cdc_get_net_changes_dbo_SourceTable，因為我們前面範例都比較單純，所以這裡看淨改變看不大出來，一般我們會使用的多半會用 all 的參數，速度會比其他幾個快。而 net_changes 和 all_changes 最大的差異在於當同一筆紀錄 ( 指的是 PK 值相同 ) 如果被改多次，則在 all_changes 裡面會取得的是多筆紀錄，而 net_changes 就只會取得最終結果的一筆紀錄。
前面所介紹的方式，是用指令自己來做處理和管理，而在 SQL Server 2012 之後，SSIS 支援了 CDC 相關的工作和元件，因此使用起來會更方便。
在使用 SSIS 來搭配 CDC 的時候，會需要兩個封裝來處理，第一個只有第一次做初始化的時候來使用的，第二個則是你可以用來定時擷取有異動的資料。在第一個封裝內，基本上我們會類似下圖放入三個工作，用來記錄每次擷取 LSN 的狀態值，並且透過資料流程工作，負責做第一次初始化的處理。為了方便後面的範例說明，我們會建立一個新的資料庫 CDCLab，利用 SSIS 搭配 CDC，讓原本資料庫內資料表可以和這個目的資料庫的資料表相同，但這只是一個範例，實際上這兩邊是可以不一樣的，就看你自己要使用在甚麼樣的場景當中了。
在上圖 1 的部分，我們會用兩個「CDC 控制元件」，基本上後面幾個部分用到這個元件，最主要要設定的就是第二個欄位 CDC 控制作業這裡的選項，剛剛前面有提到這個是初始化的處理，因此這裡我們就選擇「標示初始載入開頭」，並且需要先建立一個儲存狀態的資料表，這樣每次在執行的時候，就會把相關的 LSN 資訊存放在裡面，這樣就能知道那些處理已經執行過，那些還沒有被執行。
那 2 的部分 CDC 控制工作差異就是「標示初始載入結尾」，其他的就沒有甚麼不同了。這個部分主要就是 SSIS 要把 CDC 執行後的狀態給儲存起來。
在目的資料庫我們先建立三個資料表，為什麼要用三個，而不只有一個呢 ? 主要是希望透過 UPDATE & DELETE 的資料我們先用 BULK INSERT 的方式給存起來，後續再下指令讓他異動回 INSERT 的資料表，這樣避免有大量變更的時候，讓 UPDATE & DELETE 是一筆一筆下 SQL 指令拖慢速度。
CREATE TABLE [dbo].[TargetTable_INSERT](
[F1] [int] NOT NULL,
[F2] [nchar](10) NULL,
[F3] [nchar](10) NULL,
)
GO
CREATE TABLE [dbo].[TargetTable_UPDATE](
[F1] [int] NOT NULL,
[F2] [nchar](10) NULL,
[F3] [nchar](10) NULL,
)
GO
CREATE TABLE [dbo].[TargetTable_DELETE](
[F1] [int] NOT NULL,
[F2] [nchar](10) NULL,
[F3] [nchar](10) NULL,
)
GO
這裡在資料流程工作，我們就直接先把資料做第一次的同步過去。
因為這些只要處理一次就可以了，因此我們可以直接設定好之後，在 SSDT 上面執行就好了，不一定要佈署到 SSIS 上面。
當處理完初始化之後，那接下來我們就要來處理例行資料異動的擷取處理，此部分我們會建立一個新的封裝，這個跟初始化的封裝類似，只是後面我們再多了兩個「執行 SQL 工作」 把 DELETE 和 UPDATE 的資料合併回目的端的資料表上
此時一開始的 CDC 控制工作中要記得設定為「取得處理中的範圍」
結束的部分要設定為「標示以處理的範圍」
這裡在合併資料的部分，我使用以下的指令，這個你可以合併一起放在一個「執行 SQL 命令」上，也可以分開放到兩個「執行 SQL 命令」，前面一段是把放到 TargetTable_DELETE 資料表的資料，用他的 KEY 值去找目的資料表，並且把資料刪除；後面一段則是利用 TargetTable_UPDATE 資料表的資料，用他的 KEY 值去找目的資料表然後進行更新。
DELETE A
FROM [dbo].[TargetTable_INSERT] AS A
JOIN [dbo].[TargetTable_DELETE] AS B ON A.F1=B.F1;
UPDATE A SET A.F2=B.F2, A.F3=B.F3
FROM [dbo].[TargetTable_INSERT] AS A
JOIN [dbo].[TargetTable_UPDATE] AS B ON A.F1=B.F1;
在資料處理流程中就會比較複雜一點了。
這裡有個特別需要小心的地方，就是當我們針對 「CDC 來源」在設定的時候，要特別注意「CDC 處理模式」中選項。在這個範例中，我需要的是將來源資料表同步到目的端，那麼這個時候我就選擇使用「淨值」；如果你要的是了解異動紀錄，且資料表沒有 PK 的時候，則可以使用預設的「舊值」。下面我們會用的範例來說明使用「淨值」的原因
接下來透過 「CDC 分隔器」分成三類的資料，我把他們分別匯入到三個不同的資料表。完成相關設定之後，接下來就可以進行測試，目前來源資料表和目的資料表上因為前面的初始化封裝的處理之後，應該兩邊都會有相同的資料。
此時的處理我們就有點不一樣的方式，這兩我們先下兩次的 UPDATE 去更新同一筆資料 ( F1 欄位是 3 )；接著下 DELETE 指令刪除兩筆資料( F1 欄位是 6 和 7 )；再透過 INSERT 產生一筆之前曾經有的紀錄 ( F1 欄位是 6 )。
此時我們在 CDC 分隔器後面的三個流程，都設定資料檢視，來看一下當這樣處理的時候，會有甚麼樣的狀況。首先先看到 DELETE 的資料，明明前面我們刪除了兩筆，但這裡只有一筆，因為有一筆後面又有新增，因此 CDC 處理模式使用「淨值」的時候，那個刪除又新增的，就會變成是更新。
所以當我們查看 UPDATE 資料的時候，會發現原本下兩次 UPDATE 的時候，這裡就會合併成為一筆( F1 欄位是 3 )，而這裡多出的另外一筆，就是前面所說的刪除又新增的資料，在這裡就會是 UPDATE。那很自然地在新增的流程上，就不會有資料。
因此透過前面的「資料流程處理」之後，當我們處理執行完封裝之後，在目的資料表就會和來源資料表有著相同的資料了。
後記 :
1. 基本上關於 CDC，我個人建議參考官方的 MSDN 上文件，特別是有一張「異動資料擷取流程」的圖，那張圖很清楚的表達 CDC 的流程。
2. 會寫這一篇主要是因為有朋友在 「CDC 來源」上的 ç「CDC 處理模式」給設定為「全部」，造成資料有刪除又新增同一個 PK 值的時候發生錯誤，但因為它們的資料 PK 會抓最大的號碼加一，因此只有很特殊的時候剛好刪除到最後一筆資料，然後又新增資料的時候才發生問題，因為它設定使用「全部」，因此這個時候會發生有 INSERT 但資料已經存在目的地的狀況。
3. CDC 通常不會是拿來資料同步使用，因為你要達到這樣的功能，基本上直接使用 Replication 就可以了，而且標準版就可以使用了，只是在我們這個範例中，只是為了表達目的端可以和來源端有相同的異動結果，才這樣做的。一般來說會使用 CDC 這樣的功能，大部分用來追蹤來源資料有被甚麼樣的修改，然後要對應去改其他特定的資料表或特定的處理，來源和目的資料表的結構和處理可能不同。那或許有人會說，那麼用 TRIGGER 不是就可以達到了嗎 ? 基本上 TRIGGER 會讓原本的處理和你 TRIGGER 內的處理都綁在同一個交易內，因此會造成時間變長，且如果 TRIGGER 內發生異常，又會造成原本的處理一起被 ROLLBACK。所以在使用的時候，不要把 CDC、REPLICATION 和 TRIGGER 的使用時機和方式給搞混了。