Datafly.pro

Vous pouvez télécharger le script de démonstration ici : Change_Tracking.zip

Pour synchroniser périodiquement les données de deux tables sans faire à chaque fois un "annule et remplace", on a besoin d'identifier quelles lignes ont changé depuis la dernière synchronisation.

Il y a plusieurs méthodes pour identifier les données qui ont été modifiées :

• A l'ancienne : avec des triggers (pour update, delete et insert) qui alimentent des tables de journalisation
• En se servant des dates de mises à jour, pour peu qu'elles soient stockées dans la table (mais il faut tout de même journaliser les suppressions !)
• Trop facile : avec ce que nous offre SQL Server, à savoir le Change Tracking (et le Change Data Capture ou même la réplication, dont nous parlerons dans de prochains articles)

Le Change Tracking est une fonctionnalité intégrée à SQL Server depuis la version 2008. Il automatise, en mieux, toute la gestion de triggers et tables de journalisation et offre donc aux applications de synchronisation (ETL ou autres) la possibilité d'identifier quelles lignes d'une table ont été mises à jour (INSERT, UPDATE, DELETE).

Comme c'est destiné à synchroniser des données, le Change Tracking ne nous permet pas de connaître toutes les versions successives d'une même ligne : nous n'obtiendrons que les derniers états, ce que l'on appelle des modifications "nettes" :

• Pour un insert, suivi de 12 updates de la même ligne, la modification nette est juste un seul insert avec les dernières valeurs mises à jour.
• Un update suivi d'un delete de la même ligne ne génère qu'une seule instruction delete.
• etc...

Comment ça fonctionne ?

• Le Change Tracking effectue un suivi synchrone : lorsqu'il est activé sur une table, chaque opération de modification effectuée sur cette table contient dans son plan d'exécution une opération supplémentaire pour l'insertion dans une table interne de journalisation.
• Le Change Tracking ne journalise que la clé primaire (lors de la synchronisation, les valeurs des autres colonnes seront obtenues de la table elle-même). Il ne peut donc pas être activé sur des tables sans clés primaires.

En interne, le Change Tracking crée des tables systèmes, marquées "INTERNAL_TABLE" : on ne peut les voir dans SSMS et on ne peut les interroger (par pure curiosité) qu'avec la connexion administrateur dédiée.

• La table sys.syscommittab stocke des informations sur chaque transaction validée touchant les tables surveillées
• Pour chaque table surveillée, une table sys.change_tracking_xxxxx (ou xxxxx est l'ID d'objet de la table concernée) va stocker une ligne pour chaque modification : bien qu'on ne puisse connaitre toutes les valeurs successives d'une même ligne (seule la clé primaire, le type d'opération et quelques autres informations sont journalisées), chaque modification insère bien une nouvelle ligne, ce qui peut vite devenir volumineux...

Comment l'utiliser ?

• D'abord, authoriser le Change Tracking pour la base de données. On peut préciser la durée de rétention de la journalisation et si le nettoyage se fait automatiquement.

ALTER DATABASE MyDB SET CHANGE_TRACKING= ON( CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON )

• Ensuite, activer le CT sur chaque table concernée. On peut préciser si on veut connaitre quelles sont les colonnes qui ont été touchées (par un Update), avec l'option TRACK_COLUMNS_UPDATED

ALTER TABLE MyTable ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)

• Pour permettre de définir la plage de modifications à synchroniser, le Change Tracking utilise un numéro de version, incrémenté à chaque modification d'une table trackée. Pour connaître le numéro de version actuel, il suffit de l'interroger avec la fonction CHANGE_TRACKING_CURRENT_VERSION().

SELECT @s = CHANGE_TRACKING_CURRENT_VERSION()

• Il faudra donc, lors de chaque synchronisation, mémoriser la version actuelle, de manière à l'utiliser comme point de départ de la prochaine synchronisation. (le plus simple est de la stocker dans une table sur la destination)

• L'extraction des modifications se fait avec la fonction CHANGETABLE, pour laquelle on précise la table concernée et le numéro de version de départ.

SELECT * FROM CHANGETABLE(CHANGES MyTable, @s) AS CT

• On peut aussi connaitre quelles sont les colonnes qui ont été touchées par une modification avec la fonction CHANGE_TRACKING_IS_COLUMN_IN_MASK()

Quelques conseils

• Par sécurité, tester toujours si la version à partir de laquelle on veut synchroniser est bien valide (le thread de nettoyage a pu passer avant) avec la fonction CHANGE_TRACKING_MIN_VALID_VERSION()
• Microsoft recommande que la base soit en isolation SNAPSHOT pour s'assurer de lire une version stable en cas d'activité pendant la synchronisation.
• Surveiller le volume occupé par le Change Tracking. Limiter la rétention si nécessaire. Trop de volume peut aussi provoquer des problèmes de concurrence d'accès avec le thread de nettoyage : vous n'avez aucun moyen de contrôler à quel moment il s'exécute. En cas de problème, vous pouvez empêcher le nettoyage en le désactivant ou en prolongeant la durée de rétention.
• Pour les tables partitionnées : pas possible de faire un SWITCH PARTITION avec un Change Tracking activé
• Pour AllwaysOn : ne jamais effectuer la lecture des modifications sur un réplica, uniquement sur le primaire.