Pubblicazione dell'esecuzione delle stored procedure nella replica transazionale

  • Articolo

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 procedura, ovvero l'istruzione CREATE PROCEDURE, viene replicata nel Sottoscrittore durante l'inizializzazione della sottoscrizione. Quando la procedura viene eseguita nel server di pubblicazione, 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 stored procedure seguente nel database di pubblicazione:

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

La stored procedure assegna a ognuno dei 10.000 dipendenti della società un aumento di stipendio del 10%. Quando si esegue la stored procedure nel server di pubblicazione, viene aggiornato lo stipendio di ogni dipendente. Senza la replica dell'esecuzione della stored procedure, l'aggiornamento verrebbe inviato ai Sottoscrittori sotto forma di transazione multipla di grandi dimensioni:

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

La stessa riga di codice viene eseguita per ognuno dei 10.000 dipendenti.

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 in seguito all'applicazione di un filtro orizzontale a un articolo il server di pubblicazione include set di righe diversi rispetto al Sottoscrittore, l'esecuzione della stessa stored procedure nei due server 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 nel Sottoscrittore

Per impostazione predefinita, la definizione della stored procedure nel server di pubblicazione viene distribuita in ogni Sottoscrittore. È comunque possibile modificare la stored procedure anche nel Sottoscrittore. Ciò risulta utile se nel Sottoscrittore si desidera eseguire logica diversa da quella eseguita nel server di pubblicazione. 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. In questo caso, per ridurre la quantità di risorse di rete utilizzate, è necessario distribuire l'eliminazione del milione di righe come stored procedure pubblicando sp_big_delete. Nel Sottoscrittore è possibile modificare sp_big_delete in modo che esegua l'eliminazione delle righe, ma non l'aggiornamento successivo di big_table2.

Nota

Per impostazione predefinita tutte le modifiche apportate nel server di pubblicazione utilizzando ALTER PROCEDURE vengono distribuite nel Sottoscrittore. Per impedire questa operazione, disabilitare la distribuzione 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 di 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 viene eseguita in un contesto diverso, le modifiche ai dati delle tabelle pubblicate vengono replicate come una serie di istruzioni DML. In questo modo i dati nel Sottoscrittore risultano sempre consistenti con i dati nel server di pubblicazione. Questo risulta particolarmente utile per le operazioni batch, ad esempio operazioni di pulizia dei riferimenti molto estese.

  • Con l'opzione di esecuzione della procedura, è possibile che l'esecuzione sia stata replicata in tutti i Sottoscrittori, indipendentemente dal fatto che le singole istruzioni nella stored procedure abbiano avuto o meno esito positivo. Inoltre, poiché le modifiche ai dati apportate dalla stored procedure possono essere presenti in più transazioni, i dati nei Sottoscrittori potrebbero essere incoerenti con i dati nel server di pubblicazione. Per risolvere questi problemi, è necessario che i Sottoscrittori siano di sola lettura e che si utilizzi un livello di isolamento maggiore di 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 della transazione T1 venga eseguita prima dell'istruzione INSERT della 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. Questo significa infine che T2 verrà applicata nel Sottoscrittore prima di T1. Quando T1 viene applicata nel Sottoscrittore, è possibile che l'istruzione SELECT restituisca un valore diverso di quello presente nel server di pubblicazione e che si ottenga un risultato diverso dall'istruzione 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 verranno mantenuti più a lungo se si esegue la procedura in una transazione serializzabile e possono comportare una riduzione della concorrenza.

Impostazione XACT_ABORT

Durante la replica dell'esecuzione della stored procedure, l'impostazione XACT_ABORT della sessione nella quale avviene l'esecuzione della stored procedure deve essere impostata su ON. Se XACT_ABORT è impostata su OFF, durante l'esecuzione della procedura nel server di pubblicazione viene generato un errore, che si ripeterà anche nel Sottoscrittore, interrompendo l'attività dell'agente di distribuzione. Se si imposta XACT_ABORT su ON, per tutti gli errori rilevati durante l'esecuzione nel server di pubblicazione viene eseguito il rollback dell'intera esecuzione, il che impedisce che si verifichino errori nell'agente di distribuzione. Per ulteriori informazioni sull'impostazione di XACT_ABORT, vedi SET XACT_ABORT (Transact-SQL).

Se è necessario impostare XACT_ABORT su OFF, specificare il parametro -SkipErrors per l'agente di distribuzione. Ciò consente all'agente di continuare ad applicare eventuali modifiche nel Sottoscrittore anche in caso di errore.

Vedi anche

Article Options for Transactional Replication