Informationen zu Change Data Capture (SQL Server)

Erfassung von Änderungen zeichnet die Einfüge-, Update- und Löschaktivitäten auf, die auf eine SQL Server-Tabelle angewendet werden. Dadurch werden die Details der Änderungen in einem leicht genutzten relationalen Format verfügbar. Spalteninformationen und die Metadaten, die zum Anwenden der Änderungen auf eine Zielumgebung erforderlich sind, werden für die geänderten Zeilen erfasst und in Änderungstabellen gespeichert, die die Spaltenstruktur der nachverfolgten Quelltabellen spiegeln. Tabellenwertfunktionen werden bereitgestellt, um systematischen Zugriff auf die Änderungsdaten von Verbrauchern zu ermöglichen.

Ein gutes Beispiel für einen Datenverarbeiter, der von dieser Technologie angesprochen wird, ist ein Extraktions-, Transformations- und Ladeprozess (ETL). Eine ETL-Anwendung lädt Änderungsdaten inkrementell aus SQL Server-Quelltabellen in ein Data Warehouse oder Data Mart. Obwohl die Darstellung der Quelltabellen innerhalb des Data Warehouse Änderungen in den Quelltabellen widerspiegeln muss, ist eine End-to-End-Technologie, die ein Replikat der Quelle aktualisiert, nicht geeignet. Benötigt wird stattdessen ein zuverlässiger Datenstrom von Änderungsdaten, der so strukturiert ist, dass er vom Consumer problemlos auf unterschiedliche Darstellungen der Daten in einer Zielumgebung angewendet werden kann. Change Data Capture in SQL Server stellt diese Technologie bereit.

Datenerfassungsdatenfluss ändern

Die folgende Abbildung zeigt den Hauptdatenfluss für Change Data Capture.

Die Quelle der Änderungsdaten ist für Change Data Capture das SQL Server -Transaktionsprotokoll. Bei Einfügungen, Aktualisierungen und Löschungen in den nachverfolgten Quelltabellen werden diesem Protokoll entsprechende Einträge hinzugefügt, die diese Änderungen beschreiben. Das Protokoll dient als Eingabe für den Erfassungsvorgang. Es liest das Protokoll und fügt Informationen zu Änderungen in die Änderungstabelle der nachverfolgten Tabelle hinzu. Zur Aufzählung der Änderungen in den Änderungstabellen innerhalb eines angegebenen Bereichs werden Funktionen bereitgestellt, und die Informationen werden in Form eines gefilterten Resultsets zurückgegeben. Das gefilterte Resultset wird typischerweise von einem Anwendungsprozess verwendet, um die Darstellung der Quelle in einer externen Umgebung zu aktualisieren.

Grundlegendes zur Änderungsdatenerfassung und zur Erfassungsinstanz

Damit Änderungen an einzelnen Tabellen innerhalb einer Datenbank nachverfolgt werden können, muss Change Data Capture explizit für die Datenbank aktiviert werden. Dazu wird die gespeicherte Prozedur sys.sp_cdc_enable_dbverwendet. Wenn die Datenbank für Change Data Capture aktiviert ist, können Quelltabellen mit der gespeicherten Prozedur sys.sp_cdc_enable_tableals nachverfolgte Tabellen identifiziert werden. Wenn eine Tabelle für Change Data Capture aktiviert ist, wird eine Aufzeichnungsinstanz erstellt und zugeordnet, die die Verteilung der Änderungsdaten in der Quelltabelle unterstützt. Die Aufzeichnungsinstanz besteht aus einer Änderungstabelle und bis zu zwei Abfragefunktionen. Metadaten, die die Konfigurationsdetails der Aufnahmeinstanz beschreiben, werden in den Metadatentabellen cdc.change_tablesfür die Änderungsdatenerfassung gespeichert, cdc.index_columnsund cdc.captured_columns. Diese Informationen können mit der gespeicherten Prozedur sys.sp_cdc_help_change_data_captureabgerufen werden.

Alle einer Aufzeichnungsinstanz zugeordneten Objekte werden im Change Data Capture-Schema der aktivierten Datenbank erstellt. Die Anforderungen für den Namen der Aufnahmeinstanz sind, dass es sich um einen gültigen Objektnamen handelt und dass er in den Instanzen der Datenbankerfassung eindeutig ist. Standardmäßig lautet der Name <Schemaname_Tabellenname> der Quelltabelle. Zur Benennung der zugeordneten Änderungstabelle wird dem Namen der Aufzeichnungsinstanz _CT angehängt. Die Funktion, die verwendet wird, um nach allen Änderungen abzufragen, wird benannt, indem fn_cdc_get_all_changes_ vor den Namen der Aufnahmeinstanz gesetzt wird. Wenn die Aufnahmeinstanz so konfiguriert ist, um net changes zu unterstützen, wird die net_changes-Abfragefunktion ebenfalls erstellt und durch vorangestellte fn_cdc_get_net_changes_ an den Namen der Aufnahmeinstanz angefügt.

Tabelle ändern

Die ersten fünf Spalten einer Change Data Capture-Änderungstabelle sind Metadatenspalten. Diese enthalten zusätzliche Informationen zu den aufgezeichneten Änderungen. Die Namen und in der Regel auch der Typ der restlichen Spalten entsprechen den identifizierten nachverfolgten Spalten aus der Quelltabelle. Diese Spalten enthalten die aus der Quelltabelle erfassten Spaltendaten.

Jeder auf die Quelltabelle angewendete Einfüge- oder Löschvorgang erscheint als einzelne Zeile in der Änderungstabelle. Die Datenspalten der Zeile, die sich aus einem Einfügevorgang ergibt, enthalten die Spaltenwerte nach dem Einfügevorgang. Die Datenspalten der Zeile, die sich aus einem Löschvorgang ergibt, enthalten die Spaltenwerte vor dem Löschvorgang. Ein Aktualisierungsvorgang erfordert einen Zeileneintrag, um die Spaltenwerte vor der Aktualisierung zu identifizieren, und einen zweiten Zeileneintrag, um die Spaltenwerte nach der Aktualisierung zu identifizieren.

Jede Zeile in einer Änderungstabelle enthält auch zusätzliche Metadaten, um die Interpretation der Änderungsaktivität zu ermöglichen. Die Spalte __$start_lsn identifiziert die Commit-Protokollfolgenummer (Log Sequence Number, LSN), die der Änderung zugewiesen wurde. Die Commit-LSN identifiziert sowohl die Änderungen, die innerhalb der gleichen Transaktion ausgeführt wurden, als auch die Reihenfolge der Transaktionen. Die Spalte __$seqval kann verwendet werden, um die Reihenfolge weiterer Änderungen festzulegen, die innerhalb der gleichen Transaktion ausgeführt wurden. In der Spalte __$operation wird der mit der Änderung verbundene Vorgang aufgezeichnet: 1 = Löschung, 2 = Einfügung, 3 = Update (Anfangsimage) und 4 = Update (Endimage). Die Spalte __$update_mask ist eine variable Bitmaske mit einem definierten Bit für jede aufgezeichnete Spalte. Für das Einfügen und Löschen von Einträgen wird die Aktualisierungsmaske immer alle Bits gesetzt haben. Aktualisierungszeilen haben jedoch nur die Bits gesetzt, die den geänderten Spalten entsprechen.

Ändern des Gültigkeitsintervalls für die Datenerfassung für eine Datenbank

Beim Change Data Capture-Gültigkeitsintervall für eine Datenbank handelt es sich um dem Zeitraum, in dem Änderungsdaten für Aufzeichnungsinstanzen verfügbar sind. Das Gültigkeitsintervall beginnt mit der Erstellung der ersten Aufzeichnungsinstanz für eine Datenbanktabelle und dauert bis zum aktuellen Zeitpunkt.

Damit die Datenmenge in Änderungstabellen nicht auf eine unüberschaubare Größe anwächst, müssen die Daten regelmäßig und systematisch gekürzt werden. Der Change Data Capture-Cleanupprozess dient zur Erzwingung der beibehaltungsbasierten Cleanuprichtlinie. Zunächst wird der untere Endpunkt des Gültigkeitsintervalls verschoben, um die Zeitbeschränkung festzulegen. Anschließend werden die abgelaufenen Einträge aus der Änderungstabelle entfernt. Standardmäßig werden drei Tage Daten aufbewahrt.

Am oberen Ende, wenn der Erfassungsprozess jede neue Charge von Änderungsdaten abschließt, werden neue Einträge zu cdc.lsn_time_mapping hinzugefügt, für jede Transaktion, die Einträge in der Änderungstabelle hat. In der Zuordnungstabelle werden sowohl die Commit-Protokollfolgenummern als auch die Commitzeitpunkte der Transaktion (Spalten start_lsn und tran_end_time) beibehalten. Der maximale LSN-Wert, der in cdc.lsn_time_mapping gefunden wird, stellt den Höchststand des Gültigkeitszeitraums der Datenbank dar. Die entsprechende Commitzeit wird als Basis verwendet, von der die aufbewahrungsbasierte Bereinigung ein neues niedriges Wasserzeichen berechnet.

Da der Erfassungsprozess Änderungsdaten aus dem Transaktionsprotokoll extrahiert, gibt es eine integrierte Latenz zwischen dem Zeitpunkt, zu dem eine Änderung an einer Quelltabelle zugesichert wird, und der Uhrzeit, zu der die Änderung in der zugeordneten Änderungstabelle angezeigt wird. Obwohl diese Latenz in der Regel klein ist, ist es dennoch wichtig zu beachten, dass Änderungsdaten erst verfügbar sind, wenn der Erfassungsprozess die zugehörigen Protokolleinträge verarbeitet hat.

Ändern des Gültigkeitsintervalls für die Datenerfassung für eine Erfassungsinstanz

Obwohl es gewöhnlich ist, dass sich das Gültigkeitsintervall der Datenbank und das Gültigkeitsintervall einzelner Erfassungsinstanzen überschneiden, trifft dies nicht immer zu. Das Gültigkeitsintervall der Aufzeichnungsinstanz beginnt zu dem Zeitpunkt, an dem der Aufzeichnungsprozess die Aufzeichnungsinstanz erkennt und die Protokollierung zugeordneter Änderungen in der Änderungstabelle startet. Wenn erfassungsinstanzen zu unterschiedlichen Zeitpunkten erstellt werden, verfügt jeder anfänglich über einen anderen niedrigen Endpunkt. Die Spalte „start_lsn“ des von sys.sp_cdc_help_change_data_capture zurückgegebenen Resultsets zeigt den aktuellen unteren Endpunkt für jede definierte Aufzeichnungsinstanz. Bei der Bereinigung von Änderungstabelleneinträgen durch den Cleanupprozess werden die start_lsn-Werte für alle Aufzeichnungsinstanzen angepasst, sodass sie die neue Untergrenzenmarkierung für verfügbare Änderungsdaten widerspiegeln. Dabei werden nur die Aufzeichnungsinstanzen angepasst, deren start_lsn-Werte kleiner sind als die neue Untergrenzenmarkierung. Nach einer gewissen Zeit stimmen also die Gültigkeitsintervalle aller einzelnen Instanzen mit dem Gültigkeitsintervall der Datenbank überein, vorausgesetzt, es werden keine neuen Aufzeichnungsinstanzen erstellt.

Für Consumer von Änderungsdaten ist das Gültigkeitsintervall wichtig, da das Extrahierungsintervall einer Anforderung vollständig von dem aktuellen Change Data Capture-Gültigkeitsintervall für die Aufzeichnungsinstanz abgedeckt werden muss. Wenn der untere Endpunkt des Extrahierungsintervalls links vom unteren Endpunkt des Gültigkeitsintervalls liegt, kann es bei einem umfassenden Cleanup zu fehlenden Änderungsdaten kommen. Wenn der hohe Endpunkt des Extraktionsintervalls rechts vom hohen Endpunkt des Gültigkeitsintervalls liegt, wurde der Erfassungsprozess noch nicht über den Zeitraum verarbeitet, der durch das Extraktionsintervall dargestellt wird, und Änderungsdaten könnten ebenfalls fehlen.

Mit der Funktion sys.fn_cdc_get_min_lsn können Sie den aktuellen kleinsten LSN-Wert und mit der Funktion sys.fn_cdc_get_max_lsn den aktuellen größten LSN-Wert für eine Aufzeichnungsinstanz abrufen. Wenn beim Abfragen nach Änderungsdaten der angegebene LSN-Bereich nicht innerhalb dieser beiden LSN-Werte liegt, schlägt die Abfragefunktionen der Änderungsdatenerfassung fehl.

Behandeln von Änderungen an Quelltabellen

Um Spaltenänderungen in den nachzuverfolgenden Quelltabellen zu berücksichtigen, ist ein schwieriges Problem für nachgeschaltete Verbraucher. Obwohl das Aktivieren der Änderungsdatenerfassung in einer Quelltabelle nicht verhindert, dass solche DDL-Änderungen auftreten, trägt die Datenerfassung dazu bei, die Auswirkungen auf Verbraucher zu verringern, indem die übermittelten Resultsets, die über die API zurückgegeben werden, unverändert bleiben können, auch wenn sich die Spaltenstruktur der zugrunde liegenden Quelltabelle ändert. Diese feste Spaltenstruktur gilt auch für die zugrunde liegende Änderungstabelle, auf die die definierten Abfragefunktionen zugreifen.

Um die Änderungstabelle einer festen Spaltenstruktur zu berücksichtigen, ignoriert der Erfassungsprozess, der für das Auffüllen der Änderungstabelle verantwortlich ist, alle neuen Spalten, die nicht für die Erfassung identifiziert werden, wenn die Quelltabelle für die Änderungsdatenerfassung aktiviert wurde. Wenn eine nachverfolgte Spalte gelöscht wird, werden Nullwerte für die Spalte in den nachfolgenden Änderungseinträgen angegeben. Wenn eine vorhandene Spalte jedoch einer Änderung des Datentyps unterzogen wird, wird die Änderung an die Änderungstabelle weitergegeben, um sicherzustellen, dass der Erfassungsmechanismus keinen Datenverlust in nachverfolgte Spalten einführt. Außerdem sendet der Aufzeichnungsprozess alle erkannten Änderungen an der Spaltenstruktur nachverfolgter Tabellen an die Tabelle cdc.ddl_history. Consumer, die über notwendige Anpassungen von Downstreamanwendungen informiert werden möchten, verwenden die gespeicherte Prozedur sys.sp_cdc_get_ddl_history.

In der Regel behält die aktuelle Erfassungsinstanz ihre Form bei, wenn DDL-Änderungen auf die zugeordnete Quelltabelle angewendet werden. Es ist jedoch möglich, eine zweite Aufnahmeinstanz für die Tabelle zu erstellen, die die neue Spaltenstruktur widerspiegelt. Dadurch kann der Erfassungsprozess Änderungen an derselben Quelltabelle in zwei verschiedene Änderungstabellen mit zwei unterschiedlichen Spaltenstrukturen vornehmen. Eine dieser Änderungstabellen kann für aktuelle Betriebsprogramme und die andere für eine Entwicklungsumgebung verwendet werden, in der versucht wird, die neuen Spaltendaten aufzunehmen. Da der Aufzeichnungsmechanismus gleichzeitig Werte in beide Änderungstabellen einfügen kann, ist ein Übergang zwischen den Tabellen ohne Datenverlust möglich. Dies kann jederzeit geschehen, wenn sich die beiden Änderungsdatenerfassungszeitachsen überlappen. Wenn der Übergang wirksam wird, kann die veraltete Aufnahmeinstanz entfernt werden.

Beziehung zwischen dem Capture-Auftrag und dem Transaktionsreplikations-Logreader

Die Logik für den Change Data Capture-Prozess ist in die gespeicherte Prozedur sp_replcmdseingebettet. Hierbei handelt es sich um eine als Teil von „sqlservr.exe“ erstellte interne Serverfunktion, die auch bei der Transaktionsreplikation verwendet wird, um Änderungen aus dem Transaktionsprotokoll zu sammeln. Wenn Change Data Capture allein für eine Datenbank aktiviert ist, erstellen Sie den SQL Server-Agent-Erfassungsauftrag für Change Data Capture als Mittel zum Aufrufen von sp_replcmds. Wenn auch die Replikation vorhanden ist, wird der Transaktionsprotokollreader allein verwendet, um die Änderungsdatenanforderungen für beide dieser Verbraucher zu erfüllen. Mit dieser Strategie werden Protokollkonflikte erheblich reduziert, wenn sowohl die Replikation als auch Change Data Capture für eine Datenbank aktiviert sind.

Der Wechsel zwischen diesen beiden Betriebsmodi zum Erfassen von Änderungsdaten erfolgt automatisch, wenn eine Änderung des Replikationsstatus einer aktivierten Datenbank für änderungsdatenerfassung vorliegt.

Die Hauptaufgabe des Erfassungsprozesses besteht darin, das Protokoll zu scannen und Spaltendaten und transaktionsbezogene Informationen in die Änderungsdatenerfassungsänderungstabellen zu schreiben. Um eine transaktionskonsistente Begrenzung über alle von Change Data Capture aufgefüllten Änderungstabellen hinweg sicherzustellen, öffnet der Aufzeichnungsprozess bei jedem Scanzyklus eine eigene Transaktion und führt dafür einen Commit aus. Der Prozess erkennt neu für Change Data Capture aktivierte Tabellen und nimmt diese automatisch in die Gruppe von Tabellen auf, die aktiv auf Änderungseinträge im Protokoll überwacht werden. Auch die Deaktivierung von Change Data Capture wird erkannt. In diesem Fall wird die Quelltabelle aus der Gruppe von Tabellen, die aktiv auf Änderungseinträge überwacht werden, entfernt. Wenn die Verarbeitung für einen Protokollabschnitt beendet ist, sendet der Aufzeichnungsprozess ein Signal an die Serverprotokoll-Kürzungslogik, die anhand dieser Informationen für die Kürzung geeignete Protokolleinträge auswählt.

Der Aufzeichnungsprozess wird auch zur Speicherung des Verlaufs der an nachverfolgten Tabellen vorgenommenen DDL-Änderungen verwendet. Die mit Change Data Capture verknüpften DDL-Anweisungen nehmen Einträge im Transaktionsprotokoll der Datenbank vor, wenn eine für Change Data Capture aktivierte Datenbank oder Tabelle gelöscht wird und wenn Spalten einer für Change Data Capture aktivierten Tabelle hinzugefügt, geändert oder gelöscht werden. Diese Protokolleinträge werden vom Aufzeichnungsprozess verarbeitet, der die zugehörigen DDL-Ereignisse an die Tabelle cdc.ddl_history sendet. Sie können Informationen über DDL-Ereignisse mit Auswirkungen auf nachverfolgte Tabellen mit der gespeicherten Prozedur sys.sp_cdc_get_ddl_historyabrufen.

Ändern von Datenerfassungs-Agent-Aufträgen

Mit einer Datenbank mit aktivierter Change Data Capture-Funktion sind in der Regel zwei SQL Server Agent-Aufträge verbunden: einer, der die Änderungstabellen der Datenbank auffüllt, und einer, der für die Bereinigung der Änderungstabellen zuständig ist. Beide Aufträge bestehen aus einem Einzelschritt, der einen Transact-SQL-Befehl ausführt. Bei dem aufgerufenen The Transact-SQL-Befehl handelt es sich um eine in Change Data Capture definierte gespeicherte Prozedur zur Implementierung der Auftragslogik. Die Aufträge werden erstellt, wenn die erste Tabelle der Datenbank für Change Data Capture aktiviert wird. Der Cleanupauftrag wird immer erstellt. Der Aufzeichnungsauftrag wird nur erstellt, wenn für die Datenbank keine Transaktionsveröffentlichungen definiert wurden. Der Erfassungsauftrag wird auch erstellt, wenn sowohl die Änderungsdatenerfassung als auch die Transaktionsreplikation für eine Datenbank aktiviert sind, und der Transaktionsprotokollleserauftrag wird entfernt, da die Datenbank keine Publikationen mehr definiert hat.

Sowohl Aufzeichnungs- als auch Cleanupaufträge werden mit Standardparametern erstellt. Der Aufzeichnungsauftrag wird sofort gestartet. Er wird kontinuierlich ausgeführt und verarbeitet maximal 1000 Transaktionen pro Scanzyklus mit einer Wartezeit von 5 Sekunden. Der Bereinigungsauftrag wird täglich um 2 Uhr nachts ausgeführt. Er bewahrt die Einträge in der Änderungstabelle für 4320 Minuten oder 3 Tage auf und entfernt maximal 5000 Einträge mit einer einzigen Löschanweisung.

Die Change Data Capture-Agentaufträge werden entfernt, wenn Change Data Capture für eine Datenbank deaktiviert wird. Der Aufzeichnungsauftrag kann auch entfernt werden, wenn einer Datenbank die erste Veröffentlichung hinzugefügt wird und sowohl Change Data Capture als auch die Transaktionsreplikation aktiviert sind.

Intern werden Change Data Capture-Agentaufträge mit den gespeicherten Prozeduren sys.sp_cdc_add_job und sys.sp_cdc_drop_joberstellt bzw. gelöscht. Diese gespeicherten Prozeduren werden ebenfalls verfügbar gemacht, sodass Administratoren die Erstellung und Entfernung dieser Aufträge kontrollieren können.

Die Standardkonfiguration der Change Data Capture-Agentaufträge wird nicht explizit von Administratoren gesteuert. Mit der gespeicherten Prozedur sys.sp_cdc_change_job können die Standardkonfigurationsparameter geändert werden. Darüber hinaus ermöglicht die gespeicherte Prozedur sys.sp_cdc_help_jobs das Anzeigen der aktuellen Konfigurationsparameter. Sowohl der Aufzeichnungsauftrag als auch der Cleanupauftrag extrahieren Konfigurationsparameter aus der Tabelle msdb.dbo.cdc_jobs, wenn sie gestartet werden. Alle Änderungen, die an diesen Werten mithilfe von sys.sp_cdc_change_job vorgenommen wurden, werden erst wirksam, wenn der Job beendet und neu gestartet wird.

Es werden zwei zusätzliche gespeicherte Prozeduren bereitgestellt, damit die Datenerfassungs-Agent-Aufträge gestartet und beendet werden können: sys.sp_cdc_start_job und sys.sp_cdc_stop_job.

Beide SQL Server-Agent-Aufträge wurden mit genügend Flexibilität und Konfigurationsmöglichkeiten konzipiert, um die grundlegenden Anforderungen von Change Data Capture-Umgebungen zu erfüllen. In beiden Fällen wurden jedoch die zugrunde liegenden gespeicherten Prozeduren, die die Kernfunktionalität bereitstellen, verfügbar gemacht, um eine weitere Anpassung zu ermöglichen.

Die Änderungsdatenerfassung kann nicht ordnungsgemäß funktionieren, wenn der Datenbank-Engine-Dienst oder der SQL Server-Agent-Dienst unter dem NETWORK SERVICE-Dienstkonto ausgeführt wird. Dies kann zu Fehler 22832 führen.

Siehe auch

Nachverfolgen von Datenänderungen (SQL Server)
Aktivieren und Deaktivieren von Change Data Capture (SQL Server)
Arbeiten mit Änderungsdaten (SQL Server)
Verwalten und Überwachen von Change Data Capture (SQL Server)