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

sys.databases

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

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. captured_columns

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

cdc. change_tables

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

cdc. ddl_history

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

cdc. index_columns

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

cdc. lsn_time_mapping

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

sys.tables

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

nouvelle table

Ainsi que 2 jobs :

nouveaux 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]

Querying

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” :

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

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')

changements finaux

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');

changements depuis le débuts

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é.

Ne ratez plus aucune actualité avec la newsletter mensuelle de SoftFluent

Newsletter SoftFluent