Qu’est-ce que la capture des changements de données (CDC) ?

S’applique à : SQL Server Azure SQL Managed Instance

Dans cet article, découvrez la capture des changements de données (CDC, change data capture), qui enregistre l’activité sur une base de données lorsque des tables et des lignes ont été modifiées.

Cet article explique comment la CDC fonctionne avec 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.

Vue d’ensemble

La capture des changements de données utilise SQL Server Agent pour enregistrer les insertions, les mises à jour et les suppressions survenant dans une table. Ainsi, ces modifications de données sont accessibles pour être facilement consommées dans un format relationnel. Les données de colonne et les métadonnées essentielles nécessaires pour appliquer ces données de changement à un environnement cible sont capturées pour les lignes modifiées et stockées dans des tables de changement qui reflètent la structure de colonne des tables sources suivies. En outre, des fonctions à valeur de tableau sont disponibles pour permettre aux consommateurs d'accéder systématiquement à ces données de changement.

Les consommateurs de données auxquels s’adresse cette technologie sont par exemple les applications d’extraction, de transformation et de chargement (ETL). Une application ETL charge de façon incrémentielle les données modifiées à partir de tables sources SQL Server vers un entrepôt de données ou un mini-Data Warehouse. Même si la représentation des tables sources dans l’entrepôt de données doit refléter les modifications apportées aux tables sources, une technologie de bout en bout qui actualise un réplica de la source n’est pas appropriée. Au lieu de cela, il faut un flux de données modifiées fiable, structuré de sorte que les consommateurs puissent l'appliquer aux différentes représentations cibles des données. La capture des changements des données SQL Server apporte cette technologie.

Flux de données

L'illustration suivante décrit le flux de données principal pour la capture de données modifiées.

La source des données modifiées pour la capture de données modifiées est le journal des transactions SQL Server . À mesure que des insertions, des mises à jour et des suppressions sont appliquées aux tables sources suivies, les entrées qui décrivent ces modifications sont ajoutées au journal. Le journal sert d’entrée au processus de capture. Ensuite, le journal est lu et les informations relatives aux modifications sont ajoutées à la table de modifications associée de la table faisant l’objet d’un suivi. Des fonctions sont fournies afin d'énumérer les modifications qui apparaissent dans les tables de modifications sur une plage spécifiée ; ces fonctions retournent les informations sous la forme d'un jeu de résultats filtré. Le jeu de résultats filtré est utilisé en général par un processus d'application pour mettre à jour une représentation de la source dans un environnement externe.

Instance de capture

Pour que les modifications apportées à une table d'une base de données puissent être suivies, la capture de données modifiées doit être activée explicitement pour la base de données. Pour cela, utilisez la procédure stockée sys.sp_cdc_enable_db. Lorsque la base de données est activée, les tables sources peuvent être identifiées en tant que tables faisant l’objet d’un suivi à l’aide de la procédure stockée sys.sp_cdc_enable_table. Lorsqu'une table est activée pour la capture de données modifiées, une instance de capture associée est créée afin de prendre en charge la dissémination des données modifiées dans la table source. L'instance de capture se compose d'une table de modifications et de deux fonctions de requête maximum. Les métadonnées qui décrivent les détails de configuration de l’instance de capture sont conservées dans les tables de métadonnées de capture de données modifiées, cdc.change_tables, cdc.index_columnset cdc.captured_columns. Ces informations peuvent être extraites en utilisant la procédure stockée sys.sp_cdc_help_change_data_capture.

Tous les objets associés à une instance de capture sont créés dans le schéma de capture de données modifiées de la base de données activée. Le nom d’instance de capture doit être un nom d’objet valide et être unique parmi les instances de capture de base de données. Par défaut, le nom est <nom schéma_nom table> de la table source. Sa table de modifications associée est nommée en ajoutant _CT au nom d’instance de capture. La fonction utilisée pour vérifier la présence de modifications est nommée en ajoutant le préfixe fn_cdc_get_all_changes_ au nom d’instance de capture. Si l’instance de capture est configurée pour prendre en charge net changes, la fonction de requête net_changes est également créée et nommée en ajoutant le préfixe fn_cdc_get_net_changes_ au nom d’instance de capture.

Table des changements

Les cinq premières colonnes de la table de modifications de capture de données modifiées sont des colonnes de métadonnées. Elles fournissent des informations supplémentaires pertinentes à la modification enregistrée. Les autres colonnes reflètent les colonnes capturées de la table source, identifiées par nom et généralement par type. Ces colonnes contiennent les données de colonne capturées recueillies à partir de la table source.

Chaque opération d'insertion ou de suppression appliquée à une table source apparaît comme une ligne unique dans la table de modifications. Les colonnes de données de la ligne qui résulte d'une opération d'insertion contiennent les valeurs de colonne après l'insertion. Les colonnes de données de la ligne qui résulte d'une opération de suppression contiennent les valeurs de colonne avant la suppression. Une opération de mise à jour requiert une entrée de ligne pour identifier les valeurs de colonne avant la mise à jour et une deuxième entrée de ligne pour identifier les valeurs de colonne après la mise à jour.

Chaque ligne d'une table de modifications contient également d’autres métadonnées permettant d'interpréter l'activité de changement. La colonne __$start_lsn identifie le numéro séquentiel dans le journal de validation affecté à la modification. Ce numéro identifie les modifications qui ont été validées dans la même transaction et il ordonne ces transactions. La colonne __$seqval peut être utilisée pour ordonner davantage de modifications qui ont lieu dans la même transaction. La colonne __$operation enregistre l'opération associée à la modification : 1 = suppression, 2 = insertion, 3 = mise à jour (image avant) et 4 = mise à jour (image après). La colonne __$update_mask est un masque de bits variable avec un bit défini pour chaque colonne capturée. Pour les entrées d'insertion et de suppression, tous les bits du masque de mise à jour sont définis. Toutefois, pour les lignes mises à jour, les bits correspondant aux colonnes modifiées seront définis.

Intervalle de validité

L'intervalle de validité de capture de données modifiées pour une base de données est la durée pendant laquelle les données modifiées sont disponibles pour les instances de capture. L'intervalle de validité commence lorsque la première instance de capture est créée pour une table de base de données et il se poursuit jusqu'au moment présent.

Base de données

La taille des données déposées dans les tables de modifications augmente de manière ingérable si vous n'effectuez pas un nettoyage périodique et systématique de ces données. Le processus de nettoyage de capture de données modifiées est responsable de l'application de la stratégie de nettoyage par rétention. En premier lieu, il déplace le point de terminaison inférieur de l'intervalle de validité afin de satisfaire la restriction temporelle. Ensuite, il supprime les entrées de table de modifications périmées. Par défaut, trois jours de données sont conservés.

À mesure que le processus de capture valide chaque nouveau lot de données modifiées, de nouvelles entrées sont ajoutées à cdc.lsn_time_mapping pour chaque transaction qui a des entrées de table de modifications. Dans la table de mappages, un numéro séquentiel dans le journal de validation et une durée de validation de transaction (colonnes start_lsn et tran_end_time, respectivement) sont conservés. La valeur de numéro séquentiel dans le journal maximale indiquée dans cdc.lsn_time_mapping représente la limite supérieure de la fenêtre de validation de la base de données. Sa durée de validation correspondante est utilisée comme base à partir de laquelle le nettoyage par rétention calcule une nouvelle limite inférieure.

Dans la mesure où le processus de capture extrait les données modifiées du fichier journal de transactions, il existe une latence inhérente entre le moment où une modification est validée dans une table source et le moment où cette modification apparaît dans sa table de modifications associée. Bien que cette latence soit en général assez faible, il est néanmoins important de se souvenir que les données modifiées ne sont disponibles qu'une fois que le processus de capture a traité les entrées de journal connexes.

Instance de capture

Bien qu'il soit courant que l'intervalle de validité de la base de données et l'intervalle de validité de l'instance de capture individuelle coïncident, ce n'est pas toujours le cas. L'intervalle de validité de l'instance de capture démarre lorsque le processus de capture reconnaît l'instance de capture et commence à enregistrer les modifications associées dans sa table de modifications. En conséquence, si des instances de capture sont créées à différents moments, chacune aura initialement un point de terminaison inférieur différent. La colonne start_lsn du jeu de résultats retourné par sys.sp_cdc_help_change_data_capture indique le point d’arrêt inférieur actuel pour chaque instance de capture définie. Lorsque le processus de nettoyage nettoie des entrées de table de modifications, il ajuste les valeurs start_lsn pour toutes les instances de capture afin de refléter la nouvelle limite inférieure pour les données modifiées disponibles. Seules les instances de capture qui ont des valeurs start_lsn actuellement inférieures à la nouvelle limite inférieure sont ajustées. Avec le temps, si aucune nouvelle instance de capture n'est créée, les intervalles de validité de toutes les instances auront tendance à coïncider avec l'intervalle de validité de base de données.

L'intervalle de validité est important pour les consommateurs de données modifiées car l'intervalle d'extraction pour une demande doit être couvert entièrement par l'intervalle de validité de capture de données modifiées actuel de l'instance de capture. Si le point de terminaison inférieur de l'intervalle d'extraction se situe à gauche du point de terminaison inférieur de l'intervalle de validité, certaines données modifiées pourraient être manquantes en raison d'un nettoyage agressif. Si le point de terminaison supérieur de l'intervalle d'extraction se trouve à droite du point de terminaison supérieur de l'intervalle de validité, cela indique que le processus de capture n'a pas encore effectué de traitement sur toute la durée représentée par l'intervalle d'extraction, et qu'il pourrait également y avoir des données de changement manquantes.

La fonction sys.fn_cdc_get_min_lsn est utilisée pour extraire la valeur LSN minimale actuelle pour une instance de capture, alors que sys.fn_cdc_get_max_lsn permet d’extraire la valeur LSN maximale actuelle. Lors de l'interrogation de données de changement, si la plage de numéros séquentiels dans le journal spécifiée n'est pas comprise entre ces deux valeurs de numéros séquentiels dans le journal, les fonctions de requête de capture des changements de données échouent.

Gestion des changements apportés à la table source

La gestion des modifications de colonnes dans les tables sources pour lesquelles un suivi est effectué est un problème délicat pour les consommateurs en aval. Bien que l'activation de la capture des changements de données sur une table source n'empêche pas de telles modifications DDL de se produire, la capture des données de modification atténue l'effet sur les consommateurs en préservant les ensembles de résultats fournis renvoyés par l'API, même si la structure des colonnes de la table source sous-jacente change. Cette structure de colonne fixe est également répercutée dans la table de modifications sous-jacente à laquelle les fonctions de requête définies accèdent.

Le processus de capture responsable de la population de la table de changement s'adapte à une table de changement à structure de colonnes fixe en ignorant toute nouvelle colonne non identifiée pour la capture lorsque la table source a été activée pour la capture des changements de données. Si une colonne suivie est supprimée, des valeurs NUL sont fournies pour la colonne dans les entrées de modification suivantes. Toutefois, si une colonne existante subit une modification de son type de données, la modification est propagée à la table de modifications afin de s'assurer que le mécanisme de capture n'introduit pas de perte de données dans les colonnes suivies. Le processus de capture publie également dans la table cdc.ddl_history toutes les modifications de structure de colonne détectées pour les tables faisant l'objet d'un suivi. Les consommateurs souhaitant être informés des ajustements qui peuvent se révéler nécessaires dans les applications en aval doivent utiliser la procédure stockée sys.sp_cdc_get_ddl_history.

En général, l'instance de capture actuelle conserve sa forme lorsque des modifications DDL sont appliquées à sa table source associée. Toutefois, il est possible de créer pour la table une deuxième instance de capture qui reflète la nouvelle structure de colonne. Cette option permet au processus de capture d'apporter des modifications à la même table source dans deux tables de modifications distinctes qui ont deux structures de colonne différentes. Par conséquent, tandis qu'une table de modifications peut continuer à servir les programmes actuellement opérationnels, la deuxième peut être affectée à un environnement de développement qui tente d'incorporer les nouvelles données de colonnes. Le fait d'autoriser le mécanisme de capture à remplir les deux tables de modifications en tandem signifie qu'une transition de l'une à l'autre peut être accomplie sans perte de données de modifications. Cela peut arriver à tout moment où les deux chronologies de capture de données modifiées se chevauchent. Lorsque la transition est terminée, l’instance de capture obsolète peut être supprimée.

Relation avec l’agent de lecture du journal

La logique du processus de capture de données modifiées est incorporée à la procédure stockée sp_replcmds, une fonction de serveur interne intégrée à sqlservr.exe et utilisée également par la réplication transactionnelle pour la collecte des modifications depuis le journal des transactions. Dans SQL Server et Azure SQL Managed Instance, quand seule la capture des changements de données est activée pour une base de données, vous créez le travail de capture des changements de données de SQL Server Agent comme instrument de l’appel de sp_replcmds. Lorsque la réplication est également présente, le lecteur de journal transactionnel seul est utilisé pour répondre aux besoins en données modifiées pour ces deux consommateurs. Cette stratégie réduit considérablement les contentions de journaux lorsque la réplication et la capture de données modifiées sont toutes deux activées pour la même base de données.

Le basculement entre ces deux modes opérationnels de capture de données modifiées se produit automatiquement à chaque modification de l'état de réplication d'une base de données activée pour la capture des changements de données.

La principale tâche du processus de capture consiste à analyser le journal et à écrire des données de colonnes et des informations relatives aux transactions dans les tables de modifications de capture de données modifiées. Pour garantir une limite transactionnellement cohérente parmi toutes les tables de modifications de capture de données modifiées qu'il remplit, le processus de capture ouvre et valide sa propre transaction à chaque cycle d'analyse. Il détecte lorsque des tables sont activées pour la capture de données modifiées et les inclut automatiquement dans le jeu de tables pour lesquelles un contrôle actif est effectué pour les entrées de modification dans le journal. De la même façon, la désactivation de la capture de données modifiées est également détectée et provoque la suppression de la table source du jeu de tables contrôlé de manière active. Lorsque le traitement d'une section du journal est terminé, le processus de capture en informe la logique de troncation de journal du serveur, qui utilise ces informations pour identifier les entrées de journal éligibles pour la troncation.

Le processus de capture est également utilisé pour conserver l'historique des modifications DDL pour les tables faisant l'objet d'un suivi. Les instructions DDL qui sont associées à la capture de données modifiées créent des entrées dans le journal des transactions de base de données chaque fois qu'une base de données ou une table prenant en charge la capture de données modifiées est supprimée ou chaque fois que des colonnes d'une table prenant en charge la capture de données modifiées sont ajoutées, modifiées ou supprimées. Ces entrées de journal sont traitées par le processus de capture, qui publie ensuite les événements DDL associés dans la table cdc.ddl_history. Vous pouvez obtenir des informations sur les événements DDL qui affectent les tables faisant l’objet d’un suivi à l’aide de la procédure stockée sys.sp_cdc_get_ddl_history.

Travaux de l'Agent

Deux travaux de SQL Server Agent sont en général associés à une base de données activée pour la capture des changements de données : l'un est utilisé pour remplir les tables de modifications de base de données et l'autre est chargé de les nettoyer. Ces deux travaux sont constitués d'une seule étape qui exécute une commande Transact-SQL. La commande Transact-SQL appelée est une procédure stockée définie par la capture des changements de données qui implémente la logique du travail. Les travaux sont créés lorsque la première table de la base de données est activée pour la capture de données modifiées. Le travail de nettoyage est toujours créé. Le travail de capture est créé seulement si aucune publication transactionnelle n'est définie pour la base de données. Le travail de capture est également créé quand la capture des changements de données et la réplication transactionnelle sont toutes les deux activées pour une base de données, et le travail du lecteur de journal est supprimé car aucune publication n’est plus définie pour la base de données.

Les travaux de capture et de nettoyage sont tous deux créés à l'aide de paramètres par défaut. Le travail de capture est démarré immédiatement. Il s'exécute continuellement et traite un maximum de 1000 transactions par cycle d'analyse, avec une attente de 5 secondes entre les cycles. La tâche de nettoyage s'exécute tous les jours à 2 heures du matin. Il conserve les entrées de table de modifications pendant 4 320 minutes (trois jours) et supprime un maximum de 5 000 entrées avec une instruction de suppression unique.

Les travaux de l'agent de capture de données modifiées sont supprimés lorsque la capture de données modifiées est désactivée pour une base de données. Le travail de capture peut également être supprimé lorsque la première publication est ajoutée à une base de données et que la capture de données modifiées et la réplication transactionnelle sont toutes deux activées.

En interne, les travaux de l’agent de capture de données modifiées sont créés et supprimés à l’aide des procédures stockées sys.sp_cdc_add_job et sys.sp_cdc_drop_job, respectivement. Ces procédures stockées sont également exposées afin que les administrateurs puissent contrôler la création et la suppression de ces travaux.

Un administrateur n'a aucun contrôle explicite sur la configuration par défaut des travaux de l'agent de capture de données modifiées. La procédure stockée sys.sp_cdc_change_job est fournie afin de permettre la modification des paramètres de configuration par défaut. En outre, la procédure stockée sys.sp_cdc_help_jobs permet d’afficher les paramètres de configuration actuels. Le travail de capture et le travail de nettoyage extraient des paramètres de configuration de la table msdb.dbo.cdc_jobs lors du démarrage. Toute modification apportée à ces valeurs à l’aide de sys.sp_cdc_change_job ne prend effet qu’après l’arrêt et le redémarrage du travail.

Deux autres procédures stockées sont fournies afin de permettre le démarrage et l’arrêt des travaux de l’agent de capture des changements de données : sys.sp_cdc_start_job et sys.sp_cdc_stop_job.

Les deux travaux de l'Agent SQL Server ont été conçus pour être suffisamment flexibles et configurables afin de répondre aux besoins élémentaires des environnements de capture des changements de données. Dans les deux cas, toutefois, les procédures stockées sous-jacentes qui fournissent la fonctionnalité principale ont été exposées afin qu'une personnalisation supplémentaire soit possible.

La capture des changements de données ne peut pas fonctionner correctement lorsque le service Moteur de base de données ou le service SQL Server Agent s'exécute sous la compte SERVICE RÉSEAU. Cela peut entraîner l'erreur 22832.

Interopérabilité avec d’autres fonctionnalités

La capture des changements de données présente certaines limites lorsqu'elle est utilisée avec d'autres fonctionnalités de SQL Server. Consultez la rubrique Interopérabilité pour en savoir plus.

Problèmes connus

Pour les problèmes connus et les erreurs associées à la capture des changements de données, consultez la rubrique Problèmes connus des CDC.

Voir aussi

• Problèmes connus et limitations
• Utiliser les données modifiées
• Suivre les modifications de données
• Activer et désactiver de la capture des changements de données
• Gérer et surveiller la capture des changements de données
• Tables temporelles