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