Articles transactionnels - Spécifier le mode de propagation des modifications

S’applique à : SQL Server Azure 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 méthodes de propagation possibles d'une opération (INSERT, UPDATE ou DELETE) vers 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 à l’aide d’une instruction INSERT, UPDATE ou DELETE (option par défaut pour les abonnés qui ne sont pas abonnés à SQL Server).

• Spécifier l'utilisation d'une procédure stockée personnalisé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 Insert, Update ou Delete est effectuée sur une table du serveur de publication, elle est convertie en appel à une procédure stockée sur l'Abonné. La procédure stockée accepte des paramètres correspondant aux colonnes de la table, ce qui permet la modification de ces colonnes sur 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

Trois procédures sont créées par défaut par la réplication pour chaque article de table :

• sp_MSins_< nomdetable >, qui gère les insertions.

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

• sp_MSdel_< nomdetable >, qui gère les suppressions.

Le <nom de table> utilisé dans la procédure varie en fonction de la méthode utilisée pour ajouter l’article à la publication et si la base de données d’abonnement contient une table au nom identique mais avec un propriétaire différent.

N'importe laquelle de ces procédures peut être remplacée par une procédure personnalisée que vous spécifiez lors de l'ajout d'un article à une publication. Les procédures personnalisées sont utilisées dans le cas où l'application exige une logique personnalisée, par exemple l'insertion de données dans une table d'audit lors de la mise à jour d'une ligne sur l'Abonné. Pour plus d'informations sur la définition de procédures stockées personnalisées, reportez-vous à la liste des rubriques ci-dessus.

Si vous utilisez les procédures de réplication par défaut ou les procédures personnalisées, vous devez également spécifier la syntaxe d'appel pour chaque procédure (la réplication sélectionne les valeurs par défaut 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, consultez la section « Syntaxe d'appel des procédures stockées », plus loin dans cette rubrique.

Considérations sur l'utilisation des 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 prendre en charge la logique de la procédure stockée ; Microsoft n’assure pas la prise en charge d’une logique personnalisée.

• Afin d'éviter les conflits avec les transactions utilisées par la réplication, évitez d'utiliser des transactions explicites dans les procédures personnalisées.

• Le schéma de l'Abonné est généralement identique à celui du serveur de publication mais il peut également représenter un sous-ensemble du schéma du serveur de publication si vous utilisez le filtrage de colonnes. Toutefois, si vous devez transformer le schéma lors du déplacement des données de telle sorte que le schéma de l’abonné ne représente pas un sous-ensemble du schéma sur le serveur de publication, SQL Server 2019 Integration Services (SSIS) est la solution recommandée. Pour plus d’informations, consultez SQL Server Integration Services.

• Si vous modifiez le schéma d'une table publiée, les procédures personnalisées doivent être régénéré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 de distribution, vérifiez que les mises à jour des colonnes clés primaires se sont correctement déroulées. Par exemple :

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

  Si l'Agent de distribution utilise plusieurs connexions, ces deux mises à jour peuvent être répliquées sur différentes connexions. Si la première mise à jour (update 1) est d'abord appliquée, cela ne pose aucun problème ; en revanche, si la deuxième mise à jour (update 2) est la première appliquée, elle retourne '0 ligne affectée' parce que la première mise à jour n'a pas encore été effectuée. Dans les procédures par défaut, cette situation génère une erreur si aucune ligne n'est affectée dans une mise à jour :

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

  La génération de l'erreur force l'Agent de distribution à retenter les mises à jour sur une seule connexion, lesquelles, dans ce cas, réussissent. Les procédures stockées personnalisées doivent inclure une logique similaire.

Syntaxe d'appel des procédures stockées

Vous avez le choix entre cinq options quant à la syntaxe employée pour appeler les procédures utilisées par la réplication transactionnelle :

• Syntaxe CALL. Peut être utilisée pour les insertions, les mises à jour et les suppressions. Par défaut, la réplication utilise cette syntaxe pour les insertions et les suppressions.

• Syntaxe SCALL. Peut être utilisée uniquement pour les mises à jour. Par défaut, la réplication utilise cette syntaxe pour les mises à jour.

• Syntaxe MCALL. Peut être utilisée uniquement pour les mises à jour.

• Syntaxe XCALL Peut être utilisée pour les mises à jour et les suppressions.

• VCALL. Utilisée pour les abonnements pouvant être mis à jour. Utilisation interne uniquement.

Ces méthodes diffèrent par le volume de données propagé à l'Abonné. Par exemple, SCALL passe des valeurs uniquement pour les colonnes affectées par une mise à jour. XCALL, en revanche, exige toutes les colonnes (qu'elles soient affectées ou non par une mise à jour) et toutes les anciennes valeurs des données pour chaque colonne. La syntaxe SCALL convient généralement aux opérations de mise à jour sauf si votre application exige toutes les valeurs des données au cours d'une mise à jour, auquel cas vous pouvez utiliser XCALL.

Syntaxe CALL

Procédures stockées INSERT
Les procédures stockées qui gèrent des instructions INSERT recevront les valeurs insérées pour toutes les colonnes :

c1, c2, c3,... cn  

Procédures stockées UPDATE
Les procédures stockées gérant des instructions UPDATE recevront les valeurs mises à jour pour toutes les colonnes définies dans l'article, suivies des valeurs d'origine pour les colonnes clés primaire (aucune tentative ne sera faite pour déterminer les colonnes qui ont été modifiées) :

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

Procédures stockées DELETE
Les procédures stockées qui gèrent des instructions DELETE recevront des valeurs pour les colonnes clés primaire :

pkc1, pkc2, pkc3,... pkcn  

Syntaxe SCALL

Procédures stockées UPDATE
Les procédures stockées gérant des instructions UPDATE ne recevront les valeurs mises à jour que pour les colonnes qui ont été modifiées, suivies tout d'abord des valeurs d'origine des colonnes clés primaire, puis d'un paramètre de masque binaire (binary(n)) indiquant les colonnes modifiées. Dans l'exemple suivant, la colonne 2 (c2) n'a pas été modifiée :

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

Syntaxe MCALL

Procédures stockées UPDATE
Les procédures stockées gérant des instructions UPDATE recevront les valeurs mises à jour de toutes les colonnes définies dans l'article, suivies tout d'abord des valeurs d'origine des colonnes clés primaire, puis d'un paramètre de masque binaire (binary(n)) indiquant les colonnes modifiées :

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

Syntaxe XCALL

Procédures stockées UPDATE
Les procédures stockées gérant des instructions UPDATE recevront les valeurs d'origine (c'est-à-dire l'image avant) de toutes les colonnes définies dans l'article, suivies des valeurs mises à jour (image après) de ces mêmes colonnes :

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

Procédures stockées DELETE
Les procédures stockées qui gèrent des instructions DELETE recevront les valeurs d'origine (image avant) de 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  

Voir aussi

Article Options for Transactional Replication