SQL Server 2008

Nachverfolgen von Änderungen in Ihrer Unternehmensdatenbank

Paul S. Randal

Auf einen Blick:

• Die Notwendigkeit zum Nachverfolgen von Änderungen
• Nachverfolgen von Änderungen in SQL Server 2005
• Änderungsnachverfolgung in SQL Server 2008
• Erfassung von Änderungsdaten in SQL Server 2008

Inhalt

Nachverfolgen von Änderungen in SQL Server 2005
Einfachere Möglichkeiten zum Nachverfolgen von Änderungen in SQL Server 2008
Funktionsweise der Änderungsdatenerfassung
Funktionsweise der Änderungsnachverfolgung
Zusammenfassung

Ein schwieriges Problem besteht für Entwickler in SQL Server darin nachzuverfolgen, welche Daten sich in einer Datenbank geändert haben. Eine noch größere Herausforderung ist die Entwicklung einer einfachen Lösung, die sich nur unwesentlich auf die Arbeitsauslastungsleistung auswirkt und sich problemlos erstellen, implementieren und verwalten lässt. Also warum sich überhaupt die Mühe machen, Änderungen nachzuverfolgen? Ist das Nachverfolgen von Änderungen wirklich all diese Anstrengungen wert? Zwei häufig genannte Beispiele sind die Unterstützung von Aktualisierungen eines Data Warehouse und die Unterstützung der Synchronisierung heterogener Systeme, zu denen keine dauerhafte Verbindung besteht.

Ein Data Warehouse verfügt in der Regel über eine bestimmte Repräsentation der Tabellen in der OLTP-Datenbank (Online Transaction Processing, Onlinetransaktionsverarbeitung), die Tabellenschemas können sich aber stark unterscheiden. Dies bedeutet, dass ein ETL-Prozess (Extrahieren, Transformieren und Laden) vorhanden sein muss, durch den Daten aus der OLTP-Datenbank in das Data Warehouse verschoben werden.

Mir fallen hierfür drei Möglichkeiten ein. Die erste besteht darin, das gesamte Data Warehouse regelmäßig zu aktualisieren. Dies ist offensichtlich für große Datenmengen unpraktisch und bedeutet zudem, dass das Data Warehouse nicht kontinuierlich aktualisiert wird. Die zweite Methode besteht darin, ein Partitionierungsschema in der OLTP-Datenbank zu verwenden, damit vom ETL-Prozess nur jene Daten bearbeitet werden, die seit dem vorhergehenden ETL-Prozess neu hinzugekommen sind. Diese Methode funktioniert nur für Dateneinfügungen und nicht für Aktualisierungs- oder Löschvorgänge und erfordert einen komplexen Mechanismus für die Definition der Partitionsbegrenzungen und den Partitionswechsel. Die dritte Methode besteht darin, Änderungen der OLTP-Daten nachzuverfolgen und den ETL-Prozess nur unter Verwendung der geänderten Daten durchzuführen. Dies stellt in Bezug auf die Datenmenge die effizienteste Methode dar.

In der heutigen Geschäftsumgebung sind mobile Geräte allgegenwärtig, was bedeutet, dass gelegentlich verbundene Systeme berücksichtigt werden müssen. In Bezug auf Datenbanksysteme besteht das Problem darin, wie ein Datenspeicher auf einem Gerät, das nicht häufig eine Verbindung herstellt, effizient aktualisiert werden kann – insbesondere wenn der Datenspeicher möglicherweise klein ist und sich sein Schema fundamental von dem der Hauptdatenbank unterscheidet.

Ein Beispiel ist ein Vertriebsmitarbeiter im Außendienst, der für einen Teil eines sehr großen Produktkatalogs verantwortlich ist. Jeden Abend stellt er von seinem tragbaren Gerät eine Verbindung zur Hauptdatenbank her, um die neuesten Daten herunterzuladen – alle Änderungen an diesem Teil des Produktkatalogs, die für die Speicherung auf einem tragbaren Gerät vereinfacht wurden. Die Datenübertragung sollte so effizient wie möglich erfolgen.

Sie könnten jetzt das Datenbanksystem den gesamten relevanten Teil des Produktkatalogs für das Herunterladen auf das Gerät vorbereiten und das Gerät ihn herunterladen lassen. Mit anderen Worten: Wenn das Gerät eine Verbindung herstellt, werden immer alle Daten heruntergeladen, selbst wenn sich die Daten nicht geändert haben. Dies ist offensichtlich ein ziemlich ineffizienter Ansatz.

Ein anderer Ansatz besteht darin, das Datenbanksystem Änderungen am relevanten Teil des Produktkatalogs nachverfolgen zu lassen. Wenn das tragbare Gerät dann eine Verbindung herstellt, fragt es nach den Daten, die sich seit der letzten Verbindung geändert haben. Bei dieser Lösung muss das Datenbanksystem nur eine Teilmenge der Daten vorbereiten, und das Herunterladen erfolgt so effizient wie möglich.

Ein weiterer Grund für das Nachverfolgen von Änderungen ist die Unterstützung der Überwachung, die heutzutage eine wesentliche Rolle spielt. Bei der Überwachung werden die durchgeführten Änderungen nachverfolgt, wann die Änderung erfolgte und wer sie vorgenommen hat. Dadurch eröffnet sich wirklich eine ganz neue Dimension – mit strengen Einschränkungen, die auf die Dauerhaftigkeit, Sicherheit und Korrektheit eines vollständigen Überwachungspfads abzielen.

Die Technologien, die für das Nachverfolgen von Datenänderungen in SQL Server 2008 entwickelt wurden, sind nicht dafür vorgesehen, die Überwachung zu unterstützen. SQL Server 2008 bietet jedoch ein neues Feature namens „SQL Server Audit“, das speziell für die Überwachung entwickelt wurde. Rick Byham hat das Feature „SQL Server Audit“ in seinem Artikel „SQL Server 2008: Sicherheit“ in der Ausgabe des TechNet Magazins vom April 2008 (verfügbar unter technet.microsoft.com/magazine/cc434691) vorgestellt.

Wie Sie sehen können, gibt es einige zwingende Gründe, die dafür sprechen, Änderungen an Ihren Daten nachzuverfolgen. Daraus ergibt sich die wichtige Frage, wie die Nachverfolgung am besten durchgeführt werden kann.

Nachverfolgen von Änderungen in SQL Server 2005

Bei SQL Server 2005 (und früheren Versionen von SQL Server) gibt es keine einfache vorkonfigurierte Lösung. Daher mussten Entwickler für diese Plattformen benutzerdefinierte Lösungen für ihre Anwendungen erstellen, die in der Regel timestamp-Spalten, DML-Trigger (Data Manipulation Language, Datenbearbeitungssprache) und zusätzliche Tabellen umfassten. Diese Lösungen werfen jedoch verschiedene potenzielle Probleme auf. Beispiel:

• Das Hinzufügen von timestamp-Spalten führt zu einer Änderung des Tabellenschemas (mit möglichen Nebenwirkungen in gespeicherten Prozeduren und anderem Code).
• Ein DML-Trigger ist implizit Bestandteil der Transaktion, die die DML enthält, durch die er ausgelöst wird. Daher erhöht seine Ausführungszeit die Länge der Transaktion. Je komplexer ein Trigger ist, desto länger dauert seine Ausführung und umso höher sind die negativen Auswirkungen auf die Arbeitsauslastungsleistung. DML-Trigger, die zum Nachverfolgen von Änderungen verwendet werden, müssen die eingefügten und gelöschten Tabellen verarbeiten, um alle Änderungen zu erfassen, und sie dann in eine andere Nachverfolgungstabelle einfügen.
• Die Nachverfolgungstabelle muss in einer bestimmten Weise verwaltet werden, damit ihre Größenzunahme nicht außer Kontrolle gerät. Dies kann erfordern, dass Sie beispielsweise einen Agentauftrag erstellen, um regelmäßig alte Daten zu bereinigen.

Einfachere Möglichkeiten zum Nachverfolgen von Änderungen in SQL Server 2008

Mit SQL Server 2008 werden zwei neue Technologien eingeführt, mit denen sich Änderungen an Daten viel einfacher nachverfolgen lassen: Änderungsnachverfolgung und Änderungsdatenerfassung. Beide Features verfolgen Daten nach, die sich geändert haben (und verwenden die Einfüge-, Aktualisierungs- oder Löschvorgänge, um genau nachzuverfolgen, wie die Daten geändert wurden) und machen benutzerdefinierte Lösungen überflüssig. Von diesen Ähnlichkeiten abgesehen, unterscheiden sich ihre Mechanismen und die von ihnen nachverfolgten Elemente jedoch recht deutlich.

Die Änderungsdatenerfassung verwendet einen asynchronen Mechanismus, der alle Änderungen nachverfolgt, die an einer Tabelle (oder an einem definierten Satz von Spalten der Tabelle) vorgenommen werden, einschließlich der Spaltenwerte selbst. Dies ist für Szenarios wie den oben beschriebenen ETL-Prozess für ein Data Warehouse vorgesehen.

Abbildung 1 zeigt Änderungsdaten, die in Zeitscheiben verarbeitet werden. Der Mechanismus zur Erfassung von Änderungsdaten extrahiert die geänderten Daten in einen Satz von Tabellen, wobei sich die neuesten Änderungen am Anfang der Tabelle befinden. Der ETL-Prozess kann dann die Tabellen abfragen, die die Änderungsdaten für alle Änderungen enthalten, die innerhalb eines festgelegten Zeitraums vorgenommen wurden. Dieser Mechanismus ermöglicht dem ETL-Prozess, die Menge der Daten zu begrenzen, die in den einzelnen Batches verarbeitet werden müssen.

Abbildung 1 Verlaufsänderungsdaten, die in Zeitscheiben verarbeitet werden (zum Vergrößern auf das Bild klicken)

Bei der Änderungsnachverfolgung wird hingegen ein synchroner Mechanismus verwendet, der nur nachverfolgt, ob sich eine bestimmte Zeile in einer Tabelle geändert hat (und optional die Liste der Spalten, die sich geändert haben). Dies ist für die Lösung von Problemen wie das oben beschriebene Szenario mit gelegentlich verbundenen Systemen vorgesehen. Dieser Ansatz wird in Abbildung 2 gezeigt.

Abbildung 2 Gelegentlich verbundenes System, bei dem Änderungsnachverfolgungsdaten verwendet werden (zum Vergrößern auf das Bild klicken)

Beide Features führen zu einer Zunahme der E/A- und Protokollierungsvorgänge, aber dies trifft auch auf benutzerdefinierte Lösungen zu – die Änderungsdaten müssen schließlich irgendwo gespeichert werden. Der potenzielle Unterschied zwischen diesen beiden Features und einer benutzerdefinierten Lösung besteht darin, dass die Tabellen, die zum Speichern der Änderungsdaten verwendet werden, sich in derselben Datenbank wie die nachverfolgten Tabellen befinden müssen. Dies bedeutet, dass alle Änderungsdaten in Sicherungen enthalten sind und möglicherweise durch Protokollversand oder Datenbankspiegelung über das Netzwerk übertragen werden.

In Bezug auf die Entwicklung sollten diese beiden Features die Komplexität des Nachverfolgens von Änderungen wesentlich verringern. Für keine der beiden Technologien sind Tabellenschemaänderungen oder Trigger erforderlich. Beide Technologien verfügen über konfigurierbare, automatische Bereinigungsprozesse, sortieren Änderungen nach dem Commitzeitpunkt von Transaktionen und bieten integrierte Funktionen für das Abrufen von Änderungsinformationen.

Aus der Verwaltungsperspektive bietet jeder dieser Ansätze Vor- und Nachteile. Wie bei jeder Technologie gibt es zahlreiche Informationen, die Sie verstehen müssen, bevor Sie Lösungen, in denen diese Features verwendet werden, entwickeln und bereitstellen können. Im weiteren Verlauf dieses Artikels wird jedes dieser Features im Überblick dargestellt, wobei die Funktionsweise und die praktischen Aspekte erläutert werden, die vor der Verwendung in der Produktion zu berücksichtigen sind.

Funktionsweise der Änderungsdatenerfassung

Die Änderungsdatenerfassung führt im Rahmen der Transaktionen, durch die die nachverfolgte Tabelle geändert wird, keine Aktionen durch. Stattdessen werden die Einfüge-, Aktualisierungs- und Löschvorgänge normal in das Transaktionsprotokoll geschrieben und regelmäßig aus dem Protokoll erfasst. Die Erfassung wird durch einen Protokollleseauftrag des SQL Server-Agent durchgeführt, und die erfassten Vorgänge werden in einer separaten Tabelle gespeichert, die als Änderungstabelle bezeichnet wird. Später kann die Änderungstabelle mit einer von zwei Funktionen abgefragt werden, um die Änderungsdaten abzurufen. Die Kombination aus der Änderungstabelle und den beiden Funktionen wird als Erfassungsinstanz bezeichnet. Abbildung 3 zeigt den Fluss der Daten, wenn Änderungsdatenerfassung zur Steuerung eines ETL-Prozesses für ein Data Warehouse verwendet wird.

Die Aktivierung der Änderungsdatenerfassung ist ein zweistufiger Prozess. Zuerst muss ein Mitglied der festen Serverrolle „sysadmin“ die Änderungsdatenerfassung mit sys.sp_cdc_enable_db für die Datenbank aktivieren. Dann muss ein Mitglied der festen Serverrolle „db_owner“ die Änderungsdatenerfassung mit sys.sp_cdc_enable_table für eine bestimmte Tabelle aktivieren. Diese Sicherheitsanforderungen beruhen auf einer möglicherweise starken Datenträgerverwendung, wenn die Änderungsdatenerfassung falsch konfiguriert ist. Es ist sinnvoll, dass ein Tabellenbesitzer das Feature nicht aktivieren und einen Datenbankadministrator nicht durch zusätzliche Datenträgerverwendung überraschen kann.

Wenn die Änderungsdatenerfassung für eine Datenbank aktiviert wird, werden der Datenbank einige Elemente hinzugefügt, einschließlich eines neuen Schemas (namens „cdc“), einiger Metadatentabellen und eines Triggers zur Erfassung von DDL-Ereignissen (Data Definition Language, Datendefinitionssprache). (Eine Funktion, die ich für praktisch halte, besteht darin, dass Sie eine Liste der an einer Tabelle vorgenommenen DDL-Änderungen abrufen können.)

Durch Aktivieren der Änderungsdatenerfassung wird zudem die Erfassungsinstanz für die Tabelle erstellt – die Änderungstabelle und bis zu zwei Funktionen für die Rückgabe von Änderungsdaten. Der Name der Änderungstabelle stimmt mit dem der Erfassungsinstanz überein, wobei jedoch „_CT“ angefügt wird. Die erste Funktion wird immer erstellt und zur Rückgabe von Änderungsdaten aus der Änderungstabelle verwendet. Die zweite Funktion wird erstellt, wenn die Option zum Zulassen von Nettoänderungen angegeben wird. Dies bedeutet, dass anstelle der Zwischenänderungen, die von der ersten Funktion zurückgegeben werden, nur das Endergebnis aller aufgezeichneten Änderungen zurückgegeben wird. Die Namen der beiden Funktionen lauten „fn_cdc_get_all_changes_“ und „fn_cdc_get_net_changes_“, wobei jeweils der Name der Erfassungsinstanz angefügt wird. Beachten Sie, dass diese Funktionalität (wie das Feature zur Änderungsnachverfolgung) es erfordert, dass die Tabelle über einen Primärschlüssel oder einen anderen eindeutigen Index verfügt.

Für die erste Tabelle in der Datenbank, für die die Änderungsdatenerfassung aktiviert wurde, können zwei Aufträge des SQL Server-Agent erstellt werden: der Erfassungsauftrag und der Bereinigungsauftrag. Ich sage „können erstellt werden“, weil der Erfassungsauftrag mit dem Auftrag identisch ist, der bei der Transaktionsreplikation zum Erfassen von Transaktionen verwendet wird. Wenn die Transaktionsreplikation bereits konfiguriert ist, wird nur der Bereinigungsauftrag erstellt, und der vorhandene Protokollleseauftrag wird gleichzeitig als Erfassungsauftrag verwendet. Dies ist sinnvoll, da das Vorhandensein von zwei Protokollleseaufträgen sehr schnell zu Konflikten mit dem Protokoll und somit zu Leistungsproblemen führen würde. In jedem Fall muss der SQL Server-Agent ausgeführt werden, wenn Sie die Änderungsdatenerfassung verwenden möchten.

Die Logik im Protokollleser trägt automatisch Tabellen Rechnung, die für die Änderungsdatenerfassung aktiviert und deaktiviert werden, und sie ändert die aus dem Transaktionsprotokoll erfassten Informationen entsprechend. Als wichtiger Punkt ist hierbei zu beachten, dass sich das Transaktionsprotokoll nach der Aktivierung der Änderungsdatenerfassung genau so wie bei der Transaktionsreplikation verhält – das Protokoll kann erst abgeschnitten werden, nachdem es vom Protokollleser verarbeitet wurde. Das bedeutet, dass das Protokoll selbst im einfachen Wiederherstellungsmodus nur dann durch einen Prüfpunktvorgang abgeschnitten wird, wenn es bereits vom Protokollleser verarbeitet wurde.

Wenn das Modell der massenprotokollierten Wiederherstellung zur Verringerung der Protokolleinträge verwendet wird, erzwingt die Änderungsdatenerfassung zudem die vollständige Protokollierung aller Vorgänge – außer Vorgängen zum Erstellen/Löschen/erneuten Erstellen von Indizes. Wenn Ihnen ein derartiges Verhalten neu ist, beachten Sie, dass dadurch Probleme mit der Größe des Transaktionsprotokolls verursacht werden können, insbesondere wenn die Standardeinstellungen für den Erfassungsauftrag geändert werden, damit das Protokoll weniger häufig verarbeitet wird.

Standardmäßig wird der Erfassungsauftrag kontinuierlich ausgeführt, wobei das Protokoll alle fünf Sekunden durchsucht wird und maximal 500 Transaktionen aus dem Protokoll verarbeitet werden. Der Bereinigungsauftrag wird ebenfalls standardmäßig täglich um 2 Uhr morgens ausgeführt, wobei alle Änderungsdateneinträge, die älter als drei Tage sind, aus den Änderungstabellen entfernt werden. Sie können diese Einstellungen mithilfe der Prozedur „sys.sp_cdc_change_job“ ändern. Dann treten die Änderungen erst in Kraft, wenn Sie die Aufträge mit sys.sp_cdc_stop_job und sys.sp_cdc_start_job neu starten.

Obwohl der Protokollleseprozess in der Regel geringe Auswirkungen auf die Systemleistung hat, ist es auf stark ausgelasteten OLTP-Systemen mit einer großen Menge von sich ändernden Daten möglich, dass das Hinzufügen von nur einem Protokollleseprozess Konflikte in Bezug auf das Transaktionsprotokoll verursacht. Der eigentliche Konflikt wird dadurch verursacht, dass sich die Datenträgerköpfe zwischen dem Punkt, an dem durch Transaktionen in das Protokoll geschrieben wird, und dem Punkt, an dem es vom Protokollleseprozess gelesen wird, hin und her bewegen müssen. Um sicherzustellen, dass die OLTP-Leistung nicht beeinträchtigt wird, kann es in diesem Fall notwendig sein, die Häufigkeit zu ändern, mit der der Erfassungsauftrag ausgeführt wird. Dies führt jedoch zu einem klassischen Kompromiss zwischen Speicherplatz und Leistung – die Größe des Protokolls nimmt weiter zu, bis es vom Erfassungsauftrag verarbeitet wird.

Das gleiche Problem tritt auf, wenn die Ausführungshäufigkeit des Bereinigungsauftrags oder die Beibehaltungsdauer von Änderungsdaten geändert wird – die Größe der Änderungstabellen nimmt weiter zu, bis die Änderungsdaten bereinigt werden. Dies führt zu einer allgemeinen Überlegung beim Entwurf, welche Änderungen nachverfolgt und wie lange die Änderungsdaten aufbewahrt werden. Hierbei sind die folgenden wichtigen Aspekte zu berücksichtigen:

• Die erforderliche Spaltenliste für die Erfassungsinstanz. Wenn mehr Spalten erfasst werden, werden mehr Änderungsdaten in die Änderungstabellen eingefügt.
• Die Größe des von den Änderungstabellen belegten Speicherplatzes.
• Die Häufigkeit, mit der der Prozess ausgeführt wird, der die Änderungsdaten verarbeitet. Beachten Sie, dass die Daten nicht gelöscht werden können, wenn sie noch nicht verwendet wurden.
• Die Häufigkeit, mit der der Bereinigungsprozess ausgeführt wird – es können so viele Änderungsdaten generiert werden, dass der Bereinigungsprozess, der sie löscht, beispielsweise nur am Wochenende ausgeführt werden kann, da er andernfalls zu viele Transaktionsprotokolleinträge erzeugt.

Die Änderungsdatenerfassung kann so eingerichtet werden, dass einfach alle Änderungen an einer Tabelle nachverfolgt werden oder eine Teilmenge der Spalten in einer Tabelle nachverfolgt wird. Die Verwendung einer Teilmenge kann nützlich sein, wenn einige der unwichtigen Spalten sehr breite varchar-Spalten oder BLOB-Spalten (Binary Large Object) sind (beispielsweise Text-, Bild- oder XML-Spalten). Andernfalls kann der von der Änderungstabelle belegte Speicherplatz sehr schnell unhandlich werden.

Aufgrund des erhöhten Potenzials für Speicherplatznutzung kann beim Aktivieren der Änderungsdatenerfassung der Dateigruppen-Speicherort der Änderungstabelle festgelegt werden. Dies ermöglicht die einfachere Verwaltung des zugrunde liegenden Speicherplatzes und bedeutet, dass alle Änderungsdaten möglicherweise auf einem Volume mit einer kostengünstigeren RAID-Stufe als Hauptdatenbank gespeichert werden können. Obwohl die Einstellungen für den Bereinigungsauftrag für alle Erfassungsinstanzen gelten, kann zudem eine einzelne Erfassungsinstanz jederzeit separat bereinigt werden, wenn der Speicherplatz zu einem Problem wird. Sie können die Speicherplatznutzung mühelos überwachen, indem Sie sp_spaceused für die Erfassungstabellen verwenden.

Die konkrete Zeile, die in die Änderungstabelle geschrieben wird, enthält Metadaten zur Transaktion – die Commit-Protokollfolgenummer (Log Sequence Number, LSN) – sowie die Reihenfolge innerhalb der Transaktion, in der die Änderung stattgefunden hat, den Typ des durchgeführten Vorgangs, eine Bitmaske, die angibt, welche Spalten geändert wurden, und die eigentlichen Spaltenwerte.

Während die Änderungsdatenerfassung aktiviert ist, unterliegen DDL-Änderungen keinerlei Einschränkungen. Sie können jedoch bestimmte Auswirkungen auf die gesammelten Änderungsdaten haben, wenn Spalten hinzugefügt oder gelöscht werden. Wenn eine nachverfolgte Spalte gelöscht wird, enthalten alle weiteren Einträge in der Erfassungsinstanz für diese Spalte einen NULL-Wert. Wenn eine Spalte hinzugefügt wird, wird sie von der Erfassungsinstanz ignoriert. Anders gesagt: Die Form der Erfassungsinstanz wird bei ihrer Erstellung festgelegt.

Wenn Spaltenänderungen erforderlich sind, ist es möglich, eine weitere Erfassungsinstanz für eine Tabelle zu erstellen (wobei maximal zwei Erfassungsinstanzen pro Tabelle zulässig sind) und Nutzern der Änderungsdaten die Migration zum neuen Tabellenschema zu ermöglichen. Hierbei sollte aber mit Umsicht vorgegangen werden, da zwei Erfassungsinstanzen für eine nachverfolgte Tabelle eine Verdopplung des belegten Speicherplatzes, der E/A-Vorgänge und der Protokolleinträge nach sich ziehen.

Ohne zu sehr ins Detail zu gehen, sei hier nur gesagt, dass Änderungen mithilfe der oben beschriebenen Funktionen aus den Änderungstabellen abgerufen werden. Die Funktionen verwenden eine Start-LSN und eine End-LSN, und es werden weitere Funktionen bereitgestellt, mit denen Sie eine normale Zeitangabe in eine LSN konvertieren können. Beim Abrufen von Aktualisierungen können Sie sogar angeben, ob die alten und die neuen Werte oder nur die alten Werte angezeigt werden sollen. Unter www.technetmagazine.com/video ist ein Screencast verfügbar, in dem ich die Verwendung der Änderungsdatenerfassung zeige.

Funktionsweise der Änderungsnachverfolgung

Wie bereits erwähnt, ist die Änderungsnachverfolgung ein synchroner Prozess und viel weniger komplex als die Änderungsdatenerfassung. Sie ist Bestandteil der Transaktion, durch die eine Änderung an einer Zeile in einer nachverfolgten Tabelle vorgenommen wird, und die Tatsache, dass sich die Zeile geändert hat, wird in einer separaten Tabelle nachverfolgt. Die Tabelle ist eine so genannte interne Tabelle, und es besteht keine Kontrolle über ihren Namen oder Speicherort. Ich betrachte dies nicht als Problem, da in dieser Tabelle viel weniger Daten vorhanden sein sollten als in einer für die Änderungsdatenerfassung verwendeten Änderungstabelle. Es können aber immer noch Speicherplatzprobleme auftreten, worauf ich in Kürze eingehen werde.

Die Tatsache, dass die Änderungsnachverfolgung synchron erfolgt, bedeutet, dass innerhalb jeder Transaktion, durch die die nachverfolgte Tabelle geändert wird, eine zusätzliche Verarbeitung stattfindet. Die Auswirkung auf die Leistung ist mit der vergleichbar, die sich ergibt, wenn ein nicht gruppierter Index für die Tabelle vorhanden ist und bei jeder Änderung der Tabelle aktualisiert werden muss. Transaktionen selbst werden, wenn für sie ein Commit ausgeführt wird, durch eine Zeile in der internen Tabelle „sys.syscommittab“ ebenfalls nachverfolgt.

Die Änderungsnachverfolgung wird mit der normalen ALTER DATABASE- und ALTER TABLE-Syntax aktiviert und deaktiviert. Dabei folgt sie demselben Modell wie die Änderungsdatenerfassung, d. h., sie muss zuerst auf Datenbankebene und dann auf Tabellenebene aktiviert werden. Die Abfolge der Vorgänge würde etwa so aussehen:

ALTER DATABASE AdventureWorks2000 SET CHANGE_TRACKING = ON
  (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO
USE AdventureWorks2000;
GO
ALTER TABLE Person.Person ENABLE CHANGE_TRACKING
  WITH (TRACK_COLUMNS_UPDATED = ON);
GO

Die erforderlichen Berechtigungen zum Aktivieren der Änderungsnachverfolgung auf der Datenbank- und Tabellenebene unterscheiden sich ebenfalls von denen für die Aktivierung der Änderungsdatenerfassung: db_owner bzw. Tabellenbesitzer. Wenn die Änderungsnachverfolgung auf der Datenbankebene aktiviert wird, können die Beibehaltungsdauer sowie die automatische Bereinigung der Änderungsdaten festgelegt werden. Die Beibehaltungsdauer beträgt standardmäßig 2 Tage, wobei das Maximum bei 90 Tagen und das Minimum bei einer Minute liegt.

Die automatische Bereinigung ist ebenfalls standardmäßig aktiviert. Wenn Sie diese Einstellungen ändern, müssen Sie dieselben Kompromisse wie bei der Änderungsdatenerfassung abwägen – d. h. Speicherplatz und Leistung auf der einen Seite und Anwendungsanforderungen auf der anderen.

Standardmäßig wird für jede Zeile nur erfasst, dass sie sich geändert hat. Dies erfolgt durch Vermerken des Primärschlüssels der Zeile, die sich geändert hat (was bedeutet, dass die Änderungsnachverfolgung für eine Tabelle erfordert, dass diese über einen Primärschlüssel verfügt). Außerdem werden eine Versionsnummer (nachdem für eine Datenbank die Änderungsnachverfolgung aktiviert wurde, wird eine Versionsnummer eingeführt, die das Sortieren der Vorgänge ermöglicht) und die Art des Vorgangs, durch den die Änderung erfolgte, vermerkt. Sie können optional auch nachverfolgen, welche Spalten sich geändert haben. Dies erfordert 4 Byte pro geänderte Spalte.

Bei der Änderungsnachverfolgung erfolgt die Speicherplatzüberwachung auf etwas andere Weise, da die Änderungsdaten in internen Tabellen gespeichert werden. Um nach den Namen der verwendeten internen Tabellen zu suchen, verwenden Sie einfach die Systemkatalogsicht „sys.internal_tables“:

SELECT [name] FROM sys.internal_tables
  WHERE [internal_type_desc] = 'CHANGE_TRACKING';
GO

Übergeben Sie dann den Namen an sp_spaceused, um festzustellen, wie viel Speicherplatz verwendet wird.

Wenn die Änderungsnachverfolgung aktiviert ist, gelten im Unterschied zur Änderungsdatenerfassung Einschränkungen für die DDL-Vorgänge, die für eine nachverfolgte Tabelle durchgeführt werden können. Die wichtigste Einschränkung besteht darin, dass der Primärschlüssel nicht geändert werden kann. Eine weitere Einschränkung, auf die sich hinzuweisen lohnt, besteht darin, dass ALTER TABLE SWITCH fehlschlägt, wenn für eine der beteiligten Tabellen die Änderungsnachverfolgung aktiviert ist. Dies beruht höchstwahrscheinlich darauf, dass es nicht sinnvoll ist, die Änderungsnachverfolgung für eine Partition, die aus einer partitionierten Tabelle verschoben wird, für die Änderungen nachverfolgt werden, oder für eine Tabelle, für die Änderungen nachverfolgt werden und die in eine partitionierte Tabelle verschoben wird, automatisch zu starten oder aufzuheben.

Änderungen werden mit einer neuen CHANGETABLES(CHANGES …)-Funktion aus den internen Änderungstabellen abgerufen. Diese verwendet den Namen der Tabelle, für die Änderungen nachverfolgt werden, sowie die bei ihrer letztmaligen Verwendung zugewiesene Versionsnummer und gibt Informationen zu allen Zeilen zurück, die sich seit der letzten Verwendung der Funktion geändert haben. Für die Suche nach der aktuellen und der ältesten gültigen Version sind verschiedene Funktionen vorhanden. Die Anwendung kann dann mithilfe der zurückgegebenen Informationen die Tabelle abfragen, für die Änderungen nachverfolgt werden, um die konkreten Spaltenwerte abzurufen. Dabei handelt es sich natürlich um einen Prozess in mehreren Schritten – Sie rufen die aktuelle Version ab, verwenden diese Version zur Abfrage der Änderungsnachverfolgung und fragen dann die konkreten Tabellen nach den dieser Version entsprechenden Spaltendaten ab.

Auf einem sich ständig verändernden System ist es möglich, inkonsistente oder falsche Ergebnisse zu erhalten, sofern nicht eine bestimmte gleichbleibende Ansicht der Version, Änderungsdaten und konkrete Spaltendaten verwaltet werden. Hierzu können Sie die Snapshotisolation verwenden und den mehrstufigen Prozess in eine explizite Transaktion einschließen. Diese Methode funktioniert gut, ist aber mit potenziellen Nachteilen verbunden. Die Snapshotisolation kann sich auf die Arbeitsauslastungsleistung auswirken, und sie beeinflusst die Leistung und Speicherplatznutzung von tempdb. Weitere Informationen hierzu finden Sie unter technet.microsoft.com/library/cc280358.

Zusammenfassung

Abbildung 4 enthält eine vergleichende Gegenüberstellung von Änderungsnachverfolgung und Änderungsdatenerfassung, damit Sie eine bessere Vorstellung von den Hauptunterschieden erhalten, die für DBAs wichtig sind. Sie können aus der Tabelle entnehmen, dass die Änderungsdatenerfassung viel ressourcenintensiver als die Änderungsnachverfolgung ist. Dieses Feature erfordert eine größere Sorgfalt bei der Entscheidung über die nachzuverfolgenden Elemente, da die Größe der Nachverfolgungstabelle schnell zunehmen kann, wenn die nachverfolgte Tabelle beispielsweise BLOB-Spalten oder sehr breite Zeilen enthält. Es besteht auch die Möglichkeit von Problemen bei der Transaktionsprotokollverwaltung, da das Protokoll erst abgeschnitten wird, wenn der Protokollleser Datensätze aus dem Protokoll erfasst hat.

Abbildung 4 Vergleich zwischen Änderungsnachverfolgung und Änderungsdatenerfassung

Die Änderungsnachverfolgung ist jedoch ebenfalls mit bestimmten Anforderungen verbunden. So ist beispielsweise ein Primärschlüssel erforderlich, und es wird nachdrücklich empfohlen, dass Sie die Snapshotisolation verwenden, wenn die Änderungsnachverfolgung aktiviert ist. Die Snapshotisolation selbst kann zu einem beträchtlichen Mehraufwand in Bezug auf die Arbeitsauslastung führen und erfordert eine viel sorgfältigere Verwaltung von tempdb.

Es gibt ein weiteres Problem, dem Entwickler und DBAs Rechnung tragen müssen: die Notfallwiederherstellung. Obwohl eine ausführliche Erläuterung den Rahmen dieses Artikels sprengen würde, ist das Thema der Notfallwiederherstellung zu wichtig, um es an dieser Stelle nicht wenigstens zu erwähnen.

Beide Features funktionieren gut in Verbindung mit BACKUP und RESTORE. Problematisch wird es jedoch, wenn eine Datenbank wiederhergestellt und im Wesentlichen in die Vergangenheit zurückversetzt wird. Wie sollte sich die Gesamtanwendung/das Gesamtsystem verhalten? Bei benutzerdefinierten Lösungen, die zum Nachverfolgen von Änderungen entwickelt wurden, tritt dieses Problem ebenfalls auf, und es muss bei der Verwendung von SQL Server 2008 weiterhin berücksichtigt werden.

Wie immer sollten Sie unbedingt die gesamte verfügbare Dokumentation (technet.microsoft.com/library/bb418491) und alle vorhandenen Whitepaper lesen, bevor Sie sich an ein Entwurfs- und Bereitstellungsprojekt wagen, das die neuen Features zum Nachverfolgen von Änderungen umfasst. Sie müssen zuerst herausfinden, ob für Sie möglicherweise potenzielle Probleme gelten, auf die in diesem Artikel nicht eingegangen wurde. Zudem sollten Sie Einzelheiten zu den neuen gespeicherten Prozeduren und dynamischen Verwaltungssichten (Dynamic Management Views, DMVs) für die Überwachung in Erfahrung bringen.

Insgesamt stellen diese neuen Features einen riesigen Fortschritt gegenüber früheren Methoden für das Nachverfolgen von Datenänderungen dar. Nachdem es sie nun gibt, können Sie sicher sein, dass Entwickler sie in von Ihnen verwalteten Lösungen verwenden möchten.

Dabei sind wichtige Konfigurations- und Verwaltungsaspekte zu berücksichtigen, und ich hoffe, dass Sie durch diesen Artikel einen grundlegenden Überblick über die Technologien erhalten haben, sodass Sie einige der hier beschriebenen Probleme vorhersehen und sich darauf vorbereiten können. Wenn Sie Feedback oder Fragen zu diesem Artikel haben, schreiben Sie mir unter Paul@SQLskills.com.

Paul S. Randal ist der leitende Direktor von SQLskills.com und ein SQL Server-MVP. Paul Randal war von 1999 bis 2007 im SQL Server-Speichermodulteam von Microsoft tätig. Er schrieb DBCC CHECKDB/repair für SQL Server 2005 und war bei der Entwicklung von SQL Server 2008 für das Kernspeichermodul zuständig. Als Experte für Notfallwiederherstellung, hohe Verfügbarkeit und Datenbankwartung ist Paul Randal ein regelmäßiger Referent bei Konferenzen weltweit. Sein Blog befindet sich unter SQLskills.com/blogs/paul.