Aktivieren und Deaktivieren von Change Data Capture

Artikel
10/22/2023

Gilt für:SQL Server Azure SQL Managed Instance

In diesem Artikel wird beschrieben, wie Sie die Änderungsdatenerfassung (Change Data Capture, CDC) für eine Datenbank und eine Tabelle für SQL Server und Azure SQL verwaltete Instanz aktivieren und deaktivieren. Informationen zu Azure SQL-Datenbank finden Sie unter CDC mit Azure SQL-Datenbank.

Berechtigungen

Die sysadmin Berechtigungen sind erforderlich, um die Änderungsdatenerfassung in SQL Server und Azure SQL verwaltete Instanz zu aktivieren oder zu deaktivieren.

Aktivieren für eine Datenbank

Bevor Sie eine Aufnahmeinstanz für einzelne Tabellen erstellen können, müssen Sie die Änderungsdatenerfassung für die Datenbank aktivieren.

Führen Sie zum Aktivieren der Änderungsdatenerfassung die gespeicherte Prozedur sys.sp_cdc_enable_db (Transact-SQL) im Datenbankkontext aus. Um festzustellen, ob eine Datenbank bereits CDC aktiviert hat, fragen Sie die is_cdc_enabled Spalte in der sys.databases Katalogansicht ab.

Wenn eine Datenbank die Datenerfassung aktiviert hat, werden das CDC-Schema , cdc-Benutzer , Metadatentabellen und andere Systemobjekte für die Datenbank erstellt. Das cdc -Schema enthält die Metadatentabellen für Change Data Capture, und sobald die Quelltabellen für Change Data Capture aktiviert wurden, dienen die einzelnen Änderungstabellen als Repository für die Änderungsdaten. Das cdc -Schema enthält außerdem zugeordnete Systemfunktionen, die verwendet werden, um Änderungsdaten abzufragen.

Change Data Capture erfordert die exklusive Verwendung des cdc -Schemas und des cdc -Benutzers. Wenn derzeit ein Schema oder ein Datenbankbenutzer mit dem Namen cdc in einer Datenbank vorhanden ist, kann die Datenerfassung für die Änderung nicht aktiviert werden, bis das Schema und/oder der Benutzer abgelegt oder umbenannt wird.

-- ====
-- Enable Database for CDC
-- ====
USE MyDB
GO
EXEC sys.sp_cdc_enable_db
GO

Hinweis

Um CDC-bezogene Vorlagen in SQL Server Management Studio zu finden, wechseln Sie zu "Anzeigen", wählen Sie den Vorlagen-Explorer aus, und wählen Sie dann SQL Server-Vorlagen aus. Änderungsdatenerfassung ist ein Unterordner, der die Vorlagen enthält.

Deaktivieren für eine Datenbank

Verwenden Sie sys.sp_cdc_disable_db (Transact-SQL) im Datenbankkontext, um die Änderungsdatenerfassung für eine Datenbank zu deaktivieren. Es ist nicht erforderlich, CDC für einzelne Tabellen zu deaktivieren, bevor Sie CDC für die Datenbank deaktivieren. Durch das Deaktivieren von CDC für die Datenbank werden alle zugehörigen Metadaten für die Änderungsdatenerfassung entfernt, einschließlich des cdc-Benutzers , des Schemas und der Datenerfassungsaufträge für Änderungen. Alle von CDC erstellten Gatingrollen werden jedoch nicht automatisch entfernt und müssen explizit gelöscht werden. Um festzustellen, ob eine Datenbank CDC aktiviert hat, fragen Sie die is_cdc_enabled Spalte in der sys.databases Katalogansicht ab.

Wenn eine CDC-fähige Datenbank gelöscht wird, werden Änderungsdatenerfassungsaufträge automatisch entfernt.

-- Disable Database for change data capture
USE MyDB
GO
EXEC sys.sp_cdc_disable_db
GO

Aktivieren für eine Tabelle

Nachdem eine Datenbank für die Änderungsdatenerfassung aktiviert wurde, können Mitglieder der db_owner festen Datenbankrolle mithilfe der gespeicherten Prozedur sys.sp_cdc_enable_tableeine Aufnahmeinstanz für einzelne Quelltabellen erstellen. Um zu bestimmen, ob eine Quelltabelle bereits für Change Data Capture aktiviert ist, überprüfen Sie die is_tracked_by_cdc column-Spalte in dersys.tables-Katalogsicht.

Wichtig

Weitere Informationen zu den Argumenten der gespeicherten Prozedur finden Sie unter sys.sp_cdc_enable_table (Transact-SQL).For more information about the sys.sp_cdc_enable_table stored procedure arguments, see sys.sp_cdc_enable_table (Transact-SQL).

Wenn Sie eine Aufzeichnungsinstanz erstellen, können Sie die folgenden Optionen angeben:

Aufzuzeichnende Spalten in der Quelltabelle

Standardmäßig werden alle Spalten in der Quelltabelle als aufgezeichnete Spalten identifiziert. Wenn nur eine Teilmenge von Spalten nachverfolgt werden muss, z. B. aus Datenschutz- oder Leistungsgründen, verwenden Sie den parameter @captured_column_list , um die Teilmenge der Spalten anzugeben.

Eine Dateigruppe, welche die Änderungstabelle enthält.

Standardmäßig befindet sich die Änderungstabelle in der Standarddateigruppe der Datenbank. Zum Steuern der Position einzelner Änderungstabellen kann ein Datenbankbesitzer den Parameter @filegroup_name verwenden, um für die Änderungstabelle der Aufzeichnungsinstanz eine bestimmte Dateigruppe anzugeben. Die benannte Dateigruppe muss bereits vorhanden sein. Im Allgemeinen wird empfohlen, Tabellen in einer Dateigruppe zu ändern, die von Quelltabellen getrennt ist. Ein Beispiel für die Verwendung des Parameters @filegroup_name finden Sie in der Vorlage Enable a Table Specifying Filegroup Option .

-- Enable CDC for a table specifying filegroup
USE MyDB
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'MyTable',
    @role_name     = N'MyRole',
    @filegroup_name = N'MyDB_CT',
    @supports_net_changes = 1
GO

Eine Rolle, mit deren Hilfe der Zugriff auf eine Änderungstabelle gesteuert wird.

Der Zweck der benannten Rolle besteht darin, den Zugriff auf die Änderungsdaten zu steuern. Die angegebene Rolle kann eine vorhandene feste Serverrolle oder eine Datenbankrolle sein. Wenn die angegebene Rolle noch nicht vorhanden ist, wird automatisch eine Datenbankrolle mit diesem Namen erstellt. Benutzer müssen über DIE SELECT-Berechtigung für alle erfassten Spalten der Quelltabelle verfügen. Wenn eine Rolle angegeben wird, müssen benutzer, die weder Mitglied des Sysadmins noch db_owner Rolle sind, Mitglieder der angegebenen Rolle sein.

Wenn Sie keine Gatingrolle verwenden möchten, legen Sie den parameter @role_name explizit auf NULL fest. Ein Beispiel für das Aktivieren einer Tabelle ohne Gatingrolle finden Sie unter Enable a Table Without Using a Gating Role .

-- Enable CDC for a table using a gating role option
USE MyDB
GO
    EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'MyTable',
    @role_name     = NULL,
    @supports_net_changes = 1
GO

Eine Funktion zum Abfragen der Nettoänderungen.

Eine Aufnahmeinstanz enthält immer eine Tabellenwertfunktion (TVF), um alle Änderungstabelleneinträge zurückzugeben, die innerhalb eines definierten Intervalls aufgetreten sind. Diese Funktion wird benannt, indem sie den Namen der Aufnahmeinstanz an "cdc.fn_cdc_get_all_changes_" anfügen. Weitere Informationen finden Sie unter cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL).

Wird der Parameter @supports_net_changes auf 1 festgelegt, wird auch eine Funktion für Nettoänderungen für die Aufzeichnungsinstanz generiert. Diese Funktion gibt für jede einzelne Zeile, die innerhalb des beim Aufruf angegebenen Intervalls geändert wurde, nur eine Änderung zurück. Weitere Informationen finden Sie unter cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

Zur Unterstützung von Abfragen für Nettoänderungen muss die Quelltabelle einen Primärschlüssel oder einen eindeutigen Index aufweisen, damit die Zeilen eindeutig identifiziert werden können. Wird ein eindeutiger Index verwendet, muss dessen Name mithilfe des Parameters @index_name angegeben werden. Die für den Primärschlüssel oder den eindeutigen Index definierten Spalten müssen in der Liste der aufgezeichneten Quellspalten enthalten sein.

Ein Beispiel zur Erläuterung der Erstellung einer Aufzeichnungsinstanz mit beiden Abfragefunktionen finden Sie in der Vorlage Enable a Table for All and Net Changes Queries .

-- Enable CDC for a table for all and net changes queries
USE MyDB
GO
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'MyTable',
    @role_name     = N'MyRole',
    @supports_net_changes = 1
GO

Hinweis

Wenn die Änderungsdatenerfassung für eine Tabelle mit einem vorhandenen Primärschlüssel aktiviert ist und der parameter @index_name nicht verwendet wird, um einen alternativen eindeutigen Index zu identifizieren, verwendet das Feature "Datenerfassung ändern" den Primärschlüssel. Nachfolgende Änderungen am Primärschlüssel sind nicht zulässig, ohne zuerst die Änderungsdatenerfassung für die Tabelle zu deaktivieren. Dies gilt unabhängig davon, ob Unterstützung für Abfragen für Nettoänderungen angefordert wurde, als Change Data Capture konfiguriert wurde. Wenn zum Zeitpunkt der Aktivierung für Change Data Capture für eine Tabelle kein Primärschlüssel vorhanden ist, wird das nachträgliche Hinzufügen eines Primärschlüssels von Change Data Capture ignoriert. Da Change Data Capture keinen Primärschlüssel verwendet, der nach der Aktivierung der Tabelle erstellt wurde, können der Schlüssel und die Schlüsselspalten ohne Einschränkungen entfernt werden.

Deaktivieren für eine Tabelle

Mitglieder der db_owner festen Datenbankrolle können eine Aufnahmeinstanz für einzelne Quelltabellen mithilfe der Katalogansicht der gespeicherten Prozedur sys.sp_cdc_disable_tablee. To determine whether a source table is currently enabled for change data capture, examine the **is_tracked_by_cdc** column in the sys.tables entfernen. Wenn nach dem Deaktivieren keine Tabelle für die Datenbank aktiviert sind, werden die Change Data Capture-Aufträge ebenfalls entfernt.

Wenn eine Tabelle, für die Change Data Capture aktiviert ist, gelöscht wird, werden Change Data Capture-Metadaten, die mit der Tabelle verbunden sind, automatisch entfernt.

Ein Beispiel für das Deaktivieren einer Tabelle finden Sie in der Vorlage "Eine Aufzeichnungsinstanz für eine Tabelle deaktivieren".

-- Disable a Capture Instance for a table
USE MyDB
GO
    EXEC sys.sp_cdc_disable_table
    @source_schema = N'dbo',
    @source_name   = N'MyTable',
    @capture_instance = N'dbo_MyTable'
GO