Activation de la capture des données modifiées
Cette rubrique décrit l'activation de la capture de données modifiées pour une base de données et une table.
Activation de la capture des données modifiées pour une base de données
Avant qu'une instance de capture puisse être créée pour des tables individuelles, un membre du rôle serveur fixe sysadmin doit d'abord activer la base de données pour la capture des données modifiées. Cela se fait en exécutant 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 des données modifiées, le schéma cdc, l'utilisateur cdc, les tables de métadonnées, ainsi que d'autres objets systèmes 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 base de données 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, celle-ci ne peut pas être activée pour la capture des données modifiées tant que le schéma et/ou l'utilisateur n'a pas été supprimé ou renommé.
Pour un exemple d'activation de base de données, consultez le modèle Activer la base de données pour la capture de données modifiées.
Important
Pour repérer les modèles dans SQL Server Management Studio, allez dans Afficher, cliquez sur Explorateur de modèles, puis sélectionnez Modèles SQL Server. Capture de données modifiées est un sous-dossier. Sous ce dossier, vous trouverez tous les modèles auxquels il est fait référence dans cette rubrique. On trouve également une icône Explorateur de modèles dans la barre d'outils SQL Server Management Studio.
-- ================================
-- Activer une base de données pour CDC, modèle
-- ================================
USE MyDB
GO
EXEC sys.sp_cdc_enable_db
GO
Activation de la capture des données modifiées pour une table
Une fois qu'une base de données a été activée pour la capture des 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 les 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.
Les options suivantes peuvent être spécifiées lorsque vous créez 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 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 ce sous-ensemble.
A filegroup to contain the change table.
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. Pour un exemple d'utilisation du paramètre @filegroup_name, consultez le modèle Enable a Table Specifying Filegroup Option.
===================================================
-- Enable a Table Specifying Filegroup Option, modèle
-- ===================================================
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 portant ce nom est automatiquement créé. Les membres du rôle sysadmin ou db_owner disposent d'un accès complet aux données des tables de modification. Tous les autres utilisateurs doivent disposer de l'autorisation SELECT pour toutes les colonnes capturées de la table source. De plus, lorsqu'un rôle est spécifié, les utilisateurs qui ne sont pas membres du rôle sysadmin or db_owner doivent également être membres du rôle spécifié.
Si vous ne souhaitez pas utiliser de rôle de régulation, affectez explicitement la valeur NULL au paramètre @role_name. Consultez le modèle Enable a Table Without Using a Gating Role pour obtenir un exemple de l'activation d'une table sans un rôle de régulation.
-- ===================================================
-- Enable a Table Without Using a Gating Role, modèle
-- ===================================================
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 inclura toujours une fonction table pour retourner toutes les entrées de table de modifications qui ont eu lieu au cours d'un intervalle défini. On nomme cette fonction 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 Enable a Table for All and Net Changes Queries pour obtenir un exemple démontrant la création d'une instance de capture avec les deux fonctions de requête.
=======================================================
-- Enable a Table for All and Net Changes Queries, modèle
-- =======================================================
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
Notes
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 utilisera la clé primaire. Les modifications ultérieures à la clé primaire ne seront pas autorisées sans désactivation préalable de 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.