À propos de la capture de données modifiées (SQL Server)

Les enregistrements de capture de données modifiées insèrent, mettent à jour et suppriment l’activité appliquée à une table SQL Server. Cela rend les détails des modifications disponibles dans un format relationnel facile à consommer. Les informations de colonne et les métadonnées requises pour appliquer les modifications à un environnement cible sont capturées pour les lignes modifiées et stockées dans des tables de modification qui reflètent la structure de colonnes des tables sources suivies. Les fonctions à valeurs de table sont fournies pour permettre un accès systématique aux données de changement par les consommateurs.

Un bon exemple d’un consommateur de données ciblé par cette technologie est une application 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. Bien que la représentation des tables sources dans l’entrepôt de données reflète 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 capture des modifications de données

L’illustration suivante montre 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. Cela lit le journal et ajoute des informations sur les modifications apportées à la table de modifications associée au 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.

Présentation de la capture de données modifiées et de l’instance de capture

Avant que les modifications apportées à des tables individuelles d’une base de données puissent être suivies, la capture de données modifiées doit être explicitement activée pour la base de données. Pour ce faire, 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 comme des tables suivies à 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 pour prendre en charge la diffusion 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 cdc.change_tablesde métadonnées de capture de données modifiées, cdc.index_columnset cdc.captured_columns. Ces informations peuvent être récupérées à l’aide de 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. Les conditions requises pour le nom de l’instance de capture sont qu’il s’agit d’un nom d’objet valide et qu’il s’agit d’un nom d’objet unique dans les instances de capture de base de données. Par défaut, le nom est <nom du schéma_nom de la 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 interroger toutes les modifications est nommée en prédéfinissant fn_cdc_get_all_changes_ le nom de l’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 fn_cdc_get_net_changes_ avant le nom de l’instance de capture.

Modifier la table

Les cinq premières colonnes d’une table de capture de données modifiées sont des colonnes de métadonnées. Celles-ci fournissent des informations supplémentaires pertinentes pour la modification enregistrée. Les colonnes restantes reflètent les colonnes capturées identifiées à partir de la table source dans le nom et, généralement, en type. Ces colonnes contiennent les données de colonne capturées collectées à partir de la table source.

Chaque opération d’insertion ou de suppression appliquée à une table source apparaît sous la forme d’une seule ligne dans la table de modification. 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 nécessite 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 des métadonnées supplémentaires pour permettre l’interprétation de l’activité de modification. La colonne __$start_lsn identifie le numéro de séquence de journal de validation (LSN) affecté à la modification. Le LSN de validation identifie les modifications qui ont été validées dans la même transaction et organise ces transactions. La colonne __$seqval peut être utilisée pour commander davantage de modifications qui se produisent 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, le masque de mise à jour aura toujours tous les bits définis. Toutefois, les lignes de mise à jour ont uniquement ces bits définis qui correspondent aux colonnes modifiées.

Intervalle de validité de capture de données modifiées pour une base de données

L’intervalle de validité de la capture de données modifiées pour une base de données est l’heure 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 continue à l’heure actuelle.

La taille des données déposées dans les tables de modifications augmentera 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 basée sur la rétention. Tout d’abord, il déplace le point de terminaison faible de l’intervalle de validité pour satisfaire la restriction de temps. Ensuite, il supprime les entrées de table de modification expirées. Par défaut, trois jours de données sont conservés.

Dans les cas les plus complexes, lorsque 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 ayant des entrées dans la table de changements. Dans la table de mappage, un numéro de séquence de journal de validation (LSN) et une heure de validation de transaction (colonnes start_lsn et tran_end_time, respectivement) sont conservés. La valeur LSN maximale trouvée dans cdc.lsn_time_mapping représente le repère de niveau le plus élevé de la période de validité de la base de données. Son temps de validation correspondant est utilisé comme base à partir de laquelle le nettoyage basé sur la rétention calcule une nouvelle marque d’eau faible.

Étant donné que le processus de capture extrait les données modifiées du journal des transactions, il existe une latence intégrée entre le moment où une modification est validée dans une table source et l’heure à laquelle la modification apparaît dans sa table de modifications associée. Bien que cette latence soit généralement petite, il est néanmoins important de se rappeler que les données modifiées ne sont pas disponibles tant que le processus de capture n’a pas traité les entrées de journal associées.

Intervalle de validité pour une instance de suivi des modifications de données

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, cela n’est pas toujours vrai. L’intervalle de validité de l’instance de capture démarre lorsque le processus de capture reconnaît l’instance de capture et commence à journaliser les modifications associées à sa table de modifications. Par conséquent, si des instances de capture sont créées à des moments différents, chacune d’elles aura initialement un point de terminaison faible différent. La colonne start_lsn du jeu de résultats retourné par sys.sp_cdc_help_change_data_capture affiche le point bas actuel pour chaque instance de capture définie. Lorsque le processus de nettoyage nettoie les entrées de table de modification, il ajuste les valeurs start_lsn pour toutes les instances de capture afin de refléter le nouveau seuil bas pour les données de modification disponibles. Seules les instances de capture dont les valeurs start_lsn sont actuellement inférieures à la nouvelle limite inférieure sont ajustées. Au fil du temps, si aucune nouvelle instance de capture n’est créée, les intervalles de validité de toutes les instances individuelles coïncident avec l’intervalle de validité de la base de données.

L’intervalle de validité est important pour les consommateurs de données modifiées, car l’intervalle d’extraction d’une demande doit être entièrement couvert par l’intervalle de validité de la capture de données modifiées actuelle pour l’instance de capture. Si le point de terminaison faible de l’intervalle d’extraction est à gauche du point de terminaison faible de l’intervalle de validité, il peut y avoir des données modifiées manquantes en raison d’un nettoyage agressif. Si le point de terminaison élevé de l’intervalle d’extraction est à droite du point de terminaison élevé de l’intervalle de validité, le processus de capture n’a pas encore traité pendant la période représentée par l’intervalle d’extraction et les données modifiées peuvent également être manquantes.

La fonction sys.fn_cdc_get_min_lsn est utilisée pour récupérer le LSN minimal actuel pour une instance de capture, tandis que sys.fn_cdc_get_max_lsn est utilisé pour récupérer la valeur LSN maximale actuelle. Lors de l’interrogation de données modifiées, si la plage LSN spécifiée ne se trouve pas dans ces deux valeurs LSN, les fonctions de requête de capture de données modifiées échouent.

Gestion des modifications apportées aux tables sources

Accommoder les modifications de colonne dans les tables sources suivies est un problème difficile pour les consommateurs en aval. Bien que l’activation de la capture des données modifiées sur une table source n’empêche pas ces modifications DDL de se produire, la capture de données modifiées permet d’atténuer l’effet sur les consommateurs en permettant aux jeux de résultats remis retournés par l’API de rester inchangés même si la structure de colonne de la table source sous-jacente change. Cette structure de colonnes fixe est également reflétée dans la table de modifications sous-jacente accessible par les fonctions de requête définies.

Pour prendre en charge une table de modification de structure de colonnes fixe, le processus de capture chargé de remplir la table de modifications ignore toutes les nouvelles colonnes qui ne sont pas identifiées pour la capture lorsque la table source a été activée pour la capture de données modifiées. Si une colonne suivie est supprimée, les valeurs Null 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 pour s’assurer que le mécanisme de capture n’introduit pas de perte de données pour les colonnes suivies. Le processus de capture publie également toutes les modifications détectées apportées à la structure de colonnes des tables suivies dans la table cdc.ddl_history. Les consommateurs souhaitant être avertis des ajustements susceptibles d’être effectués dans les applications en aval, utilisent la procédure stockée sys.sp_cdc_get_ddl_history.

En règle générale, l’instance de capture actuelle continue de conserver sa forme lorsque les modifications DDL sont appliquées à sa table source associée. Toutefois, il est possible de créer une deuxième instance de capture pour la table qui reflète la nouvelle structure de colonne. Cela permet au processus de capture d’apporter des modifications à la même table source en deux tables de modifications distinctes ayant deux structures de colonnes différentes. Ainsi, alors qu’une table de modifications peut continuer à alimenter les programmes opérationnels actuels, la deuxième peut conduire un environnement de développement qui tente d’incorporer les nouvelles données de colonne. L’autorisation du mécanisme de capture pour remplir les deux tables de modification en tandem signifie qu’une transition d’un à l’autre peut être effectuée sans perte de données modifiées. Cela peut se produire chaque fois que les deux chronologies de capture de données modifiées se chevauchent. Lorsque la transition est effectuée, l’instance de capture obsolète peut être supprimée.

La relation entre la tâche de capture et le lecteur de journal de réplication transactionnelle

La logique du processus de capture des données modifiées est incorporée dans la procédure stockée sp_replcmds, une fonction serveur interne créée dans le cadre de sqlservr.exe et également utilisée par la réplication transactionnelle pour collecter les modifications du journal des transactions. Lorsque la capture des modifications de données seule est activée pour une base de données, vous créez le travail de capture de SQL Server Agent comme moyen d'invoquer sp_replcmds. Lorsque la réplication est également présente, le lecteur de journal transactionnel seul est utilisé pour répondre aux besoins des données modifiées des deux consommateurs. Cette stratégie réduit considérablement la contention des journaux lorsque la réplication et la capture de données de modification sont activées pour la même base de données.

Le basculement entre ces deux modes opérationnels pour capturer les données modifiées se produit automatiquement chaque fois qu’une modification est apportée à l’état de réplication d’une base de données activée pour la capture de données modifiées.

La tâche principale du processus de capture consiste à analyser le journal et à écrire les données de colonne et les informations relatives aux transactions dans les tables de capture de données modifiées. Pour garantir une frontière cohérente sur le plan transactionnel pour toutes les tables de capture des modifications de données qu'il remplit, le processus de capture démarre et valide sa propre transaction à chaque cycle de scan. Il détecte quand les tables sont nouvellement activées pour la capture de données modifiées et les inclut automatiquement dans l’ensemble de tables qui sont activement surveillées pour les entrées de modification dans le journal. De même, la désactivation de la capture des données modifiées est également détectée, ce qui entraîne la suppression de la table source de l’ensemble de tables surveillées activement pour les données modifiées. Lorsque le traitement d’une section du journal est terminé, le processus de capture signale la logique de troncation du journal du serveur, qui utilise ces informations pour identifier les entrées de journal éligibles à la troncation.

Le processus de capture est également utilisé pour conserver l’historique sur les modifications DDL apportées aux tables suivies. Les instructions DDL associées à la capture de données modifiées font des entrées dans le journal des transactions de base de données chaque fois qu’une base de données ou une table compatible avec la capture de données modifiées est supprimée ou que des colonnes d’une table de 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 suivies à l’aide de la procédure stockée sys.sp_cdc_get_ddl_history.

Tâches de l’Agent de capture de données modifiées

Deux travaux SQL Server Agent sont généralement associés à une base de données activée pour la capture de données modifiées : un qui est utilisé pour remplir les tables de modification de base de données et un qui est responsable du nettoyage des tables de modification. Les deux travaux se composent 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 pour la capture de données modifiées, qui implémente la logique du processus. 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. La tâche de nettoyage est toujours créée. Le travail de capture est créé uniquement s’il n’existe aucune publication transactionnelle définie pour la base de données. Le travail de capture est également créé lorsque la capture de données modifiées et la réplication transactionnelle sont activées pour une base de données et que le travail de lecture de journal transactionnel est supprimé, car la base de données n’a plus de publications définies.

Les travaux de capture et de nettoyage sont créés à l’aide de paramètres par défaut. Le travail de capture est démarré immédiatement. Il s’exécute en continu, traitant un maximum de 1 000 transactions par cycle d’analyse avec une attente de 5 secondes entre les cycles. Le travail de nettoyage s’exécute tous les jours à 2 H. Il conserve les entrées de table modifiées pendant 4320 minutes ou 3 jours, en supprimant un maximum de 5 000 entrées avec une instruction delete 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 la capture de données modifiées et la réplication transactionnelle sont 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 tâches de l’agent de capture de données modifiées. La procédure stockée sys.sp_cdc_change_job est fournie pour 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 les paramètres de configuration de la table msdb.dbo.cdc_jobs au démarrage. Toutes les modifications apportées à ces valeurs à l’aide de sys.sp_cdc_change_job n’entreront pas en vigueur tant que le travail n’est pas arrêté et redémarré.

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

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

La capture de données modifié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 le compte NETWORK SERVICE. Cela peut entraîner l’erreur 22832.

Voir aussi

Suivre les modifications de données (SQL Server)
Activer et désactiver 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)