[SQL] Pourquoi le Change Data Capture va changer notre vie

Rien que ça… Mais je parierais que ça n’est pas immérité. Avec les latences dans les adoptions de nouvelles technos ma « prédiction » ne s’avèrera peut être vraie que dans 2 ou 3 ans (oui on bosse encore beaucoup et majoritairement en 2005!).
Mais bon, reprenons depuis le début…

« C’est quoi le CDC ou Change Data Capture?« 

C’est un mécanisme de tracking de modification de contenu de tables. Basiquement le CDC crée des tables de suivi des modifications DML (INSERT, UPDATE, DELETE) effectués dans une table de données. Ces tables sont nourries par des jobs qui scannent le log de la base, comme visible sur cette belle slide de formation ci-dessous.


« Change Data Capture » n’est pas le nom d’un produit. C’est une désignation de ce genre de feature. Ce processus existe par exemple chez Oracle (depuis 9i si mes souvenirs Oracliens sont bons, mais n’hésitez pas à me démentir), sous Infosphere, Sybase…. et généralement suivant le même principe (Un RDBMS maintient un Tran Log, pourquoi ne pas s’en servir après tout?)
Il a été introduit en environnement Microsoft sous SQL Server 2008.

« Mais ça va servir à quoi en Data Warehousing? On a déjà SSIS! »

Le CDC n’a pas pour but de remplacer SSIS mais de faciliter les alimentations de Data Warehouses, en ce qui concerne les changements dans les sources. Soyons clair: SSIS est un fantastique outil qui permet de gérer des workflows complexes, d’implémenter des alimentations massivement multi-sources, des règles métier tordues et des SCD bizarroïdes, mais on génère un énorme overhead dans la gestion de la recherche des modifications sur les sources.

Par exemple on ne peut pas toujours compter sur une datation des lignes modifiées en production. On passe très – trop – souvent par une remontée intégrale sur le serveur ETL des référentiels, et des comparaisons avec le DW pour tracker les modifications.

Dans des cas plus sympathiques les modifications dans cette base de production sont trackées dans un champ de type DateTime qui stocke le dernier Update par exemple, et il suffit de récupérer les lignes déjà modifiées.
Soit ça marche déjà mieux. Mais c’est limité!

Et si vous voulez récupérer des modifications intermédiaires? Vous implémentez une gestion par trigger lentissime, à vidanger soit même…
Le CDC résout ces problèmes sans vous demander une once d’implémentation.

« Oui… enfin techniquement on doit bien faire quelque chose non? »

Je vous l’accorde. Il faut quand même faire quelques petites choses .
1) Activer CDC sur la base concernée

EXEC sp_cdc_enable_db
GO

2) Activer CDC pour une table à suivre

EXEC sys.sp_cdc_enable_table
@source_schema = ‘MonSchéma’
, @source_name = ‘MaTableASuivre’
, @role_name = NULL /*Rôle pour restreindre l’accès aux tables de tracking, Null si pas de restriction*/
, @capture_instance = ‘NomDeCaptureInstanceDeMaTableASuivre’
, @supports_net_changes = 1 /*Suivi des modifications intermédiaires, 1 pour oui*/
, @captured_column_list = ‘MaColonne1, MaColonne2’
, @filegroup_name = ‘FileGroupPourStockerMaTableDeTracking

Et c’est terminé. Le CDC est en route.

« C’est stocké où? On récupère ça comment? »

Vous avez vu ci-dessus: on spécifie un FileGroup. Tout simplement parce que c’est stocké dans une table par table tracée, et que cette table de tracking, nommée _CT est stockée sur ce FileGroup dans un schéma dédié au cdc. Il est plus que conseillé de ne pas utiliser le FileGroup de données pour le CDC !
Après on peut soit récupérer les infos directement… mais ça n’est pas la manière la plus aisée🙂
Soit utiliser deux fonctions de table:
fn_cdc_get_all_changes_MaCaptureInstance qui récupère tous les changements (U,I,D) entre deux LSN
fn_cdc_get_net_changes_MaCaptureInstance qui récupère le dernier état de chaque tuple entre deux LSN

« LSN? Quel rapport avec le log? »

On l’a dit quelques lignes au dessus: le CDC marche sur le log donc les modifications sont tracées par LSN. Le LSN, ou Log Sequence Number est la valeur unique associée à chaque enregistrement dans le log.
Mais rassurez vous! On peut facilement faire correspondre une date à un LSN, en demandant à la fonction sys.fn_cdc_map_time_to_lsn de renvoyer le LSN le plus proche d’un DateTime.


SELECT @lsn = sys.fn_cdc_map_time_to_lsn (
‘smallest greater than or equal’
, ‘2009-08-21 00:00:00.000’
);

« Ca m’inquiète ce lien avec le Log… alors on est obligés d’être en Full Recovery? »

Non. Mais c’est une bonne question. Cette forte intrication entre le Log et CDC implique quelques avertissements. Lorsque l’on est en Simple ou en Bulk, un mode de log plus verbeux est employé pour les opérations concernées par le CDC.
De plus le CDC empêchent de vider le Log tant que des informations concernées par la capture n’ont pas été lues. Ce qui désactive le SHRINK et donc l’AutoShrink et peut faire grossir des Logs en Simple.

« Et les performances dans tout ça? »

Alors forcément il y a un impact. Le nier dénoterait un tempérament marchand de tapis que je récuse🙂 Il faut absolument passer par le paramétrage de certains aspects du CDC

Choisir les bons attributs: pas toutes les colonnes!
Tuner les jobs de capture: Jouer avec le paramètre maxtrans pour déterminer le nombre de tran examinées par scan, ainsi qu’avec le nombre de scans du log avant une pause avec maxscans et la durée de ces pauses avec pollinginterval.

et beaucoup d’autres conseils et arguments exposés dans cet excellent document publié sur MSDN, le guide du tuning du Change Data Capture du MTE allemand Steffen Krause.

Conclusion?

Potentiellement, le CDC révolutionne le DataWarehousing en économisant du temps d’ETL (généralement 70-80% du projet BI). Ce temps gagné ne l’est pas en vain: toute économie sur des tâches techniques et redondantes permet de se consacrer plus profondément à la problématique métier, et c’est ce qui prime dans cette activité, plus encore qu’ailleurs, non?

A bientôt!

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s