Nachverfolgen von Datenänderungen (SQL Server)

Gilt für:SQL ServerAzure SQL-DatenbankVerwaltete Azure SQL-InstanzSQL-Datenbank in Microsoft Fabric

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 integrierten Bereinigungsmechanismus. Der Cleanup für die Änderungsnachverfolgung wird automatisch im Hintergrund ausgeführt. Eine benutzerdefinierte Bereinigung für Daten, die in einer Nebentabelle gespeichert sind, ist nicht erforderlich.

• Funktionen werden bereitgestellt, um Änderungsinformationen abzurufen.

• Der Aufwand für DML-Vorgänge ist gering. 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 Änderungsverfolgung basiert auf bestätigten Transaktionen. Die Reihenfolge der Änderungen basiert auf dem Commitzeitpunkt der Transaktion. 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.

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 Nutzung der Änderungsdaten.

Sicherheitsmodell

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

Konfiguration und Verwaltung

Um die Änderungsdatenerfassung für eine Datenbank zu aktivieren oder zu deaktivieren, muss der Aufrufer von sys.sp_cdc_enable_db oder sys.sp_cdc_disable_db mitglied der Sysadmin-Rolle des festen Servers sein. Zum Aktivieren und Deaktivieren der Änderungsdatenerfassung auf Tabellenebene muss der Aufrufer von sys.sp_cdc_enable_table und sys.sp_cdc_disable_table entweder Mitglied der Sysadmin-Rolle oder mitglied der Datenbankdatenbank 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 einer Erfassungsinstanz zugeordnet sind, muss dem Benutzer Zugriff auf alle erfassten Spalten der zugeordneten Quelltabelle gewährt SELECT werden. Außerdem muss der Aufrufer, wenn beim Erstellen der Erfassungsinstanz eine Gatingrolle angegeben wird, auch Mitglied der angegebenen Gatingrolle sein, und das Schema für Change Data Capture (cdc) muss über SELECT Zugriff auf die Gatingrolle verfügen.

Andere allgemeine Funktionen zur Erfassung von Änderungsdaten für den Zugriff auf Metadaten sind über die public-Rolle für alle Datenbankbenutzer zugänglich, obwohl der Zugriff auf die zurückgegebenen Metadaten in der Regel zusätzlich durch SELECT Zugriff auf die zugrunde liegenden Quelltabellen sowie durch die Mitgliedschaft in allen definierten Zugriffsrollen beschrä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. Benutzer, denen explizite Berechtigungen zum Ausführen von DDL-Vorgängen für die Tabelle erteilt wurden, erhalten den Fehler 22914, wenn sie versuchen, diese Vorgänge auszuführen.

Überlegungen zu Datentypen bei 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.

Integration von 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 die Erfassung und Bereinigung auf dem Spiegel automatisch erfolgen, führen Sie die folgenden Schritte aus:

1. Stellen Sie sicher, dass der SQL Server-Agent auf dem Spiegelserver ausgeführt wird.

2. Erstellen Sie den Aufzeichnungsauftrag und den Bereinigungsauftrag auf dem Spiegelserver, nachdem der primäre Server ein Failover auf den Spiegelserver ausgeführt hat. Verwenden Sie zum Erstellen der Aufträge die gespeicherte Prozedur sys.sp_cdc_add_job.

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

Transaktionsreplikation

Change Data Capture und die Transaktionsreplikation können in derselben Datenbank koexistieren, die Befüllung der Änderungstabellen erfolgt jedoch auf andere Weise, 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 eigenständig aktiviert ist, ruft ein SQL Server-Agent-Job 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 AdventureWorks2025 -Datenbank aktiviert, und zwei Tabellen sind für die Erfassung aktiviert. Um die Änderungstabellen zu füllen, ruft der Erfassungsjob 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 scannt das Protokoll weiterhin ab der letzten Protokollfolgenummer, die in der Änderungstabelle festgeschrieben 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.

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 "Anweisungen".

• 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. Daher ist die Änderungsnachverfolgung bei der Beantwortung historischer Fragen im Vergleich zu Change Data Capture stärker eingeschränkt. 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. Dies wurde so konzipiert, dass bei den DML-Vorgängen nur minimaler Mehraufwand entsteht.

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:

• Über die Änderungsverfolgung (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.

Zugehöriger Inhalt

• Ändern von Datenerfassungsfunktionen (Transact-SQL)
• Ändern gespeicherter Prozeduren für die Datenerfassung (Transact-SQL)
• Ändern von Datenerfassungstabellen (Transact-SQL)
• Dynamische Systemverwaltungssichten
• Was ist Change Data Capture (CDC)?
• Aktivieren und Deaktivieren von Change Data Capture
• Verwalten und Überwachen von Change Data Capture
• Arbeiten mit Änderungsdaten
• Änderungsverfolgungsfunktionen (Transact-SQL)
• Über die Änderungsverfolgung (SQL Server)
• Aktivieren und Deaktivieren der Änderungsnachverfolgung (SQL Server)
• Verwalten der Änderungsnachverfolgung (SQL Server)
• Verwenden der Änderungsnachverfolgung (SQL Server)