SSIS 2012異動資料擷取(上)
由於不同系統間的整合需求日益增加，同步處理資料是越來越常見的工作。雖然SQL Server提供的複寫(Replication)技術是解決方案之一，但開發人員可能會需要更精確地控制同步機制、與不支援SQL Server複寫的系統同步、設計的資料結構不符複寫的要求，乃至於需要客製化同步資料的邏輯…等，種種需求導致既定的複寫機制不敷使用，而需要自行設計資料同步機制。第一個困難往往是如何在特定時間區段理，擷取來源端有哪些變異。

例如，在規劃資料倉儲或超市的ETL工作時，一定會遇到的問題是：如何僅將線上交易資料庫日常變更的內容載入到資料倉儲或超市。而要做到漸進遞增地更新資料，需辨識某段期限內，哪些資料有更動，且是否已經同步到資料倉儲內。這需要對來源端資料結構妥善地規劃，但由於設計交易系統的初始，並未考慮到與其他系統的整合與同步，因此，如何辨識變更的資料，以及這些變更的資料是否已經同步過，都將成為困難點。

SQL Server 2008版後，新增兩種機制來追蹤資料變異的歷程：「變更追蹤(Change Tracking)」、「異動資料擷取(Change Data Capture)」。SQL Server在2012版時，其SSIS針對異動資料擷取功能新增相關的工作與資料轉換元件，本篇專欄將對這些新增功能提供示範說明。
[註] 「變更追蹤」在一般地SQL Server版本即有支援，但「異動資料擷取」則須在企業版以上的版本。

在示範如何使用SSIS 2012新增的「CDC控制工作」、「CDC來源」與「CDC分隔器」等功能前，需要稍微了解 SQL Server本身的異動資料擷取運作原理，才能設計SSIS的封裝。簡單異動資料擷取如下：
●與交易式複寫(Transactional Replication)機制類似，都是從交易記錄檔讀出需要追蹤的交易記錄。CDC會將變動細節放入到系統自動建立的資料表。
●SQL Server自動產生並提供相關函數，以便查詢特定範圍內的變更，並傳回結果集。換句話說，使用者可透過系統函數讀出時間、交易序號(Log Sequence Number LSN)與變動歷程。
●相較於透過「交易式複寫」將資料表內的變動帶到目的端，若週期時間內，同一筆資料的變動順序是1→2→3(起始值是1，最終改成3，所以淨變化是1→3)，則目的端同樣會發生兩次變化。但若用CDC，可以選擇僅要將最後的3帶到目的端，不必浪費資源轉中介值2。

異動資料擷取的運作原理如圖1所示：


▲ 圖1 SQL Server異動資料擷取的運作原理。


如圖1所示，大概的架構是SQL Server會針對啟動CDC的資料表個別建立系統資料表，以存放變動資料。同時建立系統函數，便於開發者讀取系統資料表內的異動追蹤紀錄。再利用Agent Job叫起交易記錄讀取程式，從資料庫的交易記錄檔中取出目標資料表的相關變更，並放入到對應的系統資料表。另外，將透過Agent Job定期清掉過期的資料。

使用SQL Server的異動資料擷取有下列優點：
●不影響既有的設計，例如不建立觸發程序、時間戳記欄位，或增加欄位、使用者資料表。SQL Server會建立相關的系統物件，但不影響使用者原先資料庫的設計。
●會在交易認可，而不是發生DML作業時，追蹤變更。
●SQL Server自動新增的系統函數會傳回資料表的累加變更和版本資訊。
●因為是在背景對交易記錄檔批次處理，降低啟動同步機制後，對原系統資料異動當下的效能影響。但要注意的是，也因此變動記錄並非及時同步。會延遲多久，將因系統的忙碌程度而定，無法準確預估。
●異動資料擷取的資料內容可以自動清除。

由於在SQL Server 2008版時，並沒有提供工具或物件來控管CDC，一切都需要手動，如建立資料表，以記載曾經處理過的交易記錄序號，下次同步時才知道從哪開始。並撰寫T-SQL語法從CDC相關的系統資料表中，讀取上一次完成同步後所記載的交易序號到現今交易序號間，所有的異動變更記錄。而後再執行同步資料到其他系統的邏輯。詳細的作法可以參照筆者所著<>。

SSIS 2012版本後，當然仍能完全以T-SQL語法自行實作在CDC架構下的資料同步，還可以透過新增的「CDC控制工作」，與資料流程內的「CDC來源」與「CDC分隔器」元件輔助完成同步作業，分別簡單說明這些工作和元件的用途：
●CDC 控制工作(CDC Control Task)：用來控制異動資料擷取(Change Data Capture CDC)封裝的開發週期。它會處理 CDC初始載入的同步作業，以及 CDC 封裝執行中所處理之記錄序號(LSN)範圍。此外，CDC 控制工作也會處理錯誤狀況和復原。
「CDC 控制工作」利用SSIS「封裝變數」維護CDC流程的狀態，也可以將它保存在資料庫資料表中，以便於跨封裝啟動和在一起執行通用 CDC 處理序的多個封裝之間維護狀態 (例如某個工作可能負責初始載入，而另一個工作則負責同步更新)。

除了控制流程的「CDC 控制工作」外，資料流程也增加了「CDC 來源」和「CDC分隔器」，說明如下：
●CDC 來源(CDC Source)：從 SQL Server啟動CDC功能的資料表中，讀取某個範圍的變更資料，並將這些變更傳遞至其他 SSIS 元件。它所讀取的封裝變數資料範圍稱為CDC處理範圍，由資料流程啟動之前執行的「CDC 控制工作」所決定，「CDC 來源」不會修改該變數值。「CDC 來源」傳回的資料與 SQL Server CDC 函數 cdc.fn_cdc_get_all_changes_<擷取執行個體名稱>或cdc.fn_cdc_get_net_changes_<擷取執行個體名稱>(如果針對要追蹤的資料表有啟動supports_net_changes設定，才有這個函數)傳回的資料相同。

[註] 參照以下範例程式碼1所啟動CDC功能的資料表[dbo].[DimCustomer_CDC]，這兩個函數的名稱為fn_cdc_get_all_changes_dbo_DimCustomer_CDC和fn_cdc_get_net_changes_dbo_DimCustomer_CDC。

●CDC分隔器(CDC Splitter)：將「CDC 來源」提供的資料流依「_$operation」欄位值，區分每一筆記錄，使其進入「CDC分隔器」輸出的「新增(InsertOutput)」、「修改(UpdateOutput)」和「刪除(DeleteOutput)」三種資料流。

接下來，以範例說明這些工作與元件。在此，先設計一個初始同步的流程，將來源資料表的內容批次新增到目的資料表。
首先建立示範用的資料庫與資料表：

範例程式碼 1：建立資料庫、來源資料表與需要同步的目的資料表，並針對來源資料庫和資料表啟動CDC功能

--Agent Services 要確定啟動了

create database CDCTest
GO

USE [CDCTest]
GO
--啟動資料庫的CDC功能
EXEC sys.sp_cdc_enable_db
GO

--建立需要同步的來源資料
--最好要有主鍵，供我們自行撰寫同步目標資料表的邏輯
CREATE TABLE [dbo].[DimCustomer_CDC](
[CustomerID] [nchar](5) PRIMARY KEY,
[CompanyName] [nvarchar](40) NOT NULL,
[ContactName] [nvarchar](30) NULL,
[ContactTitle] [nvarchar](30) NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NULL,
[Phone] [nvarchar](24) NULL,
[Fax] [nvarchar](24) NULL
)
GO

--啟動對 DimCustomer_CDC 資料表的CDC追蹤
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'DimCustomer_CDC',
@role_name = N'cdc_admin',
@supports_net_changes = 1
GO

INSERT DimCustomer_CDC SELECT *
FROM [Northwind].[dbo].[Customers]
WHERE CustomerID < 'K%'
GO

--建立需要同步的目的資料表
--維持與來源資料表相同的架構
SELECT TOP 0 * INTO DimCustomer_Destination
FROM DimCustomer_CDC

特別強調一點，SQL Server的CDC機制需要倚靠Agent Services，所以在進行以下的練習前，須確定Agent Services已經啟動。
接下來建立一個SSIS 2012封裝，透過「CDC控制工作」初始化資料，從範例程式碼1所建置的DimCustomer_CDC資料表同步到DimCustomer_Destination資料表。整體的設計如圖2所示：


▲ 圖2 透過「CDC控制工作」初始化需要同步的兩個資料表。


因為要保證初始化後，兩個資料表的內容一致。首先從SSIS工具箱中拖曳一個「執行SQL工作」，其內指定「OLE DB連接」對目標資料表之資料庫的連接，並在「SQLStatetment」屬性設定如下的語法：

TRUNCATE TABLE [dbo].[DimCustomer_Destination]

透過TRUNCATE TABLE語法，在載入來源資料表當下的資料前，先清空目的資料表。接著拖曳「CDC控制工作」到封裝設計環境，滑鼠雙擊該工作叫出「CDC控制工作編輯器」對話窗，並點選最上方「SQL Server CDC資料庫ADO.NET連結管理員」下拉選單旁的「新增」按鈕，在接下來的「設定ADO.NET連接管理員」對話窗中，選到先前在SQL Server建立的CDCTest測試資料庫，如圖3所示：


▲ 圖3 在封裝中新增「CDC控制工作」，並設定ADO.NET連接到啟動CDC功能的資料庫。


接著在「CDC控制工作編輯器」對話窗的「CDC控制作業」下拉選單中，選擇「標示初始載入開頭」選項，如圖4所示：


▲ 圖4 設定「CDC控制工作」當下要執行的作業。


圖4的控制作業可分為兩類，一類處理初始載入，另一類是取得CDC所記載的LSN變更處理範
圍及追蹤何者已成功處理。以下說明「CDC控制作業」處理初始載入的選項功能：

[註] SQL Server 2012的線上說明針對此提供的表格中，尚有一項作業：ResetCdcState，說是用來重設與目前 CDC 內容相關聯的永續性CDC狀態。執行此作業之後，呼叫sys.fn_cdc_get_max_lsn系統函數取得資料表中目前最大的 LSN，它會變成下一個處理範圍的範圍開頭。此作業需要來源資料庫的連接。但在圖4中找不到該作業選項:(


「CDC控制工作」為初始化兩個資料表間的記錄所提供的作業
●作業：標示初始載入開頭(MarkInitialLoadStart)
封裝開始執行初始載入時用此作業，在讀取來源資料表並轉資料之前，記錄來源資料庫中目前的 LSN。這需要來源資料庫的連接，以呼叫SQL Server為CDC提供的系統函數。[註] 線上說明表示會呼叫sys.fn_cdc_get_max_lsn，但就筆者透過SQL Profiler錄製，是呼叫[sys].[sp_replincrementlsn]系統預存程序，似乎是初始化時自行產生一個LSN當作其後的基準點。
●作業：標示已處理的範圍(MarkInitialLoadEnd)
在初始載入封裝結束時使用此作業，以便在初始載入封裝完成讀取來源資料表之後記錄來源資料庫中目前轉完的 LSN。這個 LSN 的決定方式如下：記錄進行此作業時的目前時間，然後在 CDC 資料庫中查詢 cdc.lsn_time_mapping 資料表，尋找該時間之後發生的變更。
●作業：MarkCdcStart
從快照集資料庫(Snapshot Database)進行初始載入，若執行CDC同步作業時，來源系統還持續變更，就可能需要搭配快照集資料庫。
變更處理應該在快照集 LSN 之後立即開始。你可以指定要使用的快照集資料庫名稱，CDC 控制工作就會在 SQL Server 2012 中查詢快照集 LSN。你還可以選擇直接指定快照集 LSN。

若要執行以上列舉的作業，連接管理員中指定的使用者就必須是 db_owner 或系統管理員 (sysadmin)。其他兩個作業「取得處理中的範圍」、「標示已處理的範圍」用來處理持續同步時的當下範圍，我們將在下篇專欄中介紹。

接著是設定「包含CDC狀態的變數」，可設定與保持CDC同步資料的持續性。按下「包含CDC狀態的變數」旁的「新增」按鈕，會跳出「加入新的變數」對話窗，按下「確定」後，自動在封裝中新增名為「CDC_State」的封裝變數，「範圍」為整個封裝，如圖5所示：


▲ 圖5 將封裝執行過程中使用到的LSN記載在封裝變數。


「CDC 控制工作」會在多次封裝執行的過程間維護連續性。將狀態以字串描述，儲存在封裝變數或資料表的CDC狀態資訊，可用來決定及維護CDC封裝的處理範圍，以及用於偵測錯誤狀態。

「CDC 控制工作」支援兩種狀態持續性類型：
●手動維護狀態持續性：不勾選圖5中的「自動將狀態儲存在資料庫資料表中」。「CDC 控制工作」會管理儲存在封裝變數中的狀態，但是封裝開發人員必須在呼叫「CDC 控制工作」之前從永續性存放區讀取變數，然後在最後一次呼叫 「CDC 控制工作」執行完成後，將變數寫回該永續性存放區。
●自動維護狀態持續性：勾選圖5中的「自動將狀態儲存在資料庫資料表中」。CDC 狀態會儲存在資料庫資料表中。此狀態是在「用於儲存狀態的資料表」屬性所指名資料表中，該資料表需位於「狀態儲存所在資料庫的連接管理員」指定的資料庫中。並在資料表中儲存「狀態名稱」屬性所提供的封裝變數內容。預設是來源連接管理員，但常見作法是將它做為目標連接管理員。CDC 控制工作會更新狀態資料表中的狀態值，並認可此值做為環境交易的一部分。

在此勾選「CDC控制工作編輯器」對話窗中間的「自動將狀態儲存在資料庫資料表中」，並點選下方「用於儲存狀態的資料表」旁之「新增」按鈕，會在彈出的「建立新狀態資料表」視窗呈現如下的T-SQL語法：

範例程式碼2：在來源資料庫建立儲存CDC進度的資料表

CREATE TABLE [dbo].[cdc_states]
([name] [nvarchar](256) NOT NULL,
[state] [nvarchar](256) NOT NULL) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [cdc_states_name] ON
[dbo].[cdc_states]
( [name] ASC )
WITH (PAD_INDEX = OFF) ON [PRIMARY]
GO

按下「建立新狀態資料表」視窗下方的執行健，就會在下方「狀態儲存所在資料庫的連接管理員」指定的資料庫建立範例程式碼2中的「cdc_states」資料表，「CDC控制工作」需要靠這個資料表來接續以往曾經執行過的記錄。

圖5最下方的「狀態名稱」要針對來源啟動CDC功能之不同的資料表，分別賦予不同的名稱，已分別記載不同資料表同步的進度。

接下來加入一個「資料流程工作」到設計環境，在資料流程中簡單把來源資料表內所有的記錄新增到目的資料表，其設定如圖6所示：


▲ 圖6 在資料流程中將來源資料表內容完整載入到目的端。


最後再加入一個「CDC控制工作」到控制流程中，其設定與圖5的選項大致相同，僅是「CDC控制作業」改成「標示初始載入結尾」，如圖7所示：


▲ 圖7 透過「CDC控制工作」將初始載入執行結尾的狀況寫入儲存狀態的資料表。


至此，初始載入的封裝設計完畢，它僅是簡單地將來源資料表內所有的記錄大量載入到目的端資料表，而後將處理完成的LSN記錄到資料表內。你可以執行一下該封裝，將看到目的資料表含有來源資料表的所有資料。同時，在cdc_states資料表中存有初始載入所取得資料記錄的最大LSN。

筆者在測試資料庫中建立的兩個資料表，個別啟動CDC並同步到不同的目的資料表。故在圖7的「狀態名稱」分別輸入不同的值，兩個同步都執行完畢後，查詢cdc_states資料表的結果如圖8所示：


▲ 圖8 透過資料表不同的記錄，分別記載啟動CDC功能的資料表不同的進度。


在圖8中，第一筆記錄存放的是本篇專欄所練習的初始同步，而下一筆記錄則是筆者故意模擬另一個資料表做完遞增同步後的結果。

在下期專欄中，將繼續討論如何透過「CDC控制工作」和資料流程內的「CDC來源」與「CDC分隔器」元件，持續地同步兩個資料表。