Datafly.pro

Pour compléter cette lecture, vous pouvez consulter d'abord l'article précédent sur le Change Tracking (suivi des modifications).

Vous pouvez télécharger le script de démo ici : Change_Data_Capture.zip

Le Change Data Capture est une fonctionnalité intégrée à SQL Server depuis la version 2008. Contrairement au Change Tracking disponible dès l'édition Standard, elle n'est présente que dans l'édition Enterprise.

Côté fonctionnalité, la finalité est la même que le Change Tracking : suivre et journaliser les modifications effectuées sur les données, de manière à pouvoir synchroniser des données de manière incrémentielle entre tables vers d'autres bases de données, SQL Server ou non.

Là où le Change Data Capture (CDC) se différencie du Change Tracking (CT) :

• Le CDC n'est disponible que dans l'édition Enterprise.

• Le CDC est un suivi asynchrone. Il s'appuie sur un  job de "Log Reader" exécuté par l'agent SQL Server, très similaire à ce qui existe pour la réplication transactionnelle, les modifications sont donc capturées à partir du journal de transactions.
  L'impact sur les performances des transactions serait donc à priori moindre que pour le Change Tracking pour lequel la journalisation est synchrone (l'insertion dans la table système de journalisation) fait partie du plan d'exécution de chaque requête.
  Basée sur le journal de transaction, le CDC utilise le LSN (log sequence number) pour numéroter les modifications, là où le CT utilise son propre compteur de modifications.

• Le CDC capture les données qui sont modifiées (le Change Tracking quant à lui ne capture que les clés des lignes modifiées).

• Le CDC permet si nécessaire d'obtenir toutes les modifications successives (et même les valeurs "before" et "after" dans le cas d'un update), là où le CT ne permet d'obtenir que des modification "nettes" (pour rappel, avec les modications "nettes", les opérations successives sont synthétisées et consolidées : un insert suivi de 12 updates effectués sur la même ligne est traduit par une seule opération insert lorsqu'il s'agit de synchroniser nos données...)

• Pour le CDC, il existe des composants spécifiques dans SSIS qui facilitent la gestion et permettent une prise en charge complète de la synchronisation avec l'ETL.

Ces caractéristiques en font donc un outil de choix pour la synchronisation de données, en particulier :

• Lorsqu'on ne veut pas impacter les plans d'exécution des transactions utilisateurs par de la journalisation synchrone
• Lorsqu'on souhaite capturer, stocker et obtenir les valeurs successives des modifications (comme par exemple pour traiter le cas des tables sans clés primaires)
• Lorsque l'on veut se faciliter la tâche dans SSIS grâce aux composants dédiés de l'ETL

Comment l'installer ?

• Activer tout d'abord le CDC dans la base de données concernée avec la procédure stockée sys.sp_cdc_enable_db.

EXEC sys.sp_cdc_enable_db  -- activation du CDC
GO
SELECT  name
      , is_cdc_enabled
FROM    sys.databases
GO

• Ensuite, activer sur chaque table concernée, avec la procédure stockée sys.sp_cdc_enable_table. Plusieurs options peuvent être spécifiées, en particulier le nom de l'instance de capture (plusieurs captures simultanées peuvent être actives sur une même table), prise en charge ou non des modifications "nettes" (dans ce cas une clé primaire ou un index unique est nécessaire), la liste des colonnes à capturer, les options de stockage (filegroup).
  L'activation du CDC sur la première table crée et lance le job de log reader dans l'agent SQL Server.

 

EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'person',   -- exemple avec la table 'person'
    @role_name = NULL                           -- rôle de base de donnée pour limiter l'accès aux données de modification
-- options facultatives
    , @capture_instance = 'Person_Instance1'    -- plusieurs instances de capture sur la même table sont possibles
    , @supports_net_changes = 1                 -- interrogation des modifications nettes (consolidées) : PK nécessaire, ou index unique à spécifier
    , @index_name = NULL                        -- nom d'un index pour identifier les lignes
    , @captured_column_list = NULL              -- liste des colonnes à capturer
    , @filegroup_name = NULL                    -- filegroup, sinon par filegroup par défaut
    , @allow_partition_switch = 1               -- permet les switch partition si la table est partitionnée
 GO

Le travail 'cdc.CDCSource_capture' a démarré avec succès.
Le travail 'cdc.CDCSource_cleanup' a démarré avec succès.

 

 

 

 SELECT [name], is_tracked_by_cdc FROM sys.tables  -- voir sur quelles tables le CDC est activé

 

 

Quels sont les objets système créés ?

Notre capture est maintenant active. Plusieurs tables ont été créées dans la base de données, dans un schéma nommé cdc :

• cdc.change_tables          -- liste des tables capturées
• cdc.captured_columns    -- liste des colonnes capturées
• cdc.index_columns         -- clés uniques des tables, qui sont utilisées pour les modifications nettes
• cdc.ddl_history               -- historique des modifications DDL sur les tables capturées
• cdc.lsn_time_mapping    -- table de mappage entre les lsn et le temps
• dbo.systranschemas       -- suivi des modifications de schéma

Et pour chaque table capturée, une table de journalisation :

• cdc.xxxxxx     -- (ou xxxxxx est le nom de la table ou de l'instance de capture)

Le CDC nous crée aussi des fonctions qui vont nous permettre d'extraire les modifications : pour chaque table/instance de capture, nous avons les fonctions fn_cdc_get_all_changes_xxxxxx et fn_cdc_get_net_changes_xxxxxx (ou xxxxxx est le nom de la table ou de l'instance de capture) :

• fn_cdc_get_all_changes_xxxxxx ( from_lsn , to_lsn , '<row_filter_option>' ) : retourne toutes les modifications entre deux lsn (log sequence number). Le 3ème paramètre permet de choisir si l'on souhaite n'obtenir que les nouvelles valeurs pour chaque modification, ou bien toutes les modifications (pour les mises à jour les valeurs avant et après mise à jour).
• fn_cdc_get_net_changes_xxxxxx ( from_lsn , to_lsn , '<row_filter_option>' ) : retourne les modifications "nettes" entre deux lsn, ce qui n'est possible que si les modifications nettes ont été activéées dans les options de la capture. Le 3ème paramètre détermine quelles métadonnées seront renvoyées (masque des colonnes capturées ou logique de modification pour effectuer la synchronisation par une instruction MERGE)

Extraire les données modifiées

Nous avons pour cela besoin de connaître la plage de modifications à extraire en spécifiant les lsn de début et de fin : from_lsn et to_lsn entre lesquels nous souhaitons obtenir les modifications. Plusieurs fonctions du CDC vont nous aider à obtenir les lsn :
 

• sys.fn_cdc_get_min_lsn('capture_instance') : obtenir le plus petit lsn pour une instance de capture
• sys.fn_cdc_get_max_lsn() : obtenir le plus grand lsn
• sys.fn_cdc_map_time_to_lsn ( '<relational_operator>', tracking_time ) : permet de trouver le lsn pour l'heure spécifiée. L'opérateur permet de préciser la recherche du lsn pour l'heure spécifiée : 'largest less than', 'largest less than or equa'l, 'smallest greater than', 'smallest greater than or equal'
• sys.fn_cdc_map_lsn_to_time : à l'inverse, permet de trouver l'heure pour un lsn spécifié

Par exemple, pour récupérer toutes les modifications pour notre instance de capture Person_Instance1 :

 

 

DECLARE @from_lsn BINARY(10)  , @to_lsn BINARY(10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('Person_Instance1')
SET @to_lsn = sys.fn_cdc_get_max_lsn() -- ou bien : sys.fn_cdc_map_time_to_lsn('largest less than or equal', GETDATE())

Il ne nous reste plus qu'à interroger notre fonction du CDC pour obtenir une table de modifications :

 

 

 

SELECT  * FROM    cdc.fn_cdc_get_all_changes_Person_Instance1(@from_lsn, @to_lsn, 'All update old')

La colonne __$operation indique quelle est l'opération effectuée :

 

 

• 1 pour DELETE
• 2 pour INSERT
• 3 pour UPDATE (valeur avant)
• 4 pour UPDATE (valeur après)

pour faire un peu plus joli (avec la fonction CHOOSE) :

 

 

SELECT  * , CHOOSE(__$operation, 'DELETE', 'INSERT', 'UPDATE (before)','UPDATE (after)')
FROM    cdc.fn_cdc_get_all_changes_Person_Instance1(@from_lsn, @to_lsn, 'All update old')

Nous avions utilisé la fonction fn_cdc_get_all_changes avec l'option 'All update old', ce qui nous donne toutes les versions d'une même ligne ainsi que les valeurs avant et après chaque UPDATE.
Voici ce que donnent d'autres syntaxes :

cdc.fn_cdc_get_all_changes_Person_Instance1(@from_lsn, @to_lsn, 'All') : toutes les versions, mais seulement les valeurs "après" des UPDATE


cdc.fn_cdc_get_net_changes_Person_Instance1(@from_lsn, @to_lsn, 'All') : les modifications "nettes"


Il ne nous reste plus maintenant qu'à construire notre projet de synchronisation incrémentielle : conserver le lsn de fin de la synchronisation dans une table pour servir de point de départ à la prochaine synchro, etc...
Mais il y a aussi plus simple et plus graphique : utiliser SSIS avec ses composants dédiés pour gérer la synchronisation... Nous verrons cela dans la prochaine partie de cet article !