Nachverfolgen von Datenänderungen (SQL Server)

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

SQL Server bietet zwei Features, mit denen Änderungen an Daten in einer Datenbank nachverfolgt werden: Änderungsdatenerfassung 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. Die Editionen von SQL Server, die die Erfassung und Nachverfolgung von Änderungen unterstützen, finden Sie unter Editionen und unterstützte Features von SQL Server 2022.

Vorteile der Verwendung von Änderungsdatenerfassung oder Ä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 zu ermitteln, müssen Anwendungsentwickler eine benutzerdefinierte Nachverfolgungsmethode in ihren Anwendungen implementieren, indem sie eine Kombination aus Triggern, Zeitstempelspalten 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:

• Es gibt eine reduzierte Entwicklungszeit. Da die Funktionalität in SQL Server verfügbar ist, müssen Sie keine benutzerdefinierte Lösung entwickeln.

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

• Es gibt einen integrierten sauber upmechanismus. Der Cleanup für die Änderungsnachverfolgung wird automatisch im Hintergrund ausgeführt. Benutzerdefinierte sauber up für Daten, die in einer seitlichen Tabelle gespeichert sind, sind nicht erforderlich.

• Funktionen werden bereitgestellt, um Änderungsinformationen abzurufen.

• Es gibt einen geringen Mehraufwand 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 Zeitstempelwerte verwenden, müssen für diese Szenarien entwickelt werden.

• 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.

Featureunterschiede zwischen Änderungsdatenerfassung 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 bieten eine historische Ansicht der Änderungen im Laufe der Zeit. Die Von SQL Server bereitgestellten Änderungsdatenerfassungsfunktionen ermöglichen die einfache und systematische Verwendung 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 (Transact-SQL) oder sys.sp_cdc_disable_db (Transact-SQL) 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 (Transact-SQL) und sys.sp_cdc_disable_table (Transact-SQL) entweder Mitglied der Sysadmin-Rolle oder ein 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.

Ändern von Enumerations- und Metadatenabfragen

Um Zugriff auf die Änderungsdaten zu erhalten, die einer Erfassungsinstanz zugeordnet sind, muss dem Benutzer SELECT-Zugriff auf alle erfassten Spalten der zugeordneten Quelltabelle gewährt werden. Wenn beim Erstellen der Aufnahmeinstanz eine Gatingrolle angegeben wird, muss der Aufrufer auch Mitglied der angegebenen Gating-Rolle sein, und das Änderungsdatenerfassungsschema (cdc) muss SELECT-Zugriff auf die Gatingrolle haben.

Andere allgemeine Änderungsdatenerfassungsfunktionen 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 mithilfe von SELECT-Zugriff auf die zugrunde liegenden Quelltabellen und durch Mitgliedschaft in allen definierten Gating-Rollen erfolgt.

DDL-Vorgänge zum Ändern von Datenquellentabellen mit Aktivierter Datenerfassung

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 die Änderung der Datenerfassung

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.

Ändern der Datenerfassung und anderer SQL Server-Features

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 SQL Server-Agent auf dem Spiegel 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 Datenbank-Spiegel ing finden Sie unter Datenbankspiegelung (SQL Server).For more information about database Spiegel ing, see Database Mirroring (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 die Datenerfassung eigenständig aktiviert ist, wird ein SQL Server-Agent Auftragsaufrufe ausgeführtsp_replcmds. Wenn beide Features in derselben Datenbank aktiviert sind, ruft der Log Reader Agent auf sp_replcmds. Dieser Agent füllt sowohl die Änderungstabellen als auch die distribution Datenbanktabellen 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 ist, wird der Protokolllese-Agent entfernt, und der Aufnahmeauftrag wird neu erstellt.

Wiederherstellen oder Anfügen einer Datenbank, die für die Änderungsdatenerfassung aktiviert ist

SQL Server verwendet die folgende Logik, um zu ermitteln, ob die Änderung der Datenerfassung wieder aktiviert ist Standard nachdem eine Datenbank wiederhergestellt oder angefügt wurde:

• 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, ist die Datenerfassung 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 KEEP_CDC Option einer anderen Edition als Standard oder Enterprise angefügt oder wiederhergestellt wird, wird der Vorgang blockiert, da die Datenerfassung sql Server Standard oder Enterprise-Editionen erfordert. 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

Die Änderungsnachverfolgung erfasst die Tatsache, dass Zeilen in einer Tabelle geändert wurden, erfasst jedoch 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. Für Anwendungen, die die historischen Informationen nicht benötigen, gibt es jedoch aufgrund der geänderten Daten, die nicht erfasst werden, viel weniger Speicheraufwand. 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.

Änderungsverfolgung 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 erstellen können, die Offline- und Zusammenarbeitsszenarien ansprechen. Synchronisierungsdienste für ADO.NET stellt eine API zum Synchronisieren von Änderungen bereit, verfolgt jedoch keine Änderungen in der Server- oder Peerdatenbank. Sie können ein benutzerdefiniertes Änderungsnachverfolgungssystem erstellen, dies ist jedoch in der Regel mit viel Komplexität und einem hohen Verwaltungsaufwand verbunden. Um Änderungen in einer Server- oder Peerdatenbank nachzuverfolgen, empfiehlt es sich, die Änderungsnachverfolgung in SQL Server zu verwenden, da es einfach zu konfigurieren ist und eine hohe Leistungsnachverfolgung bietet.

Weitere Informationen zur Änderungsnachverfolgung und zu Synchronisierungsdiensten für ADO.NET verwenden Sie die folgenden Links:

• Informationen zur Änderungsnachverfolgung (SQL Server)

  Beschreibt die Änderungsnachverfolgung, bietet eine allgemeine Übersicht über die Funktionsweise der Änderungsnachverfolgung und beschreibt, wie die Änderungsnachverfolgung mit anderen SQL Server-Datenbank-Engine-Features interagiert.

• Microsoft Sync Framework Developer Center

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

Nächste Schritte

Siehe auch

• Ändern von Datenerfassungsfunktionen (Transact-SQL)
• Änderungsnachverfolgung-Funktionen (Transact-SQL)
• Gespeicherte Prozeduren für die Datenerfassung ändern (Transact-SQL)
• Ändern von Datenerfassungstabellen (Transact-SQL)
• Change Data Capture Related Dynamic Management Views (Transact-SQL)