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

Article
05/17/2024

S’applique à :SQL Server Azure SQL Managed Instance

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

Autorisations

Les autorisations sysadmin sont nécessaires pour activer ou désactiver la capture des changements de donné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 des changements de données pour la base de données.

Pour activer la capture des changements de donné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 le CDC est déjà activé dans une base de données, interrogez la colonne is_cdc_enabled dans l’affichage catalogue sys.databases.

Lorsque la capture des changements de données est activée pour une base de donné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 des changements de données ne peut pas être activée pour la base de données tant que le schéma et/ou l’utilisateur n’est pas supprimé ou renommé.

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

Remarque

Pour trouver les modèles liés à la CDC dans SQL Server Management Studio, accédez à Affichage, sélectionnez Explorateur de modèles, puis sélectionnez Modèles de SQL Server. La capture des changements de donné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 des changements de données pour une base de données. Il n’est pas nécessaire de désactiver la CDC pour les tables individuelles avant de la désactiver pour la base de données. La désactivation de la CDC pour la base de données supprime toutes les métadonnées de capture des données de modification associées, y compris l'utilisateur cdc, le schéma et les tâches de la capture des changements de données. Toutefois, tous les rôles de régulation créés par CDC ne seront pas supprimés automatiquement et doivent être supprimés explicitement. Pour déterminer si une base de données à une CDC activée, interrogez la colonne is_cdc_enabled dans l’affichage catalogue sys.databases.

Si une base de données activée par la CDC est supprimée, les tâches de la capture des changements de données sont automatiquement supprimées.

-- 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 des changements de donné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 stockée sys.sp_cdc_enable_table. 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 arguments de procédure stockée sur sys.sp_cdc_enable_table, 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 un suivi concerne uniquement un sous-ensemble de colonnes, par exemple pour des raisons de confidentialité ou de performance, 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 de modifications dans un groupe de fichiers séparé 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 déjà, un rôle de base de données portant ce nom est automatiquement créé. Le utilisateurs doivent disposer de l’autorisation SELECT pour toutes les colonnes capturées de la table source. De plus, quand 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 voulez pas utiliser un rôle de blocage, attribuez explicitement la valeur NULL au paramètre @role_name. 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 comprend toujours une fonction table (TVF) permettant de renvoyer toutes les entrées de la table de modification 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 des changements de donné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 utilisera la clé primaire. Les modifications ultérieures à la clé primaire ne sont pas autorisées sans désactivation préalable de la capture des changements de donné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 les tables sources individuelles à l’aide de la procédure stockée sys.sp_cdc_disable_table. Pour déterminer si une table source est actuellement activée pour la capture des données modifiées, examinez la colonne is_tracked_by_cdc dans l'affichage catalogue 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

Partager via