Activer et désactiver la capture de données modifiées (SQL Server)

Cette rubrique explique comment activer et désactiver la capture de données modifiées pour une base de données et une table.

Activer la capture de données modifiées pour une base de données

Avant de pouvoir créer une instance de capture pour des tables individuelles, un membre du sysadmin rôle serveur fixe doit d’abord activer la base de données pour la capture de données modifiées. Pour ce faire, 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 est déjà activée, interrogez la colonne is_cdc_enabled dans l'affichage catalogue sys.databases.

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

La capture de données modifiées nécessite 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 base de données ne peut pas être activée pour la capture de données modifiées tant que le schéma et ou l’utilisateur ne sont pas supprimés ou renommés.

Consultez le modèle Activer la capture de données modifiées pour obtenir un exemple d’activation d’une base de données.

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

Désactiver la capture de données modifiées pour une base de données

Un membre du sysadmin rôle serveur fixe peut exécuter la procédure stockée 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 des tables individuelles avant de désactiver la base de données. La désactivation de la base de données supprime toutes les métadonnées de capture de données modifiées associées, notamment l’utilisateur, le schéma cdc et les tâches de capture de données modifiées. Toutefois, les rôles de gestion 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 is_cdc_enabled colonne dans la vue catalogue sys.databases.

Si une base de données sur laquelle la capture de données modifiées est activée est supprimée, les travaux de capture de données modifiées sont également et automatiquement supprimés.

Consultez le modèle Désactiver la capture de données modifiées pour obtenir un exemple de désactivation d’une base de données.

-- =======  
-- Disable Database for Change Data Capture template   
-- =======  
USE MyDB  
GO  
EXEC sys.sp_cdc_disable_db  
GO  

Activer la capture de données modifiées 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 db_owner rôle de base de données fixe 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 déjà été activée pour la capture de données modifiées, examinez la colonne is_tracked_by_cdc dans l’affichage sys.tables catalogue.

Les options suivantes peuvent être spécifiées lors de la création d’une instance de capture :

Columns in the source table to be captured.

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.

A filegroup to contain the change table.

Par défaut, la table de modifications se trouve 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 l’emplacement 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 de modification dans un groupe de fichiers distinct des tables sources. Consultez le Enable a Table Specifying Filegroup Option modèle pour obtenir un exemple montrant l’utilisation du paramètre @filegroup_name .

-- =========  
-- Enable a Table Specifying Filegroup Option Template  
-- =========  
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  

A role for controlling access to a change table.

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 déjà, un rôle de base de données de ce nom est créé automatiquement. Les membres du rôle sysadmin ou db_owner ont un accès complet aux données dans les tables de modification. Tous les autres 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 de l’un sysadmin ou db_owner l’autre des rôles 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 Enable a Table Without Using a Gating Role modèle pour obtenir un exemple d’activation d’une table sans rôle de contrôle.

-- =========  
-- Enable a Table Without Using a Gating Role template   
-- =========  
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  
  

A function to query for net changes.

Une instance de capture inclut toujours une fonction de type table 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 Enable a Table for All and Net Changes Queries modèle pour obtenir un exemple illustrant la création d’une instance de capture avec les deux fonctions de requête.

-- =============  
-- Enable a Table for All and Net Changes Queries template   
-- =============  
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  

Désactiver la capture de données modifiées pour une table

Les membres du db_owner rôle de base de données fixe peuvent supprimer une instance de capture pour des tables sources individuelles à l’aide de la procédure sys.sp_cdc_disable_tablestockée. Pour déterminer si une table source est actuellement activée pour la capture de données modifiées, examinez la is_tracked_by_cdc colonne dans l’affichage sys.tables catalogue. Si aucune table n’est activée 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.

Consultez le modèle Désactiver une instance de capture pour un modèle de table pour obtenir un exemple de désactivation d’une table.

-- =====  
-- Disable a Capture Instance for a Table template   
-- =====  
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

Suivre les modifications de données (SQL Server)
À propos de la capture de données modifiées (SQL Server)
Utiliser les données modifiées (SQL Server)
Administrer et surveiller la capture de données modifiées (SQL Server)