Was ist Change Data Capture (CDC)?

Gilt für:SQL ServerAzure SQL-DatenbankAzure 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. Die Erfassung von Änderungsdaten ist allgemein in Azure SQL Datenbank, SQL Server und Azure SQL Managed Instance verfügbar.

Übersicht

Change Data Capture (CDC) verwendet den SQL Server-Agent, um Einfüge-, Aktualisierungs- und Löschaktivitäten aufzuzeichnen, die für eine Tabelle gelten. Hierdurch werden die Details zu diesen Änderungen in einem leicht verwendbaren relationalen Format bereitgestellt. Für die geänderten Zeilen werden die Spaltendaten sowie die Metadaten, die zur Übernahme der Änderungen in einer Zielumgebung erforderlich sind, aufgezeichnet und in Änderungstabellen gespeichert, die die Spaltenstruktur der nachverfolgten Quelltabellen widerspiegeln. Für den systematischen Zugriff auf die Änderungsdaten durch den Consumer werden Tabellenwertfunktionen bereitgestellt.

Ein Beispiel für einen Datenconsumer, auf den diese Technologie abzielt, ist eine Anwendung zum Extrahieren, Transformieren und Laden (ETL-Anwendung). Eine ETL-Anwendung lädt Änderungsdaten inkrementell aus SQL Server -Quelltabellen in ein Data Warehouse oder Data Mart. Obwohl die Darstellung der Quelltabellen im 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. SQL Server stellt diese Technologie bereit.

CDC-Azure SQL-Datenbank &

In Azure SQL-Datenbank nimmt ein Change Data Capture-Planer den Platz des SQL Server-Agents ein, der gespeicherte Prozeduren aufruft, um die regelmäßige Erfassung und Bereinigung der Change Data Capture-Tabellen zu starten. Der Planer führt die Erfassung und Bereinigung in SQL-Datenbank ohne jegliche externe Abhängigkeit hinsichtlich Zuverlässigkeit oder Leistung automatisch aus. Benutzer verfügen weiterhin über die Option, die Erfassung und Bereinigung bei Bedarf manuell auszuführen.

Weitere Informationen zu Change Data Capture finden Sie in dieser Episode mit Datenexponiert:

Leistungserwägungen

Die Auswirkungen der Aktivierung von Change Data Capture auf die Leistung von Azure SQL-Datenbank sind mit den Leistungsauswirkungen der Aktivierung von CDC für SQL Server oder Azure SQL Managed Instance vergleichbar. Nachfolgend sind einige der Aspekte aufgeführt, die die Auswirkungen der Aktivierung des CDC-Features auf die Leistung betreffen:

• Die Anzahl der nachverfolgten CDC-fähigen Tabellen
• Häufigkeit von Änderungen in den nachverfolgten Tabellen
• In der Quelldatenbank verfügbarer Speicherplatz, da CDC-Artefakte (z. B. CT-Tabellen, cdc_jobs usw.) in derselben Datenbank gespeichert werden.
• Handelt es sich um eine Einzel- oder Pooldatenbank? Achten Sie bei Datenbanken in Pools für elastische Datenbanken neben der Anzahl der Tabellen mit aktiviertem CDC-Feature auch auf die Anzahl der Datenbanken, zu denen diese Tabellen gehören. Datenbanken in einem Pool nutzen gemeinsame Ressourcen (z. B. den Speicherplatz). Daher besteht beim Aktivieren von CDC für mehrere Datenbanken das Risiko, dass der gesamte Speicherplatz des Pools für elastische Datenbanken belegt wird. Überwachen Sie Ressourcen wie CPU, Arbeitsspeicher und Protokolldurchsatz.

Um Kunden spezifischere Anleitungen zur Leistungsoptimierung bereitzustellen, sind weitere Details zur Workload jedes Kunden erforderlich. Im Folgenden finden Sie jedoch einige allgemeinere Anleitungen, die auf Leistungstests basieren, die auf der TPCC-Workload ausgeführt wurden:

• Erwägen Sie, die Anzahl der virtuellen Kerne zu erhöhen oder auf eine höhere Datenbankebene (z. B. Hyperscale) zu wechseln, um das gleiche Leistungsniveau wie vor der Aktivierung von CDC für Ihre Azure SQL-Datenbank sicherzustellen.

• Überwachen Sie die Speicherplatznutzung genau, und testen Sie Ihre Workload gründlich, bevor Sie CDC für Datenbanken in der Produktion aktivieren.

• Überwachen sie die Protokollgenerierungsrate. Weitere Informationen finden Sie hier.

• Die Überprüfung/Bereinigung ist Teil der Benutzerworkload (Die Ressourcen des Benutzers werden verwendet). Die Auswirkung auf die Leistung kann erheblich sein, da ganze Zeilen zu Änderungstabellen hinzugefügt werden und für Aktualisierungsvorgänge auch vorab ein Image enthalten ist.

• Pools für elastische Datenbanken: Die Anzahl der CDC-fähigen Datenbanken sollte die Anzahl der virtuellen Kerne des Pools nicht überschreiten, um Latenzerhöhungen zu vermeiden. Weitere Informationen zur Ressourcenverwaltung in dichten Pools für elastische Datenbanken finden Sie hier.

• Bereinigen: Basierend auf der Workload des Kunden kann empfohlen werden, den Aufbewahrungszeitraum kleiner als den Standardwert von drei Tagen zu halten, um sicherzustellen, dass die Bereinigung alle Änderungen in der Änderungstabelle nachholt. Im Allgemeinen ist es gut, die Aufbewahrung niedrig zu halten und die Datenbankgröße nachzuverfolgen.

• Es wird keine Vereinbarung zum Servicelevel (SLA) bereitgestellt, wenn Änderungen in den Änderungstabellen aufgefüllt werden. Latenz unter sekunden wird ebenfalls nicht unterstützt.

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 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. Die Anforderungen für den Namen der Erfassungsinstanz besteht darin, dass es sich um einen gültigen Objektnamen handelt und dass er für die Datenbankerfassungsinstanzen 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 so konfiguriert ist, dass sie Nettoänderungen unterstützt, wird auch die net_changes-Abfragefunktion erstellt und benannt, indem fn_cdc_get_net_changes_ dem Namen der Erfassungsinstanz 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 zusätzliche 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

Daten, die in Änderungstabellen hinterlegt sind, wachsen 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, besteht eine integrierte Latenz zwischen dem Zeitpunkt, zu dem eine Änderung für eine Quelltabelle festgelegt wird, und dem Zeitpunkt, zu dem die Änderung in der zugehörigen Änderungstabelle angezeigt wird. Obwohl diese Latenz in der Regel gering ist, ist es dennoch wichtig, sich daran zu erinnern, dass Änderungsdaten erst verfügbar sind, wenn der Erfassungsprozess die zugehörigen Protokolleinträge verarbeitet hat.

Capture instance

Obwohl es üblich ist, dass das Gültigkeitsintervall der Datenbank und das Gültigkeitsintervall der einzelnen Erfassungsinstanzen übereinstimmen, ist dies nicht immer richtig. 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 hohe Endpunkt des Extraktionsintervalls rechts neben dem 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, schlagen die Abfragefunktionen der Änderungsdatenerfassung fehl.

Behandeln von Änderungen an der Quelltabelle

Die Berücksichtigung von Spaltenänderungen in den nachverfolgten Quelltabellen ist für Downstreamconsumer schwierig. Die Aktivierung der Änderungsdatenerfassung für eine Quelltabelle verhindert zwar nicht, dass solche DDL-Änderungen auftreten, aber die Änderungsdatenerfassung trägt dazu bei, die Auswirkungen auf Die Verbraucher zu verringern, indem die übermittelten Resultsets, die über die API zurückgegeben werden, unverändert bleiben, 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 eine feste Spaltenstrukturänderungstabelle 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 null-Werte für die Spalte in den nachfolgenden Änderungseinträgen angegeben. Wenn eine vorhandene Spalte jedoch eine Änderung ihres Datentyps durchläuft, wird die Änderung an die Änderungstabelle weitergegeben, um sicherzustellen, dass der Erfassungsmechanismus keinen Datenverlust in nachverfolgten Spalten fü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 wird die Form der aktuellen Aufzeichnungsinstanz beibehalten, wenn DDL-Änderungen an der verbundenen Quelltabelle vorgenommen werden. Es ist jedoch möglich, eine zweite Erfassungsinstanz für die Tabelle zu erstellen, die die neue Spaltenstruktur widerspiegelt. Hierdurch 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.

Hinweis

Einer Quelltabelle können maximal zwei Aufzeichnungsinstanzen gleichzeitig zugeordnet 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 zum Erfassen von Änderungsdaten erfolgt automatisch, wenn sich der Replikationsstatus einer Datenbank für die Änderungsdatenerfassung ändert.

Hinweis

In SQL Server und Azure SQL Managed Instance muss in beiden Instanzen der Aufzeichnungslogik der SQL Server-Agent ausgeführt werden, damit der Prozess ausgeführt werden kann.

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.

Hinweis

Wenn eine Datenbank für Change Data Capture aktiviert ist, wird, auch wenn als Wiederherstellungsmodus die einfache Wiederherstellung festgelegt ist, der Protokollkürzungspunkt nicht weiter verschoben, bevor alle für die Aufzeichnung markierten Änderungen vom Aufzeichnungsprozess erfasst wurden. Falls der Aufzeichnungsprozess nicht ausgeführt wird und Änderungen erfasst werden müssen, wird das Protokoll beim Ausführen von CHECKPOINT nicht gekürzt.

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

Einer Datenbank, für die Change Data Capture aktiviert ist, werden in der Regel zwei SQL Server -Agentaufträge zugeordnet: ein Aufzeichnungsauftrag zur Auffüllung der Datenbank-Änderungstabellen und ein Cleanupauftrag für die Bereinigung der Änderungstabellen. 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 für die Ä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 Bereinigungsauftrag wird täglich um 2 Uhr ausgeführt. Änderungstabelleneinträge werden 4320 Minuten oder 3 Tage lang beibehalten und maximal 5.000 Einträge mit einer einzelnen delete-Anweisung entfernt.

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 werden, werden erst wirksam, wenn der Auftrag beendet und neu gestartet wird.

Zwei zusätzliche gespeicherte Prozeduren ermöglichen das Starten und Beenden der Change Data Capture-Agentaufträge: sys.sp_cdc_start_job und sys.sp_cdc_stop_job.

Hinweis

Beim Starten und Beenden des Aufzeichnungsauftrags gehen keine Änderungsdaten verloren. Es wird lediglich verhindert, dass der Aufzeichnungsprozess das Protokoll aktiv nach Änderungseinträgen durchsucht, die in die Änderungstabellen eingefügt werden können. Um die zusätzliche Last durch die Protokollscanvorgänge während der Spitzenlastzeiten zu vermeiden, können Sie den Aufzeichnungsauftrag beenden und dann wieder starten, wenn der Ressourcenbedarf sinkt.

Beide SQL Server -Agentaufträ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 Änderung der 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.

Hinweis

In Azure SQL-Datenbank werden die Agent-Aufträge durch einen Planer ersetzt, der die Erfassung und Bereinigung automatisch ausführt.

CDC-Erfassung und -Bereinigung in Azure SQL-Datenbank

In Azure SQL-Datenbank nimmt ein Change Data Capture-Planer den Platz des SQL Server-Agents ein, der gespeicherte Prozeduren aufruft, um die regelmäßige Erfassung und Bereinigung der Change Data Capture-Tabellen zu starten.

Der Planer führt die Erfassung und Bereinigung in SQL-Datenbank ohne jegliche externe Abhängigkeit hinsichtlich Zuverlässigkeit oder Leistung automatisch aus. Benutzer haben weiterhin die Möglichkeit, die Erfassung und Bereinigung bei Bedarf manuell mithilfe der Prozeduren sp_cdc_scan und sp_cdc_cleanup_change_tables auszuführen. Der CDC-Erfassungsauftrag wird alle 20 Sekunden ausgeführt, und der Bereinigungsauftrag wird stündlich ausgeführt.

Azure SQL-Datenbank verfügt über zwei dynamische Verwaltungssichten, mit denen Sie Change Data Capture überwachen können: sys.dm_cdc_log_scan_sessions und sys.dm_cdc_errors.

Sortierungsunterschiede

Es ist wichtig, sich einer Situation bewusst zu sein, in der Sie unterschiedliche Sortierungen zwischen der Datenbank und den Spalten einer Tabelle haben, die für die Änderungsdatenerfassung konfiguriert sind. CDC verwendet einen Zwischenspeicher, um Tabellen aufzufüllen. Wenn in einer Tabelle CHAR- oder VARCHAR-Spalten enthalten sind, deren Sortierung von der Datenbanksortierung abweicht, und wenn in diesen Spalten Zeichen enthalten sind, bei denen es sich nicht um ASCII-Zeichen handelt (z.B. Doppelbyte-Zeichensätze), kann CDC möglicherweise die geänderten Daten nicht speichern, damit diese mit den Daten in der Basistabelle übereinstimmen. Dies liegt daran, dass den Zwischenspeichervariablen keine Sortierungen zugeordnet sein können.

Ziehen Sie einen der folgenden Ansätze in Betracht, um sicherzustellen, dass CDC-Daten mit den Daten in der Basistabelle übereinstimmen:

• Verwenden Sie die Datentypen NCHAR oder NVARCHAR für Spalten, die Daten enthalten, bei denen es sich nicht um ASCII-Daten handelt.

• Stattdessen können Sie auch die Spalten und die Datenbank in derselben Reihenfolge sortieren.

Wenn Sie beispielsweise über eine Datenbank verfügen, die eine Sortierung von SQL_Latin1_General_CP1_CI_AS verwendet, betrachten Sie die folgende Tabelle:

CREATE TABLE T1( 
     C1 INT PRIMARY KEY, 
     C2 VARCHAR(10) collate Chinese_PRC_CI_AI)

CDC kann möglicherweise die Binärdaten für die Spalte C2 nicht speichern, da deren Sortierung von dieser Sortierung abweicht (Chinese_PRC_CI_AI). Verwenden Sie NVARCHAR, um dieses Problem zu vermeiden:

CREATE TABLE T1( 
     C1 INT PRIMARY KEY, 
     C2 NVARCHAR(10) collate Chinese_PRC_CI_AI --Unicode data type, CDC works well with this data type
     )

Erforderliche Berechtigungen

Zum Aktivieren des CDC-Features für SQL Server oder Azure SQL Managed Instance sind Systemadministratorberechtigungen erforderlich. Zum Aktivieren des CDC-Features für Azure SQL-Datenbank ist die db_owner-Rolle erforderlich.

Allgemeine Richtlinien

Damit Change Data Capture (CDC) ordnungsgemäß funktioniert, sollten Sie keine CDC-Metadaten wie CDC-Schema, Änderungstabellen, gespeicherte CDC-Systemprozeduren, Standardbenutzerberechtigungen cdc (sys.database_principals) oder Benutzer umbenennen cdc .

Alle Objekte in sys.objects , deren is_ms_shipped Eigenschaft auf festgelegt ist, 1 sollten nicht geändert werden.

SELECT    name AS object_name   
        ,SCHEMA_NAME(schema_id) AS schema_name  
        ,type_desc  
        ,is_ms_shipped  
FROM sys.objects 
WHERE is_ms_shipped= 1 AND SCHEMA_NAME(schema_id) = 'cdc'

Bekannte Einschränkungen und Probleme

Dies ist die Liste der bekannten Einschränkungen und Probleme mit Change Data Capture (CDC).

Linux
CDC wird jetzt für SQL Server 2017 unter Linux ab CU18 und für SQL Server 2019 unter Linux unterstützt.

ColumnStore-Indizes
Die Änderungsdatenerfassung kann für Tabellen mit einem gruppierten Columnstore-Index nicht aktiviert werden. Ab SQL Server 2016 kann das Feature für Tabellen mit einem nicht gruppierten Columnstore-Index aktiviert werden.

Partitionswechsel mit Variablen
Die Verwendung von Variablen mit Partitionswechsel für Datenbanken oder Tabellen mit Change Data Capture (CDC) wird für die ALTER TABLE ... SWITCH TO ... PARTITION ... Anweisung nicht unterstützt. Weitere Informationen finden Sie unter Einschränkungen zu Partitionswechseln.

Verfügbarkeit von CDC in Azure SQL Datenbanken
CDC kann nur für Die Datenbankebenen S3 und höher aktiviert werden. Unterkern (Basic, S0, S1, S2) Azure SQL Datenbanken werden für CDC nicht unterstützt.

Dbcopy von Datenbankebenen über S3 mit aktiviertem CDC für einen Unterkern-SLO behält derzeit die CDC-Artefakte bei, aber CDC-Artefakte können in Zukunft entfernt werden.

Erfassungs- und Bereinigungsanpassung für Azure SQL Datenbanken
Das Konfigurieren der Häufigkeit der Erfassungs- und Bereinigungsprozesse für CDC in Azure SQL Datenbanken ist nicht möglich. Die Erfassung und Bereinigung werden automatisch vom Planer ausgeführt.

Berechnete Spalten
CDC unterstützt die Werte für berechnete Spalten nicht, auch wenn die berechnete Spalte als persistent definiert ist. Berechnete Spalten, die in einer Erfassungsinstanz enthalten sind, weisen immer den Wert auf NULL. Dieses Verhalten ist kein Fehler, sondern beabsichtigt.

Point-in-Time-Wiederherstellung
Wenn Sie CDC für Ihre Datenbank als Microsoft Azure Active Directory -Benutzer (Azure AD) aktivieren, ist es nicht möglich, die Point-in-Time-Wiederherstellung (Point-in-Time, PITR) auf einen Subcore-SLO zu verwenden. Es wird empfohlen, die Datenbank mit dem Quell- oder höheren SLO wiederherzustellen und dann bei Bedarf CDC zu deaktivieren.

Microsoft Azure Active Directory (Azure AD)
Wenn Sie eine Datenbank in Azure SQL Database as a Microsoft Azure Active Directory (Azure AD)-Benutzer erstellen und die Änderungsdatenerfassung (CDC) darauf aktivieren, kann ein SQL-Benutzer (z. B. auch nicht die Sysadmin-Rolle) CDC-Artefakte deaktivieren/ändern. Ein anderer Azure AD-Benutzer kann CDC jedoch für dieselbe Datenbank aktivieren oder deaktivieren.

Wenn Sie eine Azure SQL-Datenbank als SQL-Benutzer erstellen, funktioniert das Aktivieren/Deaktivieren der Änderungsdatenerfassung als Azure AD-Benutzer ebenfalls nicht.

Drastische Protokollkürzung
Beachten Sie beim Aktivieren der Änderungsdatenerfassung (Change Data Capture, CDC) für Azure SQL-Datenbank oder SQL Server, dass die aggressive Protokollabschneidungsfunktion der beschleunigten Datenbankwiederherstellung (ADR) deaktiviert ist. Dies liegt daran, dass der CDC-Scan auf das Datenbanktransaktionsprotokoll zugreift. Aktive Transaktionen behalten weiterhin die Transaktionsprotokollabkürzung bei, bis die Transaktion Commits und CDC-Überprüfung aufgeholt wird oder die Transaktion abgebrochen wird. Dies kann dazu führen, dass das Transaktionsprotokoll mehr als üblich füllt und überwacht werden sollte, damit sich das Transaktionsprotokoll nicht füllt.

CDC schlägt nach ALTER COLUMN zu VARCHAR und VARBINARY fehl
Wenn der Datentyp einer Spalte in einer CDC-fähigen Tabelle von TEXT in VARCHAR oder IMAGE in VARBINARY geändert wird und eine vorhandene Zeile in einen Wert außerhalb der Zeile aktualisiert wird. Nach dem Update führt der CDC-Scan zu Fehlern.

Das Aktivieren von CDC schlägt bei Azure SQL mit Microsoft Azure Active Directory erstellten Datenbank (Azure AD) fehl.
Das Aktivieren von CDC schlägt fehl, wenn Sie eine Datenbank in Azure SQL Database as a Microsoft Azure Active Directory (Azure AD)-Benutzer erstellen und CDC nicht aktivieren, dann die Datenbank wiederherstellen und CDC für die wiederhergestellte Datenbank aktivieren.

Gehen Sie folgendermaßen vor, um das Problem zu beheben:

• Anmelden als Azure AD-Administrator des Servers
• Führen Sie den ALTER AUTHORIZATION-Befehl für die Datenbank aus:

ALTER AUTHORIZATION ON DATABASE::[<restored_db_name>] TO [<azuread_admin_login_name>];

EXEC sys.sp_cdc_enable_db

Der Versuch, CDC zu aktivieren, schlägt fehl, wenn das benutzerdefinierte Schema oder der Benutzer namens cdc in der Datenbank bereits vorhanden ist.
Wenn Sie CDC für die Datenbank aktivieren, werden ein neues Schema und ein neuer Benutzer mit dem Namen cdcerstellt. Daher wird es nicht empfohlen, ein benutzerdefiniertes Schema oder einen Benutzer mit dem Namen cdcmanuell zu erstellen, da es für die Systemverwendung reserviert ist.
Wenn Sie ein benutzerdefiniertes Schema oder einen Benutzer namens cdc in Ihrer Datenbank manuell definiert haben, der nicht mit CDC verknüpft ist, kann die gespeicherte Systemprozedur sys.sp_cdc_enable_db CDC für die Datenbank mit der folgenden Fehlermeldung nicht aktivieren.

Die Datenbank <database_name> kann nicht für Change Data Capture aktiviert werden, da in der aktuellen Datenbank bereits ein Datenbankbenutzer mit dem Namen "cdc" oder ein Schema mit dem Namen "cdc" vorhanden ist. Diese Objekte sind exklusiv für Change Data Capture erforderlich. Löschen Sie den Benutzer bzw. das Schema, oder benennen Sie ihn bzw. es um, und wiederholen Sie den Vorgang.

So lösen Sie das Problem:

• Löschen Sie das leere cdc-Schema und den leeren cdc-Benutzer manuell. Anschließend kann CDC erfolgreich für die Datenbank aktiviert werden.

Importieren einer Datenbank mithilfe von Import/Export- und Extract/Publish-Vorgängen auf Datenebene
Wenn Sie für CDC-fähige SQL-Datenbanken SqlPackage, SSDT oder andere SQL-Tools zum Importieren/Exportieren oder Extrahieren/Veröffentlichen verwenden, werden das Schema und der cdc Benutzer in der neuen Datenbank ausgeschlossen. Zusätzliche CDC-Objekte, die nicht in import/Export- und Extract/Deploy-Vorgängen enthalten sind, sind die Tabellen, die als is_ms_shipped=1 in sys.objects gekennzeichnet sind.

Selbst wenn CDC nicht aktiviert ist und Sie ein benutzerdefiniertes Schema oder einen benutzerspezifischen Namen cdc in Ihrer Datenbank definiert haben, der auch in Import/Export- und Extract/Deploy-Vorgängen zum Importieren/Einrichten einer neuen Datenbank ausgeschlossen wird.

Siehe auch

Nachverfolgen von Datenänderungen (SQL Server)
Aktivieren und Deaktivieren der Änderungsdatenerfassung (SQL Server)
Arbeiten mit Änderungsdaten (SQL Server)
Verwalten und Überwachen der Änderungsdatenerfassung (SQL Server)
Temporale Tabellen