Nachverfolgen von Datenänderungen (SQL Server)

Artikel
11/19/2013

SQL Server 2012 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 2012-Editionen unterstützte Funktionen.

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, Timestampspalten 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 2012 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 solcher 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 2012 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	Change Data Capture	Ä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 Rolle database db_owner der Datenbank sein.

Gespeicherte Prozeduren zur Verwaltung von Change Data Capture-Aufträgen können nur von Mitgliedern der Serverrolle sysadmin und der Rolle database 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 von database role db_owner oder database role 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, diesen Vorgang 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:

Stellen Sie sicher, dass für die Spiegelung der SQL Server-Agent ausgeführt wird.
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 Transaktionsreplikation können in einer Datenbank nebeneinander vorhanden sein, aber die Auffüllung der Änderungstabellen wird 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 Replikationsprotokolllese-Agent.

Angenommen, Change Data Capture ist für die AdventureWorks2012 -Datenbank aktiviert, und zwei Tabellen sind für Capture 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 Aufzeichnungsauftrag 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 aufgezeichnete Änderungen in die Änderungstabellen geschrieben. Der Commit wird für beide Vorgänge zusammen ausgeführt. Wenn für das Schreiben in die Verteilungsdatenbank eine Latenzzeit vorhanden ist, gilt diese Latenzzeit auch für den Zeitraum, nach dem die Änderungen in den Änderungstabellen angezeigt werden.

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 aufgezeichnete Änderungen in die Änderungstabellen geschrieben. Der Commit wird für beide Vorgänge zusammen ausgeführt. Wenn für das Schreiben in die Verteilungsdatenbank eine Latenzzeit vorhanden ist, gilt diese Latenzzeit auch für den Zeitraum, nach dem die Änderungen in den Änderungstabellen angezeigt werden.

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 dazugehö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 für eine Datenbank auf demselben Server oder einem anderen Server die Anfügung aufgehoben und dann wiederhergestellt wird, bleibt Change Data Capture aktiviert.
Wenn eine Datenbank mit der Option KEEP_CDC an eine andere Edition als die Enterprise Edition angefügt oder dafür wiederhergestellt wird, wird der Vorgang blockiert, weil Change Data Capture SQL Server Enterprise 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.

Pfeilsymbol, dass mit dem Link "Zurück zum Anfang" verwendet wird [Nach oben]

Ä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 die folgenden 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

Mit 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 2012, da sie leicht zu konfigurieren ist und hochleistungsfähige Nachverfolgung bietet.

Weitere Informationen zur Änderungsnachverfolgung und zu 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 Database Engine (Datenbankmodul)-Funktionen.
Microsoft Sync Framework Developer Center

Stellt die vollständige Dokumentation von Sync Framework und Synchronisierungsdienste bereit. Das Thema "Vorgehensweise: Verwenden der SQL Server-Änderungsnachverfolgung" in der Dokumentation zu Synchronisierungsdienste enthält genaue Informationen und Codebeispiele.