Was ist Change Data Capture (CDC)?

Gilt für: SQL Server Azure 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 Managed Instance funktioniert. Informationen zu Azure SQL Database finden Sie unter CDC mit der Azure SQL Database.

Übersicht

Die Erfassung von Änderungsdaten verwendet den SQL Server Agent, um das Einfügen, Aktualisieren und Löschen von Daten in einer Tabelle zu protokollieren. So werden diese Datenänderungen in einem relationalen Format zugänglich gemacht und können leicht genutzt werden. Die Spaltendaten und wesentlichen Metadaten, die für die Anwendung dieser geänderten Daten auf eine Zielumgebung erforderlich sind, werden für die geänderten Zeilen erfasst und in Änderungstabellen gespeichert, die die Spaltenstruktur der verfolgten Quelltabellen widerspiegeln. Darüber hinaus stehen Tabellenwertfunktionen für den systematischen Zugriff auf diese Änderungsdaten von Verbrauchern zur Verfügung.

Ein Beispiel für einen Datenconsumer, auf den diese Technologie abzielt, ist eine Anwendung zum Extrahieren, Transformieren und Laden (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. Der Prozess liest dann das Protokoll und fügt der Änderungstabelle, die mit der nachverfolgten Tabelle verknüpft ist, Informationen über die Änderungen 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 lautet der Name <Schemaname_Tabellenname> 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 Aufzeichnungsinstanz für die Unterstützung von net changes konfiguriert ist, wird darüber hinaus eine Abfragefunktion für net_changes erstellt. Diese wird benannt, indem dem Aufzeichnungsinstanznamen fn_cdc_get_net_changes_ 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.

Darüber hinaus enthält jede Zeile in einer Änderungstabelle andere Metadaten, die eine Interpretation der Änderungsaktivitäten erlauben. 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. Bei Einträgen für Einfüge- und Löschvorgänge werden alle Bits in der Updatemaske gesetzt. Bei Einträgen für Updatevorgänge sind dagegen nur jene Bits gesetzt, die den 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

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 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 Aufzeichnungsprozess Änderungsdaten aus dem Transaktionsprotokoll extrahiert, entsteht eine Latenzzeit zwischen dem Commitzeitpunkt einer Änderung in einer Quelltabelle und dem Zeitpunkt, an dem die Änderung in der zugeordneten Änderungstabelle angezeigt wird. Diese Latenzzeit ist in der Regel sehr kurz, vergessen Sie jedoch nicht, dass die Änderungsdaten erst verfügbar sind, nachdem der Aufzeichnungsprozess die verbundenen Protokolleinträge verarbeitet hat.

Capture instance

Es ist zwar üblich, dass das Gültigkeitsintervall der Datenbank und das Gültigkeitsintervall der einzelnen Capture-Instanzen übereinstimmen, aber das ist nicht immer der Fall. 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 also Aufzeichnungsinstanzen zu verschiedenen Zeiten erstellt werden, weisen diese unterschiedliche untere Endpunkte auf. 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 obere Endpunkt des Extraktionsintervalls rechts vom oberen Endpunkt des Gültigkeitsintervalls liegt, deutet dies darauf hin, dass der Erfassungsprozess noch nicht über die durch das Extraktionsintervall repräsentierte Zeit verarbeitet wurde 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 und der angegebene LSN-Bereich nicht innerhalb dieser beiden LSN-Werte liegt, schlagen die Abfragefunktionen für die Change Data Capture fehl.

Behandeln von Änderungen an der Quelltabelle

Die Berücksichtigung von Spaltenänderungen in den nachverfolgten Quelltabellen ist für Downstream-Consumer schwierig. Durch die Aktivierung von Change Data Capture für eine Quelltabelle werden solche DDL-Änderungen zwar nicht verhindert, mit Change Data Capture können die Auswirkungen für den Heimanwender jedoch minimiert werden, indem erlaubt wird, dass die über die API zurückgegebenen Resultsets unverändert bleiben, auch wenn die Spaltenstruktur der zugrunde liegenden Quelltabelle geändert wird. Diese feste Spaltenstruktur gilt auch für die zugrunde liegende Änderungstabelle, auf die die definierten Abfragefunktionen zugreifen.

Der Capture-Prozess, der für das Auffüllen der Änderungstabelle verantwortlich ist, berücksichtigt eine Änderungstabelle mit fester Spaltenstruktur, indem er alle neuen Spalten ignoriert, die nicht für die Erfassung identifiziert wurden, als die Quelltabelle für Change Data Capture aktiviert wurde. Wenn eine nachverfolgte Spalte gelöscht wird, werden in den nachfolgenden Änderungseinträgen NULL-Werte für die Spalte angegeben. Wenn sich jedoch der Datentyp einer vorhandenen Spalte ändert, wird diese Änderung in die Änderungstabelle übernommen, um sicherzustellen, dass durch den Aufzeichnungsmechanismus keine Datenverluste für nachverfolgte Spalten entstehen. 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 wird die Form der aktuellen Aufzeichnungsinstanz beibehalten, wenn DDL-Änderungen an der verbundenen Quelltabelle vorgenommen werden. Es besteht jedoch die Möglichkeit, eine zweite Aufzeichnungsinstanz für die Tabelle zu erstellen, die die neue Spaltenstruktur widerspiegelt. Durch diese Option wird die Aufzeichnung von Änderungen an derselben Quelltabelle in zwei unterschiedliche Änderungstabellen mit zwei verschiedenen Spaltenstrukturen ermöglicht. 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 die Replikation ebenfalls aktiviert ist, wird für die Änderungsdatenanforderungen beider Consumer nur der Transaktions-Protokollleser verwendet. 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 zur Aufzeichnung von Änderungsdaten erfolgt automatisch, wenn sich der Replikationsstatus einer Datenbank ändert, für die Change Data Capture aktiviert ist.

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

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 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 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. Änderungen, die mit sys.sp_cdc_change_job an diesen Werten vorgenommen werden, treten erst in Kraft, wenn der Auftrag beendet und neu gestartet wird.

Zwei zusätzliche gespeicherte Prozeduren ermöglichen das Starten und Beenden der Change Data Capture-Agent-Aufträge: 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.

Change Data Capture kann nicht ordnungsgemäß ausgeführt werden, 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

Change Data Capture hat einige Einschränkungen beim Arbeiten mit anderen SQL Server-Features. Überprüfen Sie Interoperabilität, um mehr zu erfahren.

Bekannte Probleme

Überprüfen Sie bekannte Probleme und Fehler in Zusammenhang mit Change Data Capture, um bekannte Probleme mit CDC zu überprüfen.

Weitere Informationen

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