Utiliser des données modifiées (SQL Server)

Les données modifiées sont mises à la disposition des consommateurs de capture de données modifiées par le biais de fonctions ayant une valeur de table (TVF). Toutes les requêtes de ces fonctions nécessitent deux paramètres pour définir la plage de numéros de séquence de journaux (LSN) éligibles à prendre en compte lors de la construction du jeu de résultats renvoyé. Les valeurs LSN supérieures et inférieures liées à l’intervalle sont considérées comme incluses dans l’intervalle.

Plusieurs fonctions sont fournies pour aider à déterminer les valeurs LSN appropriées à utiliser pour interroger une fonction TVF. La fonction sys.fn_cdc_get_min_lsn retourne le plus petit LSN associé à un intervalle de validité d’instance de capture. L’intervalle de validité est l’intervalle de temps pour lequel les données modifiées sont actuellement disponibles pour ses instances de capture. La fonction sys.fn_cdc_get_max_lsn retourne le LSN le plus grand dans l’intervalle de validité. Les fonctions sys.fn_cdc_map_time_to_lsn et sys.fn_cdc_map_lsn_to_time sont disponibles pour aider à placer des valeurs LSN sur une chronologie conventionnelle. Étant donné que la capture de données modifiées utilise des intervalles de requête fermés, il est parfois nécessaire de générer la valeur LSN suivante dans une séquence pour vous assurer que les modifications ne sont pas dupliquées dans les fenêtres de requête consécutives. Les fonctions sys.fn_cdc_increment_lsn et sys.fn_cdc_decrement_lsn sont utiles lorsqu’un ajustement incrémentiel à une valeur LSN est nécessaire.

Validation des limites LSN

Nous vous recommandons de valider les limites LSN à utiliser dans une requête TVF avant leur utilisation. Les points de terminaison null ou les points de terminaison qui se trouvent en dehors de l’intervalle de validité d’une instance de capture forcent une erreur à retourner par une capture de données modifiées TVF.

Par exemple, l’erreur suivante est retournée pour une requête de toutes les modifications lorsqu’un paramètre utilisé pour définir l’intervalle de requête n’est pas valide ou hors plage, ou que l’option de filtre de ligne est invalide.

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ...

L’erreur correspondante retournée pour une net changes requête est la suivante :

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ...

Les échecs d’autorisation retournent des échecs lors de l’interrogation de toutes les modifications, comme indiqué :

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'fn_cdc_get_all_changes_...', database 'MyDB', schema 'cdc'.

Il en va de même lors de l’interrogation des modifications nettes :

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object fn_cdc_get_net_changes_...', database 'MyDB', schema 'cdc'.

Consultez le modèle Énumérer les modifications nettes à l’aide de TRY CATCH pour obtenir une démonstration de l’interception de ces erreurs TVF connues et retourner des informations plus significatives sur l’échec.

Fonctions de requête

Selon les caractéristiques de la table source en cours de suivi et de la façon dont son instance de capture est configurée, un ou deux fichiers TVF pour l’interrogation des données modifiées sont générées.

• La fonction cdc.fn_cdc_get_all_changes_<capture_instance> retourne toutes les modifications qui se sont produites pour l’intervalle spécifié. Cette fonction est toujours générée. Les entrées sont toujours triées, d’abord par le LSN de validation de transaction de la modification, puis par une valeur qui séquence la modification dans sa transaction. Selon l’option de filtre de ligne choisie, la ligne finale est retournée à la mise à jour (option de filtre de ligne « all ») ou les valeurs nouvelles et anciennes sont retournées lors de la mise à jour (option de filtre de ligne « toutes les mises à jour anciennes »).

• La fonction cdc.fn_cdc_get_net_changes_<capture_instance> est générée lorsque le paramètre @supports_net_changes est défini sur 1 lorsque la table source est activée.

  Remarque

  Cette option est prise en charge uniquement si la table source a une clé primaire définie ou si le paramètre @index_name a été utilisé pour identifier un index unique.

  La fonction netchanges retourne une modification par ligne de table source modifiée. Si plusieurs modifications sont enregistrées pour la ligne pendant l’intervalle spécifié, les valeurs de colonne reflètent le contenu final de la ligne. Pour identifier correctement l’opération nécessaire pour mettre à jour l’environnement cible, la fonction TVF doit prendre en compte à la fois l’opération initiale sur la ligne pendant l’intervalle et l’opération finale sur la ligne. Lorsque l’option de filtre de ligne « all » est spécifiée, les opérations retournées par une net changes requête seront insérées, supprimées ou mises à jour (nouvelles valeurs). Cette option retourne toujours le masque de mise à jour comme null, car il existe un coût associé au calcul d’un masque d’agrégation. Si vous avez besoin d’un masque d’agrégation qui reflète toutes les modifications apportées à une ligne, utilisez l’option « all with mask ». Si le traitement en aval ne nécessite pas de distinction entre les insertions et les mises à jour, utilisez l’option « all with merge ». Dans ce cas, la valeur de l’opération prend uniquement deux valeurs : 1 pour la suppression et 5 pour une opération qui peut être une insertion ou une mise à jour. Cette option élimine le traitement supplémentaire nécessaire pour déterminer si l’opération dérivée doit être une insertion ou une mise à jour et peut améliorer les performances de la requête lorsque cette différenciation n’est pas nécessaire.

Le masque de mise à jour retourné par une fonction de requête est une représentation compacte qui identifie toutes les colonnes qui ont changé dans une ligne de données modifiées. En règle générale, ces informations sont requises uniquement pour un petit sous-ensemble des colonnes capturées. Les fonctions sont disponibles pour faciliter l’extraction d’informations à partir du masque dans un formulaire plus directement utilisable par les applications. La fonction sys.fn_cdc_get_column_ordinal retourne la position ordinale d’une colonne nommée pour une instance de capture donnée, tandis que la fonction sys.fn_cdc_is_bit_set retourne la parité du bit dans le masque fourni en fonction de l’ordinal passé dans l’appel de fonction. Ensemble, ces deux fonctions permettent aux informations du masque de mise à jour d’être extraites et retournées efficacement avec la demande de données modifiées. Consultez le modèle Énumérer les modifications nettes à l’aide de all With Mask pour obtenir une démonstration de l’utilisation de ces fonctions.

Scénarios de fonction de requête

Les sections suivantes décrivent les scénarios courants d’interrogation des données de capture des modifications de données à l’aide des fonctions de requête cdc.fn_cdc_get_all_changes_<capture_instance> et cdc.fn_cdc_get_net_changes_<capture_instance>.

Requête pour toutes les modifications dans l’intervalle de validité de l’instance de capture

La requête la plus simple pour les données modifiées est une requête qui retourne toutes les données modifiées actuelles dans l’intervalle de validité d’une instance de capture. Pour effectuer cette requête, commencez par déterminer les limites LSN inférieures et supérieures de l’intervalle de validité. Utilisez ensuite ces valeurs pour identifier les paramètres @from_lsn et @to_lsn passés à la fonction de requête cdc.fn_cdc_get_all_changes_<capture_instance> ou cdc.fn_cdc_get_net_changes_<capture_instance>. Utilisez la fonction sys.fn_cdc_get_min_lsn pour obtenir la limite inférieure et sys.fn_cdc_get_max_lsn pour obtenir la limite supérieure. Consultez le modèle Énumérer toutes les modifications de la plage valide pour l’exemple de code pour interroger toutes les modifications valides actuelles à l’aide de la fonction de requête cdc.fn_cdc_get_all_changes_<capture_instance>. Consultez le modèle Énumérer les modifications nettes pour la plage valide pour obtenir un exemple similaire d’utilisation de la fonction cdc.fn_cdc_get_net_changes_<capture_instance>.

Recherche de toutes les nouvelles modifications depuis les dernières modifications

Pour les applications classiques, l’interrogation des données modifiées sera un processus continu, en effectuant des demandes périodiques pour toutes les modifications qui se sont produites depuis la dernière requête. Pour ces requêtes, vous pouvez utiliser la fonction sys.fn_cdc_increment_lsn pour dériver la limite inférieure de la requête actuelle à partir de la limite supérieure de la requête précédente. Cette méthode garantit qu’aucune ligne n’est répétée, car l’intervalle de requête est toujours traité comme un intervalle fermé où les deux points de terminaison sont inclus dans l’intervalle. Ensuite, utilisez la fonction sys.fn_cdc_get_max_lsn pour obtenir le point de terminaison élevé pour le nouvel intervalle de requête. Consultez le modèle Énumérer toutes les modifications depuis la demande précédente d’exemple de code pour déplacer systématiquement la fenêtre de requête pour obtenir toutes les modifications depuis la dernière requête.

Recherche de toutes les nouvelles modifications effectuées jusqu’à maintenant

Une contrainte classique placée sur les modifications retournées par une fonction de requête consiste à inclure uniquement les modifications qui se sont produites entre la demande précédente jusqu’à la date et l’heure actuelles. Pour cette requête, appliquez la fonction sys.fn_cdc_increment_lsn à la @from_lsn valeur utilisée dans la requête précédente pour déterminer la limite inférieure. Étant donné que la limite supérieure de l’intervalle de temps est exprimée sous la forme d’un point spécifique dans le temps, elle doit être convertie en valeur LSN avant de pouvoir être utilisée par une fonction de requête. Avant que la valeur datetime puisse être convertie en valeur LSN correspondante, vous devez vous assurer que le processus de capture a traité toutes les modifications validées par le biais de la limite supérieure spécifiée. Cela est nécessaire pour s’assurer que toutes les modifications qualificatives ont été propagées à la table de modifications. Une façon de procéder consiste à structurer une boucle d’attente qui vérifie régulièrement si le lsn de validation maximal actuel enregistré pour toute table de modification de base de données dépasse l’heure de fin souhaitée de l’intervalle de requête.

Une fois la boucle de retard vérifiée que le processus de capture a déjà traité toutes les entrées de journal pertinentes, utilisez la fonction sys.fn_cdc_map_time_to_lsn pour déterminer le nouveau point de terminaison élevé exprimé sous forme de valeur LSN. Pour vous assurer que toutes les entrées validées par le biais de l’heure spécifiée sont récupérées, appelez la fonction sys.fn_cdc_map_time_to_lsn et utilisez l’option « plus grande inférieure ou égale ».

Le modèle Énumérer toutes les modifications jusqu’à présent montre comment utiliser la stratégie précédente pour rechercher des données modifiées.

Ajout d’une durée de validation à un jeu de résultats de toutes les modifications

L’heure de validation de chaque transaction avec une entrée associée dans une table de modification de base de données est disponible dans la table cdc.lsn_time_mapping. En joignant la valeur __$start_lsn retournée dans une requête pour toutes les modifications avec la valeur start_lsn d'une entrée de table cdc.lsn_time_mapping, vous pouvez retourner la valeur tran_end_time ainsi que les données modifiées pour associer la modification à l'heure de validation de la transaction à la source. Le modèle Append Commit Time to All Changes Result Set montre comment effectuer cette jointure.

Jointure de données modifiées à d’autres données à partir de la même transaction

Parfois, il est utile de joindre des données modifiées à d’autres informations collectées sur la transaction lorsqu’elle a été validée à la source. La colonne tran_begin_lsn de la table cdc.lsn_time_mapping fournit les informations nécessaires pour effectuer une telle jointure. Lorsque la mise à jour de la source se produit, la valeur de database_transaction_begin_lsn de la vue dynamique système sys.dm_tran_database_transactions doit être enregistrée avec toutes les autres informations à joindre aux données modifiées. Utilisez la fonction fn_convertnumericlsntobinary pour comparer les valeurs database_transaction_begin_lsn et tran_begin_lsn. Le code permettant de créer cette fonction est disponible dans le modèle Créer une fonction fn_convertnumericlsntobinary. Le modèle Retourner toutes les modifications avec un tran_begin_lsn donné montre comment réaliser la jointure.

Interrogation à l’aide des fonctions wrapper Datetime

Un scénario d’application classique pour l’interrogation de données modifiées consiste à demander régulièrement des données modifiées à l’aide d’une fenêtre glissante limitée par les valeurs datetime. Pour cette classe de consommateurs, la capture de données modifiées fournit la procédure stockée sys.sp_cdc_generate_wrapper_function qui génère des scripts pour créer des fonctions wrapper personnalisées pour les fonctions de requête de capture de données modifiées. Ces wrappers personnalisés permettent à l’intervalle de requête d’être exprimé sous la forme d’une paire datetime.

Les options d’appel de la procédure stockée permettent aux wrappers d’être générés pour toutes les instances de capture auxquelles l’appelant a accès, ou uniquement à une instance de capture spécifiée. Les options prises en charge incluent également la possibilité de spécifier si le point de terminaison élevé de l’intervalle de capture doit être ouvert ou fermé, lequel des colonnes capturées disponibles doit être inclus dans le jeu de résultats et quelles colonnes incluses doivent avoir des indicateurs de mise à jour associés. La procédure retourne un jeu de résultats avec deux colonnes : le nom de la fonction générée, qui est dérivable à partir du nom de l’instance de capture et l’instruction create pour la procédure stockée wrapper. La fonction permettant d’encapsuler toutes les requêtes de modifications est toujours générée. Si le @supports_net_changes paramètre a été défini lors de la création de l’instance de capture, la fonction pour encapsuler la fonction de modification nette est également générée.

Il incombe au concepteur d’applications d’appeler la procédure stockée de génération de script pour générer les instructions create pour les procédures stockées wrapper et d’exécuter les scripts de création résultants pour créer les fonctions. Cela ne se produit pas automatiquement lorsqu’une instance de capture est créée.

Les wrappers Datetime appartiennent à l’utilisateur et ne sont pas créés dans le schéma par défaut de l’appelant. La fonction générée convient sans modification à la plupart des utilisateurs. Toutefois, une personnalisation supplémentaire peut toujours être appliquée au script généré avant de créer la fonction.

Le nom de la fonction pour encapsuler toutes les requêtes de modifications est fn_all_changes_ suivi du nom de l’instance de capture. Le préfixe utilisé pour le wrapper de modifications nettes est fn_net_changes_. Les deux fonctions prennent trois arguments, tout comme leurs fonctions de capture de données modifiées associées (TVFs). Toutefois, l’intervalle de requête pour les wrappers est limité par deux valeurs datetime au lieu de deux valeurs LSN. Le @row_filter_option paramètre des deux ensembles de fonctions est le même.

Les fonctions wrapper générées prennent en charge la convention suivante pour parcourir systématiquement la chronologie de capture de données modifiées : il est prévu que le @end_time paramètre de l’intervalle précédent soit utilisé comme @start_time paramètre de l’intervalle suivant. La fonction wrapper prend soin de mapper les valeurs datetime aux valeurs LSN et de s’assurer qu’aucune donnée n’est perdue ou répétée si cette convention est suivie.

Les wrappers peuvent être générés pour prendre en charge une limite supérieure fermée ou une limite supérieure ouverte sur la fenêtre de requête spécifiée. Autrement dit, l’appelant peut spécifier si les entrées ayant une durée de validation égale à la limite supérieure de l’intervalle d’extraction doivent être incluses dans l’intervalle. Par défaut, la limite supérieure est incluse.

Bien que les fonctions de requête générées échouent si elles reçoivent une valeur null pour la valeur @from_lsn ou la valeur @to_lsn, les fonctions wrapper datetime utilisent null pour autoriser les wrappers datetime à retourner toutes les modifications actuelles. Autrement dit, si la valeur null est passée comme point de terminaison inférieur de la fenêtre de requête au wrapper datetime, le point de fin bas de l’intervalle de validité de l’instance de capture est utilisé dans l’instruction SELECT sous-jacente qui est appliquée à la requête TVF. De même, si la valeur null est passée comme point de terminaison élevé de la fenêtre de requête, le point de terminaison élevé de l’intervalle de validité de l’instance de capture est utilisé lors de la sélection dans la requête TVF.

Le jeu de résultats retourné par une fonction wrapper inclut toutes les colonnes demandées suivies d’une colonne d’opération, recodées sous la forme d’un ou deux caractères pour identifier l’opération associée à la ligne. Si des indicateurs de mise à jour ont été demandés, ils apparaissent sous forme de colonnes de bits après le code d’opération, dans l’ordre spécifié dans le @update_flag_list paramètre. Pour plus d’informations sur les options d’appel permettant de personnaliser les wrappers datetime générés, consultez sys.sp_cdc_generate_wrapper_function (Transact-SQL).

Le modèle "Instancier un Wrapper TVF avec Indicateur de Mise à Jour" montre comment personnaliser une fonction wrapper générée pour ajouter un indicateur de mise à jour pour une colonne spécifiée au jeu de résultats retourné par une requête de modifications nettes. Le modèle Instanciez les fichiers TVFs du wrapper CDC pour un schéma montre comment instancier les wrappers Datetime pour les fichiers TVFs de requête pour toutes les instances de capture créées pour les tables sources dans un schéma de base de données donné.

Pour obtenir un exemple qui utilise un wrapper datetime pour rechercher des données modifiées, consultez le modèle Obtenir des modifications nettes à l’aide de wrapper avec des indicateurs de mise à jour. Ce modèle montre comment interroger les modifications nettes avec une fonction wrapper lorsque le wrapper est configuré pour retourner des indicateurs de mise à jour. Notez que l’option de filtre de ligne « all with mask » est requise pour que la fonction de requête sous-jacente retourne un masque de mise à jour non null lors de la mise à jour. Les valeurs Null sont passées pour les limites inférieure et supérieure de l'intervalle de date afin d'indiquer à la fonction d'utiliser le point de terminaison inférieur et le point de terminaison supérieur de l'intervalle de validité pour l'instance de capture lors de l'exécution de la requête basée sur les LSN sous-jacente. La requête retourne une ligne pour chaque modification d’une ligne source qui s’est produite dans la plage valide pour l’instance de capture.

Utilisation des fonctions wrapper Datetime pour passer d’une instance de capture à l’autre

La capture de données modifiées prend en charge jusqu’à deux instances de capture pour une seule table source suivie. L’utilisation principale de cette fonctionnalité consiste à prendre en charge une transition entre plusieurs instances de capture lorsque le langage de définition de données (DDL) change dans la table source développe l’ensemble de colonnes disponibles pour le suivi. Lors de la transition vers une nouvelle instance de capture, une façon de protéger les niveaux d’application supérieurs contre les modifications apportées aux noms des fonctions de requête sous-jacentes consiste à utiliser une fonction wrapper pour encapsuler l’appel sous-jacent. Vérifiez ensuite que le nom de la fonction wrapper reste le même. Lorsque le changement doit se produire, l'ancienne fonction d'enveloppement peut être supprimée et une nouvelle fonction avec le même nom créée, qui référence les nouvelles fonctions de requête. En modifiant d’abord le script généré pour créer une fonction wrapper du même nom, vous pouvez passer à une nouvelle instance de capture sans affecter les couches d’application supérieures.

Voir aussi

Suivre les modifications de données (SQL Server)
À propos de la capture de données modifiées (SQL Server)
Activer et désactiver la capture de données modifiées (SQL Server)
Administrer et surveiller la capture de données modifiées (SQL Server)