Publication de l'exécution de procédures stockées dans la réplication transactionnelle

S’applique à :SQL ServerAzure SQL Managed Instance

Si vous avez une ou plusieurs procédures stockées qui s'exécutent sur le serveur de publication et qui affectent des tables publiées, vous pouvez envisager d'inclure ces procédures stockées dans votre publication en tant qu'articles d'exécution de procédure stockée. La définition de la procédure (l’instructionCREATE PROCEDURE) est répliquée sur l’Abonné lorsque l’abonnement est initialisé ; lorsque la procédure est exécutée au Publisher, la réplication exécute la procédure correspondante sur l’Abonné. Cela peut apporter des performances significativement meilleures dans les cas où de grosses opérations de traitement sont effectuées, car seule l'exécution de la procédure est répliquée, ce qui élimine la nécessité de répliquer les modifications individuelles pour chaque ligne. Supposons par exemple que vous créez la procédure stockée suivante dans la base de données de publication :

CREATE PROC give_raise AS  
UPDATE EMPLOYEES SET salary = salary * 1.10

Cette procédure accorde à chacun des 10 000 employés de la société une augmentation de salaire de 10 %. Lorsque vous exécutez cette procédure stockée sur le serveur de publication, le salaire de chaque employé est mis à jour. Sans la réplication de l'exécution de la procédure stockée, la mise à jour est envoyée aux Abonnés comme une grosse transaction comportant plusieurs étapes :

BEGIN TRAN  
UPDATE EMPLOYEES SET salary = salary * 1.10 WHERE PK = 'emp 1'  
UPDATE EMPLOYEES SET salary = salary * 1.10 WHERE PK = 'emp 2'

Et ceci se répète pour les 10 000 mises à jour.

Avec la réplication de l'exécution de procédure stockée, la réplication envoie seulement la commande pour exécuter la procédure stockée sur l'Abonné, au lieu d'écrire toutes les mises à jour dans la base de données de distribution, puis de les envoyer à l'Abonné via le réseau :

EXEC give_raise

Important

La réplication des procédures stockées ne convient pas à toutes les applications. Si un article est filtré horizontalement, de sorte que les ensembles de lignes soient différents sur l'éditeur et sur l'abonné, l'exécution de la même procédure stockée sur les deux serveurs donne des résultats différents. De même, si une mise à jour est basée sur la sous-requête d'une autre table non répliquée, l'exécution de la même procédure stockée sur le serveur de publication et sur l'Abonné renvoie des résultats différents.

Pour publier l'exécution d'une procédure stockée

Modification de la procédure chez l’abonné

Par défaut, la définition de la procédure stockée sur le serveur de publication est propagée vers chaque Abonné. Cependant, vous pouvez aussi modifier la procédure stockée sur l'Abonné. Ceci est utile si vous souhaitez qu’une logique différente s’exécute au niveau du Publisher et du Subscriber. Considérons par exemple la procédure stockée sur le serveur de publication sp_big_delete, qui a deux fonctions : elle supprime 1 000 000 de lignes de la table répliquée big_table1 et met à jour la table non répliquée big_table2. Pour solliciter moins de ressources réseau, vous pouvez transmettre la suppression de ce million de lignes en tant que procédure stockée en publiant sp_big_delete. Au niveau de l’Abonné, vous pouvez modifier sp_big_delete pour supprimer uniquement les 1 million de lignes et ne pas effectuer la mise à jour suivante de big_table2.

Remarque

Par défaut, toutes les modifications effectuées à l’aide ALTER PROCEDURE du Publisher sont propagées à l’Abonné. Pour éviter cela, désactivez la propagation des modifications de schéma avant d’exécuter ALTER PROCEDURE. Pour obtenir des informations sur les modifications de schéma, consultez Modifier le schéma dans les bases de données de publication.

Types d'articles d'exécution de procédure stockée

Il existe deux manières de publier l'exécution d'une procédure stockée : article d'exécution de procédure sérialisable et article d'exécution de procédure.

  • L'option sérialisable est recommandée car elle réplique l'exécution de la procédure seulement si la procédure est exécutée dans le contexte d'une transaction sérialisable. Si la procédure stockée est exécutée en dehors d'une transaction sérialisable, les modifications apportées aux données dans les tables publiées sont répliquées sous la forme d'une série d'instructions DML. Ceci favorise la mise en cohérence des données côté abonné avec celles côté éditeur Cela est particulièrement utile pour les opérations par lots, telles que les opérations de nettoyage de grande envergure.

  • Avec l'option d'exécution de procédure, il est possible que l'exécution de la procédure soit répliquée vers tous les abonnés, indépendamment du fait que des instructions individuelles de la procédure stockée aient réussi ou non. En outre, étant donné que les modifications apportées aux données par la procédure stockée peuvent émaner de transactions multiples, il se peut que les données des abonnés ne soient pas identiques à celles du serveur de publication. Pour traiter ces problèmes, il est requis que les abonnés soient en lecture seule et que vous utilisiez un niveau d'isolement supérieur à la lecture non validée. Si vous utilisez une lecture non validée, les modifications apportées aux données dans les tables publiées sont répliquées comme une série d'instructions DML.

L'exemple suivant illustre l'intérêt de configurer une réplication de procédures en tant qu'articles de procédures sérialisables.

BEGIN TRANSACTION T1  
SELECT @var = max(col1) FROM tableA  
UPDATE tableA SET col2 = <value>   
   WHERE col1 = @var   
  
BEGIN TRANSACTION T2  
INSERT tableA VALUES <values>  
COMMIT TRANSACTION T2

Dans l’exemple précédent, il est supposé que l’instruction SELECT dans la transaction T1 se produit avant la INSERT transaction T2.

Si la procédure n'est pas exécutée dans une transaction sérialisable (avec le niveau d'isolement SERIALIZABLE), la transaction T2 sera autorisée à insérer une nouvelle ligne dans la plage de l'instruction SELECT de T1 et sa validation interviendra avant celle de T1. Cela signifie également qu'elle sera appliquée sur l'abonné avant T1. Lorsque T1 est appliqué au niveau de l’Abonné, le SELECT peut potentiellement renvoyer une valeur différente de celle du Publisher et entraîner un résultat différent de celui de UPDATE.

Si la procédure est exécutée dans une transaction sérialisable, la transaction T2 ne sera pas autorisée à opérer des insertions dans la plage couverte par l'instruction SELECT de T2. Elle sera neutralisée jusqu'à la validation de T1, ce qui garantit des résultats similaires sur l'abonné.

Les verrous sont maintenus plus longtemps lorsque vous exécutez la procédure dans une transaction sérialisable, ce qui peut réduire la simultanéité.

Paramètre XACT_ABORT

Lors de la réplication de l’exécution de la procédure stockée, le paramètre de la session exécutant la procédure stockée doit spécifier XACT_ABORT ON. Si XACT_ABORT est définie sur OFF et qu’une erreur se produit lors de l’exécution de la procédure sur le serveur de publication, la même erreur se produira chez l’Abonné, ce qui entraînera l’échec de l’Agent de distribution. Le fait de définir XACT_ABORT sur ON garantit que toute erreur rencontrée lors de l’exécution sur le serveur de publication provoque l’annulation complète de l’exécution, évitant ainsi l’échec de l’Agent de distribution. Pour plus d’informations sur le paramètre XACT_ABORT, consultez SET XACT_ABORT (Transact-SQL).

Si vous devez définir le paramètre XACT_ABORT sur OFF, spécifiez le paramètre -SkipErrors pour l’Agent de distribution. Cela permet à l'agent de continuer l'application des modifications sur l'Abonné même si une erreur est rencontrée.

Contenu connexe

  • Last updated on