Nachverfolgen von Datenänderungen (SQL Server)

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

SQL Serve 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 Editionen und unterstützte Funktionen von SQL Server 2022.

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. Um Datenänderungen festzustellen, müssen Anwendungsentwickler in der Regel eine benutzerdefinierte Nachverfolgungsmethode in ihren Anwendungen implementieren, indem sie eine Kombination aus Triggern, Zeitstempel-Spalten und zusätzlichen Tabellen verwenden. 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 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.

• Es gibt einen eingebauten 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, die Zeitstempel-Werte verwenden, müssen für diese Szenarien ausgelegt sein.

• Standardtools sind verfügbar, die Sie zum Konfigurieren und Verwalten verwenden können. SQL Server stellt Standard-DDL-Anweisungen, SQL Server Management Studio, Katalogansichten 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
Historische Daten	Ja	No
Ob Spalte geändert wurde	Ja	Ja
DML-Typ	Ja	Ja

Erfassung geänderter Daten

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 geben eine Übersicht über den Änderungsverlauf in einer Zeitspanne. Die von SQL Server bereitgestellten Change Data Capture-Funktionen ermöglichen die einfache und systematische Verarbeitung der Änderungsdaten.

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 Datenbank db_owner-Rolle 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.

Enumeration und Metadatenabfragen ändern

Um Zugriff auf die Änderungsdaten zu erhalten, die mit einer Capture-Instanz verbunden sind, muss dem Benutzer SELECT-Zugriff auf alle erfassten Spalten der zugehörigen Quelltabelle gewährt werden. Wenn bei der Erstellung der Capture-Instanz eine Gating-Rolle angegeben wird, muss der Aufrufer außerdem Mitglied der angegebenen Gating-Rolle sein, und das Schema für die Erfassung von Änderungsdaten (cdc) muss SELECT-Zugriff auf die Gating-Rolle haben.

Andere allgemeine Funktionen zur Erfassung von Änderungsdaten für den Zugriff auf Metadaten sind für alle Datenbankbenutzer über die öffentliche Rolle zugänglich, obwohl der Zugriff auf die zurückgegebenen Metadaten in der Regel auch durch SELECT-Zugriff auf die zugrundeliegenden Quelltabellen und durch die Mitgliedschaft in einer der definierten Gating-Rollen eingeschränkt wird.

DDL-Operationen zum Ändern von Data Capture-aktivierten Quelltabellen

Wenn eine Tabelle für Change Data Capture aktiviert ist, können DDL-Vorgänge nur von Mitgliedern der festen Serverrolle sysadminbzw. von Mitgliedern der Datenbankrolle db_owneroder db_ddladminauf 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	Begrenzungen
Sparsespalten	Ja	Unterstützt nicht die Erfassung von Änderungen bei Verwendung eines Spaltensets.
Berechnete Spalten	No	Ä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 Jobs 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 Agent-Job die sp_replcmds auf. Wenn für eine Datenbank beide Funktionen aktiviert sind, ruft der Protokolllese-Agent die sp_replcmds auf. Dieser Agent füllt sowohl die Änderungstabellen als auch die Datenbanktabellen von distribution auf. Weitere Informationen finden Sie unter Replication Log Reader Agent.

Angenommen, Change Data Capture ist für die AdventureWorks2022 -Datenbank aktiviert, und zwei Tabellen sind für die Erfassung aktiviert. Um die Änderungstabellen aufzufüllen, ruft der Aufzeichnungsauftrag 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 Capture-Job 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 Datenbank distribution 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 Datenbank distribution eine Wartezeit auftritt, werden Änderungen in den Änderungstabellen auch erst nach dieser Wartezeit 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 Option KEEP_CDC. 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 Standard oder Enterprise angehängt oder wiederhergestellt wird, wird der Vorgang blockiert, da für die Erfassung von Change Data Capture die SQL Server-Editionen Standard oder Enterprise erforderlich sind. 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

Mit der Änderungsnachverfolgung wird die Tatsache aufgezeichnet, dass Zeilen in einer Tabelle geändert wurden, aber nicht die geänderten Daten. 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 geänderten Daten 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.

Änderungsnachverfolgung und Synchronisierungsdienste für ADO.NET

Synchronisierungsdienste für ADO.NET ermöglichen die Synchronisierung zwischen Datenbanken und bieten eine intuitive und flexible API, mit der Sie Anwendungen für Offline- und Kollaborationsszenarien erstellen können. Synchronisierungsdienste für ADO.NET bieten eine API zum Synchronisieren von Änderungen, aber sie verfolgen nicht wirklich die Änderungen im Server oder in der Peer-Datenbank. 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, da sie leicht zu konfigurieren ist und hochleistungsfähige Nachverfolgung bietet.

Weitere Informationen über Änderungsnachverfolgung und Synchronisierungsdienste für ADO.NET finden 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

  Enthält eine vollständige Dokumentation für Sync Framework und Synchronisierungsdienste.

Nächste Schritte

Aufgabe	Artikel
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 Artikel 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 Functions (Transact-SQL)
• Änderungsnachverfolgungs-Funktionen (Transact-SQL)
• Change Data Capture gespeicherte Prozeduren (Transact-SQL)
• Change Data Capture Tables (Transact-SQL)
• Change Data Capture in Bezug auf dynamische Verwaltungsansicht (Transact-SQL)