Qu’est-ce que la capture des changements de données (CDC) ?

  • Article
  • 28 minutes de lecture

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Dans cet article, découvrez la capture des changements de données (CDC, change data capture), qui enregistre l’activité sur une base de données lorsque des tables et des lignes ont été modifiées. La capture des données modifiées est généralement disponible dans Azure SQL Database, SQL Server et Azure SQL Managed Instance.

Vue d’ensemble

La capture des changements de données utilise l’agent SQL Server pour enregistrer les activités d’insertion, de mise à jour et de suppression qui s’appliquent à une table. Elle rend disponibles les détails des modifications dans un format relationnel simple à utiliser. Les informations sur les colonnes et les métadonnées nécessaires à l'application des modifications à un environnement cible sont capturées pour les lignes modifiées et stockées dans des tables de modification qui reflètent la structure de colonne des tables sources suivies. Des fonctions table sont fournies afin de procurer aux consommateurs un accès systématique aux données modifiées.

Les consommateurs de données auxquels s’adresse cette technologie sont par exemple les applications d’extraction, de transformation et de chargement (ETL). Une application ETL charge de façon incrémentielle les données modifiées à partir de tables sources SQL Server vers un entrepôt de données ou un mini-Data Warehouse. Bien que la représentation des tables sources dans l’entrepôt de données doit refléter les modifications apportées aux tables sources, une technologie de bout en bout qui actualise un réplica de la source n’est pas appropriée. Au lieu de cela, il faut un flux de données modifiées fiable, structuré de sorte que les consommateurs puissent l'appliquer aux différentes représentations cibles des données. SQL Server apporte cette technologie.

Base de données cdc & Azure SQL

Dans Azure SQL Database, le planificateur de capture des changements de données prend la place de SQL Server Agent qui appelle des procédures stockées pour démarrer une capture et un nettoyage périodiques des tables de capture des changements de données. Le planificateur exécute automatiquement la capture et le nettoyage dans SQL Database, sans dépendance externe pour la fiabilité ou les performances. Les utilisateurs ont toujours la possibilité d’exécuter la capture et le nettoyage manuellement à la demande.

Pour en savoir plus sur la capture de données modifiées, vous pouvez également vous référer à cet épisode Data Exposed :

Considérations relatives aux performances

L’impact sur les performances de l’activation de la capture des changements de données (CDC) sur Azure SQL Database est semblable à l’impact sur les performances de son activation pour SQL Server ou Azure SQL Managed Instance. Voici quelques-uns des aspects qui influencent l’impact sur les performances de l’activation de la capture des changements de données :

  • Le nombre de tables suivies pour lesquelles la capture des changements de données est activée
  • La fréquence des changements dans les tables suivies
  • Espace disponible dans la base de données source, car les artefacts CDC (par exemple, les tables CT, les cdc_jobs, etc.) sont stockés dans la même base de données
  • Le fait que la base de données soit unique ou mise en pool. Pour les bases de données se trouvant dans des pools élastiques, en plus de prendre en compte le nombre de tables pour lesquelles la capture des changements de données est activée, soyez attentif au nombre de bases de données auxquelles ces tables appartiennent. Comme les bases de données d’un pool partagent des ressources (par exemple, l’espace disque), l’activation de la capture des changements de données sur plusieurs bases de données présente le risque d’atteindre la taille maximale de la taille du disque du pool élastique. Supervisez les ressources comme le processeur, la mémoire et le débit du journal.

Pour fournir des conseils d’optimisation des performances plus spécifiques aux clients, des détails supplémentaires sont nécessaires sur la charge de travail de chaque client. Toutefois, voici quelques conseils plus généraux, basés sur les tests de performances exécutés sur la charge de travail TPCC :

  • Envisagez d’augmenter le nombre de vCores ou de passer à un niveau de base de données supérieur (par exemple, Hyperscale) pour garantir le même niveau de performances qu’avant l’activation de la CAPTURE de données sur votre base de données Azure SQL.

  • Supervisez attentivement l’utilisation de l’espace et testez soigneusement votre charge de travail avant d’activer la capture des changements de données sur les bases de données en production.

  • Surveiller le taux de génération de journaux. Pour en savoir plus , cliquez ici.

  • L’analyse/nettoyage fait partie de la charge de travail de l’utilisateur (les ressources de l’utilisateur sont utilisées). L’impact sur les performances peut être substantiel, car des lignes entières sont ajoutées aux tables de modifications et la pré-image des opérations de mise à jour est également incluse.

  • Pools élastiques : le nombre de bases de données compatibles avec cdc ne doit pas dépasser le nombre de vCores du pool, afin d’éviter une augmentation de la latence. En savoir plus sur la gestion des ressources dans les pools élastiques denses ici.

  • Nettoyage : en fonction de la charge de travail du client, il peut être conseillé de garder la période de rétention inférieure à la période par défaut de trois jours, afin de s’assurer que le nettoyage rattrape toutes les modifications apportées à la table des modifications. En général, il est bon de maintenir la rétention faible et de suivre la taille de la base de données.

  • Aucun Contrat de niveau de service (SLA) n’est fourni pour le moment où les modifications sont ajoutées aux tables de modifications. La latence inférieure à la seconde n’est pas non plus prise en charge.

Flux de données

L'illustration suivante décrit le flux de données principal pour la capture de données modifiées.

Flux de données de capture de données modifiées

La source des données modifiées pour la capture de données modifiées est le journal des transactions SQL Server . À mesure que des insertions, des mises à jour et des suppressions sont appliquées aux tables sources suivies, les entrées qui décrivent ces modifications sont ajoutées au journal. Le journal sert d’entrée au processus de capture. Le journal est lu et les informations relatives aux modifications sont ajoutées à la table de modifications associée de la table faisant l’objet d’un suivi. Des fonctions sont fournies afin d'énumérer les modifications qui apparaissent dans les tables de modifications sur une plage spécifiée ; ces fonctions retournent les informations sous la forme d'un jeu de résultats filtré. Le jeu de résultats filtré est utilisé en général par un processus d'application pour mettre à jour une représentation de la source dans un environnement externe.

Instance de capture

Pour que les modifications apportées à une table d'une base de données puissent être suivies, la capture de données modifiées doit être activée explicitement pour la base de données. Pour cela, utilisez la procédure stockée sys.sp_cdc_enable_db. Lorsque la base de données est activée, les tables sources peuvent être identifiées en tant que tables faisant l’objet d’un suivi à l’aide de la procédure stockée sys.sp_cdc_enable_table. Lorsqu'une table est activée pour la capture de données modifiées, une instance de capture associée est créée afin de prendre en charge la dissémination des données modifiées dans la table source. L'instance de capture se compose d'une table de modifications et de deux fonctions de requête maximum. Les métadonnées qui décrivent les détails de configuration de l’instance de capture sont conservées dans les tables de métadonnées de capture de données modifiées, cdc.change_tables, cdc.index_columnset cdc.captured_columns. Ces informations peuvent être extraites en utilisant la procédure stockée sys.sp_cdc_help_change_data_capture.

Tous les objets associés à une instance de capture sont créés dans le schéma de capture de données modifiées de la base de données activée. La configuration requise pour le nom de l’instance de capture est qu’il s’agit d’un nom d’objet valide et qu’il soit unique parmi les instances de capture de base de données. Par défaut, le nom est < nom_nom_table> de schéma de la table source. Sa table de modifications associée est nommée en ajoutant _CT au nom d’instance de capture. La fonction utilisée pour vérifier la présence de modifications est nommée en ajoutant le préfixe fn_cdc_get_all_changes_ au nom d’instance de capture. Si l’instance de capture est configurée pour prendre en charge les modifications net, la fonction de requête net_changes est également créée et nommée en fn_cdc_get_net_changes_ préalablement au nom de l’instance de capture.

Table des changements

Les cinq premières colonnes de la table de modifications de capture de données modifiées sont des colonnes de métadonnées. Elles fournissent des informations supplémentaires pertinentes à la modification enregistrée. Les autres colonnes reflètent les colonnes capturées de la table source, identifiées par nom et généralement par type. Ces colonnes contiennent les données de colonne capturées recueillies à partir de la table source.

Chaque opération d'insertion ou de suppression appliquée à une table source apparaît comme une ligne unique dans la table de modifications. Les colonnes de données de la ligne qui résulte d'une opération d'insertion contiennent les valeurs de colonne après l'insertion. Les colonnes de données de la ligne qui résulte d'une opération de suppression contiennent les valeurs de colonne avant la suppression. Une opération de mise à jour requiert une entrée de ligne pour identifier les valeurs de colonne avant la mise à jour et une deuxième entrée de ligne pour identifier les valeurs de colonne après la mise à jour.

Chaque ligne d'une table de modifications contient également des métadonnées supplémentaires permettant d'interpréter l'activité de changement. La colonne __$start_lsn identifie le numéro séquentiel dans le journal de validation affecté à la modification. Ce numéro identifie les modifications qui ont été validées dans la même transaction et il ordonne ces transactions. La colonne __$seqval peut être utilisée pour ordonner davantage de modifications qui ont lieu dans la même transaction. La colonne __$operation enregistre l'opération associée à la modification : 1 = suppression, 2 = insertion, 3 = mise à jour (image avant) et 4 = mise à jour (image après). La colonne __$update_mask est un masque de bits variable avec un bit défini pour chaque colonne capturée. Pour les entrées d'insertion et de suppression, tous les bits du masque de mise à jour sont toujours définis. Toutefois, pour les lignes de mise à jour, seuls les bits correspondant aux colonnes modifiées sont définis.

Intervalle de validité

L'intervalle de validité de capture de données modifiées pour une base de données est la durée pendant laquelle les données modifiées sont disponibles pour les instances de capture. L'intervalle de validité commence lorsque la première instance de capture est créée pour une table de base de données et il se poursuit jusqu'au moment présent.

Base de données

Les données qui sont déposées dans les tables de modifications augmenteront de manière ingérable si vous ne taillez pas régulièrement et systématiquement les données. Le processus de nettoyage de capture de données modifiées est responsable de l'application de la stratégie de nettoyage par rétention. En premier lieu, il déplace le point de terminaison inférieur de l'intervalle de validité afin de satisfaire la restriction temporelle. Ensuite, il supprime les entrées de table de modifications périmées. Par défaut, trois jours de données sont conservés.

À mesure que le processus de capture valide chaque nouveau lot de données modifiées, de nouvelles entrées sont ajoutées à cdc.lsn_time_mapping pour chaque transaction qui a des entrées de table de modifications. Dans la table de mappages, un numéro séquentiel dans le journal de validation et une durée de validation de transaction (colonnes start_lsn et tran_end_time, respectivement) sont conservés. La valeur de numéro séquentiel dans le journal maximale indiquée dans cdc.lsn_time_mapping représente la limite supérieure de la fenêtre de validation de la base de données. Sa durée de validation correspondante est utilisée comme base à partir de laquelle le nettoyage par rétention calcule une nouvelle limite inférieure.

Étant donné que le processus de capture extrait les données modifiées du journal des transactions, il existe une latence intégrée entre le moment où une modification est validée dans une table source et le moment où la modification apparaît dans la table de modifications associée. Bien que cette latence soit généralement faible, il est néanmoins important de se rappeler que les données de modification ne sont pas disponibles tant que le processus de capture n’a pas traité les entrées de journal associées.

Instance de capture

Bien qu’il soit courant que l’intervalle de validité de la base de données et l’intervalle de validité d’une instance de capture individuelle coïncident, cela n’est pas toujours vrai. L'intervalle de validité de l'instance de capture démarre lorsque le processus de capture reconnaît l'instance de capture et commence à enregistrer les modifications associées dans sa table de modifications. En conséquence, si des instances de capture sont créées à différents moments, chacune aura initialement un point de terminaison inférieur différent. La colonne start_lsn du jeu de résultats retourné par sys.sp_cdc_help_change_data_capture indique le point d’arrêt inférieur actuel pour chaque instance de capture définie. Lorsque le processus de nettoyage nettoie des entrées de table de modifications, il ajuste les valeurs start_lsn pour toutes les instances de capture afin de refléter la nouvelle limite inférieure pour les données modifiées disponibles. Seules les instances de capture qui ont des valeurs start_lsn actuellement inférieures à la nouvelle limite inférieure sont ajustées. Avec le temps, si aucune nouvelle instance de capture n'est créée, les intervalles de validité de toutes les instances auront tendance à coïncider avec l'intervalle de validité de base de données.

L'intervalle de validité est important pour les consommateurs de données modifiées car l'intervalle d'extraction pour une demande doit être couvert entièrement par l'intervalle de validité de capture de données modifiées actuel de l'instance de capture. Si le point de terminaison inférieur de l'intervalle d'extraction se situe à gauche du point de terminaison inférieur de l'intervalle de validité, certaines données modifiées pourraient être manquantes en raison d'un nettoyage agressif. Si le point de terminaison élevé de l’intervalle d’extraction se trouve à droite du point de terminaison élevé de l’intervalle de validité, le processus de capture n’a pas encore été traité pendant la période représentée par l’intervalle d’extraction, et les données modifiées peuvent également manquer.

La fonction sys.fn_cdc_get_min_lsn est utilisée pour extraire la valeur LSN minimale actuelle pour une instance de capture, alors que sys.fn_cdc_get_max_lsn permet d’extraire la valeur LSN maximale actuelle. Lorsque vous interrogez des données modifiées, si la plage LSN spécifiée ne se trouve pas dans ces deux valeurs LSN, les fonctions de requête de capture de données modifiées échouent.

Gestion des changements apportés à la table source

La gestion des modifications de colonnes dans les tables sources pour lesquelles un suivi est effectué est un problème délicat pour les consommateurs en aval. Bien que l’activation de la capture de données modifiées sur une table source n’empêche pas de telles modifications DDL de se produire, la capture de données modifiées permet d’atténuer l’effet sur les consommateurs en permettant aux jeux de résultats fournis retournés via l’API de rester inchangés même si la structure de colonnes de la table source sous-jacente change. Cette structure de colonne fixe est également répercutée dans la table de modifications sous-jacente à laquelle les fonctions de requête définies accèdent.

Pour prendre en charge une table de modification de structure de colonne fixe, le processus de capture chargé de remplir la table des modifications ignore toutes les nouvelles colonnes qui ne sont pas identifiées pour la capture lorsque la table source a été activée pour la capture des données modifiées. Si une colonne suivie est supprimée, des valeurs null sont fournies pour la colonne dans les entrées de modification suivantes. Toutefois, si une colonne existante subit une modification de son type de données, la modification est propagée à la table des modifications pour garantir que le mécanisme de capture n’introduit pas de perte de données dans les colonnes suivies. Le processus de capture publie également dans la table cdc.ddl_history toutes les modifications de structure de colonne détectées pour les tables faisant l'objet d'un suivi. Les consommateurs souhaitant être informés des ajustements qui peuvent se révéler nécessaires dans les applications en aval doivent utiliser la procédure stockée sys.sp_cdc_get_ddl_history.

En général, l'instance de capture actuelle conserve sa forme lorsque des modifications DDL sont appliquées à sa table source associée. Toutefois, il est possible de créer une deuxième instance de capture pour la table qui reflète la nouvelle structure de colonnes. Cela permet au processus de capture d'apporter des modifications à la même table source dans deux tables de modifications distinctes qui ont deux structures de colonne différentes. Par conséquent, tandis qu'une table de modifications peut continuer à servir les programmes actuellement opérationnels, la deuxième peut être affectée à un environnement de développement qui tente d'incorporer les nouvelles données de colonnes. Le fait d'autoriser le mécanisme de capture à remplir les deux tables de modifications en tandem signifie qu'une transition de l'une à l'autre peut être accomplie sans perte de données de modifications. Cela peut arriver à tout moment où les deux chronologies de capture de données modifiées se chevauchent. Lorsque la transition est terminée, l’instance de capture obsolète peut être supprimée.

Notes

La quantité maximale d'instances de capture qui peuvent être associées simultanément à une table source unique est de deux.

Relation avec l’agent de lecture du journal

La logique du processus de capture de données modifiées est incorporée à la procédure stockée sp_replcmds, une fonction de serveur interne intégrée à sqlservr.exe et utilisée également par la réplication transactionnelle pour la collecte des modifications depuis le journal des transactions. Dans SQL Server et Azure SQL Managed Instance, quand seule la capture des changements de données est activée pour une base de données, vous créez le travail de capture des changements de données de SQL Server Agent comme instrument de l’appel de sp_replcmds. Lorsque la réplication est également présente, le lecteur de journal transactionnel seul est utilisé pour répondre aux besoins en données modifiées pour ces deux consommateurs. Cette stratégie réduit considérablement les contentions de journaux lorsque la réplication et la capture de données modifiées sont toutes deux activées pour la même base de données.

Le basculement entre ces deux modes opérationnels pour la capture des données modifiées se produit automatiquement chaque fois qu’il y a un changement dans l’état de réplication d’une base de données activée pour la capture de données modifiées.

Notes

Dans SQL Server et Azure SQL Managed Instance, les deux instances de la logique de capture exigent que SQL Server Agent soit en cours d’exécution pour que le processus s’exécute.

La principale tâche du processus de capture consiste à analyser le journal et à écrire des données de colonnes et des informations relatives aux transactions dans les tables de modifications de capture de données modifiées. Pour garantir une limite transactionnellement cohérente parmi toutes les tables de modifications de capture de données modifiées qu'il remplit, le processus de capture ouvre et valide sa propre transaction à chaque cycle d'analyse. Il détecte lorsque des tables sont activées pour la capture de données modifiées et les inclut automatiquement dans le jeu de tables pour lesquelles un contrôle actif est effectué pour les entrées de modification dans le journal. De la même façon, la désactivation de la capture de données modifiées est également détectée et provoque la suppression de la table source du jeu de tables contrôlé de manière active. Lorsque le traitement d'une section du journal est terminé, le processus de capture en informe la logique de troncation de journal du serveur, qui utilise ces informations pour identifier les entrées de journal éligibles pour la troncation.

Notes

Lorsqu'une base de données est activée pour la capture de données modifiées, même si le mode de récupération est défini sur la récupération simple le point de troncation de journal n'avancera pas tant que toutes les modifications marquées pour la capture n'ont pas été collectées par le processus de capture. Si le processus de capture ne s'exécute pas et que des modifications doivent être collectées, l'exécution de CHECKPOINT ne tronquera pas le journal.

Le processus de capture est également utilisé pour conserver l'historique des modifications DDL pour les tables faisant l'objet d'un suivi. Les instructions DDL qui sont associées à la capture de données modifiées créent des entrées dans le journal des transactions de base de données chaque fois qu'une base de données ou une table prenant en charge la capture de données modifiées est supprimée ou chaque fois que des colonnes d'une table prenant en charge la capture de données modifiées sont ajoutées, modifiées ou supprimées. Ces entrées de journal sont traitées par le processus de capture, qui publie ensuite les événements DDL associés dans la table cdc.ddl_history. Vous pouvez obtenir des informations sur les événements DDL qui affectent les tables faisant l’objet d’un suivi à l’aide de la procédure stockée sys.sp_cdc_get_ddl_history.

Travaux de l'Agent

Deux travaux de l'Agent SQL Server sont en général associés à une base de données activée pour la capture de données modifiées : l'un est utilisé pour remplir les tables de modifications de base de données et l'autre est chargé de les nettoyer. Les deux travaux se composent d’une seule étape qui exécute une commande Transact-SQL. La commande Transact-SQL appelée est une procédure stockée définie par la capture de données modifiées qui implémente la logique du travail. Les travaux sont créés lorsque la première table de la base de données est activée pour la capture de données modifiées. Le travail de nettoyage est toujours créé. Le travail de capture est créé seulement si aucune publication transactionnelle n'est définie pour la base de données. Le travail de capture est également créé quand la capture des changements de données et la réplication transactionnelle sont toutes les deux activées pour une base de données, et le travail du lecteur de journal est supprimé car aucune publication n’est plus définie pour la base de données.

Les travaux de capture et de nettoyage sont tous deux créés à l'aide de paramètres par défaut. Le travail de capture est démarré immédiatement. Il s'exécute continuellement et traite un maximum de 1000 transactions par cycle d'analyse, avec une attente de 5 secondes entre les cycles. Le travail de nettoyage s’exécute tous les jours à 2 h du matin. Il conserve les entrées de table modifiées pendant 4320 minutes ou 3 jours, supprimant un maximum de 5 000 entrées avec une seule instruction delete.

Les travaux de l'agent de capture de données modifiées sont supprimés lorsque la capture de données modifiées est désactivée pour une base de données. Le travail de capture peut également être supprimé lorsque la première publication est ajoutée à une base de données et que la capture de données modifiées et la réplication transactionnelle sont toutes deux activées.

En interne, les travaux de l’agent de capture de données modifiées sont créés et supprimés à l’aide des procédures stockées sys.sp_cdc_add_job et sys.sp_cdc_drop_job, respectivement. Ces procédures stockées sont également exposées afin que les administrateurs puissent contrôler la création et la suppression de ces travaux.

Un administrateur n'a aucun contrôle explicite sur la configuration par défaut des travaux de l'agent de capture de données modifiées. La procédure stockée sys.sp_cdc_change_job est fournie afin de permettre la modification des paramètres de configuration par défaut. En outre, la procédure stockée sys.sp_cdc_help_jobs permet d’afficher les paramètres de configuration actuels. Le travail de capture et le travail de nettoyage extraient des paramètres de configuration de la table msdb.dbo.cdc_jobs lors du démarrage. Toutes les modifications apportées à ces valeurs à l’aide de sys.sp_cdc_change_job n’entreront pas en vigueur tant que le travail n’est pas arrêté et redémarré.

Deux procédures stockées supplémentaires sont fournies afin de permettre le démarrage et l’arrêt des travaux de l’agent de capture de données modifiées : sys.sp_cdc_start_job et sys.sp_cdc_stop_job.

Notes

Le démarrage et l'arrêt du travail de capture ne provoque pas de perte des données modifiées. Il empêche seulement le processus de capture d'analyser activement le journal à la recherche d'entrées de modification à déposer dans les tables de modifications. Une stratégie raisonnable visant à empêcher l'analyse du journal d'ajouter une charge durant les périodes de demande de pointe consiste à arrêter le travail de capture et à le redémarrer lorsque la demande est réduite.

Les deux travaux de l'Agent SQL Server ont été conçus pour être suffisamment flexibles et configurables afin de répondre aux besoins élémentaires des environnements de capture de données modifiées. Dans les deux cas, toutefois, les procédures stockées sous-jacentes qui fournissent la fonctionnalité principale ont été exposées afin qu'une personnalisation supplémentaire soit possible.

La capture de données modifiées ne peut pas fonctionner correctement lorsque le service Moteur de base de données ou le service SQL Server Agent s’exécute sous le compte SERVICE RÉSEAU. Cela peut entraîner l'erreur 22832.

Notes

Dans Azure SQL Database, les travaux de l’Agent sont remplacés par un planificateur qui exécute automatiquement la capture et le nettoyage.

Capture et nettoyage CDC dans Azure SQL Database

Dans Azure SQL Database, le planificateur de capture des changements de données prend la place de SQL Server Agent qui appelle des procédures stockées pour démarrer une capture et un nettoyage périodiques des tables de capture des changements de données.

Le planificateur exécute automatiquement la capture et le nettoyage dans SQL Database, sans dépendance externe pour la fiabilité ou les performances. Les utilisateurs ont toujours la possibilité d’exécuter la capture et le nettoyage manuellement à la demande à l’aide des procédures sp_cdc_scan et sp_cdc_cleanup_change_tables . Le travail de capture cdc s’exécute toutes les 20 secondes et le travail de nettoyage s’exécute toutes les heures.

Azure SQL Database inclut deux vues de gestion dynamiques pour vous permettre de contrôler la capture des changements de données : sys.dm_cdc_log_scan_sessions et sys.dm_cdc_errors.

Différences de classement

Il est important de connaître une situation dans laquelle vous avez des classements différents entre la base de données et les colonnes d’une table configurées pour la capture de données modifiées. La capture des changements de données utilise le stockage temporaire pour remplir les tables côté. Si une table comprend des colonnes CHAR ou VARCHAR avec des classements différents de ceux de la base de données, et si ces colonnes stockent des caractères non ASCII (par exemple, des caractères DBCS codés sur deux octets), la capture des changements de données peut ne pas être en mesure de maintenir la cohérence entre les données modifiées et les données des tables de base. Cela est dû au fait que les variables de stockage intermédiaire ne peuvent pas avoir de classements associés.

Envisagez plutôt l’une des approches suivantes pour vérifier que les données modifiées sont cohérentes avec celles des tables de base :

  • Utilisez le type de données NCHAR ou NVARCHAR pour les colonnes qui contiennent des données non ASCII.

  • Vous pouvez également utiliser le même classement pour les colonnes et pour la base de données.

Par exemple, si vous avez une base de données qui utilise un classement de SQL_Latin1_General_CP1_CI_AS, considérez le tableau suivant :

CREATE TABLE T1( 
     C1 INT PRIMARY KEY, 
     C2 VARCHAR(10) collate Chinese_PRC_CI_AI)

La capture des changements de données peut échouer à capturer les données binaires de la colonne C2, car son classement est différent (Chinese_PRC_CI_AI). Utilisez NVARCHAR pour éviter ce problème :

CREATE TABLE T1( 
     C1 INT PRIMARY KEY, 
     C2 NVARCHAR(10) collate Chinese_PRC_CI_AI --Unicode data type, CDC works well with this data type
     )

Autorisations requises

Des autorisations d’administrateur système sont nécessaires pour activer la capture des changements de données pour SQL Server ou Azure SQL Managed Instance. Le rôle db_owner est nécessaire pour activer la capture des changements de données pour Azure SQL Database.

Recommandations générales

Pour que la capture de données modifiées fonctionne correctement, vous ne devez pas modifier manuellement les métadonnées CDC telles que le schéma CDC, les tables de modification, les procédures stockées système CDC, les autorisations utilisateur par défaut cdc (sys.database_principals) ou renommer cdc l’utilisateur.

Tous les objets dans sys.objects dont is_ms_shipped la propriété est définie sur 1 ne doivent pas être modifiés.

SELECT    name AS object_name   
        ,SCHEMA_NAME(schema_id) AS schema_name  
        ,type_desc  
        ,is_ms_shipped  
FROM sys.objects 
WHERE is_ms_shipped= 1 AND SCHEMA_NAME(schema_id) = 'cdc'

Limites et problèmes connus

Il s’agit de la liste des limitations connues et des problèmes liés à la capture de données modifiées (CDC).

Linux
CDC est maintenant pris en charge pour SQL Server 2017 sur Linux à partir de CU18, et pour SQL Server 2019 sur Linux.

Index Columnstore
La capture de données modifiées ne peut pas être activée sur les tables avec un index columnstore cluster. À compter de SQL Server 2016, elle peut être activée sur les tables avec un index columnstore non cluster.

Basculement de partition avec des variables
L’utilisation de variables avec basculement de partition sur des bases de données ou des tables avec capture de données modifiées (CDC) n’est pas prise en charge pour l’instruction ALTER TABLE ... SWITCH TO ... PARTITION ... . Pour en savoir plus, consultez limitations du basculement de partition.

Disponibilité de cdc dans les bases de données Azure SQL
La capture de données modifiées ne peut être activée que sur les niveaux de bases de données S3 et versions ultérieures. Les sous-cœurs (De base, S0, S1, S2) Azure SQL Les bases de données ne sont pas prises en charge pour cdc.

Dbcopy des niveaux de base de données au-dessus de S3 pour lesquels la capture de données est activée pour un SLO subcore conserve actuellement les artefacts CDC, mais les artefacts CDC peuvent être supprimés à l’avenir.

Personnalisation de la capture et du nettoyage sur les bases de données Azure SQL
La configuration de la fréquence de la capture et des processus de nettoyage pour cdc dans Azure SQL bases de données n’est pas possible. La capture et le nettoyage sont exécutés automatiquement par le planificateur.

Colonnes calculées
CDC ne prend pas en charge les valeurs des colonnes calculées même si la colonne calculée est définie comme persistante. Les colonnes calculées incluses dans une instance de NULLcapture ont toujours la valeur . Ce comportement est prévu, ce n’est pas un bogue.

Restauration à un point dans le temps
Si vous activez cdc sur votre base de données en tant qu’utilisateur Microsoft Azure Active Directory (Azure AD), il n’est pas possible de restaurer un point dans le temps (PITR) vers un SLO sous-core. Il est recommandé de restaurer la base de données au même niveau que le SLO source ou supérieur, puis de désactiver la capture de données modifiées si nécessaire.

Microsoft Azure Active Directory (Azure AD)
Si vous créez une base de données dans Azure SQL Database en tant qu’utilisateur Microsoft Azure Active Directory (Azure AD) et que vous activez la capture de données modifiées (CDC) sur celle-ci, un utilisateur SQL (par exemple, même le rôle sysadmin) ne sera pas en mesure de désactiver ou d’apporter des modifications aux artefacts CDC. Toutefois, un autre utilisateur Azure AD peut activer ou désactiver la fonctionnalité CDC sur la même base de données.

De même, si vous créez une base de données Azure SQL en tant qu’utilisateur SQL, l’activation/la désactivation de la capture de données modifiées en tant qu’utilisateur Azure AD ne fonctionnera pas.

Troncation agressive du journal
Lors de l’activation de la capture de données modifiées (CDC) sur Azure SQL base de données ou SQL Server, n’oubliez pas que la fonctionnalité de troncation de journal agressive de la récupération de base de données accélérée (ADR) est désactivée. En effet, l’analyse cdc accède au journal des transactions de base de données. Les transactions actives continueront de contenir la troncation du journal des transactions jusqu’à ce que la transaction soit validée et l’analyse CDC rattrape, ou que la transaction soit abandonnée. Cela peut entraîner le remplissage du journal des transactions plus que d’habitude et doit être surveillé afin que le journal des transactions ne se remplit pas.

Échec de la capture de données après ALTER COLUMN vers VARCHAR et VARBINARY
Lorsque le type de données d’une colonne d’une table compatible cdc est modifié de TEXT vers ou IMAGEVARBINARY vers VARCHAR et qu’une ligne existante est mise à jour vers une valeur hors ligne. Après la mise à jour, l’analyse cdc génère des erreurs.

L’activation de cdc échoue sur Azure SQL BASE de données restaurée créée avec Microsoft Azure Active Directory (Azure AD)
L’activation de la capture de données échoue si vous créez une base de données dans Azure SQL Database en tant qu’utilisateur Microsoft Azure Active Directory (Azure AD) et que vous n’activez pas la capture de données, puis si vous restaurez la base de données et activez la capture de données sur la base de données restaurée.

Pour résoudre ce problème, effectuez les étapes suivantes :

  • Connectez-vous en tant qu’administrateur Azure AD du serveur
  • Exécutez la commande ALTER AUTHORIZATION sur la base de données :
ALTER AUTHORIZATION ON DATABASE::[<restored_db_name>] TO [<azuread_admin_login_name>];

EXEC sys.sp_cdc_enable_db

La tentative d’activation de la capture de données échoue si le schéma personnalisé ou l’utilisateur nommé cdc existe déjà dans la base de données
Lorsque vous activez cdc sur la base de données, il crée un nouveau schéma et un utilisateur nommé cdc. Par conséquent, il n’est pas recommandé de créer manuellement un schéma personnalisé ou un utilisateur nommé cdc, car il est réservé à une utilisation système.
Si vous avez défini manuellement un schéma personnalisé ou un utilisateur nommé cdc dans votre base de données qui n’est pas lié à cdc, la procédure sys.sp_cdc_enable_db stockée système ne parvient pas à activer la capture de données modifiées sur la base de données avec le message d’erreur ci-dessous.

La base de données <database_name> ne peut pas être activée pour la capture de données modifiées, car un utilisateur de base de données nommé « cdc » ou un schéma nommé « cdc » existe déjà dans la base de données active. Ces objets sont requis en mode exclusif par la capture de données modifiées. Supprimez ou renommez l'utilisateur ou le schéma et réessayez l'opération.

Pour résoudre ce problème :

  • Supprimez manuellement le schéma cdc l’utilisateur cdc vides. Ensuite, la CAPTURE de données peut être activée avec succès sur la base de données.

Importer une base de données à l’aide d’opérations d’importation/exportation et d’extraction/publication de la couche Données
Pour les bases de données SQL avec cdc, lorsque vous utilisez SqlPackage, SSDT ou d’autres outils SQL pour importer/exporter ou extraire/publier, le cdc schéma et l’utilisateur sont exclus de la nouvelle base de données. Les objets CDC supplémentaires non inclus dans les opérations Import/Export et Extract/Deploy incluent les tables marquées comme is_ms_shipped=1 dans sys.objects.

Même si la capture de données n’est pas activée et que vous avez défini un schéma ou un utilisateur personnalisé nommé cdc dans votre base de données, qui sera également exclu dans les opérations d’importation/exportation et d’extraction/déploiement pour importer/configurer une nouvelle base de données.

Voir aussi

Suivre les modifications de données (SQL Server)
Activer et désactiver la capture de données modifiées (SQL Server)
Utiliser les données modifiées (SQL Server)
Administrer et surveiller la capture des données modifiées (SQL Server)
Tables temporelles