Articoli transazionali - specificare come vengono propagati i cambiamenti

Si applica a:SQL ServerIstanza gestita di SQL di Azure

La replica transazionale consente di specificare la modalità di propagazione delle modifiche dei dati dal server di pubblicazione ai Sottoscrittori. Per ogni tabella pubblicata, è possibile specificare uno dei quattro modi in cui ogni operazione (INSERT, UPDATEo DELETE) deve essere propagata al Sottoscrittore:

• Specificare che la replica transazionale deve generare uno script e successivamente chiamare una stored procedure per propagare le modifiche ai Sottoscrittori (predefinito).

• Specificare che la modifica deve essere propagata usando un'istruzione INSERT, UPDATEo DELETE (impostazione predefinita per i Sottoscrittori non SQL Server).

• Specificare che è necessario utilizzare una stored procedure personalizzata.

• Specificare che l'azione non deve essere eseguita in alcun Sottoscrittore. In tal caso le transazioni non vengono replicate.

Per impostazione predefinita, la replica transazionale propaga le modifiche ai Sottoscrittori utilizzando un set di stored procedure installate in ogni Sottoscrittore. Quando si verifica un inserimento, un aggiornamento o un'eliminazione in una tabella del Publisher, l'operazione viene convertita in una chiamata a una stored procedure nel Subscriber. La stored procedure accetta parametri che eseguono il mapping alle colonne della tabella, consentendo a tali colonne di essere modificate nel Sottoscrittore.

Per impostare il metodo di propagazione per la modifica dei dati negli articoli transazionali, vedere Impostazione del metodo di propagazione per le modifiche ai dati negli articoli transazionali.

Procedure memorizzate predefinite e personalizzate

La replica crea tre procedure memorizzate predefinite per ogni articolo della tabella:

• sp_MSins_<tablename>, per la gestione degli inserimenti.

• sp_MSupd_<tablename>, per la gestione degli aggiornamenti.

• sp_MSdel_<tablename>, che gestisce le eliminazioni.

Il < nome >della tabella utilizzato nella procedura dipende da come si aggiunge l'articolo alla pubblicazione e se il database degli abbonamenti contiene una tabella con lo stesso nome ma un proprietario diverso.

Puoi sostituire una qualsiasi di queste procedure con una procedura personalizzata che specifichi quando aggiungi un articolo a una pubblicazione. Usa procedure personalizzate se la tua applicazione richiede una logica personalizzata, come inserire dati in una tabella di audit quando una riga viene aggiornata presso un abbonato. Per maggiori informazioni sulla specificazione delle stored procedure personalizzate, consulta gli articoli pratici elencati nella sezione precedente.

Quando specifichi le procedure di replica predefinite o procedure personalizzate, specifichi anche la sintassi delle chiamate per ciascuna procedura. La replicazione seleziona la sintassi predefinita delle chiamate se si usano le procedure predefinite. La sintassi di chiamata determina la struttura dei parametri forniti alla procedura e la quantità di informazioni inviate al Sottoscrittore a ogni modifica dei dati. Per ulteriori informazioni, consulta la sezione "Sintassi di chiamata per procedure memorizzate" in questo articolo.

Considerazioni per l'utilizzo di stored procedure personalizzate

È bene tenere a mente le seguenti considerazioni quando si utilizzano stored procedure personalizzate:

• È necessario fornire supporto per la logica della procedura archiviata; Microsoft non fornisce supporto per la logica personalizzata.

• Per evitare conflitti con le transazioni utilizzate dalla replica, non usare transazioni esplicite nelle procedure personalizzate.

• Lo schema presso l'Abbonato è tipicamente identico a quello dello Publisher, ma può anche essere un sottoinsieme dello schema Publisher se si utilizza il filtraggio a colonne. Se devi trasformare lo schema man mano che i dati si spostano in modo che lo schema dell'Abbonato non sia un sottoinsieme dello schema del Publisher, usa SQL Server 2019 Integration Services (SSIS). Per altre informazioni, vedere SQL Server Integration Services.

• Se apporti modifiche allo schema a una tabella pubblicata, rigenera le procedure personalizzate. Per altre informazioni, vedere Rigenerare procedure transazionali personalizzate per riflettere le modifiche dello schema.

• Se usi un valore maggiore di 1 per il parametro -SubscriptionStreams del agente di distribuzione, assicurati che gli aggiornamenti alle colonne chiave primarie abbiano successo. Ad esempio:

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

  Se l'agente di distribuzione utilizza più di una connessione, questi due aggiornamenti potrebbero replicarsi su connessioni diverse. Se viene applicato prima l'aggiornamento 1, non ci sono problemi. Se l'aggiornamento 2 viene applicato per primo, torna 0 rows affected perché l'aggiornamento 1 non è ancora avvenuto. Le procedure predefinite gestiscono questa situazione generando un errore se nessuna riga è interessata durante un aggiornamento:

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

  La generazione dell'errore costringe l'agente di distribuzione a ripetere gli aggiornamenti tramite un'unica connessione, operazione che ha esito positivo. È necessario che le stored procedure personalizzate includano una logica simile.

Sintassi di chiamata per le stored procedure

Puoi utilizzare cinque diverse opzioni di sintassi per chiamare le procedure che la replica transazionale utilizza:

• Sintassi CALL. Usa questa sintassi per inserti, aggiornamenti e cancellazioni. Per impostazione predefinita, la replica utilizza questa sintassi per gli inserimenti e le eliminazioni.

• Sintassi SCALL. Usa questa sintassi solo per gli aggiornamenti. Per impostazione predefinita, la replica utilizza questa sintassi per gli aggiornamenti.

• Sintassi MCALL. Usa questa sintassi solo per gli aggiornamenti.

• Sintassi XCALL. Usa questa sintassi per aggiornamenti e cancellazioni.

• VCALL. Usa questa sintassi per abbonamenti aggiornabili. Solo per uso interno.

Ogni metodo differisce nella quantità di dati che invia all'Abbonato. Ad esempio, SCALL invia valori solo per le colonne che un aggiornamento effettivamente influenza. XCALL richiede tutte le colonne, che un aggiornamento le influenzi o meno, e tutti i vecchi valori di dati per ogni colonna. In molti casi, SCALL è appropriato per gli aggiornamenti, ma se la tua applicazione richiede tutti i valori dati durante un aggiornamento, XCALL supporta questa esigenza.

Sintassi CALL

INSERT stored procedure
Le procedure memorizzate che gestiscono le istruzioni INSERT ricevono i valori inseriti in tutte le colonne:

c1, c2, c3,... cn  

UPDATE stored procedure
Le stored procedure che gestiscono le istruzioni UPDATE ricevono i valori aggiornati per tutte le colonne definite nell'articolo, seguiti dai valori originali delle colonne della chiave primaria. Il processo non tenta di determinare quali colonne siano cambiate:

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

DELETE stored procedure
Le stored procedure che gestiscono le istruzioni DELETE ricevono valori per le colonne della chiave primaria:

pkc1, pkc2, pkc3,... pkcn  

Sintassi SCALL

UPDATE stored procedure
Le stored procedure che gestiscono UPDATE le istruzioni ricevono i valori aggiornati solo per quelle colonne che sono cambiate, seguiti dai valori originali per le colonne chiave primarie, e poi da un parametro bitmask (binary(n)) che indica le colonne modificate. Nel seguente esempio, la colonna 2 (c2) non è cambiata:

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

Sintassi MCALL

UPDATE stored procedure
Le stored procedure che gestiscono UPDATE le istruzioni ricevono i valori aggiornati per tutte le colonne definite nell'articolo, seguiti dai valori originali per le colonne chiave primarie, e poi da un parametro bitmask (binary(n)) che indica le colonne modificate:

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

Sintassi XCALL

UPDATE stored procedure
Le stored procedure che gestiscono UPDATE le istruzioni ricevono i valori originali (l'immagine prima) per tutte le colonne definite nell'articolo, seguiti dai valori aggiornati (l'immagine dopo) per tutte le colonne definite nell'articolo:

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

DELETE stored procedure
Le stored procedure che gestiscono DELETE istruzioni ricevono i valori originali (l'immagine prima) per tutte le colonne definite nell'articolo:

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

Esempi

Le procedure che seguono sono le procedure predefinite create per Vendor Table nel database di esempio di 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  

Contenuto correlato

• Opzioni Articolo per la Replicazione Transazionale