Partager via

sys.sp_cdc_enable_table (Transact-SQL)

  • Article

S'applique à : SQL Server

Active la capture de données modifiées pour la table source spécifiée dans la base de données actuelle. Lorsqu'une table est activée pour la capture de données modifiées, un enregistrement de chaque opération DML (Data Manipulation Language) appliquée à la table est écrit dans le journal des transactions. Le processus de capture de données modifiées extrait ces informations du journal et les écrit dans les tables de modifications accédées à l'aide d'un ensemble de fonctions.

La capture de données modifiées n’est pas disponible dans chaque édition de SQL Server. Pour obtenir la liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Éditions et fonctionnalités prises en charge de SQL Server 2022.

Conventions de la syntaxe Transact-SQL

Syntaxe

sys.sp_cdc_enable_table
    [ @source_schema = ] 'source_schema'
      , [ @source_name = ] 'source_name'
    [ , [ @capture_instance = ] 'capture_instance' ]
    [ , [ @supports_net_changes = ] supports_net_changes ]
      , [ @role_name = ] 'role_name'
    [ , [ @index_name = ] 'index_name' ]
    [ , [ @captured_column_list = ] N'captured_column_list' ]
    [ , [ @filegroup_name = ] 'filegroup_name' ]
    [ , [ @allow_partition_switch = ] 'allow_partition_switch' ]
[ ; ]

Arguments

[ @source_schema = ] 'source_schema'

Nom du schéma dans lequel la table source appartient. @source_schema est sysname, sans valeur par défaut et ne peut pas être NULL.

[ @source_name = ] 'source_name'

Nom de la table source sur laquelle activer la capture de données modifiées. @source_name est sysname, sans valeur par défaut et ne peut pas être NULL.

source_name doit exister dans la base de données active. Les tables du schéma ne peuvent pas être activées pour la cdc capture de données modifiées.

[ @role_name = ] 'role_name'

Nom du rôle de base de données utilisé pour gérer l’accès aux données modifiées. @role_name est sysname et doit être spécifié. Si elle est définie NULLexplicitement sur , aucun rôle de gating n’est utilisé pour limiter l’accès aux données modifiées.

Si le rôle existe actuellement, il est utilisé. Si le rôle n’existe pas, une tentative est effectuée pour créer un rôle de base de données avec le nom spécifié. L'espace blanc est retiré à la droite de la chaîne dans le nom de rôle avant d'essayer de créer le rôle. Si l’appelant n’est pas autorisé à créer un rôle dans la base de données, l’opération de procédure stockée échoue.

[ @capture_instance = ] 'capture_instance'

Nom de l’instance de capture utilisée pour nommer les objets de capture de données modifiées spécifiques à l’instance. @capture_instance est sysname et ne peut pas être NULL.

S'il n'est pas spécifié, le nom est dérivé du nom du schéma d'origine plus le nom de la table source au format <schemaname>_<sourcename>. @capture_instance ne peut pas dépasser 100 caractères et doit être unique dans la base de données. Que ce soit spécifié ou dérivé, @capture_instance est rogné d’un espace blanc à droite de la chaîne.

Une table source peut avoir un maximum de deux instances de capture. Pour plus d’informations, consultez sys.sp_cdc_help_change_data_capture.

[ @supports_net_changes = ] supports_net_changes

Indique si la prise en charge de l'interrogation des modifications nettes doit être activée pour cette instance de capture. @supports_net_changes est bit par défaut 1 si la table a une clé primaire ou si la table a un index unique identifié à l’aide du paramètre @index_name. Sinon, le paramètre est défini par défaut sur 0.

  • Si 0, seules les fonctions de prise en charge à interroger pour toutes les modifications sont générées.
  • Si 1, les fonctions nécessaires pour interroger les modifications nettes sont également générées.

Si @supports_net_changes est défini 1sur , @index_name doit être spécifié, ou la table source doit avoir une clé primaire définie.

Lorsque @supports_net_changes est défini 1sur , un index non cluster supplémentaire est créé sur la table de modifications et la fonction de requête de modification est créée. Étant donné que cet index doit être maintenu, l’activation des modifications nettes peut avoir un effet négatif sur les performances de capture de données modifiées.

[ @index_name = ] 'index_name'

Nom d'un index unique utilisé pour identifier de manière unique les lignes dans la table source. @index_name est sysname et peut être NULL. Si elle est spécifiée, @index_name doit être un index unique valide sur la table source. Si @index_name est spécifié, les colonnes d’index identifiées sont prioritaires sur les colonnes clés primaires définies comme identificateur de ligne unique pour la table.

[ @captured_column_list = ] N’captured_column_list'

Identifie les colonnes de la table source qui doivent être incluses dans la table de modifications. @captured_column_list est nvarchar(max) et peut être NULL. Si NULL, toutes les colonnes sont incluses dans la table de modification.

Les noms de colonnes doivent être des colonnes valides dans la table source. Les colonnes définies dans un index de clé primaire ou les colonnes définies dans un index référencé par @index_name doivent être incluses.

@captured_column_list est une liste séparée par des virgules de noms de colonnes. Les noms de colonnes individuels dans la liste peuvent éventuellement être entre guillemets doubles ("") ou entre crochets ([]). Si un nom de colonne contient une virgule incorporée, il doit être entouré de guillemets.

@captured_column_list ne peut pas contenir les noms de colonnes réservés suivants : __$start_lsn, , __$end_lsn, __$seqval, __$operationet __$update_mask.

[ @filegroup_name = ] 'filegroup_name'

Groupe de fichiers à utiliser pour la table de modifications créée pour l’instance de capture. @filegroup_name est sysname et peut être NULL. Si elle est spécifiée, @filegroup_name doit être définie pour la base de données active. Si NULL, le groupe de fichiers par défaut est utilisé.

Nous recommandons de créer un groupe de fichiers séparé pour les tables de modifications de capture des données modifiées.

[ @allow_partition_switch = ] 'allow_partition_switch'

Indique si la commande SWITCH PARTITION d'ALTER TABLE peut être exécutée sur une table activée pour la capture de données modifiées. @allow_partition_switch est bit, avec la valeur par défaut 1.

Pour les tables non partitionnées, le paramètre de commutation est toujours 1 et le paramètre réel est ignoré. Si le commutateur est explicitement défini 0 pour une table nonpartitionnée, l’avertissement 22857 est émis pour indiquer que le paramètre du commutateur a été ignoré. Si le commutateur est explicitement défini 0 pour une table partitionnée, l’avertissement 22356 est émis pour indiquer que les opérations de commutateur de partition sur la table source ne sont pas autorisées. Enfin, si le paramètre de commutateur est défini explicitement 1 sur ou autorisé par 1 défaut et que la table activée est partitionnée, l’avertissement 22855 est émis pour indiquer que les commutateurs de partition ne seront pas bloqués. Si des commutateurs de partition se produisent, la capture de données modifiées ne suit pas les modifications résultant du commutateur. Cela provoque des incohérences de données lorsque les données modifiées sont consommées.

SWITCH PARTITION est une opération de métadonnées, mais elle entraîne des modifications de données. Les modifications de données associées à cette opération ne sont pas capturées dans les tables de modification de capture de données modifiées. Pensez à une table qui comprend trois partitions et à laquelle des modifications sont apportées. Le processus de capture effectue le suivi des opérations d’insertion, de mise à jour et de suppression de l’utilisateur exécutées sur la table. Toutefois, si une partition est déplacée vers une autre table (par exemple, pour effectuer une suppression en bloc), les lignes déplacées dans le cadre de cette opération ne sont pas capturées en tant que lignes supprimées dans la table de modification. De même, si une nouvelle partition qui a préremplies des lignes est ajoutée à la table, ces lignes ne sont pas reflétées dans la table de modification. Cette situation peut aboutir à des données incohérentes lorsque les modifications sont utilisées par une application et appliquées à une destination.

Si vous activez le basculement de partition sur SQL Server, vous devrez peut-être également fractionner et fusionner des opérations dans un avenir proche. Avant d’exécuter une opération de fractionnement ou de fusion sur une table répliquée ou activée par capture de données modifiées, vérifiez que la partition en question n’a pas de commandes répliquées en attente. Vous devez également vous assurer qu’aucune opération DML n’est exécutée sur la partition pendant les opérations de fractionnement et de fusion. S’il existe des transactions que le lecteur du journal ou le travail de capture cdc n’a pas traité, ou si les opérations DML sont effectuées sur une partition d’une table répliquée ou activée par capture de capture modifiées pendant qu’une opération de fractionnement ou de fusion est exécutée (impliquant la même partition), cela peut entraîner une erreur de traitement (erreur 608 - Aucune entrée de catalogue trouvée pour l’ID de partition) avec l’agent de lecture du journal ou le travail de capture CDC. Pour corriger l’erreur, une réinitialisation de l’abonnement ou la désactivation de CDC sur cette table ou cette base de données peut s’avérer nécessaire.

Valeurs des codes de retour

0 (réussite) or 1 (échec).

Jeu de résultats

Aucune.

Notes

Pour pouvoir activer une table pour la capture de données modifiées, la base de données doit être activée. Pour déterminer si la base de données est activée pour la capture de données modifiées, interrogez la is_cdc_enabled colonne dans la vue catalogue sys.databases . Pour activer la base de données, utilisez la procédure stockée sys.sp_cdc_enable_db .

Lorsque la capture de données modifiées est activée pour une table, une table de modifications et une ou deux fonctions de requêtes sont générées. La table de modifications sert de base de données de référentiel pour les modifications de table source extraites du journal des transactions par le processus de capture. Les fonctions de requête sont utilisées pour extraire des données de la table de modifications. Les noms de ces fonctions sont dérivés du paramètre @capture_instance de la manière suivante :

  • Toutes les fonctions de modification : cdc.fn_cdc_get_all_changes_<capture_instance>
  • Fonction de modifications nettes : cdc.fn_cdc_get_net_changes_<capture_instance>

sys.sp_cdc_enable_table crée également les travaux de capture et de nettoyage de la base de données si la table source est la première table de la base de données à activer pour la capture de données modifiées et qu’aucune publication transactionnelle n’existe pour la base de données. Elle définit la colonne dans l’affichage is_tracked_by_cdc catalogue sys.tables sur 1.

SQL Server Agent n’a pas besoin d’être en cours d’exécution lorsque la capture de données modifiées est activée pour une table. Toutefois, le processus de capture ne traite pas le journal des transactions et écrit les entrées dans la table de modifications, sauf si SQL Server Agent est en cours d’exécution.

autorisations

Nécessite l'appartenance au rôle de base de données fixe db_owner.

Exemples

R. Activer la capture des données modifiées en spécifiant uniquement les paramètres requis

L'exemple suivant active la capture des données modifiées pour la table HumanResources.Employee. Seuls les paramètres requis sont spécifiés.

USE AdventureWorks2022;
GO

EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'HumanResources',
    @source_name = N'Employee',
    @role_name = N'cdc_Admin';
GO

B. Activer la capture des données modifiées en spécifiant des paramètres facultatifs supplémentaires

L'exemple suivant active la capture des données modifiées pour la table HumanResources.Department. Tous les paramètres, sauf @allow_partition_switch sont spécifiés.

USE AdventureWorks2022;
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'HumanResources',
    @source_name = N'Department',
    @role_name = N'cdc_admin',
    @capture_instance = N'HR_Department',
    @supports_net_changes = 1,
    @index_name = N'AK_Department_Name',
    @captured_column_list = N'DepartmentID, Name, GroupName',
    @filegroup_name = N'PRIMARY';
GO

Contenu connexe