Comme beaucoup de fonctionnalités 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é.
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).