Nachverfolgen von Datenänderungen (SQL Server)

SQL Server 2014 bietet zwei Features, mit denen Änderungen an Daten in einer Datenbank nachverfolgt werden: Änderungsdatenerfassung und Änderungsnachverfolgung. Mit diesen Features können Anwendungen die DML-Änderungen (Einfüge-, Aktualisierungs- und Löschvorgänge) ermitteln, die an Benutzertabellen in einer Datenbank vorgenommen wurden. Änderungsdatenerfassung und Änderungsnachverfolgung können in derselben Datenbank aktiviert werden; Es sind keine besonderen Überlegungen erforderlich. Informationen zu den Editionen von SQL Server, die die Erfassung und Nachverfolgung von Änderungen unterstützen, finden Sie unter Features, die von den Editionen von SQL Server 2014 unterstützt werden.

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 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 Änderungsdatenerfassung oder Änderungsnachverfolgung in Anwendungen zum Nachverfolgen von Änderungen in einer Datenbank, anstatt eine benutzerdefinierte Lösung zu entwickeln, hat die folgenden Vorteile:

• Es gibt eine reduzierte Entwicklungszeit. Da die Funktionalität in SQL Server 2014 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 eine Zwischentabelle erstellen, in der gelöschte Zeilen nachverfolgt werden oder um Änderungsnachverfolgungsinformationen zu speichern, wenn die Spalten nicht zu den Benutzertabellen hinzugefügt werden können.

• Es gibt einen integrierten Bereinigungsmechanismus. Die Bereinigung für die Änderungsnachverfolgung wird automatisch im Hintergrund ausgeführt. Benutzerdefinierte Bereinigung von Daten, die in einer Seitentabelle gespeichert sind, ist nicht erforderlich.

• Funktionen werden bereitgestellt, um Änderungsinformationen abzurufen.

• Es gibt geringen Mehraufwand für DML-Vorgänge. Die synchrone Änderungsnachverfolgung hat immer etwas Mehraufwand. Die Verwendung der Änderungsnachverfolgung kann jedoch dazu beitragen, den Aufwand zu minimieren. Der Aufwand ist häufig kleiner als bei der Verwendung alternativer Lösungen, insbesondere bei Lösungen, die die Verwendungstrigger erfordern.

• Die Änderungsnachverfolgung basiert auf zugesicherten Transaktionen. Die Reihenfolge der Änderungen basiert auf der Transaktions-Commit-Zeit. Dies ermöglicht es, zuverlässige Ergebnisse zu erhalten, wenn lange ausgeführte und überlappende Transaktionen vorhanden sind. Benutzerdefinierte Lösungen, die timestamp Werte verwenden, müssen speziell für die Behandlung dieser Szenarien entwickelt werden.

• Standardtools stehen zur Verfügung, die Sie zum Konfigurieren und Verwalten verwenden können. SQL Server 2014 stellt Standard-DDL-Anweisungen, SQL Server Management Studio, Katalogansichten und Sicherheitsberechtigungen bereit.

Unterschiede der Merkmale zwischen der Änderungsdatenerfassung und der Änderungsnachverfolgung

In der folgenden Tabelle sind die Featureunterschiede zwischen der Änderungsdatenerfassung und der Änderungsnachverfolgung aufgeführt. Der Nachverfolgungsmechanismus bei der Änderungsdatenerfassung umfasst eine asynchrone Erfassung von Änderungen aus dem Transaktionsprotokoll, sodass Änderungen nach dem DML-Vorgang verfügbar sind. Bei der Änderungsnachverfolgung umfasst der Nachverfolgungsmechanismus synchrone Nachverfolgung von Änderungen in Übereinstimmung mit DML-Vorgängen, sodass Änderungsinformationen sofort verfügbar sind.

Ändern der Datenerfassung

Die Datensatzerfassung von Änderungsdaten stellt verlaufsgeschichtliche Änderungsinformationen für eine Benutzertabelle bereit, indem sowohl die Tatsache erfasst wird, dass DML-Änderungen vorgenommen wurden, als auch die tatsächlichen Daten, die geändert wurden. Änderungen werden mithilfe eines asynchronen Prozesses erfasst, der das Transaktionsprotokoll liest und einen geringen Einfluss auf das System hat.

Wie in der folgenden Abbildung dargestellt, werden die Änderungen, die an Benutzertabellen vorgenommen wurden, in den entsprechenden Änderungstabellen erfasst. Diese Änderungstabellen bieten eine historische Ansicht der Änderungen im Laufe der Zeit. Die Von SQL Server bereitgestellten Änderungsdatenerfassungsfunktionenermöglichen die einfache und systematische Verwendung der Änderungsdaten.

Sicherheitsmodell

In diesem Abschnitt wird das Sicherheitsmodell für die Änderung der Datenerfassung 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 festen Serverrolle sysadmin sein. Das Aktivieren und Deaktivieren der Änderungsdatenerfassung auf Tabellenebene erfordert den Aufrufer von sys.sp_cdc_enable_table (Transact-SQL) und sys.sp_cdc_disable_table (Transact-SQL) entweder als Mitglied der Sysadmin-Rolle oder als Mitglied der Datenbankrolle database db_owner .

Die Verwendung der gespeicherten Prozeduren zur Unterstützung der Verwaltung von Änderungsdatenerfassungsaufträgen ist auf Mitglieder der Serverrolle sysadmin und mitglieder der database db_owner Rolle beschränkt.

Ändern von Enumerations- und Metadatenabfragen
Um Zugriff auf die Änderungsdaten zu erhalten, die einer Aufnahmeinstanz zugeordnet sind, muss dem Benutzer der Auswahlzugriff auf alle erfassten Spalten der zugeordneten Quelltabelle gewährt werden. Wenn beim Erstellen der Erfassungsinstanz eine Gatingrolle angegeben wird, muss der Aufrufer außerdem Mitglied der angegebenen Gatingrolle sein. 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 des ausgewählten Zugriffs auf die zugrunde liegenden Quelltabellen und durch Mitgliedschaft in allen definierten Gating-Rollen erfolgt.

DDL-Vorgänge zum Ändern der Datenerfassung aktivierte Quelltabellen
Wenn eine Tabelle für die Änderungsdatenerfassung aktiviert ist, können DDL-Vorgänge nur von einem Mitglied der festen Serverrolle sysadmin, einem Mitglied der database role db_ownerOder eines Mitglieds der database role db_ddladminTabelle angewendet werden. Benutzer, die explizite Zuschüsse zum Ausführen von DDL-Vorgängen in der Tabelle haben, erhalten den Fehler 22914, wenn sie diesen Vorgang ausprobieren.

Überlegungen zum Datentyp für die Änderungsdatenerfassung

Alle Basisspaltentypen werden durch Änderungsdatenerfassung unterstützt. In der folgenden Tabelle sind das Verhalten und die Einschränkungen für mehrere Spaltentypen aufgeführt.

Ändern der Datenerfassung und anderer SQL Server-Features

In diesem Abschnitt wird beschrieben, wie die folgenden Features mit der Änderungsdatenerfassung interagieren:

• Datenbankspiegelung

• Transaktionsreplikation

• Datenbankwiederherstellung oder -anfügen

Datenbankspiegelung

Eine Datenbank, die für die Änderungsdatenerfassung aktiviert ist, kann gespiegelt werden. Führen Sie die folgenden Schritte aus, um sicherzustellen, dass die Erfassung und Bereinigung automatisch auf der Spiegelung erfolgt:

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

2. Erstellen Sie den Aufnahmeauftrag und den Bereinigungsauftrag auf der Spiegelung, nachdem der Prinzipal zur Spiegelung übergegangen ist. 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

Änderungsdatenerfassung und Transaktionsreplikation können in derselben Datenbank koexistieren, die Grundgesamtheit der Änderungstabellen wird jedoch anders behandelt, wenn beide Features aktiviert sind. Ändern der Datenerfassung und Transaktionsreplikation verwenden immer dasselbe Verfahren, sp_replcmds, um Änderungen aus dem Transaktionsprotokoll zu lesen. Wenn die Änderungsdatenerfassung allein aktiviert ist, wird ein SQL Server Agent-Job aufgerufen sp_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 Verteilerdatenbanktabellen auf. Weitere Informationen finden Sie unter Replikationsprotokollleser-Agent.

Betrachten Sie ein Szenario, in dem die Änderungsdatenerfassung in der AdventureWorks2012-Datenbank aktiviert ist, und zwei Tabellen sind für die Erfassung aktiviert. Um die Änderungstabellen zu befüllen, ruft der Capture-Job sp_replcmds auf. Die Datenbank ist für die Transaktionsreplikation aktiviert, und eine Publikation wird erstellt. Nun wird der Protokolllese-Agent für die Datenbank erstellt, und der Aufnahmeauftrag wird gelöscht. Der Protokollleser-Agent scannt weiterhin das Protokoll ab der letzten Protokollsequenznummer, die der Änderungstabelle übergeben wurde. Dadurch 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 Datenerfassung nach dem Wiederherstellen oder Anfügen einer Datenbank aktiviert bleibt:

• Wenn eine Datenbank auf demselben Server mit demselben Datenbanknamen wiederhergestellt wird, bleibt die Datenerfassung aktiviert.

• Wenn eine Datenbank auf einem anderen Server wiederhergestellt wird, ist die Datenerfassung standardmäßig deaktiviert, und alle zugehörigen Metadaten werden gelöscht.

  Verwenden Sie bei der Wiederherstellung der Datenbank die Option KEEP_CDC, um die Aufbewahrung der Änderungsdatenerfassung sicherzustellen. Weitere Informationen zu dieser Option finden Sie unter RESTORE.

• Wenn eine Datenbank getrennt und wieder mit demselben Server oder einem anderen Server verbunden wird, bleibt Change Data Capture aktiviert.

• Wenn eine Datenbank mit der KEEP_CDC Option einer anderen Edition als Enterprise angefügt oder wiederhergestellt wird, wird der Vorgang blockiert, da die Datenerfassung von Änderungen SQL Server Enterprise erfordert. 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 die Änderungsdatenerfassung 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 keine historischen Informationen benötigen, gibt es jedoch aufgrund der geänderten Daten, die nicht erfasst werden, viel weniger Speicheraufwand. Ein synchroner Nachverfolgungsmechanismus wird verwendet, um die Änderungen nachzuverfolgen. Dies wurde so konzipiert, dass der DML-Vorgang nur minimalen Aufwand hat.

Die folgende Abbildung zeigt ein Synchronisierungsszenario, das von der Änderungsnachverfolgung profitieren würde. Im 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 ein synchroner Mechanismus zum Nachverfolgen der Änderungen verwendet wird, kann eine Anwendung eine bidirektionale Synchronisierung durchführen und zuverlässig Konflikte erkennen, die möglicherweise aufgetreten sind.

Ä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. Sync Services for ADO.NET stellt eine API zum Synchronisieren von Änderungen bereit, verfolgt jedoch keine Änderungen in der Server- oder Peerdatenbank. Sie können ein benutzerdefiniertes Änderungsverfolgungssystem erstellen, dies führt jedoch in der Regel zu erheblicher Komplexität und Leistungsaufwand. Um Änderungen in einer Server- oder Peerdatenbank nachzuverfolgen, empfehlen wir, die Änderungsnachverfolgung in SQL Server 2014 zu verwenden, da es einfach zu konfigurieren ist und eine hohe Leistungsnachverfolgung bietet.

Für weitere Informationen zur Änderungsnachverfolgung und zu den Synchronisierungsdiensten für ADO.NET verwenden Sie bitte 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-Datenbankmodulfeatures interagiert.

• Microsoft Sync Framework Developer Center

  Enthält eine vollständige Dokumentation für Sync Framework und Synchronisierungsdienste. In der Dokumentation für Sync Services enthält das Thema "How to: Use SQL Server Change Tracking" ausführliche Informationen und Codebeispiele.

Verwandte Aufgaben (erforderlich)

Siehe auch

Ändern von Datenerfassungsfunktionen (Transact-SQL)
Änderungsverfolgungsfunktionen (Transact-SQL)
Gespeicherte Prozeduren für die Datenerfassung ändern (Transact-SQL)
Ändern von Datenerfassungstabellen (Transact-SQL)
Ändern der datenerfassungsbezogenen dynamischen Verwaltungsansichten (Transact-SQL)