Pubblicare l'esecuzione delle procedure archiviate nella replica transazionale

Si applica a:SQL ServerIstanza gestita di SQL di Azure

Se una o più stored procedure vengono eseguite nel server di pubblicazione e influiscono su tabelle pubblicate, è possibile includerle nella pubblicazione sotto forma di articoli di esecuzione delle stored procedure. La definizione della routine (istruzioneCREATE PROCEDURE) viene replicata nel Sottoscrittore quando viene inizializzata la sottoscrizione. Quando la routine viene eseguita nella Publisher, la replica esegue la procedura corrispondente nel Sottoscrittore. Ciò può migliorare sensibilmente le prestazioni, ad esempio nel caso di operazioni batch di grandi dimensioni, poiché viene replicata solo l'esecuzione della procedura senza necessità di replicare le singole modifiche di ogni riga. Si supponga, ad esempio, di creare la seguente procedura memorizzata nel database di pubblicazione:

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

Questa procedura concede a ciascuno dei 10.000 dipendenti della tua azienda un aumento di stipendio del 10%. Quando si esegue questa procedura memorizzata nel server Publisher, viene aggiornato lo stipendio di ciascun dipendente. Senza la replicazione dell'esecuzione della stored procedure, l'aggiornamento verrebbe inviato ai Sottoscrittori come un'unica transazione di grandi dimensioni e articolata in più passaggi:

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

E questo si ripete per 10.000 aggiornamenti.

Tramite la replica dell'esecuzione della stored procedure, viene inviato solo il comando di esecuzione della stored procedure nel Sottoscrittore, anziché scrivere tutti gli aggiornamenti nel database di distribuzione e inviarli in rete al Sottoscrittore.

EXEC give_raise

Importante

La replica delle stored procedure non è adatta a tutte le applicazioni. Se un articolo viene filtrato orizzontalmente, in modo che nel server di pubblicazione siano presenti insiemi di righe diversi rispetto al Sottoscrittore, l'esecuzione della stessa stored procedure in entrambi restituisce risultati diversi. In modo analogo, se un aggiornamento è basato su una sottoquery di un'altra tabella non replicata, l'esecuzione della stessa stored procedure nel server di pubblicazione e nel Sottoscrittore restituisce risultati diversi.

Per pubblicare l'esecuzione di una stored procedure

Modifica della procedura presso l'abbonato

Per impostazione predefinita, la definizione della procedura archiviata nell'Editore viene propagata a ogni Sottoscrittore. Tuttavia, è anche possibile modificare la procedura memorizzata nel Sottoscrittore. Ciò è utile se si desidera che venga eseguita una logica diversa nel Publisher e nel Subscriber. Si supponga, ad esempio, che la stored procedure sp_big_deletenel server di pubblicazione svolga due funzioni, ovvero elimini 1.000.000 di righe dalla tabella replicata big_table1 e aggiorni la tabella non replicata big_table2. Per ridurre il carico sulle risorse di rete, è consigliabile propagare l'eliminazione di 1 milione di righe sotto forma di stored procedure pubblicando sp_big_delete. Nel Sottoscrittore, è possibile modificare sp_big_delete in modo da eliminare solo 1 milione di righe e non eseguire il successivo aggiornamento di big_table2.

Nota

Per impostazione predefinita, tutte le modifiche apportate utilizzando ALTER PROCEDURE al Publisher vengono propagate al Sottoscrittore. Per evitare questo problema, disabilitare la propagazione delle modifiche dello schema prima di eseguire ALTER PROCEDURE. Per informazioni sulle modifiche dello schema, vedere Apportare modifiche allo schema nei database di pubblicazione.

Tipi di articoli sull'esecuzione delle stored procedure

L'esecuzione di una stored procedure può essere pubblicata in due modi diversi, ovvero sotto forma di articolo di esecuzione delle procedure serializzabili e sotto forma di articolo di esecuzione delle procedure.

  • È consigliabile utilizzare l'opzione serializzabile poiché esegue la replica dell'esecuzione della procedura solo se la procedura viene eseguita nel contesto di una transazione serializzabile. Se la stored procedure viene eseguita al di fuori di una transazione serializzabile, le modifiche ai dati nelle tabelle pubblicate vengono replicate come una serie di istruzioni DML. Questo comportamento contribuisce a far sì che i dati nel Sottoscrittore siano coerenti con i dati nel Pubblicatore. Questo è particolarmente utile per le operazioni in batch, ad esempio operazioni di pulizia di grandi dimensioni.

  • Con l'opzione di esecuzione della procedura, è possibile che l'esecuzione possa essere replicata a tutti i Sottoscrittori indipendentemente dal fatto che le singole istruzioni nella procedura archiviata abbiano avuto esito positivo o meno. Inoltre, poiché le modifiche ai dati apportate dalla procedura archiviata possono verificarsi nell'ambito di più transazioni, i dati nei Sottoscrittori potrebbero non essere coerenti con i dati nel Publisher. Per risolvere questi problemi, è necessario che i Sottoscrittori siano di sola lettura e che si utilizzi un livello di isolamento superiore a Read Uncommitted. Se si utilizza Read Uncommitted, le modifiche ai dati nelle tabelle pubblicate vengono replicate come una serie di istruzioni DML.

Nell'esempio seguente viene illustrato il motivo per cui è consigliabile impostare la replica delle procedure sotto forma di articoli di procedure serializzabili.

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

Nell'esempio precedente si presuppone che l'istruzione SELECT nella transazione T1 venga eseguita prima della INSERT transazione T2.

Se la procedura non viene eseguita nell'ambito di una transazione serializzabile, ad esempio con il livello di isolamento impostato su SERIALIZABLE, la transazione T2 potrà inserire una nuova riga nell'intervallo dell'istruzione SELECT della transazione T1. Inoltre il commit della transazione T2 verrà eseguito prima di quello della transazione T1. Ciò significa anche che verrà applicato al sottoscrittore prima di T1. Quando T1 viene applicato al Sottoscrittore, select può potenzialmente restituire un valore diverso rispetto al Publisher e può generare un risultato diverso da UPDATE.

Se la procedura viene eseguita nell'ambito di una transazione serializzabile, la transazione T2 non potrà eseguire inserimenti nell'intervallo dell'istruzione SELECT di T2, ma rimarrà bloccata fino al commit di T1 in modo da garantire gli stessi risultati nel Sottoscrittore.

I blocchi saranno mantenuti più a lungo se si esegue la procedura all'interno di una transazione serializzabile e possono comportare una riduzione della concorrenza.

Impostazione XACT_ABORT

Quando si esegue la replica dell'esecuzione della stored procedure, l'impostazione per la sessione che esegue la stored procedure deve specificare XACT_ABORT ON. Se XACT_ABORT è impostato su OFF e si verifica un errore durante l'esecuzione della procedura nel Publisher, lo stesso errore si verificherà nel Subscriber, causando un errore dell'agente di distribuzione. Specificare XACT_ABORT ON garantisce che eventuali errori riscontrati durante l'esecuzione nel server di pubblicazione comportino il rollback dell'intera esecuzione, evitando l'errore dell'agente di distribuzione. Per altre informazioni sull'impostazione di XACT_ABORT, vedere SET XACT_ABORT (Transact-SQL).

Se è necessaria un'impostazione XACT_ABORT off, specificare il parametro -SkipErrors per il agente di distribuzione. Ciò consente all'agente di continuare ad applicare eventuali modifiche nel Sottoscrittore anche in caso di errore.

Contenuto correlato

  • Last updated on