Dans cet article sera présenté une des fonctionnalités de SQL SERVER : CDC (Change Data Capture  ou en français « La capture de données modifiées »). Cette fonctionnalité est présente depuis la version 2008. Cette présentation se fera sur la  version 2012.

Qu’est-ce que le CDC ?

Le CDC  est une fonctionnalité  de SQL SERVER qui permet de suivre et de capturer les différentes modifications ayant eu  lieu sur une table. Les modifications capturées par le « CDC » sont toutes celles effectuées sur la table via les instructions d’insertion (INSERT), de mise à jour (UPDATE) ou de suppression (DELETE).

Quelle est l’importance du CDC ?

Si vous vous demandez à quoi peut bien servir cette fonctionnalité, alors cette section est faite pour vous.  L’objectif du CDC  est d’optimiser l’intégration des données ( du processus ETL)  en requêtant directement les modifications faites sur  une table au lieu de travailler sur l’intégralité de la table et ce faisant augmenter les temps de traitement.  Elle permet entre autres de faire de l’audit de base, de faire de la synchronisation entre deux bases.

Mise en place

Rien de plus simple qu’un exemple pour  vous montrer le fonctionnement du CDC.
La mise en place du CDC va se faire en 2 étapes :
– L’activation du CDC pour la base et la table que l’on veut auditer : cette étape se fera dans SSMS
– La création de deux lots (packages) afin de detecter les modifications : cette étape se fera dans SSIS

Nous allons donc commencer par créer une base « DEMO »  qui va servir de socle pour notre démonstration.

–  Code de création de la base de données

CREATE DATABASE [Demo]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N’Demo‘, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Demo.mdf’ , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’Demo_log‘, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Demo_log.ldf’ , SIZE = 2816KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Puis nous allons créer les tables suivantes  pour les besoins de la démonstration :

– Table Personne

–       Code de création de la table Personne

CREATE TABLE [dbo].[Personne](
[Id] [int] NOT NULL,
[Nom] [nvarchar](50) NULL,
[Prenom] [nvarchar](50) NULL,
[Age] [int] NULL,
[Salaire] [nvarchar](50) NULL,
CONSTRAINT [PK_Personne]
PRIMARY KEY CLUSTERED ( [Id] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] )
ON [PRIMARY]

–        Alimentation de la table Personne

INSERT [dbo].[Personne] ([Id], [Nom], [Prenom], [Age], [Salaire])
VALUES (1, N’OlaOla’, N’Ismail’, 30, N’90000′)
INSERT [dbo].[Personne] ([Id], [Nom], [Prenom], [Age], [Salaire])
VALUES (2, N’Canon’, N’Harry’, 60, N »)
INSERT [dbo].[Personne] ([Id], [Nom], [Prenom], [Age], [Salaire])
VALUES (3, N’Nikon’, N’Malik’, 27, N’36000′)

– Table PersonneDestination

Identique à la table précédente, elle va nous permettre de faire un flux d’ETL simple avec comme source la table Personne.

CREATE TABLE [dbo].[PersonneDestination](
[Id] [int] NOT NULL,
[Nom] [nvarchar](50) NULL,
[Prenom] [nvarchar](50) NULL,
[Age] [int] NULL,
[Salaire] [nvarchar](50) NULL,
CONSTRAINT [PK_PersonneDest] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Avant de pouvoir utiliser le CDC sur une table donnée, il faut faire un certain nombre d’actions.

Activation du CDC sur la base de données

1. Il faut tout d’abord activer le cdc sur la base de données avec la requête suivante :

EXEC sys.sp_cdc_enable_db

Note : Si vous rencontrez l’erreur suivante : 

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 193

Could not update the metadata that indicates database AdventureWorks2012 is enabled for Change Data Capture. The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15517: ‘Cannot execute as the database principal because the principal « dbo » does not exist, this type of principal cannot be impersonated, or you do not have permission.’. Use the action and error to determine the cause of the failure and resubmit the request.

C’est que le propriétaire de la base de données n’est pas l’utilisateur « sa« .

Database Owner ca

Pour cela, il faut exécuter la requête suivante : USE <Nom de votre Base de donnée>  EXEC sp_changedbowner ‘sa’

4. Pour voir si la requête a bien été exécutée et donc que le CDC est bien activé sur votre base, exécutez la requête suivante :

                                     SELECT name AS Databases, is_cdc_enabled  FROM sys.databases

Note : Une fois la requête exécutée, les tables systèmes suivantes sont crées :

cdc.captured_columns : Retourne une ligne pour chaque colonne suivie dans une instance de capture.

cdc.change_tables : Retourne une ligne pour chaque table de modifications de la base de données.

cdc.ddl_history : Retourne une ligne pour chaque modification du langage de définition de données (DDL) apportée aux tables qui sont activées pour la capture de données modifiées.

cdc.index_columns : Retourne une ligne pour chaque colonne d’index associée à une table de modifications.

cdc.lsn_time_mapping : Retourne une ligne pour chaque transaction qui a des lignes dans une table de modifications.

Activation du CDC sur la table

Après avoir activé le « CDC » sur la base, il faut l’activer sur la table dont on désire tracer les évolutions. Dans notre démonstration, il s’agit de la table Personne.

Pour se faire  il faut :

–       S’assurer que  SQL Server agent est bien activé car la détection des changements de données ne peut se faire sans cela.En effet deux jobs permettant cette action sont executés.Nous les verrons par la suite.

–       Créer le rôle d’exécution du CDC

CREATE ROLE cdc_role;

–       Activer le CDC sur la table en exécutant la procédure stockée sp_cdc_enable_table :

EXEC sys.sp_cdc_enable_table
@source_schema = N’dbo’,         //Nom du schéma auquel appartient la table source
@source_name = N’Personne’,  //Nom de la table source sur laquelle activer la capture de données modifiées
@role_name = N’cdc_role’,        //Nom du rôle de base de données utilisé pour réguler l’accès aux données modifiées
@supports_net_changes = 1;

Note : il est possible de ne pas spécifier de role en laissant la variable à nulle : @role_name = NULL

Une fois la commande exécutée de nouveaux éléments sont crées :

– deux jobs : cdc.Demo_capture et cdc.Demo_cleanup

– la table système  cdc.dbo_Personne_CT qui est retourne une ligne pour chaque opération d’insertion et de suppression effectuée sur la table source, et deux lignes pour chaque opération de mise à jour effectuée sur la table source.
Son nom peut être défini lors de l’activation du CDC pour la table source avec le paramètre @Capture_instance, sinon par défaut, elle aura la forme suivante : cdc.nom_de_la_table_CT (dans notre cas CDC.DBO_PERSONNE_CT )..

                                   

2. Pour vérifier que la table dbo.Personne est bien activée pour le CDC, exécutez la requête suivante : SELECT name, is_tracked_by_cdc FROM sys.tables

CREATION D’UN ETL et EXEMPLE D’UTILISATION DU CDC 

L’exemple d’utilisation du CDC lors d’un processus ETL se fera en deux parties (2 lots) :

– une premier lot d’initialisation qui va permettre de charger toutes les lignes de la table source à la table destination afin de lire les données.
– une deuxieme lot qui va permettre de detecter les modifications (Ajout, Suppression, Mise à Jour) de données de la table source.

PREMIER LOT  : Création du lot de chargement initial

1. Créez un lot ssis
2. Créez une connexion ADO.net vers la base de données qui contient la table dbo.Personne

3. Ajoutez une tache « CDC Control Task » et double-cliquez pour l’éditer.

Cette tache va gérer le cycle de vie des lots CDC ainsi que plages de numéros séquentiels dans le journal LSN.
Note : Les numéros de séquence (Log Sequence Number, LSN) représente les détails des opérations DML et DDL qui sont enregistrés dans le journal de transaction.

Pour plus d’informations sur le LSN : http://technet.microsoft.com/fr-fr/library/ms190411(v=sql.105).aspx
et sur le CDC Controle Task : http://technet.microsoft.com/fr-fr/library/hh758674.aspx

(1) : Sélectionnez la connexion à la base « Demo ».
(2) : Pour le champ « CDC control operation« , sélectionnez « Mark initial load start« .
Note : L’ opération « Mark initial load start » est utilisée au début du lot de charge initiale pour enregistrer le LSN dans la base de données source avant que le lot de charge initiale commence à lire les tables sources.
(3) (4) : Créez la variable « CDC_State » dans laquelle sera enregistré l’état du CDC.
(5) : Sélectionnez la base de données de destination
(6) (7) : Créez la table « cdc_states ».Cette table sera utiliser afin de connaitre l’état du cdc.
(8) : Sélectionnez la variable ou sera lu l’état du cdc. 

4. Ajoutez un tache Data Flow Task.
Ajoutez une source et une destination ADO.NET
et Configurez les comme ci-dessous :

5. Ajoutez une nouvelle tache CDC Control Task et configurez-là comme suit :

L’opération « Mark initial load end » est utilisée à la fin d’un package de charge initiale pour enregistrer le LSN dans la base de données source une fois que le package de charge initiale a fini de lire les tables sources.

DEUXIEME LOT : Création du lot de chargement incrémental

1. Créez un nouveau package et créez la connexion ADO.net vers la base Demo.

2. Ajoutez une tache CDC Control Task et configurez-le de la manière suivante :

Sélectionnez l’opération Get processing range est utilisée avant d’appeler le Data Flow qui utilise le composant CDC Source. Elle établit une plage de LSN que le composant CDC Source lit lorsqu’il est appelé. La plage est stockée dans une variable de package SSIS qui est utilisée par le CDC Source pendant le traitement du DataFlow.

3. Ajoutez un Data Flow Task et insérez le composant CDC Source.
  Configurez le composant comme ci-dessous :

Note : Les différents modes de traitement CDC (CDC Processing mode) :

All : retourne les modifications de la plage de capture CDC sans les valeurs Avant la mise à jour.

All with old values : retourne les modifications de la plage de traitement CDC actuelle, y compris les anciennes valeurs (Avant la mise à jour). Chaque opération de mise à jour utilise deux lignes, une avec les valeurs avant la mise à jour et une avec la valeur après la mise à jour.

Net : retourne une seule ligne modifiée par ligne source modifiée dans la plage de traitement CDC. Si une ligne source a été mise à jour plusieurs fois, la modification associée est appliquée (par exemple, l’insertion et la mise à jour sont considérées comme une mise à jour unique, et la mise à jour et la suppression sont considérées comme une suppression unique). Lorsque vous travaillez en mode de traitement de modifications Net, il est possible de fractionner les modifications apportées aux sorties de suppression, d’insertion et de mise à jour et de les traiter en parallèle car la ligne source apparaît dans plusieurs sorties.

Net with update mask : ce mode est semblable au mode Net standard, à ceci près qu’il ajoute des colonnes booléennes au modèle de nom __$<nom-colonne>__Changed indiquant les colonnes modifiées dans la ligne modifiée actuelle.

Net with merge : ce mode est semblable au mode Net standard, à ceci près que les opérations d’insertion et de mise à jour sont fusionnées en une seule opération de fusion (UPSERT).

4. Ajouter le composant CDC Splitter

Le CDC Splitter va nous permettre de séparer le flux de données entrant en plusieurs flux.
On pourra donc créer un flux pour les opérations de suppression, d’insertion et de mise à jour.
Pour plus d’informations : http://technet.microsoft.com/fr-fr/library/hh758656.aspx

5. Ajoutez 3 Composants Derived Colunm Transformation et liez les au CDC Splitter

6. Ajouter une tache CDC Control Task dans le flux de contrôle avec pour opération « Mark processed range« .
Cette opération est exécutée après chaque exécution du CDC (une fois le flux du CDC terminé avec succès) pour consigner le dernier LSN qui a été entièrement traité dans le cadre de l’exécution du CDC. Lors de la prochaine exécution de GetProcessingRange, cette position constitue le début de la plage de traitement.

Exécution des lots

Premier lot : Chargement initiale

Avant l’exécution de ce lot, analysons la table Cdc_States.
Nous avons bien un table vide.

Etape 1 : Exécution du « CDC Control Task – Mark Initial Load Start »

On peut voir que l’état du CDC est ILSTART.
Il s’agit tout simplement de l’état au démarrage de l’étape initiale Mark Initial Load Start, qui marque le début de la charge initiale.

Etape 2 : Exécution du Data Flow Task – Insertion

L’insertion des données de la table source à la table destination, se déroule avec succès. Les données de la table Personne sont insérées dans la table PersonneDestination.

Etape 3 : Exécution du « CDC Control Task – Mark Initial Load End »

L’état du CDC est maintenant ILEND, qui est l’état qui marque la fin, avec succès, de la charge initiale.
Cet état fait référence à l’opération Mark Initial Load End.

Deuxième Lot : Détection des modifications

Voici notre lot dans son ensemble :

Etape 1 : Exécution du « CDC Control Task – Get Processing Range »

Ici, nous avions utilisé l’opération Get Processing Range.
Nous avons comme état du cdc la valeur ILUPDATE qui est l’état à la première exécution du lot.

Etape 2 : Exécution du « Data Flow Task – Capture des données modifiées »
Il ne se passe rien, car aucunes modifications n’a été détectées.

Etape 3 : Exécution du « CDC Control Task – Mark Process Range »

L’état TFEND qu’en à lui, permet de signaler que l’exécution précédente a réussi et qu’une nouvelle exécution avec une nouvelle plage de traitement peut démarrer.

Maintenant ajoutons 3 composants « Execute SQL Task » pour effectuer 2 updates, 1 insert et 1 delete dans notre base.
On peut tout aussi bien faire les requêtes dans SSMS pour faire ces modifications, mais compliquons nous la vie pour le plaisir de faire du IS :-p

Pour la commande Update on a :

UPDATE [Demo].[dbo].[Personne]
SET Nom = ‘KANCEL’
WHERE Prenom = ‘Harry’

UPDATE [Demo].[dbo].[Personne]
SET Salaire = 2000
WHERE Prenom = ‘Harry’

Pour la commande Delete on a :

DELETE FROM [Demo].[dbo].[Personne]
WHERE id = 3

Pour la commande Insert on a :
IF NOT EXISTS(SELECT * FROM [dbo].[Personne] WHERE Id = 8)
INSERT [dbo].[Personne] ([Id], [Nom], [Prenom], [Age], [Salaire])
VALUES (8, N’Parrain’, N’Pascal’, 25, 36000)

Exécution du lot avec modifications en base

Etape 1 : Exécution du « CDC Control Task – Get Processing Range »

L’état du CDC est maintenant TFSTART et non ILSTART comme précédemment.
Nous sommes à l’opération GetProcessingRange.
ILSTART marque l’état pour toutes les éxécutions qui sont faites après le premier appel de cette meme opération, ci-dessus.

Etape 2 : Exécution du « CDC Control Task – Mark Process Range »

Les modifications ont bien été effectuées, et nous arrivons à l’étape de l’opération Mark Process Range qui nous donne l’état TFEND comme précedemment.

Pour vous aider à mieux voir les différents mouvements effectués lors de l’exécution du lot, voici un diagramme qui montre ce qu’il se passe.

Source : http://msdn.microsoft.com/fr-fr/library/hh231087.aspx

Maintenant lançons de nouveau et observons le comportement de notre lot.

Aller plus loin :

Composants de flux CDC : http://msdn.microsoft.com/fr-fr/library/hh231087.aspx
Éditeur de source CDC (page Gestionnaire de connexions) : http://msdn.microsoft.com/fr-fr/library/hh231004.aspx
Définir une variable d’état : http://msdn.microsoft.com/fr-fr/library/hh758667.aspx
Tâche de contrôle de capture de données modifiées : http://msdn.microsoft.com/fr-fr/library/hh758674.aspx