Datenbanken und mehr

23. November 2007

Data Change Capture

Filed under: Katmai — Urs Gehrig @ 21:24

Data Change Capture ist ein cooles neues Feature von SQL Server 2008; Änderungen an Datensätzen (INSERT, UPDATE und DELETE) können automatisch in einer Logtabelle protokolliert werden. Beim Einrichten von Data Change Capture spezifizierst du, von welchen Tabellen, welche Spalten beobachtet, respektive protokolliert werden sollen. Ab dann schreibt der SQL Server jede Mutation in eine Logtabelle; die Daten hierfür holt er dabei direkt aus dem Transaktions Logfile. Mit Hilfe speziellen UDF’s kannst du auf diese Daten elegant zugegriffen werden. Dabei legt der Server für jede zu beobachtende Tabelle einen eigenen Satz von UDF’s an.

Die Logtabellen können zum Beispiel für das Aktualisieren eines Datawarehouse nützlich zu sein. Anstelle immer die ganzen riesigen Tabellen nach Mutationen zu durchstöbern, genügt es so sich auf diese Logtabellen zu konzentrieren. SSIS kennt hierfür sogar einen eigenen Task.

Bei grossen Datenbeständen mit häufigen Mutationen können diese Logtabellen sehr schnell riesig werden. SQL Server schmeisst daher automatisch alte Datensätze fort; per Default behält er jeden Datensatz drei Tage lang; aber auch dies kannst du nach Belieben abändern. Für dieses Aufräumen legt der SQL Server eigens einen SQL Server Agent Job an.

So, genug geschrieben; erst mache ich mal mit einem Beispiel weiter:

USE AdventureWorks;
GO
 
— determine whether a database is enabled
SELECT name, is_cdc_enabled
FROM sys.databases;

— enable the database for change data capture
EXECUTE sys.sp_cdc_enable_db_change_data_capture;

— determine whether a source table has been enabled for change data capture
SELECT name, is_tracked_by_cdc
FROM sys.tables;

— create a capture instance for individual source tables
— (maximum 2 instances per table)
EXECUTE sys.sp_cdc_enable_table_change_data_capture
    @source_schema = ‚Person‘,
    @source_name = ‚Contact‘,
    @role_name = ‚cdc_admin‘,
    @supports_net_changes = 1,
    @captured_column_list = N‚ContactID, LastName, EmailAddress‘;

— make some changes
UPDATE Person.Contact
SET LastName = N‚Gehrig‘
FROM Person.Contact
WHERE ContactID = 1;

UPDATE
Person.Contact
SET LastName = N‚Meier‘
FROM Person.Contact
WHERE ContactID = 1;

UPDATE
Person.Contact
SET LastName = N‚Achong‘
FROM Person.Contact
WHERE ContactID = 1;

— querying change data
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn(‚person_contact‘);
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT *
FROM cdc.fn_cdc_get_all_changes_person_contact(@from_lsn, @to_lsn, ‚all‘);
SELECT *
FROM cdc.fn_cdc_get_net_changes_person_contact(@from_lsn, @to_lsn, ‚all‘);

— disable the database for change data capture
EXECUTE sys.sp_cdc_disable_db_change_data_capture

Die beiden letzten SELECT Statements rufen mit Hilfe der speziellen UDF’s die mutierten Daten ab. Das erste Statement (all changes) liefert:

__$start_lsn           __$seqval              __$operation __$update_mask ContactID LastName EmailAddress
0x0000002D000016E80016 0x0000002D000016E80014 4            0x02           1         Gehrig   gustavo0@adventure-works.com
0x0000002D000016F80004 0x0000002D000016F80002 4            0x02           1         Meier    gustavo0@adventure-works.com
0x0000002D000017080004 0x0000002D000017080002 4            0x02           1         Achong   gustavo0@adventure-works.com

Für jede Mutation gibt es also genau einen Eintrag. Die ersten beiden Spalten beschreiben den zeitlichen Ablauf der Updates und kommen aus dem Transaktionslog raus. Eine $operation von 4 beschreibt einen Update Befehl und die $update_mask 0x2 gibt an, dass nur das zweite Feld, welches beobachtet wird (also LastName) mutiert wurde. Am Schluss werden dann noch die aktuellen, d.h. nach dem Commit der Transaktion gültigen, Werte aller Felder aufgeführt. Cool oder? Wenn du in deiner Tabelle auch noch Spalten für Benutzer, PC-Name etc. einfügst, dann hast du ein vollständiges Security-Log.

Das zweite Statement (net changes) liefert:

__$start_lsn           __$operation __$update_mask ContactID LastName EmailAddress
0x0000002D000017080004 4            NULL           1         Achong   gustavo0@adventure-works.com

Jetzt bekommst du nur noch eine Zeile; nämlich den Zusammenzug aller Änderungen pro Datensatz. Die $operation ist wiederum ein Update Befehl, die $update_mask aber NULL. Warum? Ganz einfach. Der LastName vom letzten Update (Achong) ist nämlich der Wert, der zu Begin schon drin stand. Somit hat der Datensatz unter dem Strich keine Änderung erfahren; daher der Wert NULL.

So, das war’s für heute. Viel Spass beim ausprobieren.

Advertisements

Schreibe einen Kommentar »

Es gibt noch keine Kommentare.

RSS feed for comments on this post. TrackBack URI

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

Bloggen auf WordPress.com.

%d Bloggern gefällt das: