Die Funktionalität Change Data Capture ist schon seit SQL Server 2008 verfügbar. Im SQL Server 2012 sind noch zusätzlich neue Tasks im SSIS hinzugekommen. Genau diese sollen in diesem Artikel beleuchtet werden. Das Beispiel wird anhand unseres Showcases „Privatbilanz“ durchgeführt und ist nur ein möglicher Lösungsweg. Mit Hilfe von CDC kann sowohl eine initiale als auch eine inkrementelle Beladung durchgeführt werden.
Die Umgebung für den Showcase besteht aus einer Privatbilanz Quelldatenbank, und einer Privatbilanz-BI Zieldatenbank, die über eine ETL-Strecke miteinander verbunden werden. In dieser ETL-Strecke werden die CDC-Komponenten integriert.
Zunächst muss die Quelldatenbank mittels einer StoredProcedure für CDC bereitgestellt werden. Dies geschieht über folgenden Befehl:

   1: USE Privatbilanz 

   2: GO 

   3: EXEC sys.sp_cdc_enable_db 

   4: GO

Diesen Schritt kann man mit folgendem Statement überprüfen:

   1: USE master 

   2: GO 

   3: SELECT [name], database_id, is_cdc_enabled  

   4: FROM sys.databases       

   5: GO

Anschließend muss die Tabelle, die man tracken möchte, ebenfalls für CDC vorbereitet werden.

Dies geschieht wie folgt:

   1: USE Privatbilanz 

   2: GO 

   3: exec sys.sp_cdc_enable_table

   4: @source_schema = N'dbo',

   5: @source_name = N'Institut',

   6: @role_name = NULL,

   7: @supports_net_changes = 1

   8: GO

Wichtig: Supports Net Changes ist optional. Wenn diese angegeben wird, dann hat man später die Möglichkeit im Data Flow Task, in der CDC Source den Processing Mode auf „Net“ zu stellen. Somit werden nur die reinen Änderungen getrackt (dazu später mehr).

Auch diesen Schritt kann man überprüfen:

   1: USE Privatbilanz

   2: GO

   3: SELECT [name], is_tracked_by_cdc

   4: FROM sys.tables

   5: GO

Oder (alternativ):

   1: EXECUTE sys.sp_cdc_help_change_data_capture

   2: @source_schema = N'dbo',

   3: @source_name = N'Institut';

Im SQL Server Agent werden hierzu pro Datenbank zwei Jobs angelegt:

Nachdem man diese Schritte ausgeführt hat, erkennt man, dass mehrere Tabellen mit dem Schema Namen cdc angelegt wurden.

Was passiert nun genau im Hintergrund?

Nachdem man CDC für eine Tabelle aktiviert hat, wird eine weitere Tabelle vom System angelegt, in unserem Beispiel ist dies cdc.dbo_Institut_CT. Wie im unteren Abbild zu erkennen, sind zu den vorhandenen ursprünglichen Tabellen neue Spalten hinzugekommen. Die mit dem „__$“ Spalten sind Eigenschaften des CDC.

Wenn Daten in der Quelltabelle geändert werden, dann werden diese im SQL Server zunächst in das Transaction Log geschrieben. Nun, das ist keine Neuerung des CDC, sondern üblich. Das CDC nutzt hierbei lediglich eine Eigenschaft des SQL Servers. Erfolgt eine Änderung des Datensatzes liest das CDC asynchron aus dem Transaction Log und schreibt diese in die bereits angelegte cdc.dbo_Institut_CT Tabelle. Im ETL-Vorgang kann man die Änderungen auslesen.

Initial ist die Tabelle leer. Nach einem Insert/Update/Delete-Vorgang werden die betroffenen Sätze und CDC-spezifische Informationen in der Tabelle abgelegt.

Wenn man beispielsweise ein Update auf einen Datensatz durchführt, dann erhählt man zwei Datensätze in der cdc.dbo_Institut_CT Tabelle. Die Spalte __$operation erhält bei einem Update eines Datensatzes die Id=3, dies ist der Datensatz in seiner ursprünglichen Form. Es kommt ein weiterer Datensatz hinzu, der die Id=4 hat. Dies ist der Datensatz in seiner neuen Form. Nähere Informationen zu den Operations und den weiteren Spalten finden Sie: http://msdn.microsoft.com/de-de/library/hh758656.aspx

Nachdem die Datenbank und die Tabellen für Change Data Capture vorbereitet wurden, konfiguriert man die Pakete im SSDT 2010 (neuerdings ist das auch in SSDT für Visual Studio 2012 möglich).

Der Control-Flow sieht für unser Beispiel wie folgt aus (diese werden näher beschrieben):

Für die initiale Beladung muss ein Control Task die jeweilige Tabelle „markieren“. –> CDC Control Operation: Mark initial load start.

Nach der Übertragung der Tabelle aus der Referenz Datenbank in die stage.Institut Tabelle in der Stage Datenbank (im DFT) muss der Endpunkt im CDC Control Task markiert werden. CDC Control operation: Mark initial load end.

Hinweis: CDC benötigt eine ADO.Net Datenquelle

Für die inkrementelle Beladung werden folgende Einstellungen für CDC vorgenommen:

Der DFT für die inkrementelle Beladung sieht wie folgt aus:

Der Delete Strang fällt in unserem Beispiel weg. In unserem Beispiel wollen wir nicht, dass durch das Quellsystem bestimmt wird, was geändert, gelöscht, hinzugefügt wird. Um die Unterscheidung was konkret geändert, hinzugefügt wird, kümmern wir uns im folgenden DFT. Es gibt allerdings auch andere Lösungsszenarien, wo die Outputs direkt dafür verwendet werden, Datensätze zu aktualisieren, löschen, hinzuzufügen.

Die beiden Datenströme aus dem CDC-Splitter werden über ein Union-All Task in die stage Zieltabelle zusammengeführt.

CDC Processing Mode in der CDC Source ist auf Net gestellt, damit nur die Netto-Änderungen übertragen werden. Es wird also bei mehrfachen Änderungen eines Datensatzes in der Quelle nur die finale Version geliefert. Weitergehende Informationen zu den Processing-Modes finden Sie unter http://msdn.microsoft.com/de-de/library/hh231004.aspx

Anschließend wird der Endpunkt der inkrementellen Beladung markiert.

Fazit

Change Data Capture als Datenbankfeature ist schon seit SQL Server 2008 verfügbar. Im SQL Server 2012 sind noch zusätzlich neue SSIS Tasks hinzugekommen, die die ETL-Verarbeitung von im Quellsystem erfassten Datenänderungen deutlich erleichtern. Da die Änderungen asynchron aus dem Transaction Log ausgelesen werden, erzeugt CDC relativ wenig Last in der Quelldatenbank. Auch in der Implementierung ist der Einsatz deutlich einfacher und wartungsfreundlicher als übliche Lösungen wie z.B. Trigger und Timestamps.