Nachverfolgen von Datenänderungen (SQL Server)

Anwendungsbereich: JaSQL Server (alle unterstützten Versionen) JaAzure SQL-Datenbank

SQL Server 2019 (15.x) stellt zwei Funktionen bereit, mit denen Änderungen in einer Datenbank nachverfolgt werden: Change Data Capture und Änderungsnachverfolgung. Mit diesen Funktionen können Anwendungen die DML-Änderungen (Einfüge-, Aktualisierungs- und Löschvorgänge) ermitteln, die an Benutzertabellen in einer Datenbank vorgenommen wurden. Change Data Capture und die Änderungsnachverfolgung können auf derselben Datenbank aktiviert werden, d. h., es sind keine zusätzlichen Überlegungen erforderlich. Informationen zu den Editionen von SQL Server , die Change Data Capture und die Änderungsnachverfolgung unterstützen, finden Sie unter Von den SQL Server 2016-Editionen unterstützte Funktionen. Änderungsnachverfolgung wird von Azure SQL-Datenbankunterstützt. Change Data Capture wird nur in SQL Server und Azure SQL Managed Instance unterstützt.

Vorteile der Verwendung von Change Data Capture oder der Änderungsnachverfolgung

Damit bestimmte Anwendungen effizient ausgeführt werden können, muss eine wichtige Anforderung erfüllt sein: Die Anwendungen müssen in der Lage sein, Daten abzufragen, die in einer Datenbank geändert wurden. Zum Ermitteln von Datenänderungen mussten Anwendungsentwickler normalerweise eine benutzerdefinierte Nachverfolgungsmethode in ihren Anwendungen implementieren, wobei sie eine Kombination von Triggern, Zeitstempelspalten und zusätzlichen Tabellen verwendeten. Die Erstellung solcher Anwendungen ist normalerweise sehr arbeitsintensiv, führt zu Schemaupdates und ist häufig mit hohem Verwaltungsaufwand verbunden.

Die Verwendung von Change Data Capture oder der Änderungsnachverfolgung in Anwendungen zum Nachverfolgen von Änderungen in einer Datenbank (im Gegensatz zur Entwicklung einer benutzerdefinierten Lösung) bietet die folgenden Vorteile:

  • Kürzere Entwicklungszeit. Aufgrund der in SQL Server 2019 (15.x)verfügbaren Funktionalität müssen Sie keine benutzerdefinierte Lösung entwickeln.

  • Schemaänderungen sind nicht erforderlich. Sie müssen weder Spalten noch Trigger hinzufügen oder Seitentabellen erstellen, in denen gelöschte Zeilen nachverfolgt oder Änderungsnachverfolgungsinformationen gespeichert werden, wenn den Benutzertabellen keine Spalten hinzugefügt werden können

  • Integrierter Cleanup-Mechanismus. Der Cleanup für die Änderungsnachverfolgung wird automatisch im Hintergrund ausgeführt. Ein benutzerdefinierter Cleanup für Daten, die in einer Seitentabelle gespeichert werden, ist nicht erforderlich.

  • Funktionen werden bereitgestellt, um Änderungsinformationen abzurufen.

  • Niedriger Aufwand für DML-Vorgänge. Bei der synchronen Änderungsnachverfolgung entsteht immer ein gewisser Aufwand. Allerdings kann dieser Aufwand durch Verwendung der Änderungsnachverfolgung minimiert werden. Der Aufwand ist dabei in vielen Fällen geringer als bei der Verwendung alternativer Lösungen, insbesondere bei solchen Lösungen, die die Verwendung von Triggern erfordern.

  • Die Änderungsnachverfolgung basiert auf Transaktionen, für die ein Commit ausgeführt wurde. Die Reihenfolge der Änderungen wird durch den Commitzeitpunkt der Transaktion bestimmt. Auf diese Weise werden bei umfangreichen und überlappenden Transaktionen zuverlässige Ergebnisse erzielt. Benutzerdefinierte Lösungen, in denen timestamp -Werte verwendet werden, müssen speziell für solche Szenarien entworfen werden.

  • Standardtools sind verfügbar, die Sie zum Konfigurieren und Verwalten verwenden können. SQL Server 2019 (15.x) stellt Standard-DDL-Anweisungen, SQL Server Management Studio, Katalogsichten und Sicherheitsberechtigungen bereit.

Funktionsunterschiede zwischen Change Data Capture und Änderungsnachverfolgung

In der folgenden Tabelle sind die Funktionsunterschiede zwischen Change Data Capture und Änderungsnachverfolgung aufgelistet. Der Nachverfolgungsmechanismus in Change Data Capture umfasst die asynchrone Erfassung der Änderungen aus dem Transaktionsprotokoll, sodass die Änderungen nach Abschluss des jeweiligen DML-Vorgangs verfügbar sind. Der Nachverfolgungsmechanismus bei der Änderungsnachverfolgung umfasst die synchrone Erfassung der Änderungen im Einklang mit den DML-Vorgängen, sodass die Änderungen unmittelbar verfügbar sind.

Funktion Erfassung geänderter Daten Änderungsnachverfolgung
Nachverfolgte Änderungen
DML-Änderungen Ja Ja
Nachverfolgte Informationen
Verlaufsdaten Ja Nein
Ob Spalte geändert wurde Ja Ja
DML-Typ Ja Ja

Change Data Capture

Change Data Capture stellt Änderungsverlaufsinformationen für Benutzertabellen bereit, indem sowohl die Tatsache, dass DML-Änderungen vorgenommen wurden, als auch die geänderten Daten erfasst werden. Die Änderungen werden über einen asynchronen Prozess durch Lesen des Transaktionsprotokolls erfasst, der keine großen Auswirkungen auf die Systemleistung hat.

Wie in der folgenden Abbildung gezeigt, werden die an Benutzertabellen vorgenommenen Änderungen in entsprechenden Änderungstabellen aufgezeichnet. Diese Änderungstabellen stellen eine Übersicht über den Änderungsverlauf dar. Die von SQL Server bereitgestellten Change Data Capture-Funktionen ermöglichen die einfache und systematische Verarbeitung der Änderungsdaten.

Konzeptdarstellung von Change Data Capture

Sicherheitsmodell

In diesem Abschnitt wird das Sicherheitsmodell von Change Data Capture beschrieben.

Konfiguration und Verwaltung
Um Change Data Capture für eine Datenbank aktivieren oder deaktivieren zu können, muss der Benutzer, der sys.sp_cdc_enable_db (Transact-SQL) oder sys.sp_cdc_disable_db (Transact-SQL) aufruft, ein Mitglied der festen Serverrolle sysadmin sein. Zur Aktivierung und Deaktivierung von Change Data Capture auf Tabellenebene muss der Benutzer, der sys.sp_cdc_enable_table (Transact-SQL) und sys.sp_cdc_disable_table (Transact-SQL) aufruft, entweder ein Mitglied der Rolle „sysadmin“ oder ein Mitglied der Datenbankrolle db_owner sein.

Gespeicherte Prozeduren zur Verwaltung von Change Data Capture-Aufträgen können nur von Mitgliedern der Serverrolle sysadmin und der Datenbankrolle db_owner verwendet werden.

Änderungsenumeration und Metadatenabfragen
Für den Zugriff auf die mit einer Änderungsinstanz verbundenen Änderungsdaten muss dem Benutzer die Zugriffsberechtigung für alle aufgezeichneten Spalten der zugeordneten Quelltabelle erteilt werden. Wenn bei Erstellung der Aufzeichnungsinstanz eine Gatingrolle angegeben wird, muss der Aufrufer außerdem Mitglied der angegebenen Gatingrolle sein. Andere allgemeine Change Data Capture-Funktionen für den Zugriff auf Metadaten stehen für alle Datenbankbenutzer mit der Rolle public zur Verfügung. Der Zugriff auf die zurückgegebenen Metadaten wird jedoch in der Regel auch hier durch die Zugriffsberechtigungen auf die zugrunde liegenden Quelltabellen und die Mitgliedschaft in definierten Gatingrollen beschränkt.

Anwendung von DDL-Vorgängen auf Change Data Capture-aktivierte Quelltabellen
Wenn eine Tabelle für Change Data Capture aktiviert ist, können DDL-Vorgänge nur von Mitgliedern der festen Serverrolle sysadmin bzw. von Mitgliedern der Datenbankrolle db_owner oder db_ddladmin auf die Tabelle angewendet werden. Wenn Benutzer, denen die Berechtigung zur Ausführung von DDL-Vorgängen auf die Tabelle explizit erteilt wurde, diese Vorgänge versuchen, wird die Fehlermeldung 22914 zurückgegeben.

Überlegungen zum Datentyp für Change Data Capture

Alle Basisspaltentypen werden von Change Data Capture unterstützt. In der folgenden Tabelle sind jeweils das Verhalten und die Einschränkungen verschiedener Spaltentypen aufgeführt.

Typ der Spalte In Änderungstabellen aufgezeichnete Änderungen Einschränkungen
Spalten mit geringer Dichte Ja Bei Verwendung eines Spaltensatzes wird das Aufzeichnen von Änderungen nicht unterstützt.
Berechnete Spalten Nein Änderungen an berechneten Spalten werden nicht nachverfolgt. Die Spalte wird in der Änderungstabelle mit dem entsprechenden Typ angezeigt, hat aber einen Wert von NULL.
XML Ja Änderungen an einzelnen XML-Elementen werden nicht nachverfolgt.
Timestamp Ja Der Datentyp in der Änderungstabelle wird in Binärformat umgewandelt.
BLOB-Datentypen Ja Das vorherige Image der BLOB-Spalte wird nur gespeichert, wenn die Spalte selbst geändert wird.

Change Data Capture und andere SQL Server-Funktionen

In diesem Abschnitt wird beschrieben, wie die folgenden Funktionen mit Change Data Capture interagieren:

  • Datenbankspiegelung

  • Transaktionsreplikation

  • Datenbankwiederherstellung oder -anfügung

Datenbankspiegelung

Eine Datenbank, die für Change Data Capture aktiviert ist, kann gespiegelt werden. Um sicherzustellen, dass Capture und Cleanup für die Spiegelung automatisch durchgeführt werden, führen Sie folgende Schritte aus:

  1. Stellen Sie sicher, dass für die Spiegelung der SQL Server -Agent ausgeführt wird.

  2. Erstellen Sie den Aufzeichnungsauftrag und den Cleanupauftrag für die Spiegelung, nachdem der Prinzipal einen Failover auf die Spiegelung durchgeführt hat. Verwenden Sie zum Erstellen der Aufträge die gespeicherte Prozedur sys.sp_cdc_add_job (Transact-SQL).

Weitere Informationen zur Datenbankspiegelung finden Sie unter Datenbankspiegelung (SQL Server).

Transaktionsreplikation

Change Data Capture und die Transaktionsreplikation können in einer Datenbank parallel vorhanden sein, allerdings wird die Auffüllung der Änderungstabellen anders behandelt, wenn beide Funktionen aktiviert sind. Change Data Capture und die Transaktionsreplikation verwenden immer dieselbe Prozedur, nämlich sp_replcmds, um die Änderungen aus dem Transaktionsprotokoll auszulesen. Wenn Change-Data-Capture allein aktiviert ist, ruft ein SQL Server -Agentauftrag die Prozedur sp_replcmds auf. Wenn für eine Datenbank beide Funktionen aktiviert sind, ruft der Protokolllese-Agent die Prozedur sp_replcmds auf. Dieser Agent füllt sowohl die Änderungstabellen als auch die Tabellen der Verteilungsdatenbank auf. Weitere Informationen finden Sie unter Replication Log Reader Agent.

Angenommen, Change Data Capture ist für die AdventureWorks2012 -Datenbank aktiviert, und zwei Tabellen sind für die Erfassung aktiviert. Um die Änderungstabellen aufzufüllen, ruft der Capture-Auftrag sp_replcmds auf. Die Datenbank wird für die Transaktionsreplikation aktiviert, und eine Veröffentlichung wird erstellt. Anschließend wird der Protokolllese-Agent für die Datenbank erstellt, und der Erfassungsauftrag wird gelöscht. Der Protokolllese-Agent fährt fort, das Protokoll ab der letzten Protokollfolgenummer zu durchsuchen, für die ein Commit in die Änderungstabelle ausgeführt wurde. Auf diese Weise wird die Datenkonsistenz in den Änderungstabellen sichergestellt. Wenn die Transaktionsreplikation in dieser Datenbank deaktiviert wird, wird der Protokolllese-Agent entfernt und der Aufzeichnungsauftrag neu erstellt.

Hinweis

Falls der Protokolllese-Agent sowohl für Change Data Capture als auch für die Transaktionsreplikation verwendet wird, werden die replizierten Änderungen zuerst in die Verteilungsdatenbank geschrieben. Anschließend werden erfasste Änderungen in die Änderungstabellen geschrieben. Der Commit wird für beide Vorgänge zusammen ausgeführt. Wenn beim Schreiben in die Verteilungsdatenbank eine Latenz auftritt, werden Änderungen in den Änderungstabellen auch erst nach dieser Latenzzeit angezeigt.

Wiederherstellen oder Anfügen einer Datenbank, die für Change Data Capture aktiviert ist

SQL Server verwendet die folgende Logik, um zu ermitteln, ob Change Data Capture nach dem Wiederherstellen oder Anfügen einer Datenbank aktiviert bleibt:

  • Wenn eine Datenbank auf demselben Server mit demselben Datenbanknamen wiederhergestellt wird, bleibt Change Data Capture aktiviert.

  • Wenn eine Datenbank auf einem anderen Server wiederhergestellt wird, wird Change Data Capture standardmäßig deaktiviert, und alle zugehörigen Metadaten werden gelöscht.

    Um Change-Data-Capture beizubehalten, verwenden Sie beim Wiederherstellen der Datenbank die KEEP_CDC -Option. Weitere Informationen zu dieser Option finden Sie unter RESTORE.

  • Wenn eine Datenbank getrennt und an denselben Server oder einen anderen Server angefügt wird, bleibt Change Data Capture aktiviert.

  • Wenn eine Datenbank mit der Option KEEP_CDC an eine andere Edition als die Standard oder Enterprise Edition angefügt oder dafür wiederhergestellt wird, wird der Vorgang blockiert, weil für Change-Data-Capture die SQL Server Standard oder Enterprise Edition erforderlich ist. Die Fehlermeldung 932 wird angezeigt:

    SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.

Sie können sys.sp_cdc_disable_db verwenden, um Change Data Capture aus einer wiederhergestellten oder angefügten Datenbank zu entfernen.

Änderungsnachverfolgung

Bei der Änderungsnachverfolgung wird die Tatsache erfasst, dass Zeilen in einer Tabelle geändert wurden. Die geänderten Daten werden nicht erfasst. Hierdurch können Anwendungen die geänderten Zeilen ermitteln, wobei die aktuellen Zeilendaten direkt von den Benutzertabellen abgerufen werden. Im Hinblick auf den Verlauf ist die Änderungsnachverfolgung also nicht so aussagekräftig wie Change Data Capture. Der Vorteil für die Anwendungen, die keine Verlaufsinformationen erfordern, liegt darin, dass viel weniger Speicherplatz benötigt wird, da die Änderungsdaten nicht aufgezeichnet werden. Zur Nachverfolgung der Änderungen wird ein synchroner Nachverfolgungsmechanismus verwendet. Dieser wurde so konzipiert, dass er sich minimal auf die Leistung der DML-Vorgänge auswirkt.

Die folgende Abbildung zeigt ein Synchronisierungsszenario, in dem die Verwendung der Änderungsnachverfolgung vorteilhaft ist. In diesem Szenario erfordert eine Anwendung folgende Informationen: alle Zeilen in der Tabelle, die seit der letzten Synchronisierung der Tabelle geändert wurden, und nur die aktuellen Zeilendaten. Da zur Nachverfolgung der Änderungen ein synchroner Mechanismus verwendet wird, kann eine Anwendung die bidirektionale Synchronisierung anwenden und eventuelle Konflikte zuverlässig ermitteln.

Konzeptdarstellung der Änderungsnachverfolgung

Änderungsnachverfolgung und Synchronisierungsdienste für ADO.NET

Synchronisierungsdienste für ADO.NET wird die Synchronisierung zwischen Datenbanken ermöglicht, und es stellt eine intuitive und flexible API zur Erstellung von Anwendungen bereit, die für Offline- und Zusammenarbeitsszenarien konzipiert sind. Synchronisierungsdienste für ADO.NET stellt eine API bereit, um Änderungen zu synchronisieren. Es werden jedoch keine Änderungen im Server oder in der Peer-Datenbank nachverfolgt. Sie können ein benutzerdefiniertes Änderungsnachverfolgungssystem erstellen, dies ist jedoch in der Regel mit viel Komplexität und einem hohen Verwaltungsaufwand verbunden. Zur Nachverfolgung von Änderungen in einer Server- oder Peerdatenbank empfehlen wir die Änderungsnachverfolgung in SQL Server 2019 (15.x) , da sie leicht zu konfigurieren ist und hochleistungsfähige Nachverfolgung bietet.

Weitere Informationen zur Änderungsnachverfolgung und zu Synchronisierungsdienste für ADO.NETfinden Sie unter den folgenden Links:

  • Informationen zur Änderungsnachverfolgung (SQL Server)

    Beschreibt die Änderungsnachverfolgung, bietet einen allgemeinen Überblick über die Funktion der Änderungsnachverfolgung und erläutert das Zusammenwirken der Änderungsnachverfolgung mit anderen SQL Server-Datenbank-Engine -Funktionen.

  • Microsoft Sync Framework Developer Center

    Stellt vollständige Dokumentation für Sync Framework und Synchronisierungsdienstebereit. Das Kapitel „Vorgehensweise: Verwenden der SQL Server-Änderungsnachverfolgung“ in der Dokumentation zu Synchronisierungsdiensteenthält genaue Informationen und Codebeispiele.

Aufgabe Thema
Bietet eine Übersicht über Change Data Capture. Über Change Data Capture (SQL Server)
Beschreibt das Aktivieren und Deaktivieren von Change Data Capture für eine Datenbank und eine Tabelle Aktivieren und Deaktivieren von Change Data Capture (SQL Server)
Beschreibt, wie Sie Change Data Capture verwalten und überwachen können. Verwalten und Überwachen von Change Data Capture (SQL Server)
Beschreibt die Verwendung der Änderungsdaten, die Change Data Capture-Kunden zur Verfügung stehen. In diesem Thema werden die Überprüfungen von LSN-Grenzwerten, die Abfragefunktionen und Abfragefunktionsszenarien beschrieben. Arbeiten mit Änderungsdaten (SQL Server)
Bietet eine Übersicht über die Änderungsnachverfolgung. Informationen zur Änderungsnachverfolgung (SQL Server)
Beschreibt das Aktivieren und Deaktivieren der Änderungsnachverfolgung für eine Datenbank oder eine Tabelle. Aktivieren und Deaktivieren der Änderungsnachverfolgung (SQL Server)
Beschreibt die Vorgehensweisen zum Verwalten der Änderungsnachverfolgung, zum Konfigurieren der Sicherheit und zum Ermitteln der Auswirkungen der Änderungsnachverfolgung auf Speicherung und Leistung. Verwalten der Änderungsnachverfolgung (SQL Server)
Beschreibt, wie Anwendungen, die die Änderungsnachverfolgung verwenden, Überarbeitungen abrufen, diese auf einen anderen Datenspeicher anwenden und die Quelldatenbank aktualisieren können. In diesem Thema wird beschrieben, welche Rolle die Änderungsnachverfolgung spielt, wenn ein Failover auftritt und eine Datenbank von einer Sicherung wiederhergestellt werden muss. Verwenden der Änderungsnachverfolgung (SQL Server)

Weitere Informationen

Change Data Capture-Funktionen (Transact-SQL)
Änderungsnachverfolgungsfunktionen (Transact-SQL)
Gespeicherte Prozeduren für Change Data Capture (Transact-SQL)
Change Data Capture-Tabellen (Transact-SQL)
Dynamische Verwaltungssichten in Bezug auf Change Data Capture (Transact-SQL)