Un moyen plus simple d'implémenter la capture de données de modification basée sur des déclencheurs dans SQL Server

Supposons que vous disposiez d'un SQL avec des informations client connectées à un entrepôt de données. Vos analystes utilisent cette base de données pour créer des campagnes marketing personnalisées pour vos clients. De nouvelles données client arrivent régulièrement et les anciennes données client changent fréquemment. Pour tenir compte de ces changements, vous devez mettre à jour votre entrepôt de données régulièrement, ce qui peut entraîner des problèmes de latence et ralentir les analyses. Le résultat? Vous vous appuyez sur des données obsolètes pour vos décisions cruciales.

Pour vous assurer de toujours prendre des décisions fiables, vous pouvez utiliser la capture de données modifiées (CDC), un moyen plus simple de mettre à jour les données dans une destination cible. CDC détecte les changements dans les données de la table source et met à jour votre destination cible en conséquence. Il garantit que vos données ne sont jamais obsolètes et que vos décisions sont toujours basées sur des informations fiables et à jour. Il existe différents types de capture de données modifiées, telles que celles basées sur le journal, les données modifiées, les différences et les déclencheurs.

Ce blog fournira un aperçu de la façon dont CDC est utile dans la gestion des données et discutera de la capture de données de changement basée sur les déclencheurs dans Serveur SQL.

CDC et gestion des données

CDC change la donne dans la gestion des données. Avec CDC, vous n'avez qu'à mettre à jour les changements dans votre destination cible. En conséquence, vous pouvez exécuter rapidement des processus ETL.

Il peut jouer un rôle central dans la planification d'urgence. Imaginez que votre centre de données ait perdu des données et qu'il manque certains fichiers à la sauvegarde. Avec CDC, vous pouvez synchroniser vos données principales et vos sources de sauvegarde et vous assurer que vos plans de reprise après sinistre sont infaillibles.

CDC accélère la Business Intelligence (BI) en déplaçant les données entre les bases de données en temps réel. Il est également utile dans le secteur de la santé, où l'exactitude et la rapidité des données sont essentielles pour fournir des soins optimaux aux patients. Si une base de données est connectée à un tableau de bord, CDC peut s'assurer que toutes les données transmises par les dispositifs médicaux sont mises à jour en temps opportun dans l'entrepôt de données.

Les temps d'arrêt sont l'un des plus grands défis lors de la migration des données. À l'aide de CDC, vous pouvez effectuer des migrations presque sans temps d'arrêt, car cela ne perturbe pas les charges de travail.

Maintenant que vous avez une compréhension de base du CDC, explorons les types de CDC.

Types de CDC

CDC dans SQL Server se présente sous trois formes principales : basé sur des déclencheurs, basé sur des journaux et basé sur des requêtes. Chaque type offre des avantages uniques adaptés aux différents environnements de données au sein du paysage SQL Server. Le choix du type de CDC dans SQL Server dépend des besoins et contraintes spécifiques de votre environnement de données SQL Server. Pour aider les organisations à prendre une décision éclairée, la section suivante examinera ces types de CDC dans SQL Server, en mettant en évidence leurs fonctions, leurs avantages et leurs cas d'utilisation idéaux.

1. CDC basé sur des déclencheurs

Le CDC basé sur des déclencheurs dans SQL Server implique l'utilisation de déclencheurs de base de données pour capturer les modifications. Un déclencheur est une procédure stockée dans une base de données qui répond automatiquement à un événement spécifique, tel qu'une opération d'insertion, de mise à jour ou de suppression. Lorsque l'un de ces événements se produit, le déclencheur se déclenche et enregistre les modifications dans une table distincte, appelée table des modifications. Bien que cette méthode soit relativement simple à mettre en œuvre et fonctionne sur différents types de bases de données, elle peut avoir un impact sur les performances de SQL Server en raison de la surcharge supplémentaire liée à la gestion des déclencheurs.

Le CDC basé sur des déclencheurs constitue un excellent choix pour les applications SQL Server qui nécessitent des mises à jour immédiates des données, telles que les applications destinées aux clients dans lesquelles les données en temps réel sont cruciales pour maintenir une bonne expérience utilisateur.

2. CDC basé sur les journaux

Le CDC basé sur les journaux dans SQL Server capture les modifications en lisant le journal des transactions de la base de données. Le journal des transactions est un fichier qui enregistre toutes les transactions et modifications de la base de données apportées par chaque transaction. En lisant ce journal, Log-Based CDC peut identifier quelles données ont changé, quand elles ont changé et dans quel ordre les modifications se sont produites. Cette méthode est généralement plus efficace et moins intrusive que le CDC basé sur des déclencheurs, car elle n'a pas d'impact sur les performances de SQL Server. Cependant, cela nécessite une compréhension approfondie des opérations internes de la base de données.

CDC basé sur les journaux est idéal pour les environnements SQL Server avec des volumes de transactions élevés, tels que les systèmes financiers, où des changements fréquents se produisent. Il convient également aux scénarios dans lesquels les changements doivent être capturés dans l'ordre exact dans lequel ils se sont produits, comme les pistes d'audit ou les rapports de conformité où la séquence des événements est importante.

3. CDC basé sur des requêtes

Le CDC basé sur des requêtes dans SQL Server capture les modifications en interrogeant périodiquement la base de données source et en comparant l'état actuel des données avec un état précédemment enregistré. Cette méthode est souvent utilisée lorsque ni le CDC basé sur les déclencheurs ni celui basé sur les journaux ne peuvent être implémentés, par exemple lorsque la base de données SQL Server source ne prend pas en charge les déclencheurs ou les journaux de transactions. Cependant, le CDC basé sur des requêtes peut être gourmand en ressources et peut ne pas capturer toutes les modifications si plusieurs modifications se produisent entre les requêtes.

Le CDC basé sur des requêtes convient mieux aux bases de données SQL Server qui ne prennent pas en charge les déclencheurs ou les journaux de transactions ou dans les scénarios où les mises à jour des données en temps réel ne sont pas une priorité. Il est également utile dans les environnements SQL Server où l'accès au journal des transactions est restreint en raison de contraintes de sécurité ou techniques, telles que des bases de données basées sur le cloud ou des bases de données tierces.

Zoom sur le CDC basé sur les déclencheurs

Le CDC basé sur des déclencheurs est une méthode de signalisation au système cible qu'il y a eu un changement dans le système source. Comme son nom l'indique, le processus de capture des données modifiées est lancé en fonction de déclencheurs spécifiques.

Il existe trois déclencheurs dans une base de données SQL : Insérer pour signaler une nouvelle entrée, Mettre à jour pour indiquer un changement et Supprimer pour supprimer une entrée. Chaque fois que ces commandes sont exécutées, elles sont enregistrées dans une table fantôme ou de modification, qui conserve un enregistrement détaillé de toutes les modifications. Une fois enregistrées, ces modifications sont ensuite propagées à la destination cible.

Étant donné que tous les déclencheurs sont basés sur SQL, le CDC basé sur les déclencheurs est un choix préféré pour SQL Server.

Travailler avec Microsoft SQL Server

SQL Server est une base de données relationnelle utilisée en arrière-plan de diverses applications. SQL Server permet aux utilisateurs de gérer et de stocker des données sans effort. La base de données est équipée d'une foule de fonctionnalités de gestion et d'analyse des données, ce qui permet aux utilisateurs de l'exploiter plus facilement pour les initiatives de BI et d'apprentissage automatique.

Il existe plusieurs raisons pour lesquelles les développeurs préfèrent travailler avec Microsoft SQL Server :

Modifier la capture de données dans SQL Server

Microsoft propose Change Data Capture dans SQL Server, Azure SQL et Azure SQL Managed Instance.

Change Data Capture dans SQL Server utilise un agent SQL Server pour enregistrer toute activité d'insertion, de mise à jour ou de suppression sur une table, puis la rend disponible dans un format facile à utiliser.

Voici une base de données typique dans Microsoft SQL Server :

Chaque fois que des modifications sont apportées à ce tableau, CDC les enregistre dans un tableau séparé.

CDC facilite considérablement l'extraction des données de SQL Server et leur chargement dans une base de données ou un entrepôt de données. Un outil ETL/ELT extrait généralement les données de SQL Server et les charge de manière incrémentielle dans un entrepôt de données. En chargement incrémentiel, vous ne chargez que les données récentes au lieu de l'intégralité de la base de données, ce qui permet de gagner du temps et d'améliorer les performances. Une combinaison d'ETL/ELT et de CDC garantit qu'une solution fiable est disponible dans l'entrepôt de données avec un minimum de ressources.

Vous pouvez utiliser CDC dans Microsoft SQL Server à l'aide des fonctionnalités natives de Microsoft ou d'un outil ETL tiers.

Option 1 : Activer la capture de données modifiées Microsoft SQL Server en mode natif

Vous devez remplir certaines conditions pour activer CDC dans SQL Server en mode natif. Seul un utilisateur avec un rôle de serveur fixe sysadmin ou un db_owner peut activer CDC dans une base de données. Comme CDC n'est pas disponible dans la version Web, vous devez donc disposer de SQL Server Developer, Enterprise ou Standard Edition.

Lors de l'activation de CDC, SQL Server crée un schéma CDC, des tables de métadonnées et un utilisateur CDC. Le schéma CDC contient toutes les tables de métadonnées avec CDC. Une fois que vous avez activé les tables source pour la capture des données modifiées, les tables de modifications servent de référentiel pour les données modifiées.

Activation de la capture de données modifiées Microsoft SQL Server en mode natif

Bien que vous puissiez utiliser les fonctionnalités CDC natives de SQL Server, elles présentent certaines limitations. Pour utiliser SQL Server CDC, SQL Server Agent doit l'être. En outre, SQL Server CDC ne s'étend pas aux bases de données non hébergées sur des instances SQL Server. Si vous envoyez des données de plusieurs sources vers un entrepôt de données, il est nécessaire de gérer le processus CDC pour chaque source séparément.

Vous pouvez utiliser un outil tiers tel que Astera Centerprise pour dépasser ces limites. Astera Centerprise est un moyen plus simple de tirer parti de la combinaison de CDC et ETL/ELT. L'outil sans code est livré avec un puissant moteur ETL/ELT qui traite rapidement de gros volumes de données. Combiné avec une interface utilisateur intuitive et une courte courbe d'apprentissage, Astera Centerprise facilite l'implémentation de CDC dans SQL Server.

Option 2 : implémentation de la capture de données modifiées basée sur des déclencheurs dans SQL Server avec Astera Centerprise

Astera Centerprise simplifie considérablement le processus d'implémentation de CDC dans SQL Server. Faites simplement glisser et déposez la base de données SQL Server dans le concepteur de flux de données et choisissez l'option "Activer la capture de données modifiées dans la table" dans le menu déroulant.

Implémentation de Change Data Capture dans SQL Server avec Astera Centerprise

Après avoir activé CDC, vous pouvez écrire toutes les modifications dans la destination de votre choix. Il existe deux options : vous pouvez soit les écrire dans une destination de format de fichier, une base de données ou un entrepôt de données.

Lecture des modifications de la base de données à partir des tables source

Une fois que vous avez activé CDC dans les tables source mentionnées ci-dessus, choisissez CDC basé sur des déclencheurs dans la section des propriétés. Si vous souhaitez charger des données source existantes dans une destination, l'option "Effectuer un chargement complet lors de la première exécution", qui est activée par défaut. Décochez cette option si vous souhaitez uniquement charger les modifications.

Lecture des modifications de la base de données à partir des tables source

Écriture des modifications de la base de données dans un format de fichier Destination

Vous pouvez écrire vos modifications de base de données dans n'importe quel format de fichier de votre choix. Astera Centerprise prend en charge divers formats de fichiers, notamment JSON, Excel, CSV et délimité. Vous pouvez simplement faire glisser et déposer la destination dans le concepteur de flux de données et mapper les données de la source à la destination sans écrire de code.

Écriture des modifications de la base de données dans la destination du format de fichier avec Astera Centerprise

Écriture des modifications de la base de données dans un entrepôt de données ou une base de données

L'écriture de données dans un entrepôt de données est un peu différente mais simple. Vous mettrez à jour les anciens enregistrements ou ajouterez de nouveaux enregistrements à votre base de données ou à votre entrepôt de données. Pour ces destinations, Centerprise donne l'option pour Upsert. Vous pouvez activer l'option Upsert et choisir la clé primaire.

Si vous n'avez pas d'enregistrement dans la destination, l'option Upsert l'insérera. Si l'enregistrement est déjà , il le mettra à jour.

Utiliser Upsert dans Astera Centerprise

Une fois cela fait, vous pouvez facilement mapper les changements dans votre base de données ou votre entrepôt de données. Astera Centerprise dispose d'une connectivité native aux bases de données et aux entrepôts de données populaires tels que MySQL, Snowflake, Amazon Redshift, PostgresSQL, etc.

Écriture des modifications de la base de données dans la destination de la base de données/de l'entrepôt de données avec Astera Centerprise

Automatiser la capture de données de modification basée sur des déclencheurs dans SQL Server

La meilleure partie de la mise en œuvre de CDC avec Astera Centerprise est que vous pouvez profiter de ses fonctionnalités d'automatisation. Avec Centerprise, vous pouvez facilement configurer un flux de données de SQL Server vers la destination cible et planifier son exécution automatique.

Centerprise dispose d'un planificateur de tâches intégré que vous pouvez exécuter en fonction de vos paramètres préférés. Vous avez le choix entre différents événements et déclencheurs temporels. Vous pouvez définir la fréquence par intervalles hebdomadaires, mensuels, quotidiens ou même horaires.

Vous pouvez également configurer une notification par e-mail pour vous signaler chaque exécution d'un flux de données.

CDC comme solution d'audit

CDC dans SQL Server sert de solution d'audit en fournissant un suivi systématique et détaillé de toutes les modifications apportées aux données dans les bases de données. Voici comment cela fonctionne :

• Suivi détaillé : CDC dans SQL Server fonctionne en créant des tables de modifications qui reflètent la structure de colonnes des tables sources suivies. Ces tables de modifications stockent un enregistrement détaillé de toutes les modifications de données, y compris les insertions, les mises à jour et les suppressions. Étant donné que CDC fournit une piste d'audit et permet un examen et une enquête approfondis sur les modifications des données au fil du temps, cela est crucial pour maintenir l'intégrité des données.
• Responsabilité: En plus du suivi des modifications des données, la capture des données modifiées dans SQL Server capture également des métadonnées cruciales telles que la nature de la modification (insertion, mise à jour, suppression), l'heure de la modification et la transaction spécifique associée à la modification. La tenue d'un enregistrement détaillé favorise la responsabilité, car les utilisateurs savent que leurs actions dans la base de données sont enregistrées, favorisant ainsi une culture de responsabilité et de transparence.
• Conformité : CDC utilise un processus appelé Log Reader Agent dans SQL Server pour lire le journal des transactions et remplir les tables de modifications. L'agent Log Reader est une tâche qui s'exécute en continu en arrière-plan, analysant le journal des transactions de la base de données et copiant toutes les modifications dans la base de données de distribution. En suivant et en surveillant activement les modifications apportées au système, les organisations peuvent se conformer aux exigences réglementaires telles que le Règlement général sur la protection des données (RGPD).
• Surveillance en temps réel: La capture des données modifiées dans SQL Server fonctionne en temps quasi réel. Il utilise des instances de capture, chacune composée d'une table de modifications et de fonctions de requête, pour surveiller en permanence des tables spécifiques pour détecter les modifications de données. À mesure que des modifications se produisent, les instances de capture enregistrent les détails dans les tables de modifications. En suivant les données en temps réel, les organisations peuvent détecter et réagir rapidement à tout changement non autorisé ou inapproprié, améliorant ainsi la sécurité des données.

Pourquoi devriez-vous choisir Astera Centerprise?

Astera Centerprise élimine la complexité de la gestion des données. L'environnement sans code et l'interface utilisateur intuitive permettent aux utilisateurs professionnels de prendre en charge leurs initiatives axées sur les données. Voici quelques fonctionnalités qui font Astera Centerprise un excellent choix pour activer CDC :

• Astera Centerprise prend en charge divers connecteurs pour les bases de données, les entrepôts de données et les formats de fichiers populaires.
• Avec Astera Centerprise, vous pouvez gérer CDC pour toutes les bases de données relationnelles sur une seule plate-forme plutôt que de les gérer séparément.
• Astera prend en charge les transformations intégrées que vous pouvez utiliser pour nettoyer et manipuler vos données
• Vous pouvez utiliser Astera profilage des données et caractéristiques de qualité pour assurer l'exactitude et la fiabilité des données.
• Vous pouvez utiliser Astera Centerprise fonctionnalités d'automatisation et de planification des tâches pour accélérer le transfert de données

Télécharger Astera Centerprise aujourd'hui et essayez-le gratuitement pendant 14 jours.