Le Change Data Capture (que nous appellerons CDC) est une nouvelle fonctionnalité de SQL Server 2008. Bien qu’apparue dès la CTP2, cet article se base sur la CTP5 et il n’est pas exclu qu’il y ait des modifications dans les versions suivantes.
Le CDC a une orientation initiale pour les processus d’ETL. L’objectif de CDC est d’optimiser l’intégration des données en requêtant directement les modifications faites sur les bases de production, plutôt que de comparer la source et la destination.
Bien entendu, on peut étendre l’utilisation du CDC à de la synchronisation entre 2 bases, à de l’audit ou à tout autre besoin nécessitant de connaître ce qu’il se passe sur une table.
Les exemples de cet article se basent sur AdventureWorks. Nous utiliserons la table des commandes SalesOrderHeader.
Le code source des démos se trouve sur : http://blog.djeepy1.net/public/labs/cdc/cdc_work.sql
Le CDC capture les modifications qui se font sur les tables. On choisit les tables sur lesquelles on souhaite faire la capture, toutes les modifications ne sont évidemment pas monitorées. On peut même restreindre la capture à quelques colonnes d’une table.
Le CDC est un processus asynchrone. Il fonctionne comme la réplication transactionnelle à savoir qu’un « agent » lit le journal de transaction (transaction log) et met de côté les modifications dans des tables spécifiques.
C’est la (ou les) applications « clients » qui viennent chercher les modifications. La récupération des modifications se fait sur demande en mode pull.
Comme beaucoup de fonctionnalité depuis SQL Server 2005, la première étape est d’activer le Change Data Capture (CDC). On l’active au niveau de la base de données via une procédure stockée système :
Exec sys.sp_cdc_enable_db_change_data_capture
Pour savoir si votre base est déjà prête pour le CDC, il suffit de requêter le champ is_cdc_enabled de la vue système des bases de données (sys.databases).
Select name, is_cdc_enabled
From sys.databases
L’activation du CDC ne change pas seulement ce flag mais crée tout un ensemble d’objets dans la base de données pour gérer la fonctionnalité.
Ces objets sont créés dans le schéma cdc ce qui permet de les masquer aux simples utilisateurs de votre base et de les protéger.
Note : si un schéma ou un utilisateur nommé cdc existe déjà, l’activation du CDC est impossible car cdc est un mot réservé ; il faut juste le savoir.
Une fois la base prête, il faut enregistrer une table à surveiller. Cela se fait aussi par une procédure stockée système : sys.sp_cdc_enable_table_change_data_capture. On doit préciser la table (schéma et nom) et un rôle avec lequel le système va atteindre la table. On peut préciser des options pour tirer partie des fonctionnalités avancées du CDC ; ces fonctions seront décrites plus en avant dans l’article.
exec sys.sp_cdc_enable_table_change_data_capture
@source_schema = 'Sales',
@source_name = 'SalesOrderHeader',
@role_name = 'datacapture'
L’appel de cette procédure système crée aussi des objets dans la base de données, le fonctionnement de ces objets est décrit plus loin :
- Une table( cdc.Sales_SalesOrderHeader_CT) qui est une copie de la table enregistrée mais avec des champs techniques en plus, on l’appelle la change table
- Des procédures stockées : sp_insdel_<id> et sp_upd_<id>
- Des fonctions : fn_cdc_get_all_changes_<captureinstance>
Ce qu’il est important de comprendre, c’est la notion d’instance de capture. Lorsque l’on enregistre une table au CDC, on crée une instance de capture sur cette table. Cette instance de capture sera utilisée dans les différentes opérations relatives au CDC, il est donc important de connaître son nom. Par défaut, il est constitué du schéma et du nom de la table enregistrée, ex : Sales_SalesOrderHeader. Pour le personnaliser, on doit le fournir à l’enregistrement de la table.
Comme pour la base de données, on peut savoir si une table est déjà enregistrée en requêtant les vues systèmes :
Select name, is_tracked_by_cdc
From sys.tables
On peut restreindre la capture à une liste définie de colonnes de la table. Par défaut, toutes les colonnes sont incluses dans la capture. Si on décide de réduire le nombre de colonnes incluses, il faut obligatoirement inclure un index unique ou la clé primaire pour que le CDC puisse identifier de façon unique l’enregistrement modifié.
La liste des colonnes se passe à la procédure d’enregistrement de la table au CDC :
exec sys.sp_cdc_enable_table_change_data_capture
[…]
, @captured_column_list = 'salesorderid,status';
Attention, il y a des noms de colonnes réservés mais vu leur format (__$xxx), je doute que la plupart d’entre nous soit concerné.
On peut préciser de stocker les changements capturés dans un groupe de fichiers séparé. Pour cela, on le précise à l’enregistrement de la table pour le CDC.
exec sys.sp_cdc_enable_table_change_data_capture
[…]
, @filegroup_name = N'PRIMARY';
Chaque entité peut être suivie par 2 captures différentes. Pour cela, on identifie chaque capture au moment de sa mise en place avec le paramètre optionnel @capture_instance. Par défaut, le nom de la capture est schema_table (ex : dbo_simpletable).
Le CDC présente 2 modes de fonctionnement :
· Tous les changements : on récupère toutes les opérations sur chaque enregistrement de façon unitaire. Si 2 opérations ont lieu sur la même donnée, on récupèrera les 2.
· « Net changes » : permet de récupérer les modifications « nettes », c'est-à-dire que l’on ne récupère que la version finale de chaque enregistrement modifié. Par exemple, je mets à jour puis je supprime un enregistrement, je ne récupèrerai que la suppression via le CDC.
Ce dernier mode nécessite l’utilisation de la clé primaire ou d’un index unique pour fonctionner, il faudra le préciser lors de l’enregistrement de la table pour le CDC.
Source : Books On Line SQL Server 2008 CTP5
Le CDC fonctionne avec le SQL Agent. Un agent lit en permanence le journal de transaction et repère les modifications opérées sur les tables enregistrées au CDC. A chaque modification, les procédures stockées insdel ou upd de l’élément monitoré (ex : cdc.sp_ins_xxx) sont appelées. Ces procédures insèrent la modification dans la change table. Ce processus se fait de façon asynchrone.
Une insertion ou une suppression dans la table surveillée ajoute une ligne dans la change table. Une mise à jour ajoute 2 lignes dans la change table, une pour les anciennes valeurs des champs, l’autre pour les nouvelles.
Attention, dans les exemples, il y a un trigger sur la date donc une modification entraine 4 entrées dans la Change Table (cf. chapitre 6.2).
Cela se fait par l’appel d’une fonction. La fonction est différente selon le mode de fonctionnement :
- cdc.fn_cdc_get_all_changes_<capture_instance> : tous les changements sont récupérés
- cdc.fn_cdc_get_net_changes_<capture_instance> : seuls les changements finaux sont récupérés
Ces fonctions sont des Table-valued fonctions, on les utilise donc dans la clause FROM. Elles prennent en paramètres l’intervalle sur lequel on souhaite récupérer les changements. Cette intervalle est matérialisé par ses 2 bornes, sous la forme de paramètres de type binary(10) appelés LSN. LSN signifie Logical Sequence Number et correspond à la transaction dans le journal de la base de données. En effet, les changements étant récupérés par la lecture du journal de la base via un LogReader, tout leur séquencement dans le temps se base sur l’unité du journal à savoir les LSN.
Select *
From cdc.fn_cdc_get_all_changes_Sales_SalesOrderHeader(
@from_LSN
,@to_LSN
'all')
Rassurons nous, des fonctions nous permettent de translater une date en LSN (et inversement). Les premières permettent de récupérer le LSN le plus ancien et le LSN le plus récent pour une instance de capture. A noter que le plus récent est commun à toutes les instances de capture d’une table puisque c’est la dernière modification de la table.
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn('Sales_SalesOrderHeader');
SET @to_lsn = sys.fn_cdc_get_max_lsn();
Le système d’écoute des modifications du CDC peut alors conserver le dernier LSN récupéré et aura juste à l’incrémenter pour obtenir la borne inférieure du prochain intervalle de requête :
SET @from_lsn = sys.fn_cdc_increment_lsn(@to_lsn);
On peut aussi travailler avec des dates et obtenir les LSN à partir de celles-ci. Pour cela, on utilisera les fonctions sys.fn_cdc_map_time_to_lsn qui nous retourne le LSN le plus proche (en précisant si on cherche l’inférieur ou le supérieur) d’une date.
SET @from_lsn = sys. fn_cdc_map_time_to_lsn (‘smaller greater than’, ‘2008-01-21 22:00’);
Dans les informations récupérées, on notera les suivantes :
- __$operation : cette valeur indique le type de modification opérée sur la ligne (1 pour delete, 2 pour insert, etc.)
- LSN : les 2 valeurs __$start_lsn et __$eqval permettent d’ordonner les modifications. La première indique l’identifiant de transaction et le second indique l’ordre du traitement dans la transaction.
- Champs : on trouve dans le résultat la liste des champs dont on capture les modifications
- UpdateMask : ce masque de bits permet dans le cas d’une opération d’update de savoir quels champs ont été modifiés
La lecture des changements ne les « invalide » pas. C’est pour cela que c’est à l’application qui consomme les changements de garder le dernier LSN lu (ou de travailler avec des dates). Cela permet de mettre en place plusieurs consommateurs de CDC.
--on doit garder une référence sur les LSN déjà lus
DECLARE @from_lsn binary(10), @to_lsn binary(10);
--on utilise des fonctions pour obtenir les LSN min et max
SET @from_lsn = sys.fn_cdc_get_min_lsn('Sales_SalesOrderHeader');
--capture instance
SET @to_lsn = sys.fn_cdc_get_max_lsn();
Select @from_lsn as [From], @to_lsn as [To]
Select *
From cdc.fn_cdc_get_all_changes_Sales_SalesOrderHeader(
@from_lsn,
@to_lsn,
'all'); --'all update old' pour ajouter les anciennes valeurs (__$operation = 3)
update sales.salesorderheader
set duedate = '2008-01-02'
where salesorderid = 43670
--
--on incrémente les LSN min et max (ATTENTION, si @from_lsn > @to_lsn ==> error)
SET @from_lsn = sys.fn_cdc_increment_lsn(@to_lsn);
SET @to_lsn = sys.fn_cdc_get_max_lsn();
Select @from_lsn as [From], @to_lsn as [To]
if @from_lsn > @to_lsn
print 'Pas de changement (ou non encore reportés dans la CT)'
else
Select *
From cdc.fn_cdc_get_all_changes_Sales_SalesOrderHeader(
@from_lsn,
@to_lsn,
'all');
Les données sont gardées pendant 3 jours. Un processus de nettoyage vide les changements obsolètes automatiquement. Cette durée est paramétrable mais faîtes attention à la volumétrie.
On modifie le batch de nettoyage par la procédure stockée système :
Cette procédure permet aussi de configurer l’agent de collecte du CDC.
Les triggers sont pris en compte puisque le CDC se base sur le journal de transaction de la base de données. Par contre, l’utilisation de triggers augmentera le nombre d’enregistrements dans la change table. Par exemple, si j’ai un trigger qui met à jour un champ après une insertion, j’aurai 3 lignes dans la change table, 1 pour l’insertion (operation=2) et 2 pour la mise à jour (operation=3 et 4).
Le CDC supporte des modifications dans le schéma des tables surveillées. Pour cela, un trigger DDL est mis en place et met à jour la Change Table, voire la supprime en cas de suppression de la table source.
Le CDC est ouvert car on peut modifier les éléments qui le constituent. On peut modifier les procédures appelées lors des modifications pour envoyer les données sur un serveur lié par exemple ou pour valider une jointure avec une table technique. On peut aussi modifier les fonctions de récupération des modifications pour filtrer sur certains critères par exemple.
Ce ne sont que des exemples mais je laisse libre cours à votre imagination.
La fonctionnalité Change Data Capture est simple à mettre en œuvre. Elle permet de tracer l’activité sur une base de données avec une orientation ETL puisque l’on sauvegarde les modifications sur les données. Son mode de fonctionnement lui permet de ne pas être intrusive dans les transactions puisque c’est un agent asynchrone qui lit le journal de transaction.
Même si on peut l’utiliser dans cet objectif, CDC n’est pas une fonctionnalité d’Audit de base de données. Pour cela vous aurez les fonctionnalités d’AUDIT prévues dans les prochaines CTP ou vous avez le système actuel d’événements de SQL Server même si leur but n’est pas de conserver les modifications, encore moins sur plusieurs « abonnés ».
A ne pas aussi confondre avec le CHANGE TRACKING qui fournit le même type de service sauf qu’il ne stocke pas les données modifiées mais les clés primaires et qu’il est synchrone. Il est donc plus adapté pour de la synchronisation bi-directionnelle. Mais je laisserai Sébastien Pertus vous en parler dans un de ces prochains articles.
Par contre, attention à la volumétrie générée car les Change Tables contiennent toutes les données modifiées. Pensez à les lire régulièrement et à les purger.