Activation et désactivation de la capture des changements de données

S’applique à :SQL ServerAzure SQL Managed Instance

Cet article explique comment activer et désactiver la capture de données modifiées (CDC) pour une base de données et une table pour SQL Server et Azure SQL Managed Instance. Pour Azure SQL Database, consultez capture de données modifiées avec Azure SQL Database.

Autorisations

Les sysadmin autorisations sont nécessaires pour activer ou désactiver la capture de données modifiées dans SQL Server et Azure SQL Managed Instance.

Activer pour une base de données

Avant de pouvoir créer une instance de capture pour des tables individuelles, vous devez activer la capture de données modifiées pour la base de données.

Pour activer la capture de données modifiées, exécutez la procédure stockée sys.sp_cdc_enable_db (Transact-SQL) dans le contexte de la base de données. Pour déterminer si une base de données a déjà activé la capture de données modifiées, interrogez la colonne is_cdc_enabled dans l’affichage sys.databases catalogue.

Lorsqu’une base de données a activé la capture de données modifiées, le schéma cdc, l’utilisateur cdc, les tables de métadonnées et d’autres objets système sont créés pour la base de données. Le schéma cdc contient les tables de métadonnées de capture des données modifiées et, une fois que les tables sources sont activées pour la capture des données modifiées, les tables de modifications individuelles servent de référentiel pour les données modifiées. Le schéma cdc contient également les fonctions système associées utilisées pour rechercher les données modifiées.

La capture des données modifiées requiert une utilisation exclusive du schéma cdc et de l’utilisateur cdc . Si un schéma ou un utilisateur de base de données nommé cdc existe actuellement dans une base de données, la capture de données modifiées ne peut pas être activée pour la base de données tant que le schéma et/ou l’utilisateur n’ont pas été supprimés ou renommés.

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

Remarque

Pour rechercher des modèles liés à la capture de données modifiées dans SQL Server Management Studio, accédez à Afficher, sélectionnez Explorateur de modèles, puis sélectionnez Modèles SQL Server. La capture de données modifiées est un sous-dossier qui contient les modèles

Désactiver pour une base de données

Utilisez sys.sp_cdc_disable_db (Transact-SQL) dans le contexte de la base de données pour désactiver la capture de données modifiées pour une base de données. Il n’est pas nécessaire de désactiver la capture de données modifiées pour les tables individuelles avant de désactiver cdc pour la base de données. La désactivation de cdc pour la base de données supprime toutes les métadonnées de capture de données modifiées associées, notamment l’utilisateur cdc, le schéma et les travaux de capture de données modifiées. Toutefois, les rôles de gating créés par la capture de données modifiées ne seront pas supprimés automatiquement et doivent être supprimés explicitement. Pour déterminer si une base de données est activée, interrogez la colonne is_cdc_enabled dans l’affichage sys.databases catalogue.

Si une base de données avec capture de données modifiées est supprimée, les travaux de capture de données modifiées sont automatiquement supprimés.

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

Activer pour une table

Une fois qu’une base de données a été activée pour la capture de données modifiées, les membres du rôle de base de données fixe db_owner peuvent créer une instance de capture pour des tables sources individuelles à l’aide de la procédure sys.sp_cdc_enable_tablestockée. Pour déterminer si une table source a été déjà activée pour la capture des données modifiées, examinez la colonne is_tracked_by_cdc dans l'affichage catalogue sys.tables.

Important

Pour plus d’informations sur les sys.sp_cdc_enable_table arguments de procédure stockée, consultez sys.sp_cdc_enable_table (Transact-SQL).

Les options suivantes peuvent être spécifiées lorsque vous créez une instance de capture :

Les colonnes de la table source à capturer.

Par défaut, toutes les colonnes de la table source sont identifiées comme colonnes capturées. Si seul un sous-ensemble de colonnes doit être suivi, par exemple pour des raisons de confidentialité ou de performances, utilisez le paramètre @captured_column_list pour spécifier le sous-ensemble de colonnes.

Groupe de fichiers devant contenir la table de modifications.

Par défaut, la table de modifications se situe dans le groupe de fichiers par défaut de la base de données. Les propriétaires de base de données qui souhaitent contrôler le placement des tables de modifications individuelles peuvent utiliser le paramètre @filegroup_name pour spécifier un groupe de fichiers particulier pour la table de modifications associée à l’instance de capture. Le groupe de fichiers nommé doit déjà exister. En règle générale, il est recommandé de placer des tables modifiées dans un groupe de fichiers distinct des tables sources. Consultez le modèle Activer une table en spécifiant l’option Filegroup pour un exemple montrant l’utilisation du paramètre @filegroup_name .

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

Rôle pour contrôler l'accès à une table de modifications.

L'objectif du rôle nommé consiste à contrôler l'accès aux données modifiées. Le rôle spécifié peut être un rôle serveur fixe existant ou un rôle de base de données. Si le rôle spécifié n’existe pas encore, un rôle de base de données de ce nom est créé automatiquement. Les utilisateurs doivent disposer de l’autorisation SELECT sur toutes les colonnes capturées de la table source. En outre, lorsqu’un rôle est spécifié, les utilisateurs qui ne sont pas membres du rôle sysadmin ou db_owner doivent également être membres du rôle spécifié.

Si vous ne souhaitez pas utiliser un rôle de gating, définissez explicitement le paramètre @role_name sur NULL. Consultez le modèle Activer une table sans utiliser un rôle de régulation pour obtenir un exemple d’activation d’une table sans un rôle de régulation.

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

Fonction pour interroger les modifications nettes.

Une instance de capture inclut toujours une fonction table (TVF) pour retourner toutes les entrées de table modifiées qui se sont produites dans un intervalle défini. Cette fonction est nommée en ajoutant le nom de l’instance de capture à « cdc.fn_cdc_get_all_changes_ ». Pour plus d’informations, consultez cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL).

Si le paramètre @supports_net_changes a la valeur 1, une fonction de suivi des modifications nettes est également générée pour l’instance de la capture. Cette fonction retourne une seule modification pour chaque ligne distincte modifiée dans l'intervalle spécifié dans l'appel. Pour plus d’informations, consultez cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

Pour prendre en charge les requêtes de modifications nettes, la table source doit disposer d'une clé primaire ou d'un index unique permettant d'identifier sans ambiguïté les lignes. Si un index unique est utilisé, le nom de l’index doit être spécifié à l’aide du paramètre @index_name . Les colonnes définies dans la clé primaire ou l'index unique doivent être incluses dans la liste des colonnes sources à capturer.

Consultez le modèle Activer une table pour tous et pour les requêtes de modifications nettes pour un exemple montrant la création d’une instance de capture avec les deux fonctions de requête.

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

Remarque

Si la capture de données modifiées est activée sur une table avec une clé primaire existante et que le paramètre @index_name n’est pas utilisé pour identifier un autre index unique, la fonctionnalité de capture de données modifiées utilise la clé primaire. Les modifications suivantes apportées à la clé primaire ne sont pas autorisées sans d’abord désactiver la capture de données modifiées pour la table. Cela est vrai, que la prise en charge des requêtes de modifications nettes ait été demandée ou non lors de la configuration de la capture de données. Si une table ne contient pas de clé primaire au moment de son activation pour la capture de données modifiées, tout ajout ultérieur de clé primaire sera ignoré par la capture des données modifiées. Étant donné que la capture de données modifiées n'utilisera pas de clé primaire créée une fois la table activée, la clé et les colonnes clés peuvent être supprimées sans restrictions.

Désactiver pour une table

Les membres du rôle de base de données fixe db_owner peuvent supprimer une instance de capture pour des tables sources individuelles à l’aide de la procédure sys.sp_cdc_disable_tablestockée en mode catalogue de. To determine whether a source table is currently enabled for change data capture, examine the **is_tracked_by_cdc** column in the sys.tables. S'il n'y a pas de tables activées pour la base de données après la désactivation, les travaux de capture de données modifiées sont également supprimés.

Si une table pour laquelle la capture de données modifiées est activée est supprimée, les métadonnées de capture de données modifiées associées à la table sont automatiquement supprimées.

Pour un exemple de désactivation de table, consultez le modèle Désactiver une instance de capture pour une table.

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

Voir aussi