Der Blick in die Datenvergangenheit mit Change Data Capture

Applikationen nutzen Datenbanken, um Informationen zu speichern, zum Beispiel zu den Fragestellungen "Wie lautet die Adresse des Kunden?" oder "Welchen Wert hat ein Konfigurationsparameter?" Gespeichert werden jedoch vielfach nur die aktuellen Daten. Datenänderungen können so nicht nachverfolgt werden. Historische Daten, wie z. B. "Wo hat der Kunde vorher gewohnt?" stehen nicht mehr zur Verfügung. Solche Informationen liefert Change Data Capture - eine neue Funktion des MS SQL Server 2008.

Voraussetzungen und Einsatzszenarien

Wie viele andere neue Funktionen ist auch Change Data Capture (CDC) nur in der Enterprise Edition des SQL Servers enthalten. Zudem muss der SQL Server Agent aktiviert werden, da Aufträge eine zentrale Rolle spielen.

Ein mögliches Szenario ist der in der Einleitung angesprochene Zugriff auf historische Daten. Aber auch die möglichst zeitnahe Synchronisation eines Data Warehouse kann mit CDC umgesetzt werden.

create table kontakte
( id       integer primary key
, name     varchar(50)
, adresse  varchar(500)
);
go

insert into kontakte
values (1,'Jordan','Taunusstraße')
     , (2,'Meier' ,'Bergstraße'  )
     , (3,'Müller','Schlossallee');
go

exec sp_cdc_enable_table
  @source_schema = 'dbo', 
  @source_name = 'kontakte',
  @role_name = 'cdc_admin';

insert into kontakte
values (4,'Schulze','Meisenweg');

update kontakte
   set adresse = 'Birkenallee'
 where name = 'Jordan';
 
delete kontakte
 where name = 'Meier';

update kontakte
   set id = 33
 where id = 3;

Denkbar ist auch die Einführung eines nachgelagerten Prozesses bei Datenänderungen, ohne die eigentliche Applikation ändern zu müssen, wie z. B. die automatische Weiterleitung einer Adressänderung an ein anderes System.

Bisher konnten diese Anforderungen meist nur mit Hilfe von Triggern umgesetzt werden. Allerdings werden Trigger synchron abgearbeitet; sie beeinträchtigen also die Performance unter Umständen erheblich.

Das Konzept von CDC

Change Data Capture geht einen anderen Weg. Denn alle Informationen über erfolgte Datenänderungen stehen nicht nur während der Verarbeitung, sondern auch noch danach im Transaktionsprotokoll zur Verfügung.

Damit ist eine asynchrone Verarbeitung möglich, bei der ein von der eigentlichen Datenänderung unabhängiger Prozess das Transaktionsprotokoll auswertet und die Änderungsinformationen in eigenen Tabellen ablegt.

Die Änderungsverfolgung wird dabei für jede einzelne Tabelle ein- oder ausgeschaltet. Auch die Änderungsinformationen stehen in separaten Tabellen zur Verfügung, um den Zugriff auf diese Daten individuell steuern zu können.

In Abbildung 1 wird das Verfahren dargestellt. Jede Datenänderung wird im Transaktionsprotokoll der Datenbank vermerkt. Ein Auftrag im SQL Server Agenten liest regelmäßig die neu in das Transaktionsprotokoll geschriebenen Einträge und bearbeitet diejenigen, die sich auf zu überwachende Tabellen beziehen.

Die Einträge werden ausgewertet und die Datenänderung wird in die zugehörige Änderungstabelle eingetragen.

Da dieser Vorgang asynchron, also unabhängig von der eigentlichen Datenänderung abläuft, wirkt er sich nicht auf die Performance der Applikation aus. Lediglich durch das Einlesen und Verarbeiten des Transaktionsprotokolls entsteht eine geringe Last auf dem Server.

Allerdings bedeutet diese asynchrone Verarbeitung auch, dass es eine gewisse Zeit dauert, bis die Datenänderungen in der Änderungstabelle abrufbar sind.

Damit die Änderungstabelle nicht grenzenlos wächst, löscht ein zweiter Auftrag im SQL Server Agenten in regelmäßigen Abständen veraltete Einträge aus der Änderungstabelle. Standardmäßig werden nur Änderungen von drei Tagen gespeichert. Dies kann jedoch in der Tabelle msdb.dbo.cdc_jobs individuell konfiguriert werden.

CDC einrichten

Kommen wir nun zur Einrichtung von CDC. In Abbildung 2 ist eine kleine Beispieltabelle mit Beispieldaten angegeben, deren Veränderung aufgezeichnet werden soll.

CDC muss zunächst für die Datenbank aktiviert werden. Dazu wird innerhalb der Datenbank der Befehl exec sp_cdc_enable_db; ausgeführt. In welchen Datenbanken CDC bereits aktiviert wurde, kann man der Spalte is_cdc_enabled in der Systemsicht sys.databases entnehmen. Anschließend ist für jede zu überwachende Tabelle einzeln die Prozedur sp_cdc_enable_table auszuführen. Der in Abbildung 3 dargestellte Befehl schaltet z. B. die Überwachung für die Tabelle dbo.kontakte ein. Die angegebene Rolle cdc_admin wird für den Zugriff auf die gesammelten Daten verwendet und automatisch angelegt, sollte sie noch nicht existieren.

In welchen Tabellen CDC bereits aktiviert wurde, kann der Spalte is_tracked_by_cdc der Systemsicht sys.tables entnommen werden.

Neue Datenbankobjekte

Mit der Einrichtung von CDC wird in der Datenbank ein neues Schema cdc mit einer ganzen Reihe von Datenbankobjekten angelegt. Zudem werden zwei neue Jobs im SQL Server Agenten angelegt.

Die wichtigsten Tabellen sind die Änderungstabellen, in denen die Datenänderungen protokolliert werden. Die Namen der Tabellen lauten cdc.<Schemaname>_<Tabellenname>_CT, also cdc.dbo_kontakte_CT in unserem Beispiel. Diese Tabellen enthalten zum einen Metadatenspalten mit Angaben zu den durchgeführten Operationen, zum anderen alle Spalten der überwachten Tabellen.

Dabei identifiziert die Metadatenspalte __$start_lsn die LSN, die der Änderung zugewiesen wurde und __$seqval die Reihenfolge der Änderungen innerhalb einer Transaktion. In der Spalte __$operation wird der mit der Änderung verbundene Vorgang aufgezeichnet: 1 = Löschung, 2 = Einfügung, 3 = Aktualisierung (Anfangsabbild) und 4 = Aktualisierung (Endabbild). Die Spalte __$update_mask ist eine variable Bitmaske mit einem definierten Bit für jede aufgezeichnete Spalte.

Bei Einträgen für Einfüge- und Löschvorgänge werden alle Bits in der Update-Maske gesetzt. Bei Einträgen für Aktualisierungsvorgänge sind dagegen nur jene Bits gesetzt, die den geänderten Spalten entsprechen. Die weiteren Spalten entsprechen den Spalten der überwachten Tabelle.

Die Namen der beiden neuen Aufträge im SQL Server Agenten lauten cdc.<DB-Name>_capture und cdc.<DB-Name>_cleanup, die Aufträge werden also für jede Datenbank separat angelegt und können daher auch separat konfiguriert werden.

Daneben existieren einige Tabellen mit generellen Informationen zur Überwachung, so enthält z. B. cdc.change_tables Informationen über die mit CDC konfigurierten Tabellen und cdc.captured_columns eine Liste der überwachten Spalten.

Änderungsdaten abfragen

Die protokollierten Datenänderungen können direkt aus der Änderungstabelle ausgelesen und weiterverarbeitet werden. Abbildung 5 zeigt den Inhalt der Änderungstabelle nach der Durchführung der in Abbildung 4 angegebenen SQL-Anweisungen.

Um die dort angegebenen Protokollfolgenummern in die zugehörigen Zeitpunkte umzurechnen, stehen einerseits die Tabelle cdc.lsn_time_mapping und andererseits die beiden darauf basierenden Funktionen sys.fn_cdc_map_time_to_lsn und sys.fn_cdc_map_lsn_to_time zur Verfügung.

Zur Anzeige der Datenänderungen kann man darüber hinaus die Tabellenwertfunktion cdc.fn_cdc_get_all_changes_<Schemaname>_<Tabellenname> nutzen, die als Parameter die untere und obere Grenze der Protokollfolgenummern sowie die Angabe, ob bei Update-Vorgängen nur die neuen oder auch die alten Daten angezeigt werden sollen, übernimmt.

Was CDC nicht kann

Generell werden nur die Datenänderungen selbst und nicht die ausgeführte SQL-Anweisung oder Informationen über den ausführenden Benutzer gespeichert. Somit ist diese Funktion nur begrenzt für Audit-Zwecke geeignet.

Darüber hinaus sollte sich die Struktur der überwachten Tabelle nicht ändern, da die erzeugten CDC-Tabellen später nicht mehr angepasst werden können. Zusätzliche Spalten werden also nicht überwacht. Die einzige Möglichkeit ist die Deaktivierung und erneute Aktivierung von Change Data Capture für diese Tabelle, was allerdings zum Verlust der bisher gesammelten Daten führt.

Fazit

Change Data Capture kann in vielen Fällen bisher notwendige Trigger ablösen und so einen performanten Zugriff auf Datenänderungen bieten. Die Einrichtung und Konfiguration ist sehr einfach durchzuführen.

Im Rahmen dieses Artikels wurden jedoch nur Teilaspekte von Change Data Capture behandelt. Sollten Sie zusätzliche Anforderungen an die Verfolgung von Änderungen haben, dann sprechen Sie uns an.

Andreas Jordan (info@ordix.de).

Seitenanfang

Seite drucken

Seite weiterempfehlen