In diesem Blog Eintrag geht es um Change Data Capture (CDC). Dieses Werkzeug im Microsoft SQL Server 2008 (verfügbar (leider) ab der Enterprise Edition) dient nicht für das Autiting von Benutzern sondern um Daten zu versionieren und so Veränderungen oder Bewegungen auswerten oder wiederherstellen zu können. Das bedeutet, jede Tabelle in der CDC aktiviert ist, wird entsprecht mit protokolliert und die Änderungen werden in einer eigenen Systemtabelle pro Objekt gespeichert. Wie man zu erst vermuten möchte, handelt es sich nicht um typische DML Trigger sondern um eine Technologie, die direkt vom Transaktionslog liest und somit sicher die Datenbewegungen registriert.

Als Fallbeispiel wird eine Buchungstabelle „überwacht“:
CREATE TABLE dbo.buchungen (
uid uniqueidentifier default newid() not null primary key,
buchungsid int not null,
buchungswert money not null,
buchungsdatum datetime not null);
GO

Über folgenden Befehl wird CDC in der Datenbank aktiviert:
EXEC sys.sp_cdc_enable_db
GO

Nun wird CDC für die Tabelle aktiviert:
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'buchungen',
@role_name     = NULL,
@supports_net_changes = 1
GO

Nun fügen wir einen Datensatz in die Buchungstabelle ein:
INSERT INTO dbo.buchungen (buchungsid, buchungswert, buchungsdatum)
VALUES (1, 200, GETDATE());
GO

Zuletzt verändern wir diesen Datensatz nochmal:
UPDATE dbo.buchungen SET buchungswert = 17300 WHERE buchungsid = 1
GO

Mit folgender Abfrage kann die Datenveränderung dargestellt werden mit den jeweiligen Statis:
SELECT
CASE __$operation
WHEN 1 THEN 'DELETED'
WHEN 2 THEN 'INSERTED'
WHEN 3 THEN 'BEFORE UPDATED'
WHEN 4 THEN 'AFTER UPDATED'
END 'Aktion', buchungsid, buchungswert, buchungsdatum FROM cdc.dbo_buchungen_CT

Ergebnis:

Weitere Informationen zu den Statis gibt es hier:
http://technet.microsoft.com/en-us/library/bb522511.aspx

Sobald CDC aktiviert wird, werden folgende Tabellen erstellt:

• cdc.change_tables
  Enthält alle CDC überwachten Tabellen
• cdc.captured_columns
  Enthält die Felder der CDC überwachten Tabellen
• cdc.dbo_buchungen_CT
  Eine solche Tabelle wird für jedes CDC Objekt erstellt und speichert die Datenänderungen
• cdc.ddl_history
  Speichert Änderungen an der CDC überwachten Tabelle
• cdc.index_columns
  Enthält die Index Felder der CDC Tabellen
• cdc.lsn_time_mapping
  Über diese Tabelle kann nachvollzogen werden, wann welche Änderung durchgeführt wurde. Dies kann anhand der LSN mit der Benutzertabelle verglichen werden.

Weitere Informationen zu den Tabellen gibt es hier: http://technet.microsoft.com/de-de/library/bb500353.aspx

Weiterhin erstellt CDC noch zwei SQL Agent Jobs:

1. cdc.CDC_capture
   Dieser Job steuert CDC für die jeweiligen konfigurierten Objekte die für CDC aktiviert worden sind
2. cdc.CDC_cleanup
   Entfernt abgelaufene, bzw. deaktivierte CDC Objekte die sonst vom Capture Job ausgeführt werden

Hinweis:
Das Wiederherstellungsmodell für CDC ist nicht relavant. CDC liest lediglich aus dem Aktiven Teil des Transaktionslog und somit wir auch das Einfache Wiederherstellungsmodell unterstützt.

Fazit:
CDC eignet sich hervorragend für Auswertungszwecke um Datenveränderungen zu speichern und darzustellen. Eine Speicherung von Benutzernamen etc. ist nicht vorgesehen und könnte max. selber durch DML Trigger hinzugeführt werden.