Le suivi des modifications pour SQL Server est une technologie flexible et facile à utiliser pour surveiller les tables pour les insertions, les mises à jour et les suppressions. Dans cet article, je vais discuter de la mise en route du suivi des modifications dans SQL Server et montrer un exemple de la façon de commencer avec cela.
Suivi des modifications dans SQL Server
Le suivi des modifications est un mécanisme léger permettant de suivre les lignes qui ont été insérées, mises à jour et supprimées dans les tables surveillées par le suivi des modifications. Le suivi des modifications est apparu pour la première fois dans SQL Server 2008 et a été dans toutes les versions depuis. Mieux encore, le suivi des modifications est disponible dans chaque édition de SQL Server, même l’édition express gratuite.
En un mot, voici comment cela fonctionne: Dans les tables pour lesquelles le suivi des modifications est activé, la modification nette de chaque ligne est suivie en interne et est accessible via les fonctions de suivi des modifications. Chaque modification est associée à un ID de version et un nouvel ID de version sera ajouté pour chaque ligne insérée, mise à jour ou supprimée. La version de suivi des modifications est un entier de 8 octets (BIGINT) qui reflète l’ID de modification le plus récent dans cette base de données. Il est important de noter que la version de suivi des modifications n’est pas spécifique à une table – une opération DML dans une table suivie de chaque base de données incrémente le numéro de version. Le numéro de version sera toujours séquentiel, mais ne sera pas nécessairement contigu dans une seule table si plusieurs tables sont activées pour le suivi des modifications.
Le suivi des modifications est activé au niveau de la base de données. Après cela, chaque table qui sera surveillée doit être inscrite individuellement dans le suivi des modifications. Chaque table à surveiller par le suivi des modifications doit avoir une clé primaire, car il s’agit de l’identifiant au niveau de la ligne utilisé pour rendre compte des opérations DML dans le suivi des modifications. Lorsque vous activez le suivi des modifications au niveau de la table, vous pouvez choisir de suivre la ou les colonnes modifiées dans la dernière mise à jour, ce qui vous donnera une plus grande visibilité sur ce qui a été modifié.
Une fois configuré, l’utilisation du suivi des modifications est un processus relativement simple. Il y a quelques fonctions – notamment CHANGE_TRACKING_CURRENT_VERSION() et CHANGETABLE(), qui peuvent être utilisées pour vérifier l’estampille de la version actuelle dans le suivi des modifications et récupérer la liste des modifications récentes. Je présenterai ces deux fonctions sous peu.
Le suivi des modifications n’est pas une journalisation d’audit
Je vais faire attention à ne pas utiliser les mots audit ou journalisation pour décrire le suivi des modifications. Permettez-moi d’être clair: ce n’est pas un mécanisme de journalisation complet. L’historique des modifications n’est pas suivi du tout – le suivi des modifications signale uniquement le fait qu’une modification s’est produite, mais ne conserve pas l’historique des versions. Considérons le cas d’une ligne de données avec un identifiant de 1234. Cette ligne est insérée, puis mise à jour 5 fois, puis supprimée. Le suivi des modifications n’affiche pas l’historique d’insertion, de mise à jour et de suppression; au contraire, il ne rapporterait que le changement net, que l’ID de ligne 1234 a été supprimé. Si votre processus de chargement nécessite un historique de journalisation détaillé pour chaque modification (plutôt que le delta de toutes les modifications), vous devrez utiliser quelque chose comme la capture de données de modification.
Configuration du suivi des modifications dans SQL Server
L’activation du suivi des modifications au niveau de la table est un processus en deux étapes. Tout d’abord, il doit être activé sur la base de données. Cela peut être fait via l’interface utilisateur dans les propriétés de la base de données, sous l’onglet Suivi des modifications.
Comme indiqué, il n’y a pas grand chose à configurer lors de l’activation du suivi des modifications sur une base de données. Définissez simplement la valeur de suivi des modifications sur True pour configurer le suivi des modifications pour cette base de données. En option, la valeur de la période de rétention peut également être modifiée. La valeur par défaut est de 2 jours, que j’ai remplacé dans cet exemple pour utiliser 14 jours à la place. Comme pour la plupart des opérations d’interface utilisateur, il existe une commande T-SQL pour faire la même chose. La commande pour configurer le suivi des modifications sur cette base de données est ci-dessous.
Après cette étape, le suivi des modifications est activé, mais il ne suit encore rien. Il doit encore être activé pour que chaque table soit suivie. L’interface utilisateur des propriétés de la table rend cela très facile.
Comme indiqué, la simple modification de la valeur de suivi des modifications à True active le suivi des modifications pour cette table. Dans cet exemple, j’ai également choisi de suivre les colonnes modifiées lors des mises à jour (plus à ce sujet dans un peu).
La dernière étape ci-dessus serait répétée pour chaque table à suivre dans le suivi des modifications. Une fois le suivi des modifications activé, toutes les modifications (insertions, mises à jour ou suppressions) de cette table seront stockées dans le cache de suivi des modifications.
Configuration du suivi des modifications
Pour l’exemple ci-dessus, je vais insérer, mettre à jour et supprimer certaines données pour montrer comment accéder aux données de suivi des modifications générées pour ces opérations DML. Pour référence, voici la structure du tableau.
J’ai montré plus tôt comment activer le suivi des modifications pour une seule table à l’aide de l’interface utilisateur. Je préfère utiliser T-SQL pour cette tâche, car elle est plus facilement répétable. L’activation du suivi des modifications pour la table que j’ai créée ci-dessus peut être effectuée comme indiqué ici:
Rappelons que j’ai mentionné précédemment que le suivi des modifications utilise un ID de version pour suivre la version actuelle des tables suivies. Cet ID de version est notre marqueur de chronologie pour détecter les modifications. Pour récupérer cette valeur, il existe une fonction très simple : CHANGE_TRACKING_CURRENT_VERSION(). Il est utilisé comme indiqué ci-dessous.
Sur mon système de test, cette valeur est 470 (puisque j’ai exécuté plusieurs tests avant cette écriture). C’est le point de départ, et toute modification apportée à partir de ce point déclencherait un nouveau numéro de version. Je vais prendre note de cette valeur et je vais maintenant apporter quelques modifications au tableau décrit ci-dessus. Je vais insérer une poignée de lignes pour montrer comment le suivi des modifications affiche les insertions.
Après avoir inséré ces six lignes, je vérifie à nouveau la valeur CHANGE_TRACKING_CURRENT_VERSION() et constate que la valeur est maintenant 476. Il a été augmenté de 6 – un par ligne insérée, ce à quoi je m’attendais.
Utilisation des fonctions de suivi des modifications
Ensuite, nous utiliserons la fonction de suivi des modifications CHANGETABLE() pour afficher les modifications nettes sur ce tableau.
Pour décomposer cela:
- CHANGETABLE est la fonction système à valeur de table qui renverra la liste des modifications stockées dans le suivi des modifications
- Les modifications indiquent que je recherche les modifications car la version spécifiée
- @ver est la variable que j’ai configurée pour stocker le numéro de version. CHANGETABLE renverra tous les résultats reflétant les changements depuis cette version. Notez que vous pouvez utiliser une variable comme je l’ai fait, ou simplement passer un nombre scalaire (en utilisant le littéral 470 ici, vous auriez accompli la même chose)
Lorsque j’exécute le code ci-dessus, je reçois le jeu de résultats suivant.
Cela m’indique la version de l’insertion et / ou de la mise à jour, l’opération (I, U ou D pour insérer, mettre à jour ou supprimer, respectivement), le masque de colonne pour les opérations de mise à jour (plus à ce sujet momentanément) et la clé primaire de la ligne affectée par cette modification. Parce que CHANGETABLE() renvoie une table, je pourrais facilement joindre ce résultat à la table d’origine pour voir l’opération de modification avec les données actuelles de cette table.
Cela sera un peu différent pour une opération de mise à jour. Ensuite, j’exécuterai une instruction de mise à jour, mais d’abord, je vais noter la version actuelle du suivi des modifications (qui est toujours 476).
Maintenant l’instruction update, qui mettra à jour deux lignes dans la table:
Maintenant, lorsque j’exécute le code CHANGETABLE() d’en haut, en utilisant la version de suivi des modifications la plus récente (476) comme point de départ, j’obtiens un ensemble de résultats différent:
Il s’agit des métadonnées pour toutes les modifications depuis la version 476, qui n’inclut que les deux lignes mises à jour à partir de l’instruction UPDATE ci-dessus. Notez que la version de création est nulle, car cette modification était une mise à jour, pas une insertion. De plus, la valeur SYS_CHANGE_COLUMNS est maintenant remplie, bien que la valeur ne nous montre pas vraiment ce qui a changé (encore). C’est le bon moment pour parler de la fonction de suivi des modifications CHANGE_TRACKING_IS_COLUMN_IN_MASK(). Cette fonction vérifie si la colonne spécifiée a été mise à jour depuis la version la plus récente. Sa syntaxe est un peu bizarre, mais pour vérifier si le MiddleName a été mis à jour, la requête ressemblerait à ceci:
Honnêtement, je ne sais pas que j’ai déjà utilisé la fonction CHANGE_TRACKING_IS_COLUMN_IN_MASK. C’est un peu pénible car vous devez exécuter cela pour chaque colonne que vous souhaitez vérifier. La majeure partie de mon travail est dans l’entreposage de données, et j’ai rencontré peu de cas où j’ai besoin de savoir exactement quelles colonnes ont été mises à jour – je veux juste savoir si la ligne a été mise à jour. Cependant, pour d’autres scénarios (en particulier dans OLTP), je peux voir la nécessité de cela.
J’ai démontré des insertions et des mises à jour. Regardons à quoi ressemblerait une suppression. Encore une fois, je noterai le numéro de version actuel – 478 – pour la prochaine opération. Je vais maintenant supprimer une ligne de données:
Après avoir supprimé une ligne, j’exécuterai à nouveau CHANGETABLE() pour voir quels rapports de suivi des modifications pour cette opération.
Je trouve la ligne que j’ai supprimée lors de la dernière opération, avec SYS_CHANGE_OPERATION définie sur D(delete):
Maintenant, rappelez-vous que le numéro de version fait une différence ici! Le numéro de version passé dans CHANGETABLE() est le point de départ de toute modification renvoyée par cette fonction. Grâce à cet exercice, j’ai vérifié les résultats du suivi des modifications après chaque opération DML. Cependant, je peux définir le numéro de version de départ sur n’importe quel numéro de version valide, ou simplement utiliser NULL pour obtenir tous les résultats de suivi des modifications disponibles pour cette table. Pour démontrer, je vais remettre la valeur à la version 470 – le point de départ avant toute mise à jour – pour montrer à quoi ressemblerait l’historique complet. Lorsque je relance CHANGETABLE() en utilisant notre version de suivi des modifications d’origine, j’obtiens ce qui suit:
Il y a quelques nuances prévisibles ici. Tout d’abord, la ligne affichant l’ID d’enregistrement de 1 (qui était l’enregistrement de Phoebe Buffay que j’ai supprimé) apparaît simplement comme une opération de suppression, même si cette ligne a été insérée puis supprimée depuis le numéro de version de départ. N’oubliez pas que c’est le delta qui sera affiché – chaque opération contre cette ligne n’est pas conservée dans le suivi des modifications. Pour les ID numérotés 2 et 4 – qui étaient les deux lignes que j’ai insérées et mises à jour par la suite – SYS_CHANGE_OPERATION affiche une insertion, même si nous avons mis à jour les deux enregistrements après insertion. Le tell est que SYS_CHANGE_VERSION et SYS_CHANGE_CREATION_VERSION sur ces lignes ne correspondent pas, indiquant que le changement le plus récent n’était pas l’insert.
Conclusion
Le suivi des modifications est un moyen simple et léger de détection des modifications dans SQL Server. L’utilisation du suivi des modifications permet d’identifier facilement les données nouvelles, modifiées et supprimées, éliminant ainsi le besoin de comparaisons par force brute. Dans mon prochain article, je regarderai cela du point de vue de l’ETL, en intégrant le suivi des modifications dans un processus de chargement de bout en bout.