In den meisten BI-Systemen nimmt das Laden der Faktendaten die meiste Zeit in Anspruch. Daher kommt man schnell auf die Idee, nicht immer alle Faktendaten zu laden, sondern nur die neuen und die geänderten Datensätze. Dabei gibt es unterschiedliche Szenarios in Bezug auf das Quell-System:

• Es werden nur neue Datensätze geladen und es ist eine Spalte vorhanden, mittels derer genau bestimmt werden kann, welche Datensätze zu laden sind. Dies könnte z. B. ein fortlaufender Datensatzzähler sein.
• Es kann ein überlappendes Zeitfenster auf einer Datumsspalte bestimmt werden, in welchem sich Daten ändern können, z. B. das Buchungsdatum.
• Es sind keine Informationen vorhanden, mit Hilfe derer nur die geänderten Daten selektiert werden können.

Der SQL Server bietet unterschiedliche Möglichkeiten, diese Probleme zu lösen. Mittels selbst programmiertem T-SQL Code ist selbstverständlich alles möglich, jedoch ist das nicht immer die beste Lösung. Gerade für das dritte oben genannte Szenario wäre viel Aufwand notwendig.

Bereits für den SQL Server 2008 hat Microsoft eine Funktion mit dem Namen Change Data Capture (CDC) eingeführt. Mit dem SQL Server 2012 wurde die Funktion weiter ausgebaut, u. a. ist eine SSIS- Unterstützung hinzugekommen. In diesem Blog wird gezeigt, wie CDC für ein Delta Ladevorgang verwendet werden kann.

Es gibt unterschiedliche Methoden zur Identifizierung neuer und geänderter Datensätze, wenn keine Geschäftslogik zur Identifizierung vorhanden ist. Nachfolgend sind drei häufige Methoden genannt:

1. Je Datensatz wird ein Zeitstempel, eine Versionsnummer, ein Status oder eine Kombination aus diesen Möglichkeiten hinzugefügt. Beispiel: Eine Spalte LAST_UPDATE enthält immer den Zeitstempel, wann ein Datensatz zuletzt bearbeitet wurde.
2. Über einen Trigger wird ereignisgesteuert beim Anlegen/bei der Änderung eines Datensatzes gleich ein weiterer identischer Datensatz in einer separaten Tabelle erzeugt.
3. Auslesen der Datenbank-Logfiles und Schreiben der Änderungen in eine separate Tabelle. Dieses Verfahren zum Auslesen der Datenbank-Logfiles nutzt Microsoft mit CDC.

Aktivierung von CDC

Nachfolgend wird Schritt für Schritt die Aktivierung von CDC beschrieben. Als Beispiel dient die bekannte Demo-Anwendung Chair. Zieltabelle ist die T_Import_Deckungsbeitragsrechnung, Quelltabelle eine identisch aufgebaute Tabelle T_Sales.

Hinweise:

• CDC kann nur von einem System-Admin aktiviert werden.
• Der SQL Server Agent muss vor Ausführung des nachfolgenden SQL Codes aktiviert sein.
• CDC ist ausschließlich in der Enterprise Version des SQL Servers vorhanden.

1. CDC auf einer Datenbank aktivieren

   -- Enable Database for CDC
   EXEC sys.sp_cdc_enable_db;

   Sollte folgende Fehlermeldung erscheinen,

   „Meldung 22830, Ebene 16, Status 1, Prozedur sp_cdc_enable_db_internal, Zeile 193

   Die Metadaten, die angeben, dass die AdventureWorks2012-Datenbank für Change Data Capture aktiviert ist, konnten nicht aktualisiert werden. Der Fehler ist beim Ausführen des Befehls ‘SetCDCTracked(Value = 1)’ aufgetreten. Der zurückgegebene Fehler lautete 15517: ‘Die Ausführung als Datenbankprinzipal ist nicht möglich, weil der Prinzipal ‘dbo’ nicht vorhanden ist, für diesen Typ von Prinzipal kein Identitätswechsel möglich ist, oder Sie nicht die erforderliche Berechtigung haben.’. Verwenden Sie die Aktion und den Fehler, um die Ursache des Fehlers zu bestimmen, und senden Sie die Anforderung erneut.“

   dann kann über den nachfolgenden Befehl der Datenbankbesitzer geändert werden. Anschließend sollte die Aktivierung von CDC funktionieren:

   Exec sp_changedbowner ‘sa’;

   1. Für einen sicheren Zugriff eigene Datenbank-Rolle für CDC hinzufügen

   -- Add a custom role for CDC
   CREATE ROLE cdc_role;

   2. Tabelle zu CDC hinzufügen; der SQL Server Agent sollte bereits aktiviert sein.

   -- Ensure that the SQL Server Agent is running
   -- Enable table for CDC
   EXEC sys.sp_cdc_enable_table
   @source_schema = N'dbo', -- NOT NULL
   @source_name = N'T_Sales', -- NOT NULL
   @role_name = N'cdc_role', -- Role with Access to CDC data
   @supports_net_changes = 1; -- Support for querying net changes

Die Prozedur legt ein Datenbankschema, zwei SQL Server Agent Jobs und sechs Systemtabellen an.

Abb. 1: CDC Systemtabellen

Die Systemtabellen verfolgen dabei folgenden Zweck:

• captured_colums: Alle Spalten, die überwacht werden
• change_tables: Alle Tabellen, für die CDC aktiviert wurde
• <source_schema>_<source_name>_CT: Für jede Tabelle, für die CDC aktiviert wurde, wird eine exakte Kopie angelegt, in der alle Änderungen gespeichert werden mit fünf zusätzlichen Spalten. Wichtig ist die Spalte __$operation, die die folgenden 4 Werte annehmen kann:
  • 1: Delete Statement
  • 2: Insert Statement
  • 3: Values before Update Statement
  • 4: Values after Update Statement
• ddl_history: Alle Änderungen an Definitionen der für CDC aktivierten Tabellen; zunächst leer
• index_columns: Index-Spalten der für CDC aktivierten Tabellen
• lsn_time_mapping: Mapping zwischen Log Sequence Number (LSN) und dem Zeitpunkt einer Transaktion

Die angelegten SQL Server Agent Jobs sehen wie folgt aus:

Abb. 2: CDC SQL Server Agent Jobs

• <databasename>_capture: Liest die Logfiles aus und schreibt Änderungen in die Tabelle cdc.<source_schema>_<source_name>_CT; wird alle fünf Sekunden ausgeführt
• <databasename>_cleanup: Löscht die oben genannten Tabellen, damit diese nicht zu groß werden; wird täglich ausgeführt

SSIS-Integration

Seit SQL Server 2012 gibt es drei CDC-Tasks:

1. CDC-Kontrollaufgabe (CDC Control task oder auch CDC-Steuerungsaufgabe): Verwaltet und synchronisiert CDC. Hier wird eingestellt, ob es sich um einen initialen Ladevorgang oder einen Delta Ladevorgang handelt.
2. CDC-Quelle (CDC source adapter): Angabe der Quelltabelle und des Verarbeitungsmodus. Es gibt fünf verschiede Modi:
   • Alle: Für jede Änderung wird ein Datensatz zurückgeliefert, d. h. wenn auf einem Datensatz drei Updates durchgeführt wurden, dann werden auch drei Datensätze zurückgeliefert.
   • Alle mit alten Werten: Zusätzlich zum Modus Alle werden auch noch die Werte vor und nach einem Update geliefert.
   • Netto: Liefert nur den letzten Stand für jeden Datensatz und ist daher der gebräuchlichste Modus.
   • Netto mit Updatemaske: Zusätzlich zum Modus Netto wird für jede Spalte eine Informationsspalte vom Typ Boolean geliefert, die anzeigt, ob sich der Wert verändert hat.
   • Netto mit Merge: Wie Netto, nur dass neue und aktualisierte Datensätze in einem abgehenden Zweig zur Performance-Optimierung zusammengefasst werden.
3. CDC-Teiler (CDC splitter): Teilt den Datenfluss aus der CDC-Quelle auf in zwei bis drei Zweige (Insert, Update, Delete) auf Basis des Wertes in der Spalte __$operation je nach Einstellung des Verarbeitungsmodus.

Initialer Ladevorgang

Bevor die Delta-Logik von CDC genutzt werden kann, muss zunächst ein initialer Ladevorgang durchgeführt werden. Zur Vereinfachung empfiehlt es sich, dafür ein eigenes kleines SSIS-Paket zu erstellen, das genau diese Aufgabe übernimmt. Selbstverständlich ist es möglich, z. B. über Variablensteuerung, den initialen Ladevorgang mit in das eigentliche Paket zu verlagern.

Das Paket besteht in der Ablaufsteuerung lediglich aus drei Komponenten (siehe Abb. 3), zwei CDC-Kontrollaufgaben und einer Datenfluss-Aufgabe.

Abb. 3: Ablaufsteuerung im SSIS-Paket zum initialen Ladevorgang

Die Einrichtung der CDC-Kontrollaufgaben ist einfach. Zunächst muss die oben verwendete Datenbank angegeben werden, in der CDC aktiviert wurde. Dann wird in der CDC-Startkomponente angegeben, dass es sich um den „Start des anfänglichen Ladevorgangs …“ handelt (bei CDC-End wird alles identisch angegeben, mit dem Unterschied, dass es sich um das Ende handelt). Anschließend kann komfortabel eine User-Variable und eine Tabelle zum Speichern des CDC-Status angelegt werden. Die Tabelle kann auch in einer dritten Datenbank angelegt werden. Die Setzung des automatischen Status in einer Datenbanktabelle sollte laut Microsoft verwendet werden.

Abb. 4: Editor für die CDC-Kontrollaufgabe

Im Datenfluss werden alle Datensätze aus einer Quelltabelle in eine Zieltabelle geladen.

Abb. 5: Datenfluss des initialen Ladevorgangs

Delta Ladevorgang

Das zweite SSIS-Paket für den Delta Ladevorgang ist ebenfalls unkompliziert. In der Ablaufsteuerung gibt es wieder zwei CDC-Kontrollaufgaben, die genauso wie oben beschrieben konfiguriert werden. Einzig der CDC-Kontrollvorgang muss auf „Verarbeitungsbereich abrufen“ beim Start und „Verarbeitungsbereich kennzeichnen“ beim Ende gesetzt werden.

Abb. 6: Ablaufsteuerung beim Delta Ladevorgang

Der Datenfluss beim Delta Ladevorgang besteht aus einer CDC-Quelle, einem CDC-Teiler und je einem Ziel für neue, geänderte und gelöschte Datensätze (siehe Abb. 7). Neue Datensätze können direkt der Zieltabelle hinzugefügt werden, in diesem Beispiel T_Import_Deckungsbeitragsrechnung. Die geänderten und gelöschten Datensätze sollten zunächst in Staging-Tabellen geschrieben werden. Anschließend können diese Tabellen in der Ablaufsteuerung über SQL-Tasks (siehe Abb. 6) verarbeitet und zum Abschluss des SSIS-Pakets geleert werden.

Abb. 7: Datenfluss des Delta Ladevorgangs

Auch die Einstellung der CDC-Quelle ist denkbar einfach (siehe Abb. 8), einzig der CDC-Verarbeitungsmodus muss passend gewählt werden (siehe oben). Der CDC-Teiler benötigt keine weitere Konfiguration.

Abb. 8: CDC-Quelle

Die Abbildung 9 zeigt einen Ausschnitt der CDC-Tabelle cdc.dbo_T_Sales_CT nach einem Insert-, einem Update- und einem Delete-Vorgang.

Abb. 9: cdc.dbo_T_Sales_CT Tabelle

Fazit

Wie immer gibt es Vorteile, aber auch Punkte, die beim Einsatz von CDC beachtet werden müssen.

Vorteile:

• Minimale Auswirkungen auf die Datenbank-Performance, insbesondere im Vergleich zu Datenbank-Triggern.
• Tabellen (und Spalten), die mitgeloggt werden sollen, können explizit ausgewählt werden.
• Vorhandene Tabellen in der Datenbank müssen nicht geändert werden.
• Das Verfahren ist leicht zu aktivieren bzw. zu deaktivieren.
• CDC ist bemerkenswert leicht zu konfigurieren.
• Das Verfahren kann zur Untersuchung von Update-Verhalten genutzt werden.

Zu beachten:

• Das Verfahren basiert auf dem SQL Server-Agent. Ist dieser deaktiviert oder abgestürzt, dann werden auch keine Änderungen verfolgt! Sofern die Logfiles noch vorhanden sind, können diese aber noch ausgelesen werden.
• Logfiles werden häufig archiviert. Die Archivierung darf erst dann erfolgen, wenn das Log bereits ausgelesen wurde.
• Rollbacks in der Datenbank sind zu beachten.
• Die Menge der geloggten Daten kann schnell sehr groß werden.
• Es wird nicht mitgeloggt, wer Änderungen durchgeführt hat.
• Es ist ein zeitlicher Versatz vorhanden, da die Logs erst ausgelesen werden müssen.

Grundsätzlich sollte CDC verwendet werden, wenn

• keine eindeutige Regel zur Identifikation geänderter Daten festgelegt werden kann und
• das Quell-System CDC unterstützt.

Quellen

• http://en.wikipedia.org/wiki/Change_data_capture
• http://geekswithblogs.net/Compudicted/archive/2012/03/06/top-10-ssis-2012-improvements.aspx
• http://msdn.microsoft.com/de-de/library/bb500353.aspx
• http://msdn.microsoft.com/de-de/library/bb522475.aspx
• http://msdn.microsoft.com/en-us/library/bb522489%28v=sql.105%29.aspx
• http://msdn.microsoft.com/en-us/library/dd266396%28v=sql.100%29.aspx
• http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=DE-DE&k=k%28SQL11.SSIS.DESIGNER.CDCCONTROLTASK.F1%29&rd=true
• http://www.mattmasson.com/2011/12/cdc-in-ssis-for-sql-server-2012-2/
• http://www.sanssql.com/2011/02/error-while-configuring-change-data.html
• http://www.sql-server-performance.com/2013/understanding-sql-server-change-data-capture-cdc/
• https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/