Nachverfolgen von Datenänderungen (SQL Server)

  • Artikel

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.

Diagramm mit dem Konzept 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 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.

SQL Server-Feature-Integration

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.

Diagramm zur Veranschaulichung des Konzepts der Änderungsnachverfolgung.

Ä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:

Zugehöriger Inhalt