Articles transactionnels - spécifier comment les changements sont propagés

S’applique à :SQL ServerAzure SQL Managed Instance

La réplication transactionnelle permet de préciser comment les modifications des données sont propagées entre le serveur de publication et les Abonnés. Pour chaque table publiée, vous pouvez spécifier l’une des quatre façons dont chaque opération (INSERT, UPDATEou DELETE) doit être propagée à l’Abonné :

• Spécifier que la réplication transactionnelle doit générer un script puis appeler une procédure stockée pour propager les modifications aux Abonnés (option par défaut).

• Spécifiez que la modification doit être propagée au moyen d’une instruction INSERT, UPDATE ou DELETE (valeur par défaut pour les abonnés autres que ceux de SQL Server).

• Indiquez qu'une procédure stockée personnalisée doit être utilisée.

• Spécifier que cette action ne doit pas être effectuée sur les Abonnés. Les transactions de ce type ne sont pas répliquées.

Par défaut, la réplication transactionnelle propage les modifications vers les abonnés via un groupe de procédures stockées installées sur chaque abonné. Lorsqu'une opération d'insertion, de mise à jour ou de suppression se produit sur une table du serveur de publication, l'opération est traduite par un appel à une procédure stockée sur l'Abonné. La procédure stockée accepte des paramètres associés aux colonnes de la table, permettant ainsi de modifier ces colonnes chez l’Abonné.

Pour définir la méthode de propagation des modifications de données des articles transactionnels, consultez Définir la méthode de propagation des modifications de données des articles transactionnels.

Procédures stockées par défaut et personnalisées

La réplication crée trois procédures stockées par défaut pour chaque article de tableau :

• sp_MSins_<tablename>, qui gère les opérations d’insertion.

• sp_MSupd_<nomdetable>, qui gère les mises à jour.

• sp_MSdel_<nom_de_table>, qui gère les opérations de suppression.

Le >< utilisé dans la procédure dépend de la manière dont vous ajoutez l’article à la publication et selon que la base de données d’abonnement contient une table portant le même nom mais appartenant à un autre propriétaire.

Vous pouvez remplacer l’une de ces procédures par une procédure personnalisée que vous spécifiez lors de l’ajout d’un article à une publication. Utilisez des procédures personnalisées si votre application nécessite une logique personnalisée, comme l’insertion de données dans une table d’audit lorsqu’une ligne est mise à jour chez un abonné. Pour plus d’informations sur la spécification des procédures stockées personnalisées, consultez les articles pratiques listés dans la section précédente.

Lorsque vous spécifiez soit les procédures de réplication par défaut, soit les procédures personnalisées, vous spécifiez également la syntaxe des appels pour chaque procédure. La réplication sélectionne la syntaxe par défaut des appels si vous utilisez les procédures par défaut. La syntaxe d'appel détermine la structure des paramètres fournis à la procédure et la quantité d'informations envoyées à l'Abonné avec chaque modification de données. Pour plus d’informations, voir la section « Syntaxe d’appel pour les procédures stockées » dans cet article.

Considérations pour l’utilisation de procédures stockées personnalisées

Les éléments suivants doivent être pris en compte lors de l'utilisation de procédures stockées personnalisées :

• Vous devez assurer la prise en charge de la logique dans la procédure stockée ; Microsoft n’assure pas la prise en charge de la logique personnalisée.

• Pour éviter les conflits avec les transactions utilisées par la réplication, n’utilisez pas de transactions explicites dans les procédures personnalisées.

• Le schéma au niveau de l’abonné est généralement identique à celui du Publisher, mais il peut aussi être un sous-ensemble du schéma Publisher si vous utilisez le filtrage par colonnes. Si vous devez transformer le schéma au fur et à mesure que les données se déplacent afin que le schéma à l'abonné ne soit pas un sous-ensemble du schéma du Publisher, utilisez SQL Server 2019 Integration Services (SSIS). Pour plus d’informations, consultez SQL Server Integration Services.

• Si vous modifiez un schéma sur une table publiée, régénérez les procédures personnalisées. Pour plus d’informations, consultez Régénérer des procédures transactionnelles personnalisées pour refléter des modifications de schéma.

• Si vous utilisez une valeur supérieure à 1 pour le paramètre -SubscriptionStreams de l’Agent Distribution, assurez-vous que les mises à jour des colonnes clés primaires réussissent. Par exemple :

  update ... set pk = 2 where pk = 1 -- update 1  
  update ... set pk = 3 where pk = 2 -- update 2  
  

  Si l’Agent Distribution utilise plus d’une connexion, ces deux mises à jour peuvent se reproduire sur des connexions différentes. Si la mise à jour 1 est appliquée en premier, il n’y a pas de problème. Si la mise à jour 2 est appliquée en premier, elle revient 0 rows affected car la mise à jour 1 n’a pas encore eu lieu. Les procédures par défaut gèrent cette situation en générant une erreur si aucune ligne n’est affectée lors d’une mise à jour :

  if @@rowcount = 0  
      if @@microsoftversion>0x07320000  
          exec sys.sp_MSreplraiserror 20598  
  

  Le déclenchement de l’erreur force l’Agent de distribution à réessayer les mises à jour via une seule connexion, ce qui aboutit. Les procédures stockées personnalisées doivent inclure une logique similaire.

Syntaxe d'appel des procédures stockées

Vous pouvez utiliser cinq options de syntaxe différentes pour appeler les procédures utilisées par la réplication transactionnelle :

• Syntaxe de CALL. Utilisez cette syntaxe pour les insertions, mises à jour et suppressions. Par défaut, la réplication utilise cette syntaxe pour les insertions et les suppressions.

• Syntaxe SCALL. Utilisez cette syntaxe uniquement pour les mises à jour. Par défaut, la réplication utilise cette syntaxe pour les mises à jour.

• Syntaxe MCALL. Utilisez cette syntaxe uniquement pour les mises à jour.

• Syntaxe XCALL Utilisez cette syntaxe pour les mises à jour et les suppressions.

• VCALL. Utilisez cette syntaxe pour des abonnements à mettre à jour. Utilisation interne uniquement.

Chaque méthode diffère dans la quantité de données qu’elle envoie à l’abonné. Par exemple, SCALL ne transmet des valeurs que pour les colonnes qu’une mise à jour affecte réellement. XCALL nécessite toutes les colonnes, qu’une mise à jour les affecte ou non, ainsi que toutes les anciennes valeurs de données pour chaque colonne. Dans de nombreux cas, SCALL est approprié pour les mises à jour, mais si votre application nécessite toutes les valeurs de données lors d’une mise à jour, XCALL répond à ce besoin.

Syntaxe de CALL

INSERT procédures stockées
Les procédures stockées qui traitent les instructions INSERT reçoivent les valeurs insérées pour toutes les colonnes :

c1, c2, c3,... cn  

UPDATE procédures stockées
Les procédures stockées qui gèrent UPDATE les instructions reçoivent les valeurs mises à jour pour toutes les colonnes définies dans l’article, suivies des valeurs originales des colonnes clés primaires. Le processus ne cherche pas à déterminer quelles colonnes ont changé :

c1, c2, c3,... cn, pkc1, pkc2, pkc3,... pkcn  

DELETE procédures stockées
Les procédures stockées qui traitent les instructions DELETE reçoivent les valeurs des colonnes de clé primaire :

pkc1, pkc2, pkc3,... pkcn  

Syntaxe SCALL

UPDATE procédures stockées
Les procédures stockées qui gèrent UPDATE les instructions reçoivent les valeurs mises à jour uniquement pour les colonnes qui ont changé, puis les valeurs originales pour les colonnes clés primaires, puis un paramètre bitmask (binary(n)) indiquant les colonnes modifiées. Dans l’exemple suivant, la colonne 2 (c2) n’a pas changé :

c1, , c3,... cn, pkc1, pkc2, pkc3,... pkcn, bitmask  

Syntaxe MCALL

UPDATE procédures stockées
Les procédures stockées qui gèrent UPDATE les instructions reçoivent les valeurs mises à jour pour toutes les colonnes définies dans l’article, puis les valeurs originales pour les colonnes clés primaires, puis un paramètre bitmask (binary(n)) qui indique les colonnes modifiées :

c1, c2, c3,... cn, pkc1, pkc2, pkc3,... pkcn, bitmask  

Syntaxe XCALL

UPDATE procédures stockées
Les procédures stockées qui gèrent UPDATE les instructions reçoivent les valeurs originales (l’image avant) pour toutes les colonnes définies dans l’article, suivies des valeurs mises à jour (l’image après) pour toutes les colonnes définies dans l’article :

old-c1, old-c2, old-c3,... old-cn, c1, c2, c3,... cn,  

DELETE procédures stockées
Les procédures stockées qui gèrent DELETE les instructions reçoivent les valeurs originales (l’image avant) pour toutes les colonnes définies dans l’article :

old-c1, old-c2, old-c3,... old-cn  

Exemples

Les procédures suivantes représentent les procédures par défaut créées pour la Vendor Table dans la base de données exemple de Adventure Works.

--INSERT procedure using CALL syntax  
create procedure [sp_MSins_PurchasingVendor]   
  @c1 int,@c2 nvarchar(15),@c3 nvarchar(50),@c4 tinyint,@c5 bit,@c6 bit,@c7 nvarchar(1024),@c8 datetime  
as   
begin   
insert into [Purchasing].[Vendor]([VendorID]  
,[AccountNumber]  
,[Name]  
,[CreditRating]  
,[PreferredVendorStatus]  
,[ActiveFlag]  
,[PurchasingWebServiceURL]  
,[ModifiedDate])  
values (   
 @c1  
,@c2  
,@c3  
,@c4  
,@c5  
,@c6  
,@c7  
,@c8  
 )   
end  
go  
  
--UPDATE procedure using SCALL syntax  
create procedure [sp_MSupd_PurchasingVendor]   
 @c1 int = null,@c2 nvarchar(15) = null,@c3 nvarchar(50) = null,@c4 tinyint = null,@c5 bit = null,@c6 bit = null,@c7 nvarchar(1024) = null,@c8 datetime = null,@pkc1 int  
,@bitmap binary(2)  
as  
begin  
update [Purchasing].[Vendor] set   
 [AccountNumber] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [AccountNumber] end  
,[Name] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [Name] end  
,[CreditRating] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [CreditRating] end  
,[PreferredVendorStatus] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [PreferredVendorStatus] end  
,[ActiveFlag] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [ActiveFlag] end  
,[PurchasingWebServiceURL] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [PurchasingWebServiceURL] end  
,[ModifiedDate] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [ModifiedDate] end  
where [VendorID] = @pkc1  
if @@rowcount = 0  
    if @@microsoftversion>0x07320000  
        exec sp_MSreplraiserror 20598  
end  
go  
  
--DELETE procedure using CALL syntax  
create procedure [sp_MSdel_PurchasingVendor]   
  @pkc1 int  
as   
begin   
delete [Purchasing].[Vendor]  
where [VendorID] = @pkc1  
if @@rowcount = 0  
    if @@microsoftversion>0x07320000  
        exec sp_MSreplraiserror 20598  
end   
go  

Contenu connexe

• Options d’article pour la réplication transactionnelle