Arbeiten mit Änderungsdaten (SQL Server)

Änderungsdaten werden zur Änderung von Datenerfassungskunden über Tabellenwertfunktionen (TVFs) zur Verfügung gestellt. Alle Abfragen dieser Funktionen erfordern zwei Parameter, um den Bereich von Log Sequence Numbers (LSNs) zu definieren, die bei der Entwicklung des zurückgegebenen Resultsets berücksichtigt werden können. Sowohl die oberen als auch die unteren LSN-Werte, die das Intervall begrenzen, werden als innerhalb des Intervalls eingeschlossen betrachtet.

Es werden mehrere Funktionen bereitgestellt, um geeignete LSN-Werte für die Abfrage eines TVF zu ermitteln. Die Funktion sys.fn_cdc_get_min_lsn gibt den kleinsten LSN zurück, der einem Gültigkeitsintervall der Erfassungsinstanz zugeordnet ist. Das Gültigkeitsintervall ist das Zeitintervall, für das Änderungsdaten derzeit für die Erfassungsinstanzen verfügbar sind. Die Funktion sys.fn_cdc_get_max_lsn gibt den größten LSN im Gültigkeitsintervall zurück. Die Funktionen sys.fn_cdc_map_time_to_lsn und sys.fn_cdc_map_lsn_to_time stehen zur Verfügung, um LSN-Werte auf einer herkömmlichen Zeitachse zu platzieren. Da änderungsdatenerfassung geschlossene Abfrageintervalle verwendet, ist es manchmal erforderlich, den nächsten LSN-Wert in einer Sequenz zu generieren, um sicherzustellen, dass Änderungen in aufeinander folgenden Abfragefenstern nicht dupliziert werden. Die Funktionen sys.fn_cdc_increment_lsn und sys.fn_cdc_decrement_lsn sind nützlich, wenn eine inkrementelle Anpassung an einen LSN-Wert erforderlich ist.

Überprüfen von LSN-Grenzen

Es wird empfohlen, die LSN-Begrenzungen zu überprüfen, die vor deren Verwendung in einer TVF-Abfrage verwendet werden sollen. Null-Endpunkte oder Endpunkte, die außerhalb des Gültigkeitsintervalls für eine Aufnahmeinstanz liegen, erzwingen, dass ein Fehler durch eine Änderungsdatenaufnahme TVF zurückgegeben wird.

Der folgende Fehler wird beispielsweise für eine Abfrage für alle Änderungen zurückgegeben, wenn ein Parameter, der zum Definieren des Abfrageintervalls verwendet wird, ungültig ist oder außerhalb des Gültigen Bereichs liegt, oder die Zeilenfilteroption ungültig ist.

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ...

Der für eine net changes Abfrage zurückgegebene entsprechende Fehler lautet wie folgt:

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ...

Autorisierungsfehler führen zu Fehlern bei der Abfrage nach allen Änderungen, wie gezeigt:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'fn_cdc_get_all_changes_...', database 'MyDB', schema 'cdc'.

Das gleiche gilt beim Abfragen von Nettoänderungen:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object fn_cdc_get_net_changes_...', database 'MyDB', schema 'cdc'.

Sehen Sie sich die Vorlage "Enumerate Net Changes Using TRY CATCH" an, um zu erfahren, wie diese bekannten TVF-Fehler abgefangen werden können und wie aussagekräftigere Informationen über den Fehler bereitgestellt werden.

Abfragefunktionen

Abhängig von den Merkmalen der nachverfolgten Quelltabelle und der Art und Weise, in der die Aufnahmeinstanz konfiguriert ist, werden entweder ein oder zwei TVFs zum Abfragen von Änderungsdaten generiert.

• Die Funktion cdc.fn_cdc_get_all_changes_<capture_instance> gibt alle Änderungen zurück, die für das angegebene Intervall aufgetreten sind. Diese Funktion wird immer generiert. Einträge werden immer sortiert zurückgegeben, zuerst nach dem Transaktions-Commit-LSN der Änderung und dann nach einem Wert, der die Änderung innerhalb ihrer Transaktion sequenziert. Abhängig von der ausgewählten Zeilenfilteroption wird entweder die letzte Zeile für die Aktualisierung zurückgegeben (Zeilenfilteroption "alle") oder sowohl die neuen als auch die alten Werte werden bei der Aktualisierung zurückgegeben (Zeilenfilteroption "alle aktualisieren alt").

• Die Funktion cdc.fn_cdc_get_net_changes_<capture_instance> wird generiert, wenn der Parameter @supports_net_changes auf 1 festgelegt wird, wenn die Quelltabelle aktiviert ist.

  Hinweis

  Diese Option wird nur unterstützt, wenn die Quelltabelle über einen definierten Primärschlüssel verfügt oder wenn der Parameter @index_name verwendet wurde, um einen eindeutigen Index zu identifizieren.

  Die Netchanges-Funktion gibt eine Änderung pro geänderter Quelltabellenzeile zurück. Wenn während des angegebenen Intervalls mehrere Änderungen für die Zeile protokolliert werden, spiegeln die Spaltenwerte den endgültigen Inhalt der Zeile wider. Um den Vorgang, der zum Aktualisieren der Zielumgebung erforderlich ist, korrekt zu identifizieren, muss der TVF sowohl den anfänglichen Vorgang in der Zeile während des Intervalls als auch den endgültigen Vorgang in der Zeile berücksichtigen. Wenn die Zeilenfilteroption "all" angegeben wird, werden die von einer net changes Abfrage zurückgegebenen Vorgänge entweder eingefügt, gelöscht oder aktualisiert (neue Werte). Diese Option gibt die Aktualisierungsmaske immer als NULL zurück, da kosten für das Berechnen einer Aggregatmaske anfallen. Wenn Sie eine Aggregatmaske benötigen, die alle Änderungen an einer Zeile widerspiegelt, verwenden Sie die Option "Alle mit Maske". Wenn die nachgeschaltete Verarbeitung nicht erfordert, dass Einfügungen und Aktualisierungen unterschieden werden, verwenden Sie die Option "Alle mit Zusammenführung". In diesem Fall übernimmt der Vorgangswert nur zwei Werte: 1 für Löschvorgänge und 5 für einen Vorgang, der entweder ein Einfügen oder eine Aktualisierung sein kann. Diese Option beseitigt die zusätzliche Verarbeitung, die erforderlich ist, um zu bestimmen, ob der abgeleitete Vorgang ein Einfügen oder eine Aktualisierung sein soll, und kann die Leistung der Abfrage verbessern, wenn diese Differenzierung nicht erforderlich ist.

Die Aktualisierungsmaske, die von einer Abfragefunktion zurückgegeben wird, ist eine kompakte Darstellung, die alle Spalten identifiziert, die in einer Zeile mit Änderungsdaten geändert wurden. In der Regel sind diese Informationen nur für eine kleine Teilmenge der erfassten Spalten erforderlich. Funktionen stehen zur Verfügung, um Informationen aus der Maske in einer Form zu extrahieren, die direkt von Anwendungen verwendet werden kann. Die Funktion sys.fn_cdc_get_column_ordinal gibt die Ordnungsposition einer benannten Spalte für eine bestimmte Aufnahmeinstanz zurück, während die Funktion sys.fn_cdc_is_bit_set die Parität des Bits in der bereitgestellten Maske basierend auf dem Ordinal zurückgibt, das im Funktionsaufruf übergeben wurde. Zusammen ermöglichen diese beiden Funktionen, dass Informationen aus der Aktualisierungsmaske effizient extrahiert und mit der Anforderung für Änderungsdaten zurückgegeben werden. Sehen Sie sich die Vorlage "Enumerate Net Changes Using All With Mask" für eine Demonstration an, wie diese Funktionen verwendet werden.

Abfragefunktionsszenarien

In den folgenden Abschnitten werden allgemeine Szenarien zum Abfragen von Änderungsdatenerfassungsdaten mithilfe der Abfragefunktionen cdc.fn_cdc_get_all_changes_<capture_instance> und cdc.fn_cdc_get_net_changes_<capture_instance> beschrieben.

Abfragen aller Änderungen innerhalb des Gültigkeitsintervalls der Erfassungsinstanz

Die einfachste Anforderung für Änderungsdaten ist eine Anforderung, die alle aktuellen Änderungsdaten im Gültigkeitsintervall einer Erfassungsinstanz zurückgibt. Um diese Anforderung zu stellen, bestimmen Sie zuerst die unteren und oberen LSN-Grenzen des Gültigkeitsintervalls. Verwenden Sie dann diese Werte, um die Parameter @from_lsn und @to_lsn zu identifizieren, die an die Abfragefunktion cdc.fn_cdc_get_all_changes_<capture_instance> oder cdc.fn_cdc_get_net_changes_<capture_instance> übergeben werden. Verwenden Sie die Funktion sys.fn_cdc_get_min_lsn , um die untere Grenze abzurufen, und sys.fn_cdc_get_max_lsn , um die obere Grenze zu erhalten. In der Vorlage "Enumerate All Changes for the Valid Range" finden Sie ein Beispielcode, um alle aktuellen gültigen Änderungen mithilfe der Abfragefunktion cdc.fn_cdc_get_all_changes_<capture_instance> abzufragen. Ein ähnliches Beispiel für die Verwendung der Funktion cdc.fn_cdc_get_net_changes_<capture_instance> finden Sie in der Vorlage "Enumerate Net Changes for the Valid Range".

Abfragen aller neuen Änderungen seit dem letzten Satz von Änderungen

Bei typischen Anwendungen ist das Abfragen von Änderungsdaten ein fortlaufender Prozess, bei dem regelmäßig Abfragen zu allen seit der letzten Anforderung aufgetretenen Änderungen durchgeführt werden. Für solche Abfragen können Sie die Funktion sys.fn_cdc_increment_lsn verwenden, um die untere Grenze der aktuellen Abfrage von der oberen Grenze der vorherigen Abfrage abzuleiten. Diese Methode stellt sicher, dass keine Zeilen wiederholt werden, da das Abfrageintervall immer als geschlossenes Intervall behandelt wird, in dem beide Endpunkte im Intervall enthalten sind. Verwenden Sie dann die Funktion sys.fn_cdc_get_max_lsn , um den High-End-Punkt für das neue Anforderungsintervall abzurufen. Bitte konsultieren Sie die Vorlage "Alle Änderungen seit der letzten Anfrage auflisten", um systematisch das Abfragefenster zu verschieben und alle Änderungen seit der vorherigen Anfrage abzurufen.

Abfragen aller neuen Änderungen bis zum jetzigen Zeitpunkt

Eine typische Einschränkung, die für die von einer Abfragefunktion zurückgegebenen Änderungen platziert wird, besteht darin, nur die Änderungen einzuschließen, die zwischen der vorherigen Anforderung bis zum aktuellen Datum und der aktuellen Uhrzeit aufgetreten sind. Wenden Sie für diese Anfrage die Funktion sys.fn_cdc_increment_lsn auf den @from_lsn-Wert an, der in der vorherigen Anforderung verwendet wurde, um die untere Grenze zu bestimmen. Da die obere Grenze für das Zeitintervall als bestimmter Zeitpunkt ausgedrückt wird, muss sie in einen LSN-Wert konvertiert werden, bevor sie von einer Abfragefunktion verwendet werden kann. Bevor der Datetime-Wert in einen entsprechenden LSN-Wert konvertiert werden kann, müssen Sie sicherstellen, dass der Erfassungsprozess alle Änderungen verarbeitet hat, die bis zur angegebenen oberen Grenze bestätigt wurden. Dies ist erforderlich, um sicherzustellen, dass alle qualifizierten Änderungen auf die Änderungstabelle übertragen wurden. Eine Möglichkeit hierzu besteht darin, eine Warteschleife zu strukturieren, die regelmäßig überprüft, ob der aktuelle maximale Commit lsn, der für eine Datenbankänderungstabelle aufgezeichnet wurde, die gewünschte Endzeit des Anforderungsintervalls überschreitet.

Nachdem die Verzögerungsschleife überprüft hat, ob der Erfassungsprozess bereits alle relevanten Protokolleinträge verarbeitet hat, verwenden Sie die Funktion sys.fn_cdc_map_time_to_lsn , um den neuen High-End-Punkt zu ermitteln, der als LSN-Wert ausgedrückt wird. Um sicherzustellen, dass alle Einträge, die im angegebenen Zeitraum abgeschlossen wurden, abgerufen werden, rufen Sie die Funktion sys.fn_cdc_map_time_to_lsn auf, und verwenden Sie die Option "größter Wert, der kleiner oder gleich ist".

Die Vorlage Enumerate All Changes Up Until Now veranschaulicht, wie die vorherige Strategie zum Abfragen von Änderungsdaten verwendet wird.

Hinzufügen einer Commitzeit zu einem Resultset für alle Änderungen

Die Commitzeit jeder Transaktion mit einem zugeordneten Eintrag in einer Datenbankänderungstabelle ist in der Tabelle cdc.lsn_time_mapping verfügbar. Indem Sie den in einer Anforderung zurückgegebenen Wert __$start_lsn für alle Änderungen mit dem Wert start_lsn eines Eintrags in der Tabelle cdc.lsn_time_mapping verknüpfen, können Sie die tran_end_time zusammen mit den Änderungsdaten zurückgeben, um die Änderung mit der Commit-Zeit der Transaktion an der Quelle zu versehen. Die Vorlage append commit time to All Changes Result Set veranschaulicht, wie diese Verknüpfung ausgeführt wird.

Verknüpfen von Änderungsdaten mit anderen Daten aus derselben Transaktion

Gelegentlich ist es hilfreich, Änderungsdaten mit anderen Informationen zu der Transaktion zu verknüpfen, wenn sie an der Quelle zugesichert wurde. Die tran_begin_lsn Spalte in der Tabelle cdc.lsn_time_mapping enthält die informationen, die zum Ausführen einer solchen Verknüpfung erforderlich sind. Wenn die Aktualisierung der Quelle erfolgt, muss der Wert für database_transaction_begin_lsn aus der dynamischen Systemansicht sys.dm_tran_database_transactions zusammen mit allen anderen Informationen gespeichert werden, die mit den Änderungsdaten verknüpft werden sollen. Verwenden Sie die Funktion fn_convertnumericlsntobinary, um die werte database_transaction_begin_lsn und tran_begin_lsn zu vergleichen. Der Code zum Erstellen dieser Funktion ist in der Vorlage "Funktion erstellen" fn_convertnumericlsntobinary verfügbar. Die Vorlage gibt alle Änderungen mit einer bestimmten tran_begin_lsn zurück, die veranschaulicht, wie die Verknüpfung wirksam wird.

Abfragen mithilfe von Datetime Wrapper-Funktionen

Ein typisches Anwendungsszenario zum Abfragen von Änderungsdaten besteht darin, Änderungsdaten regelmäßig mithilfe eines gleitenden Fensters anzufordern, das durch Datumstimewerte begrenzt ist. Für diese Verbraucherklasse stellt die Änderungsdatenerfassung die gespeicherte Prozedur sys.sp_cdc_generate_wrapper_function bereit, die Skripts generiert, um benutzerdefinierte Wrapperfunktionen für die Abfragefunktionen der Änderungsdatenerfassung zu erstellen. Mit diesen benutzerdefinierten Wrappern kann das Abfrageintervall als Datetime-Paar ausgedrückt werden.

Aufrufoptionen für die gespeicherte Prozedur ermöglichen das Generieren von Wrappern für alle Aufnahmeinstanzen, auf die der Aufrufer Zugriff hat, oder nur für eine angegebene Aufnahmeinstanz. Zu den unterstützten Optionen gehört auch die Möglichkeit anzugeben, ob der Endpunkt des Erfassungsintervalls offen oder geschlossen sein soll, welche der verfügbaren erfassten Spalten in den Ergebnisdatensatz aufgenommen werden sollen und welche der enthaltenen Spalten Aktualisierungskennzeichnungen haben sollen. Die Prozedur gibt ein Resultset mit zwei Spalten zurück: den generierten Funktionsnamen, der von der Erfassungsinstanz abgeleitet werden kann, und die Erstellungsanweisung für die gespeicherte Wrapper-Prozedur. Die Funktion zum Einbetten der Änderungsabfrage wird stets generiert. Wenn der @supports_net_changes Parameter beim Erstellen der Aufnahmeinstanz festgelegt wurde, wird auch die Funktion, die die Nettoänderungsfunktion umschließt, generiert.

Es liegt in der Verantwortung des Anwendungs-Designers, die gespeicherte Prozedur der Skriptgenerierung aufzurufen, um die Erstellungsanweisungen für die gespeicherten Prozeduren des Wrappers zu generieren und die resultierenden Erstellungsskripts zum Erstellen der Funktionen auszuführen. Dies tritt nicht automatisch auf, wenn eine Aufnahmeinstanz erstellt wird.

Datetime-Wrapper gehören dem Benutzer und werden nicht im Standardschema des Aufrufers erstellt. Die generierte Funktion eignet sich ohne Änderung für die meisten Benutzer. Weitere Anpassungen können jedoch immer vor dem Erstellen der Funktion auf das generierte Skript angewendet werden.

Der Name der Funktion, um alle Änderungsabfragen einzuschließen, ist fn_all_changes_, gefolgt vom Namen der Erfassungsinstanz. Das Präfix, das für den Net Changes Wrapper verwendet wird, ist fn_net_changes_. Beide Funktionen verwenden drei Argumente, genau wie ihre zugeordneten Änderungsdatenerfassungs-TVFs. Das Abfrageintervall für die Wrapper ist jedoch durch zwei Datetime-Werte anstelle von zwei LSN-Werten gebunden. Der @row_filter_option Parameter für beide Funktionssätze ist identisch.

Die generierten Wrapperfunktionen unterstützen die folgende Konvention zum systematischen Durchlaufen der Zeitachse für die Änderungsdatenerfassung: Es wird erwartet, dass der @end_time Parameter des vorherigen Intervalls als @start_time Parameter des nachfolgenden Intervalls verwendet wird. Die Wrapperfunktion übernimmt die Zuordnung der Datetime-Werte zu LSN-Werten und stellt sicher, dass keine Daten verloren gehen oder wiederholt werden, wenn diese Konvention befolgt wird.

Die Wrapper können generiert werden, um entweder eine geschlossene obere Grenze oder eine offene obere Grenze im angegebenen Abfragefenster zu unterstützen. Das heißt, der Aufrufer kann angeben, ob Einträge mit einer Commit-Zeit gleich der oberen Grenze des Extraktionsintervalls innerhalb des Intervalls eingeschlossen werden sollen. Standardmäßig ist die obere Grenze enthalten.

Während die generierten Abfrage-TVFs fehlschlagen, wenn ein NULL-Wert für den @from_lsn Wert oder den @to_lsn Wert angegeben wird, verwenden die Datetime-Wrapperfunktionen NULL, damit die Datetime-Wrapper alle aktuellen Änderungen zurückgeben können. Wenn null als unterer Endpunkt des Abfragefensters an den Datetime-Wrapper übergeben wird, wird der untere Endpunkt des Gültigkeitsintervalls der Erfassungsinstanz in der zugrunde liegenden SELECT-Anweisung verwendet, die auf die Abfrage-TVF angewendet wird. Ähnlich wird, wenn NULL als oberen Endpunkt des Abfragefensters übergeben wird, der obere Endpunkt des Gültigkeitsintervalls der Aufnahmeinstanz beim Auswählen aus der Abfrage-TVF verwendet.

Die von einer Wrapperfunktion zurückgegebene Ergebnismenge enthält alle angeforderten Spalten, gefolgt von einer Vorgangsspalte, die in ein oder zwei Zeichen umcodiert wird, um den Vorgang zu identifizieren, der mit der Zeile verbunden ist. Wenn Aktualisierungskennzeichnungen angefordert wurden, werden sie als Bitspalten nach dem Vorgangscode in der im @update_flag_list Parameter angegebenen Reihenfolge angezeigt. Informationen zu den Aufrufoptionen zum Anpassen der generierten Datetime-Wrapper finden Sie unter sys.sp_cdc_generate_wrapper_function (Transact-SQL).

Die Vorlage "Instanziieren eines Wrapper-TVF mit Update-Flag" zeigt, wie eine generierte Wrapperfunktion angepasst wird, um eine Aktualisierungskennzeichnung für eine angegebene Spalte dem von einer Net Changes Query zurückgegebenen Resultset anzufügen. Die Vorlage "Instantiate CDC Wrapper TVFs" für ein Schema zeigt, wie Sie die Datetime Wrapper für die Abfrage-TVFs für alle Aufnahmeinstanzen instanziieren, die für die Quelltabellen in einem bestimmten Datenbankschema erstellt wurden.

Ein Beispiel für die Verwendung eines Datetime-Wrappers zum Abfragen von Änderungsdaten finden Sie in der Vorlage "Abrufen von Nettoänderungen mit Wrapper und Aktualisierungsmarkierungen". Diese Vorlage veranschaulicht, wie Sie net-Änderungen mit einer Wrapperfunktion abfragen, wenn der Wrapper so konfiguriert ist, dass Aktualisierungskennzeichnungen zurückgegeben werden. Beachten Sie, dass die Zeilenfilteroption "alle mit Maske" für die zugrunde liegende Abfragefunktion erforderlich ist, um ein Updateformat ohne Null zurückzugeben. Nullwerte werden sowohl für die Grenzen des unteren als auch für das obere Datumsintervall übergeben, um die Funktion zu signalisieren, dass der niedrige Endpunkt und der High-End-Punkt des Gültigkeitsintervalls für die Aufnahmeinstanz beim Ausführen der zugrunde liegenden LSN-basierten Abfrage verwendet werden. Die Abfrage gibt eine Zeile für jede Änderung an einer Quellzeile zurück, die innerhalb des gültigen Bereichs für die Aufnahmeinstanz aufgetreten ist.

Verwenden der Datetime-Wrapperfunktionen zum Übergang zwischen Aufnahmeinstanzen

Die Änderungsdatenerfassung unterstützt bis zu zwei Erfassungsinstanzen für eine einzelne nachverfolgte Quelltabelle. Die Hauptverwendung dieser Funktion besteht darin, einen Übergang zwischen mehreren Erfassungsinstanzen zu ermöglichen, wenn Änderungen in der Datendefinitionssprache (DDL) an der Quelltabelle vorgenommen werden, um die Anzahl der verfügbaren Spalten zur Nachverfolgung zu erweitern. Beim Übergang zu einer neuen Aufnahmeinstanz besteht eine Möglichkeit zum Schutz höherer Anwendungsebenen vor Änderungen in den Namen der zugrunde liegenden Abfragefunktionen darin, eine Wrapperfunktion zum Umschließen des zugrunde liegenden Aufrufs zu verwenden. Stellen Sie dann sicher, dass der Name der Wrapperfunktion unverändert bleibt. Wenn die Umschaltung erfolgt, kann die alte Wrapperfunktion gelöscht werden, und eine neue Funktion mit demselben Namen erstellt werden, die auf die neuen Abfragefunktionen referenziert. Indem Sie zuerst das generierte Skript so ändern, dass eine Wrapperfunktion mit demselben Namen erstellt wird, können Sie den Umstieg auf eine neue Aufnahmeinstanz vornehmen, ohne dass sich dies auf höhere Anwendungsebenen auswirkt.

Siehe auch

Nachverfolgen von Datenänderungen (SQL Server)
Über Change Data Capture (SQL Server)
Aktivieren und Deaktivieren von Change Data Capture (SQL Server)
Verwalten und Überwachen von Change Data Capture (SQL Server)