Was ist Change Data Capture (CDC)?

Gilt für:SQL ServerAzure SQL Managed Instance

In diesem Artikel erfahren Sie mehr über das CDC-Feature (Change Data Capture), das Aktivitäten in einer Datenbank aufzeichnet, wenn Tabellen und Zeilen geändert wurden.

In diesem Artikel wird erläutert, wie CDC mit SQL Server und Azure SQL verwaltete Instanz funktioniert. Informationen zu Azure SQL-Datenbank finden Sie unter CDC mit Azure SQL-Datenbank.

Überblick

Die Änderungsdatenerfassung verwendet die SQL Server-Agent zum Protokollieren von Einfügungen, Aktualisierungen und Löschungen in einer Tabelle. Daher können diese Datenänderungen mithilfe eines relationalen Formats problemlos genutzt werden. Die Spaltendaten und wesentlichen Metadaten müssen diese Änderungsdaten auf eine Zielumgebung anwenden, werden für die geänderten Zeilen erfasst und in Änderungstabellen gespeichert, die die Spaltenstruktur der nachverfolgten Quelltabellen Spiegel. Darüber hinaus stehen Tabellenwertfunktionen für den systematischen Zugriff auf diese Änderungsdaten von Verbrauchern zur Verfügung.

Ein gutes Beispiel für einen Datenanwender, auf den diese Technologie abzielt, ist eine Extraktions-, Transformations- und Ladeanwendung (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, sind End-to-End-Technologien, die eine Kopie der Quelle aktualisieren, ungeeignet. 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.

Datenfluss

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. Anschließend liest es das Protokoll und fügt Informationen zu Änderungen der zugeordneten Ä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.

Capture instance

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. Die Metadaten, mit denen die Konfigurationsdetails der Aufzeichnungsinstanz beschrieben werden, befinden sich in den Change Data Capture-Metadatentabellen cdc.change_tables, 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. Der Name der Aufzeichnungsinstanz muss ein gültiger Objektname sein, der innerhalb aller Aufzeichnungsinstanzen der Datenbank eindeutig ist. Standardmäßig ist <der Name schemaname_table name> der Quelltabelle. Zur Benennung der zugeordneten Änderungstabelle wird dem Namen der Aufzeichnungsinstanz _CT angehängt. Zur Benennung der Funktion zur Abfrage aller Änderungen wird dem Namen der Aufzeichnungsinstanz fn_cdc_get_all_changes_ vorangestellt. Wenn die Erfassungsinstanz für die Unterstützung von Net-Änderungen konfiguriert ist, wird die net_changes Abfragefunktion auch erstellt und benannt, indem fn_cdc_get_net_changes_ dem Namen der Aufnahmeinstanz vorangestellt wird.

Änderungstabelle

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 Updatevorgang erfordert einen Zeileneintrag, der die Spaltenwerte vor dem Updatevorgang angibt, und einen Zeileneintrag, der die Spaltenwerte nach dem Updatevorgang angibt.

Jede Zeile in einer Änderungstabelle enthält auch andere 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 weitere Änderungen anzuordnen, die in derselben Transaktion auftreten. 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 erfasste Spalte. Zum Einfügen und Löschen von Einträgen hat das Updateformat alle Bits festgelegt. Aktualisierungszeilen werden jedoch mit diesen Bits festgelegt, die geänderten Spalten entsprechen.

Gültigkeitsintervall

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.

Datenbank

Daten, die in Änderungstabellen abgelegt werden, werden unmanageabel, wenn Sie die Daten nicht regelmäßig und systematisch löschen. 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 die Daten drei Tage beibehalten.

Am oberen Endpunkt werden bei jedem Commit neuer Änderungsdaten durch den Aufzeichnungsprozess für jede Transaktion, die Änderungstabelleneinträge beinhaltet, neue Einträge zu cdc.lsn_time_mapping hinzugefügt. 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 in cdc.lsn_time_mapping entspricht der Obergrenzenmarkierung des Datenbank-Gültigkeitsfensters. Die entsprechende Commitzeit wird als Basis für die Berechnung einer neuen Untergrenzenmarkierung durch die beibehaltungsbasierte Cleanuprichtlinie verwendet.

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

Capture instance

Obwohl es für das Gültigkeitsintervall der Datenbank und das Gültigkeitsintervall einzelner Erfassungsinstanzen üblich ist, ist es nicht immer wahr. 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 Zeiten erstellt werden, verfügt jeder ü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 sich der hohe Endpunkt des Extraktionsintervalls rechts vom hohen Endpunkt des Gültigkeitsintervalls befindet, gibt er an, dass der Erfassungsprozess noch nicht durch die Zeit verarbeitet wurde, die durch das Extraktionsintervall dargestellt wird, und es könnten auch Änderungsdaten 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 Sie die Änderungsdaten abfragen, liegt der angegebene LSN-Bereich nicht innerhalb dieser beiden LSN-Werte, schlägt die Änderung der Abfragefunktionen für die Datenerfassung fehl.

Behandeln von Änderungen an der Quelltabelle

Die Aufnahme von Spaltenänderungen in den Quelltabellen, die nachverfolgt werden, ist ein schwieriges Problem für nachgeschaltete Verbraucher. Obwohl das Aktivieren der Änderungsdatenerfassung in einer Quelltabelle nicht verhindert, dass solche DDL-Änderungen auftreten, verringert die Datenerfassung die Auswirkungen auf Verbraucher, indem die bereitgestellten Resultsets beibehalten werden, die über die API zurückgegeben werden, 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.

Der Erfassungsprozess, der für das Auffüllen der Änderungstabelle verantwortlich ist, enthält eine Änderungstabelle mit fester Spaltenstruktur, indem alle neuen Spalten ignoriert werden, die nicht für die Erfassung identifiziert wurden, wenn die Quelltabelle für die Änderungsdatenerfassung aktiviert wurde. Wenn eine nachverfolgte Spalte verworfen wird, werden NULL-Werte 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 Aufnahmeinstanz 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. Mit dieser Option kann der Erfassungsprozess Änderungen an derselben Quelltabelle in zwei unterschiedliche Ä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 immer der Fall sein, wenn die beiden Change Data Capture-Zeitachsen überlappen. Wenn der Übergang betroffen ist, kann die überflüssige Aufzeichnungsinstanz entfernt werden.

Beziehung zum Protokolllese-Agent

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 für eine Datenbank in SQL Server und Azure SQL Managed Instance nur Change Data Capture aktiviert wurde, erstellen Sie den Change Data Capture-Aufzeichnungsauftrag des SQL Server-Agents als Mittel zum Aufrufen von sp_replcmds. Wenn auch die Replikation vorhanden ist, wird der Transaktionsprotokollleser 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 die Änderungsdatenerfassung vorliegt.

Die Hauptaufgabe des Capture-Prozesses besteht darin, das Protokoll zu durchsuchen und Spaltendaten und transaktionsrelevante Informationen in die Change Data Capture-Ä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.

-Agentaufträge

Zwei SQL Server-Agent Aufträgen sind in der Regel mit einer aktivierten Datenbank für die Änderungsdatenerfassung verknüpft: eine, die zum Auffüllen der Datenbankänderungstabellen verwendet wird, und eine, die für die Änderungstabelle sauber up verantwortlich ist. Beide Aufträge bestehen aus einem einzelnen Schritt, der einen Transact-SQL-Befehl ausführt. Der transact-SQL-Befehl, der aufgerufen wird, ist eine definierte gespeicherte Prozedur zur Änderungsdatenerfassung, die die Logik des Auftrags implementiert. 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 Aufzeichnungsauftrag wird auch erstellt, wenn sowohl Change Data Capture als auch die Transaktionsreplikation für eine Datenbank aktiviert sind, und der Leseauftrag für das Transaktionsprotokoll wird entfernt, weil die Datenbank über keine definierten Veröffentlichungen mehr verfügt.

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 sauber upauftrag wird täglich um 2.00 Uhr ausgeführt. Es behält Änderungstabelleneinträge für 4320 Minuten oder 3 Tage bei, wobei maximal 5000 Einträge mit einer einzelnen Delete-Anweisung entfernt werden.

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 mit sys.sp_cdc_change_job an diesen Werten vorgenommen wurden, werden erst wirksam, wenn der Auftrag beendet und neu gestartet wird.

Zwei weitere gespeicherte Prozeduren werden 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 so konzipiert, dass sie flexibel genug sind und ausreichend konfigurierbar sind, um die grundlegenden Anforderungen von Änderungsdatenerfassungsumgebungen 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 Datenerfassung kann nicht ordnungsgemäß funktionieren, wenn der Datenbank-Engine Dienst oder der SQL Server-Agent-Dienst unter dem NETWORK SERVICE-Konto ausgeführt wird. Dies kann zu Fehler 22832 führen.

Interoperabilität mit anderen Funktionen

Die Änderungsdatenerfassung hat einige Einschränkungen beim Arbeiten mit anderen SQL Server-Features. Überprüfen Sie die Interoperabilität , um mehr zu erfahren.

Bekannte Probleme

Überprüfen Sie bekannte Probleme und Fehler im Zusammenhang mit der Änderungsdatenerfassung, um bekannte Probleme mit CDC zu überprüfen.

Siehe auch

• Bekannte Probleme und Einschränkungen
• Arbeiten mit Änderungsdaten
• Nachverfolgen von Datenänderungen
• Aktivieren und Deaktivieren der Änderungsdatenerfassung
• Verwalten und Überwachen von Change Data Capture
• Temporale Tabellen