Pubblicazione dell'esecuzione delle stored procedure nella replica transazionalePublishing Stored Procedure Execution in Transactional Replication

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.If you have one or more stored procedures that execute at the Publisher and affect published tables, consider including those stored procedures in your publication as stored procedure execution articles. 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.The definition of the procedure (the CREATE PROCEDURE statement) is replicated to the Subscriber when the subscription is initialized; when the procedure is executed at the Publisher, replication executes the corresponding procedure at the Subscriber. 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.This can provide significantly better performance for cases where large batch operations are performed, because only the procedure execution is replicated, bypassing the need to replicate the individual changes for each row. Si supponga, ad esempio, di creare la stored procedure seguente nel database di pubblicazione:For example, assume you create the following stored procedure in the publication database:

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%.This procedure gives each of the 10,000 employees in your company a 10 percent pay increase. Quando si esegue la stored procedure nel server di pubblicazione, viene aggiornato lo stipendio di ogni dipendente.When you execute this stored procedure at the Publisher, it updates the salary for each employee. Senza la replica dell'esecuzione della stored procedure, l'aggiornamento verrebbe inviato ai Sottoscrittori sotto forma di transazione multipla di grandi dimensioni:Without the replication of stored procedure execution, the update would be sent to Subscribers as a large, multi-step transaction:

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.And this repeats for 10,000 updates.

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.With the replication of stored procedure execution, replication sends only the command to execute the stored procedure at the Subscriber, rather than writing all the updates to the distribution database and then sending them over the network to the Subscriber:

EXEC give_raise  

Importante

La replica delle stored procedure non è adatta a tutte le applicazioni.Stored procedure replication is not appropriate for all applications. 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.If an article is filtered horizontally, so that there are different sets of rows at the Publisher than at the Subscriber, executing the same stored procedure at both returns different results. 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.Similarly, if an update is based on a subquery of another, nonreplicated table, executing the same stored procedure at both the Publisher and Subscriber returns different results.

Per pubblicare l'esecuzione di una stored procedureTo publish the execution of a stored procedure

Modifica della procedura nel SottoscrittoreModifying the Procedure at the Subscriber

Per impostazione predefinita, la definizione della stored procedure nel server di pubblicazione viene distribuita in ogni Sottoscrittore.By default, the stored procedure definition at the Publisher is propagated to each Subscriber. È comunque possibile modificare la stored procedure anche nel Sottoscrittore.However, you can also modify the stored procedure at the Subscriber. Ciò risulta utile se nel Sottoscrittore si desidera eseguire logica diversa da quella eseguita nel server di pubblicazione.This is useful if you want different logic to be executed at the Publisher and 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.For example, consider sp_big_delete, a stored procedure at the Publisher that has two functions: it deletes 1,000,000 rows from the replicated table big_table1 and updates the nonreplicated table 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.To reduce the demand on network resources, you should propagate the 1 million row delete as a stored procedure by publishing 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.At the Subscriber, you can modify sp_big_delete to delete only the 1 million rows and not perform the subsequent update to big_table2.

Nota

Per impostazione predefinita tutte le modifiche apportate nel server di pubblicazione utilizzando ALTER PROCEDURE vengono distribuite nel Sottoscrittore.By default, any changes made using ALTER PROCEDURE at the Publisher are propagated to the Subscriber. Per impedire questa operazione, disabilitare la distribuzione delle modifiche dello schema prima di eseguire ALTER PROCEDURE.To prevent this, disable the propagation of schema changes before executing ALTER PROCEDURE. Per informazioni sulle modifiche dello schema, vedere Apportare modifiche allo schema nei database di pubblicazione.For information about schema changes, see Make Schema Changes on Publication Databases.

Tipi di articoli di esecuzione delle stored procedureTypes of Stored Procedure Execution Articles

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.There are two different ways in which the execution of a stored procedure can be published: serializable procedure execution article and procedure execution article.

  • È 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.The serializable option is recommended because it replicates the procedure execution only if the procedure is executed within the context of a serializable transaction. Se viene eseguita in un contesto diverso, le modifiche ai dati delle tabelle pubblicate vengono replicate come una serie di istruzioni DML.If the stored procedure is executed from outside a serializable transaction, changes to data in published tables are replicated as a series of DML statements. In questo modo i dati nel Sottoscrittore risultano sempre consistenti con i dati nel server di pubblicazione.This behavior contributes to making data at the Subscriber consistent with data at the Publisher. Questo risulta particolarmente utile per le operazioni batch, ad esempio operazioni di pulizia dei riferimenti molto estese.This is especially useful for batch operations, such as large cleanup operations.

  • 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.With the procedure execution option, it is possible that execution could be replicated to all Subscribers regardless of whether individual statements in the stored procedure were successful. 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.Furthermore, because changes made to data by the stored procedure can occur within multiple transactions, data at the Subscribers might not be consistent with data at the Publisher. Per risolvere questi problemi, è necessario che i Sottoscrittori siano di sola lettura e che si utilizzi un livello di isolamento maggiore di Read Uncommitted.To address these issues, it is required that Subscribers are read-only and that you use an isolation level greater than read uncommitted. Se si utilizza Read Uncommitted, le modifiche ai dati nelle tabelle pubblicate vengono replicate come una serie di istruzioni DML.If you use read uncommitted, changes to data in published tables are replicated as a series of DML statements.

    Nell'esempio seguente viene illustrato il motivo per cui è consigliabile impostare la replica delle procedure sotto forma di articoli di procedure serializzabili.The following example illustrates why it is recommended that you set up replication of procedures as serializable procedure articles.

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.In the previous example, it is assumed that the SELECT in transaction T1 happens before the INSERT in transaction 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.If the procedure is not executed within a serializable transaction (with isolation level set to SERIALIZABLE), transaction T2 will be allowed to insert a new row within the range of the SELECT statement in T1 and it will commit before T1. Questo significa infine che T2 verrà applicata nel Sottoscrittore prima di T1.This also means that it will be applied at the Subscriber before 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.When T1 is applied at the Subscriber, the SELECT can potentially return a different value than at the Publisher and can result in a different outcome from the 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,If the procedure is executed within a serializable transaction, transaction T2 will not be allowed to insert within the range covered by the SELECT statement in T2. ma rimarrà bloccata fino al commit di T1 in modo da garantire gli stessi risultati nel Sottoscrittore.It will be blocked until T1 commits ensuring the same results at the Subscriber.

I blocchi verranno mantenuti più a lungo se si esegue la procedura in una transazione serializzabile e possono comportare una riduzione della concorrenza.Locks will be held longer when you execute the procedure within a serializable transaction and may result in reduced concurrency.

Impostazione XACT_ABORTThe XACT_ABORT Setting

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.When replicating stored procedure execution, the setting for the session executing the stored procedure should specify XACT_ABORT 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.If XACT_ABORT is set to OFF, and an error occurs during execution of the procedure at the Publisher, the same error will occur at the Subscriber, causing the Distribution Agent to fail. 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.Specifying XACT_ABORT ON ensures that any errors encountered during execution at the Publisher cause the entire execution to be rolled back, avoiding the Distribution Agent failure. Per altre informazioni sull'impostazione XACT_ABORT, vedere SET XACT_ABORT (Transact-SQL).For more information about setting XACT_ABORT, see SET XACT_ABORT (Transact-SQL).

Se è necessario impostare XACT_ABORT su OFF, specificare il parametro -SkipErrors per l'agente di distribuzione.If you require a setting of XACT_ABORT OFF, specify the -SkipErrors parameter for the Distribution Agent. Ciò consente all'agente di continuare ad applicare eventuali modifiche nel Sottoscrittore anche in caso di errore.This allows the agent to continue applying changes at the Subscriber even if an error is encountered.

Vedere ancheSee Also

Article Options for Transactional ReplicationArticle Options for Transactional Replication