Synchroniser ses données : le Change Data Capture (partie 2)

Encore une suite de la conférence du SQL Saturday : "Synchroniser ses données, c'est plus pas facile que c'est compliqué". Nous parlons ici du Change Data Capture utilisé avec les composants dédiés de SQL Server Integration Services.

Pour compléter cette lecture, vous pouvez consulter d'abord la première partie de cet article sur le Change Data Capture.

Vous pouvez télécharger le projet SSIS de démo ici : SSDTBI-SSIS_CDC.zip

Nous avons vu dans l'article précédent comment fonctionne le CDC (Change Data Capture). Bien que ce soit tout à fait utilisable avec du TSQL, il y a encore plus pratique pour gérer ses synchronisations de données grâce aux composants dédiés de l'éditeur Attunity qu'intègre SQL Server Integration Services depuis sa version 2012. Rappelons au passage que le CDC n'est disponible qu'en édition Enterprise de SQL Server, il en est bien sûr de même pour ces composants SSIS spécialisés...

Dans SSIS 2012 et ultérieur, édition Enterprise, nous trouverons les 3 composants suivants :

  • CDC Control Task : composant de flux de contrôle
  • CDC Source : composant de flux de données
  • CDC Splitter : composant de flux de données

Voyons à quoi ils sont destinés.

CDC Control Task

Ce composant de flux de contrôle assure la gestion de l'état de synchronisation et des plages de LSN (log sequence number).

Utilisé dans les packages d'initialisation et de synchronisation, ce composant va permettre de gérer et de stocker les états de synchronisation, automatisant ainsi le processus incrémentiel : une variable contenant une valeur d'état est utilisée, et cet état peut être automatiquement stocké dans une table pour rester durable entre deux synchronisations.

Voyons comment il se configure : 

1. Choisir la connexion source des données modifiées

2. Choisir l'opération de contrôle. Plusieurs opérations sont disponibles :

  • Marquer le début du chargement initial : vous positionnerez la tâche au début du chargement initial
  • Marquer la fin du chargement initial : vous positionnerez la tâche à la fin du chargement initial
  • Marquer le début de la capture de données modifiées : à utiliser si la première synchronisation faite à partir d'un snapshot (on peut préciser dans ce cas la base de snapshot ou le LSN de départ)
  • Obtenir l'étendue de traitement : à positionner au début d'un chargement incrémentiel
  • Marquer l'étendue traitée : à positionner à la fin d'un chargement incrémentiel

3. Choisir la connexion utilisée pour stocker les valeurs d'état

4. Choisir la table ou stocker la valeur d'état, ou créer une nouvelle table avec le bouton "nouveau"

5. Donner le nom de la valeur d'état, par défaut CDC_State

Gestion des états de synchronisation

L'état de la synchronisation est stocké sous la forme d'une chaîne comprenant différents éléments séparés par des /

  • un statut,
  • les lsn de début et/ou de fin de plage, selon le contexte (chacun précédé d'un préfixe indiquant son utilisation : CR, CE, IS, TS),
  • un timestamp (précédé du préfixe TS)
  • le cas échéant un message d'erreur (précédé du préfixe ER)

Cela donne par exemple : ILSTART/IR/0x0000162B158700000000//TS/2011-08-07T17:10:43.0031645/

Les statuts sont :

  • ILSTART :état au lancement de la synchronisation initiale
  • ILEND : état après la réussite de la synchronisation initiale
  • ILUPDATE : état au premier lancement d'une synchronisation incrémentielle, les lignes éventuellement ajoutées ou modifiées pendant la synchronisation intiale seront prises en charge
  • TFSTART : état au lancement d'une synchronisation incrémentielle
  • TFEND : état à la fin d'une synchronisation incrémentielle réussie
  • TFREDO : état si la synchronisation précédente ne s'est pas correctement exécutée
  • ERROR :état d'erreur

Les préfixes de lsn sont :

  • CS :current start
  • CE :current end
  • IR : plage de chargement initial
  • TS :timestamp de la dernière mise à jour d'état

Les statuts vont donc s'enchaîner ainsi :

  • ILSTART -> chargement initial réussi -> ILEND
  • ILUPDATE -> premier chargement incrémentiel réussi -> TFEND
  • TFSTART -> chargement incrémentiel réussi -> TFEND
  • etc...

Pour notre package de synchronisation initiale, une tâche de contrôle CDC sera donc positionnée avant le chargement, et une autre après.

  • La première, configurée "Marquer le début du chargement initial", fait basculer l'état à ILSTART
  • La seconde, configurée "Marquer la fin du chargement initial" fait basculer l'état à ILEND

Pour notre package de synchronisation incrémentielle, une tâche de contôle CDC sera positionnée avant le chargement, une autre après.

  • La première, configurée "Obtenir l'étendue de traitement", fait basculer l'état à TFSTART (ou ILUPDATE si c'est la première exécution)
  • La seconde, configurée "Marquer la fin du chargement initial" fait basculer l'état à TFEND

Organisation alternative pour le chargement initial : L'inconvénient de l'organisation précédente est que nous devrons lancer deux fois le chargement incrémentiel : pour la première exécution, le package n'exécutera qu'une mise à jour du chargement initial (ILUPDATE->TFEND). Ce premier passage n'est peut être pas nécessaire si aucune activité n'était en cours sur notre base source. Pour modifier ce comportement, changeons l'organisation de notre package initial :

Nous marquons le démarrage du CDC (tâche Marquer le début de la capture de données modifiées), ce qui assurera que les prochaines mises à jour seront bien prises en compte à partir de maintenant. Et nous entourons notre chargement initial d'une tâche "Obtenir l'étendue de traitement" en amont et "Marquer l'étendue traitée" en aval. Notre initialisation se termine ainsi avec un statut TFEND (et non ILEND), ce qui permettra à nos chargements incrémentiels de prendre en charge les modifications capturées dès leur première exécution.

CDC Source

Ce composant de flux de données est utilisé comme source de données pour extraire les modifications capturées par le CDC. Il sera donc utilisé dans notre synchronisation incrémentielle.

J'ai juste à préciser la source de données et :

  • 1. La table concernée
  • 2. L'instance de CDC
  • 3. Le mode de traitement des modifications (modifications nettes, toutes, avec ou sans anciennes valeurs, etc...)
  • 4. La variable où est stockée l'état (CDC_State)

CDC Splitter

Ce composant de flux de données sépare les données modifiées (et reçues via le composant CDC Source) en 3 flux de sortie pour les INSERT, DELETE et UPDATE. Cela nous permettra de gérer les différents traitements : dans notre exemple, nous alimentons 3 tables de staging qui serviront au traitement de mise à jour de la table de destination.

Et voilà : il ne nous reste plus qu'à appliquer les mises à jour sur notre table destination avec des instructions INSERT SELECT, UPDATE FROM, DELETE FROM...

Bonne capture de données modifiées avec SSIS...