La capture des données modifiées (CDC) est une fonctionnalité puissante dans SQL Server qui vous permet de suivre les modifications apportées à une table. Elle offre un moyen efficace de capturer et de stocker les modifications de données sans avoir besoin de déclencheurs ou de codage manuel.
Lors d’une récente session chez Bangalore Infosys, j’ai reçu une question intéressante sur le CDC et son comportement lors d’une opération de troncature. Dans cet article de blog, nous explorerons cette question et comprendrons comment le CDC gère les opérations de troncature.
Tout d’abord, clarifions ce qu’est une opération de troncature. La troncature est une opération rapide qui supprime toutes les données d’une table, mais elle ne journalise pas les suppressions de lignes individuelles. Au lieu de cela, elle désalloue les pages de données et met à jour les métadonnées de la table. Cela signifie que l’opération de troncature n’est pas journalisée dans le fichier journal des transactions.
Maintenant, discutons de la façon dont le CDC fonctionne. Le CDC est un processus asynchrone qui lit le fichier journal des transactions pour capturer et stocker les modifications de données. Il s’appuie sur les enregistrements du journal pour identifier les modifications apportées à la table. Étant donné que l’opération de troncature n’est pas journalisée, le CDC ne peut pas capturer les modifications de données lors d’une opération de troncature.
Si vous essayez de tronquer une table qui est activée pour le CDC, vous recevrez un message d’erreur indiquant que la table ne peut pas être tronquée car elle est publiée pour la réplication ou activée pour la capture des données modifiées.
Voici un exemple de scénario qui génère l’erreur ci-dessus :
USE AdventureWorks
-- Créer une table
CREATE TABLE dbo.TestTable (ID INT)
-- Insérer cent enregistrements
INSERT INTO dbo.TestTable (ID)
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
-- Activer le CDC
EXEC sys.sp_cdc_enable_db
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'TestTable', @role_name = NULL
-- Tenter de tronquer la table
TRUNCATE TABLE TestTable
Pour contourner cette limitation, vous avez deux options :
- Utilisez l’instruction DELETE au lieu de TRUNCATE pour supprimer les données de la table. L’instruction DELETE est journalisée et peut être capturée par le CDC.
- Désactivez le CDC sur la table avant d’effectuer l’opération de troncature, puis réactivez le CDC après la fin de l’opération.
Voici un exemple de désactivation et de réactivation du CDC :
USE AdventureWorks
-- Désactiver le CDC sur la table
EXECUTE sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = N'TestTable', @capture_instance = N'dbo_TestTable'
-- Désactiver le CDC sur la base de données
EXEC sys.sp_cdc_disable_db
-- Tronquer la table
-- Activer le CDC sur la base de données
EXEC sys.sp_cdc_enable_db
-- Activer le CDC sur la table
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'TestTable', @role_name = NULL
Il est important de noter que la désactivation et la réactivation du CDC peuvent avoir un impact sur les performances de votre base de données, il est donc recommandé de planifier et de tester ces opérations avec soin.
En conclusion, le CDC ne capture pas les modifications de données lors d’une opération de troncature car la troncature n’est pas journalisée dans le fichier journal des transactions. Pour capturer les modifications de données, vous pouvez utiliser l’instruction DELETE ou désactiver et réactiver le CDC avant et après l’opération de troncature.
Si vous avez des faits intéressants ou des informations sur le CDC, je serais ravi de les entendre. N’hésitez pas à partager vos réflexions dans la section des commentaires ci-dessous.
Restez à l’écoute pour plus d’articles sur les concepts et les meilleures pratiques de SQL Server !