Change Data Capture est un mécanisme intégré à SQL Server depuis la version 2008 et dont le but est de permettre de conserver un historique des modifications apportées aux données d’une ou plusieurs tables. En effet dans certains cas on souhaite sauvegarder toutes les étapes (création, modification, suppression) permettant d’arriver aux données d’une table à un instant T.

Note : CDC n’est disponible que pour les versions entreprise et developer

Activer Change Data Capture

Change Data Capture n’est pas activé par défaut, il faut donc utiliser la commande suivante :

USE Sample     
GO     
EXEC sys.sp_cdc_enable_db     
GO 

Vous pouvez vérifier la bonne exécution de la commande via la requête suivante :

SELECT [name], database_id, is_cdc_enabled     
FROM sys.databases

Vous pouvez également voir que SQL Server à ajouter quelques tables dans la base de données :

Le nom des tables est explicite, mais voici tout de même un exemple des données contenues dans chacune d’entre-elle :

• cdc. captured_columns : liste des colonnes capturées

• cdc. change_tables : Tables dont la  capture est activée

• cdc. ddl_history : Contient tous les changements de structure (DDL)

• cdc. index_columns : Contient les index des tables capturées

• cdc. lsn_time_mapping : Contient le lien entre les LSN et la date.

Activer Change Data Capture sur une table

Une fois CDC activé au niveau de la base de données, il faut l’activer sur les tables pour lesquels on souhaite suivre les changements. Pour cela on exécute la procédure suivante :

EXEC sys.sp_cdc_enable_table     
@source_schema = N'dbo',     
@source_name   = N'Employee',     
@role_name     = NULL     
GO

Au besoin on peut filtrer les colonnes à tracker, cela permet de réduire la quantité de données sauvegardée :

EXEC sys.sp_cdc_enable_table     
@source_schema = N'dbo',     
@source_name   = N'Employee',     
@role_name     = NULL,     
@index_name    = N'PK_Employee',     
@captured_column_list = N'[Id],[FirstName],[LastName]'     
GO

Encore une fois on peut vérifier que CDC est bien activé :

SELECT [name], is_tracked_by_cdc     
FROM sys.tables

On peut également voir qu’une nouvelle table a été créée :

Ainsi que 2 jobs :

Et on peut également utiliser la commande suivante :

EXEC sys.sp_cdc_help_change_data_capture

Maintenant tout changement dans la table Employee (insert, update, delete) ajoutera automatique une ou plusieurs lignes dans la table cdc. dbo_Employee_CT, et ce sans aucune modification des requêtes.

Querying

Commençons par faire quelques opérations sur la table :

INSERT INTO Employee (FirstName, LastName, hid) VALUES ('John', 'Doe', '/1/')     
  
UPDATE Employee     
SET FirstName = 'Jane'     
WHERE Id = 20     
  
UPDATE Employee     
SET hid = '/2/'     
WHERE Id = 20

On peut voir que les changements sont directement enregistrés sous forme brute dans la table créée par CDC :

SELECT * FROM [Sample].[cdc].[dbo_Employee_CT]

Dans cette table on trouve :

• L’opération effectuée (__$operation)
  • 1 = Delete
  • 2 = Insert
  • 3 = Valeur de la ligne avant un Update
  • 4 = Valeur de la ligne après un Update
• Un masque indiquant les colonnes ayant été modifiées en se basant sur leur ordinal
  • 0x0F => colonne 1, 2, 3 et 4
  • 0x02 => colonne 2
  • 0x08 => colonne 4
• Les dernières colonnes correspondent aux valeurs de la ligne dans la table

Dans l’exemple ci-dessus, les 3 opérations sont donc :

• Insertion de la ligne
• Mise à jour de la colonne FirstName de John à Jane
• Mise à jour de la colonne hid de 0x58 à 0x68

SQL Server fourni quelques fonctions permettant de requêter cette table “plus facilement” :

• cdc.fn_cdc_get_all_changes_<capture_instance>(from_lsn, to_lsn, row_filter_options) : Renvoie tous les changements entre 2 dates pour toutes les lignes
• cdc.fn_cdc_get_net_changes_<capture_instance>(from_lsn, to_lsn, row_filter_options) : Renvoie un seul changement par ligne de la table. Ce changement représente le contenu final de la ligne. Par exemple si on fait un insert et trois update, le résultat sera la ligne résultant de ces quatre opérations.

Ces opérations n’utilisent pas le format date mais des LSN. Pour obtenir ceux-ci on peut utiliser les fonctions suivantes :

• sys.fn_cdc_get_min_lsn(‘dbo_Employee’)
• sys.fn_cdc_get_max_lsn()
• sys.fn_cdc_map_time_to_lsn(‘smallest greater than’, GETDATE())
• sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’, GETDATE())

Pour illustrer voici un exemple listant les changements finaux de la table durant les 30 dernières minutes :

SELECT *     
FROM cdc.fn_cdc_get_net_changes_dbo_Employee (     
   sys.fn_cdc_map_time_to_lsn('smallest greater than', DATEADD(minute, -30, GETDATE())),     
   sys.fn_cdc_map_time_to_lsn('largest less than or equal', GETDATE()),     
   'all')

Un autre permettant de lister tous les changements de la table depuis le début :

SELECT *     
FROM cdc.fn_cdc_get_all_changes_dbo_Employee (     
   sys.fn_cdc_get_min_lsn('dbo_Employee'),     
   sys.fn_cdc_get_max_lsn(),     
   N'all');

La capture des données peut vite faire exploser la taille de la base de données. C’est pour cela que SQL Server ajoute par défaut le job cdc. <database>_cleanup. Ce job supprime le contenu des tables régulièrement. Cette tâche est configurée par défaut pour être exécutée tous les jours et conserver le contenu des 3 derniers jours. Pour cela il utilise la procédure sys.sp_cdc_cleanup_change_table

Nous avons fait un tour de la fonctionnalité Change Data Capture. Comme on a pu le voir cette fonctionnalité permet de conserver l’historique des changements effectués sur les données et ce sans avoir quoique ce soit à coder. En effet quelques lignes de configuration suffise à arriver au résultat souhaité.