Capture des changements de données (cdc: change data capture)

Commenter

Contenu du snippet

Change Data Capture (CDC) est une nouvelle fonctionnalité intégrée à SQL Server 2008 et qui permet de détecter et récupérer les changements effectués sur une base de données
On peut activer CDC pour récupérer les changements effectués sur une table donnée et les mettres dans une table spécifique qui contient les colonnes suivantes : type de changement (insertion, modification ou suppression) et le changement réellement effectué (l'enregistrement et les colonnes modifiées). ce qui nou permettra de suivre les modifications (ajout, modification, supprerssion des enregistrement)sur une table donnée.

Source / Exemple : 


'Activer CDC :

'Avant d'activer CDC sur une table, il
' faudra activer CDC pour  la base de 
' données :

USE AdventureWorks

GO

EXECUTE sys.sp_cdc_enable_db_change_data_capture;

GO

'Voici un exemple d'un script permettant 
'l'activation du CDC sur la table 
'SalesPerson de AdventureWorks

USE AdventureWorks

EXEC sys.sp_cdc_enable_table_change_data_capture

      @source_schema = 'Sales',

      @source_name = 'SalesPerson',

      @supports_net_changes = 1,

      @role_name = NULL

'Dés que ce script est exécuté une table 
'sera automatiquement crée dans le schéma CDC, dans notre cas 
'cdc.Sales_Salespeople_CT cette table en 
'plus des colonnes de la table 
'SalesPerson elle contient un ensemble 
'd'autre colonnes identifiant et 
'décrivant le changement. Des fonctions 
'sont aussi ajouté pour permettre de 
'requêter la table des changements 
'cdc.Sales_Salespeople_CT. 

'Vous pouvez après faire une simulation 
'en faisant des 
'insertions/modification/suppression et 
'voir les effets  que vos modifications 
'auront sur la table
' cdc.Sales_Salespeople_CT :

INSERT INTO [AdventureWorks].[Sales].[SalesPerson]

        ([SalesPersonID],[TerritoryID],[SalesQuota],[Bonus],[CommissionPct],[SalesYTD])

     VALUES(1,9,250000,5650,0.01,225000)

GO

UPDATE [AdventureWorks].[Sales].[SalesPerson]

   SET [TerritoryID] = 10

 WHERE [SalesPersonID]=290

GO

DELETE FROM [AdventureWorks].[Sales].[SalesPerson]

WHERE [SalesPersonID]=1

GO

'Notter qu'on a insérer puis supprimer 
'l'enregistrement 
'(1,9,250000,5650,0.01,225000).

'Comment récupérer les changements ?

'On peut récupérer les changement tout 
'simplement avec une requette Select sur 
'la table cdc.Sales_Salespeople_CT ou en 
'utilisant les fonctions crées.

'Exemple :

DECLARE @from_lsn binary(10);

DECLARE @to_lsn Binary(10); 

SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', '03-01-2008');

SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', '03-12-2008');

SELECT * FROM cdc.fn_cdc_get_all_changes_Sales_SalesPerson

((SELECT MIN(HRCDC.__$start_lsn) from cdc.Sales_SalesPerson_CT HRCDC WHERE __$start_lsn>=@from_lsn ), 

 (SELECT MAX(HRCDC.__$start_lsn) from cdc.Sales_SalesPerson_CT HRCDC WHERE __$start_lsn<=@to_lsn ),'ALL');

Conclusion :


Ce script récupère les changement effectués sur notre table du 01 au 12 mars 2008. La fonction fn_cdc_get_all_changes_?. permet de récupérer l'ensemble des modifications effectuées, dans notre exemple les trois opérations qu'on effectués sur la table SalesPerson.

Par contre fn_cdc_get_net_changes_?.permet de récupérer les changement nettes (effectives). Dans notre exemple le changement effective est une seule modification car on a insérer puis supprimer l'enregistrement dont le SalesPersonID=1.

en combinant CDC avec proactive-caching et les nouveautés Datawarehousing de SQL Server 2008, ouvre les portes à une BI en temps réel avec synchronisation des changements à partir des bases opérationnelles vers le datawarehouse.

A voir également

S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.