SQL Server 2008 (2): Änderungsverfolgung in Tabellen - Change Data Capture
Eine der häufigsten Anforderungen an Datenbankdesigns ist die Möglichkeit herauszufinden, was sich im an den Daten einer Tabelle in einen gegebenen Zeitraum verändert hat. Anwendungsfälle dafür sind Datensynchronisation zwischen verschiedenen Anwendungen, Stammdatenpflege in einem Data Warehouse oder auch bestimmte Arten von Auditing. Bisher gab es dafür vor allem 2 Methoden:
Trigger: Auf die zu überwachende Tabelle wird ein Trigger gelegt, der bei Änderungsoperationen eine Änderungstabelle pflegt. Vorteil: Sehr flexibel. Nachteil: Kostet erheblich Performance und ist komplex zu implementieren.
Anwendungslogik: Die Anwendungslogik implementiert eine Änderungsverfolgung (im Middle Tier Code oder in Stored Procedures). Vorteil: Auch flexibel. Nachteil: Geht nur, wenn alle schreibenden Zugriffe über die Anwendung erfolgen.
Change Data Capture (CDC) ist eine neue Methode, um eine Änderungsverfolgung zu implementieren. Der wichtige Unterschied besteht darin, dass CDC die Tabellen für die Änderungsverfolgung nicht synchron während eines schreibenden Vorgangs (INSERT, UPDATE, DELETE, MERGE) pflegt sondern asynchron aus den Log Records. Das bedeutet, dass die schreibenden Operationen mit voller Geschwindigkeit laufen und auch nicht durch Fehler beim Pflegen der Änderungsverfolgung fehlschlagen können. Die Änderungsverfolgung liest im Hintergrund das Log und pflegt daraus die Tabellen zur Änderungsverfolgung. Diese Tabellen werden bei der Einrichtung von CDC automatisch erzeugt. Allerdings sollten die Change-Tabellen nicht direkt von der Anwendung verwendet werden. Stattdessen gibt es einen Satz Funktionen zur Abfrage der Änderungen in einem bestimmten Zeitraum. Grafisch sieht das so aus:
Wie richtet man CDC ein? Zuerst muss ein Sysadmin CDC für eine Datenbank aktivieren, was eine Reihe von Objekten im Schema cdc anlegt:
EXECUTE sys.sp_cdc_enable_db_change_data_capture
Mit select [name], is_cdc_enabled from sys.databases kann man herausfinden, ob das erfolgreich war.
Als nächstes wird CDC für die Tabellen aktiviert, deren Änderungen man verfolgen will. Das kann beim ersten Mal lange dauern!
EXECUTE sys.sp_cdc_enable_table_change_data_capture
@source_schema = N'dbo'
, @source_name = N'Products'
, @role_name = N'cdc_admin'
, @supports_net_changes = 1;
Dabei hat man die Möglichkeit, eine Vielzahl von Einstellungen anzugeben. So kann man spezifizieren, welche Datenbankrolle für die Aufzeichnung der Änderungen verwendet werden soll, welche Spalten auf Änderungen überwacht werden sollen oder in welcher Dateigruppe die Change-Tabellen erzeugt werden sollen. Wichtig ist vor allem, ob man Netto-Änderungen (siehe unten) unterstützt haben möchte.
Nach schreibenden Befehlen kann man jetzt abfragen, welche Änderungen es in einem bestimmten Zeitraum gegeben hat. Der Zeitraum wird über Log Sequence Numbers (LSNs) identifiziert. Das sind einfach Zahlen, die alle Log Records streng monoton steigend identifizieren. In einer realen Anwendung merkt man sich am besten, welche LSN man zuletzt verarbeitet hat und lässt sich beim nächsten Mal dann alle Änderungen seit dieser LSN ausgeben. Hier wollen wir aber erst mal alle Änderungen sehen:
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Products');
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Products(@from_lsn, @to_lsn, 'all');0x0000001A0000010C0006 0x0000001A0000010C0002 4 0x02 1 Office 2007
0x0000001A0000010C0006 0x0000001A0000010C0004 4 0x02 2 SQL Server 2008
0x0000001A0000010E0003 0x0000001A0000010E0002 2 0x03 4 Office SharePoint Server 2007
0x0000001A0000015D0004 0x0000001A0000015D0002 4 0x02 1 2007 Office System
Konfigurationsinformationen und den Capture-Instanznamen, den man für die Abfragefunktionen braucht bekommt man mit sys.sp_cdc_help_change_data_capture.
Die LSN für die nächste Abfrage erhält man dann mit sys.fn_cdc_increment_lsn
Man kann sich auch die Änderungen in einem bestimmten Zeitraum ausgeben lassen, indem man sich die LSNs für den entsprechenden Zeitpunkt geben lässt:
DECLARE @from_lsn binary(10), @to_lsn binary(10);
DECLARE @to_time datetime, @from_time datetime;
SET @from_time = '2007-08-03 16:00:00';
SET @to_time = '2007-08-03 17:15:00';
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @from_time);
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @to_time);select @from_time, @from_lsn, @to_time, @to_lsn
SELECT * from cdc.fn_cdc_get_all_changes_dbo_Products(@from_lsn, @to_lsn, 'all');
Bisher waren das Bruttoänderungen, das heißt, wenn ein bestimmter Eintrag mehrfach geändert wurde, so zeigt die Änderungsverfolgung das auch mehrfach an. Daran kann man übrigens auch sehen, dass SQL Server einige Update-Vorgänge (z.B. des Primary Key) als Kombination aus Delete und Insert implementiert.
In vielen Fällen möchte man aber Nettoänderungen haben. Dafür gibt es cdc.fn_cdc_get_net_changes_...
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Products');
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Products(@from_lsn, @to_lsn, 'all');0x0000001A0000010C0006 4 NULL 2 SQL Server 2008
0x0000001A0000010E0003 2 NULL 4 Office SharePoint Server 2007
0x0000001A0000015D0004 4 NULL 1 2007 Office System
Wie man hier sieht sind die beiden Änderungen von Eintrag 1 zu einem Ergebnis zusammengefasst.
In Summe bietet Change Data Capture eine performante und mächtige Methode, um Änderungen an Tabellen nachzuverfolgen. Anbei wieder ein
Projekt zur Verwendung mit der Juli-CTP von SQL Server 2008
Gruß,
Steffen