[SQL]SQL Server 異動資料擷取 ( CDC ) 測試筆記

[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 的使用時機和方式給搞混了。