Partager via

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

Si vous avez une ou plusieurs procédures stockées qui s'exécutent sur l'Éditeur et affectent les tables publiées, envisagez d'inclure ces procédures stockées dans votre publipostage en tant qu'articles d'exécution de procédures stockées. La définition de la procédure (instruction CREATE PROCEDURE) est répliquée sur l’Abonné lorsque l’abonnement est initialisé ; lorsque la procédure est exécutée sur le serveur de publication, la réplication exécute la procédure correspondante sur l’Abonné. Cela peut fournir de meilleures performances dans les cas où des opérations de traitement par lots volumineuses sont effectuées, car seule l’exécution de la procédure est répliquée, en contournant la nécessité de répliquer les modifications individuelles pour chaque ligne. Par exemple, supposons 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 donne à chacun des 10 000 employés de votre entreprise une augmentation de salaire de 10 %. Lorsque vous exécutez cette procédure stockée sur le serveur de publication, elle met à jour le salaire de chaque employé. Sans la réplication de l’exécution de procédure stockée, la mise à jour est envoyée aux Abonnés sous la forme d’une transaction en plusieurs étapes volumineuse :

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 cela se répète pour 10 000 mises à jour.

Avec la réplication de l’exécution de procédure stockée, la réplication envoie uniquement la commande pour exécuter la procédure stockée sur l’Abonné, plutôt que d’écrire toutes les mises à jour dans la base de données de distribution, puis de les envoyer sur le réseau à l’Abonné :

EXEC give_raise

Important

La réplication de procédure stockée n’est pas appropriée pour toutes les applications. Si un article est filtré horizontalement, ce qui entraîne l'existence de différents ensembles de lignes sur le serveur de publication par rapport au récepteur, l'exécution de la même procédure stockée retourne des résultats différents. De même, si une mise à jour est basée sur une 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 l’Abonné retourne des résultats différents.

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

Modification de la procédure sur l’Abonné

Par défaut, la définition de procédure stockée sur le serveur de publication est propagée à chaque Abonné. Toutefois, vous pouvez également modifier la procédure stockée sur l’Abonné. Cela est utile si vous souhaitez que différentes logiques soient exécutées sur le serveur de publication et l’Abonné. Par exemple, considérez sp_big_delete, une procédure stockée sur le serveur de publication qui a deux fonctions : elle supprime 1 000 000 lignes de la table répliquée big_table1 et met à jour la table non répliquée big_table2. Pour réduire la demande sur les ressources réseau, vous devez propager la suppression de 1 million de lignes en tant que procédure stockée en publiant sp_big_delete. Au récepteur, vous pouvez modifier sp_big_delete pour supprimer uniquement le million de lignes sans effectuer la mise à jour suivante de big_table2.

Remarque

Par défaut, toutes les modifications apportées à l’aide de ALTER PROCEDURE sur le serveur de publication sont propagées à l’Abonné. Pour éviter cela, désactivez la propagation des modifications de schéma avant d’exécuter ALTER PROCEDURE. Pour plus d’informations sur les modifications de schéma, consultez Apporter des modifications de schéma sur les bases de données de publication.

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

Il existe deux façons différentes 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 uniquement 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. Ce comportement contribue à rendre les données sur l’Abonné cohérentes avec les données du serveur de publication. Cela est particulièrement utile pour les opérations de traitement par lots, telles que les opérations de nettoyage volumineuses.

  • Avec l’option d’exécution de procédure, il est possible que l’exécution puisse être répliquée sur tous les Abonnés, que les instructions individuelles de la procédure stockée réussissent. En outre, étant donné que les modifications apportées aux données par la procédure stockée peuvent se produire dans plusieurs transactions, les données des Abonnés peuvent ne pas être cohérentes avec les données du serveur de publication. Pour résoudre ces problèmes, il est nécessaire que les Abonnés soient en lecture seule et que vous utilisez un niveau d’isolation supérieur à celui de lecture non validée. Si vous utilisez la lecture non validée, 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.

L’exemple suivant illustre pourquoi il est recommandé de configurer la réplication des procédures en tant qu’articles de procédure sérialisable.

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 l’insertion dans la transaction T2.

Si la procédure n’est pas exécutée dans une transaction sérialisable (avec le niveau d’isolation défini sur SERIALIZABLE), la transaction T2 est autorisée à insérer une nouvelle ligne dans la plage de l’instruction SELECT dans T1 et elle est validée avant T1. Cela signifie également qu’elle sera appliquée à l’Abonné avant T1. Lorsque T1 est appliqué à l’Abonné, le SELECT peut potentiellement retourner une valeur différente de l'Éditeur et peut avoir un résultat différent de celui de l'UPDATE.

Si la procédure est exécutée dans une transaction sérialisable, la transaction T2 n’est pas autorisée à insérer dans la plage couverte par l’instruction SELECT dans T2. Elle sera bloquée jusqu’à ce que les validations T1 garantissent les mêmes résultats sur l’Abonné.

Les verrous sont conservés plus longtemps lorsque vous exécutez la procédure dans une transaction sérialisable et peuvent entraîner une réduction de la concurrence.

Paramètre de 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éfini sur OFF et qu’une erreur se produit pendant l’exécution de la procédure sur le serveur de publication, la même erreur se produit sur l’Abonné, ce qui entraîne l’échec de l’Agent de distribution. La spécification de XACT_ABORT ON garantit que toutes les erreurs rencontrées pendant l’exécution au niveau du serveur de publication entraînent la restauration de l’intégralité de l’exécution, ce qui évite l’échec de l’Agent de distribution. Pour plus d’informations sur la définition XACT_ABORT, consultez SET XACT_ABORT (Transact-SQL).

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

Voir aussi

Options d’article pour la réplication transactionnelle

  • Last updated on