Publicando execução de procedimento armazenado em replicação transacionalPublishing Stored Procedure Execution in Transactional Replication

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure (somente a Instância Gerenciada) nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Caso haja um ou mais procedimentos armazenados executados no Publicador e que afetem as tabelas publicadas, considere excluir esses procedimentos armazenados na publicação como artigos de execução de procedimentos armazenados.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. A definição do procedimento (instrução CREATE PROCEDURE) será replicada para o Assinante quando a inscrição for inicializada. Quando o procedimento armazenado for executado no Publicador, a replicação executará o procedimento correspondente no Assinante.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. Isso pode fornecer um desempenho significativamente melhor nos casos em que são executadas grandes operações em lote, pois apenas a execução do procedimento é replicada, ignorando-se a necessidade de replicar as alterações individuais de cada linha.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. Por exemplo, supondo que o procedimento armazenado a seguir seja criado no banco de dados de publicação: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  

Esse procedimento dá a cada um dos 10.000 funcionários da empresa um aumento salarial de 10 por cento.This procedure gives each of the 10,000 employees in your company a 10 percent pay increase. Quando executado no Publicador, esse procedimento armazenado atualiza o salário de todos os funcionários.When you execute this stored procedure at the Publisher, it updates the salary for each employee. Sem a replicação de execução de procedimento armazenado, a atualização seria enviada aos Assinantes como uma transação extensa e de várias etapas: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'  

E isto se repetiria em 10.000 atualizações.And this repeats for 10,000 updates.

Com a execução de procedimento armazenado, a replicação envia apenas o comando para execução do procedimento armazenado do Assinante, em vez de gravar todas as atualizações no banco de dados de distribuição, enviando-as em seguida ao Assinante, pela rede: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

A replicação de procedimento armazenado não é apropriada para todos os aplicativos.Stored procedure replication is not appropriate for all applications. Se um artigo for filtrado horizontalmente, de modo que os conjuntos de linhas do Publicador sejam diferentes do Assinante, a execução do mesmo procedimento armazenado em ambos retornará diferentes resultados.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. De forma similar, quando uma atualização é baseada em uma subconsulta de outra tabela não replicada, a execução do mesmo procedimento armazenado no Publicador e no Assinante retornará diferentes resultados.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.

Para publicar a execução de um procedimento armazenadoTo publish the execution of a stored procedure

Modificando o procedimento no AssinanteModifying the Procedure at the Subscriber

Por padrão, a definição de procedimento armazenado no Publicador é propagada para todos os Assinantes.By default, the stored procedure definition at the Publisher is propagated to each Subscriber. Porém, é igualmente possível modificar o procedimento armazenado no Assinante.However, you can also modify the stored procedure at the Subscriber. Isso será útil para executar lógicas diferentes no Publicador e no Assinante.This is useful if you want different logic to be executed at the Publisher and Subscriber. Por exemplo, considere sp_big_delete, um procedimento armazenado do Publicador que tem duas funções: exclui 1.000.000 linhas da tabela replicada big_table1 e atualiza a tabela não replicada 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. Para reduzir a demanda por recursos de rede, propague a exclusão de 1 milhão de linhas como procedimento armazenado publicando 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. No Assinante, modifique sp_big_delete para excluir apenas o 1 milhão de linhas e não realizar a atualização subsequente em 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.

Observação

Por padrão, todas as alterações feitas com ALTER PROCEDURE, no Publicador, são propagadas para o Assinante.By default, any changes made using ALTER PROCEDURE at the Publisher are propagated to the Subscriber. Para impedir isso, desative a propagação de alterações de esquema antes de executar ALTER PROCEDURE.To prevent this, disable the propagation of schema changes before executing ALTER PROCEDURE. Para obter informações sobre alterações de esquema, consulte Fazer alterações de esquema em bancos de dados de publicação.For information about schema changes, see Make Schema Changes on Publication Databases.

Tipos de artigos de execução de procedimento armazenadoTypes of Stored Procedure Execution Articles

Há duas formas diferentes pelas quais a execução de um procedimento armazenado pode ser publicada: artigo de execução de procedimento serializável e artigo de execução de procedimento.There are two different ways in which the execution of a stored procedure can be published: serializable procedure execution article and procedure execution article.

  • A opção serializável é recomendada uma vez que ela replica a execução do procedimento apenas se o procedimento for executado no contexto de uma transação serializável.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 o procedimento armazenado for executado fora de uma transação serializável, as alterações dos dados nas tabelas publicadas serão replicadas como uma série de instruções 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. Esse comportamento contribui para tornar os dados do Assinante consistentes com os dados do Publicador.This behavior contributes to making data at the Subscriber consistent with data at the Publisher. Isso é especialmente útil para operações em lote, como grandes operações de limpeza.This is especially useful for batch operations, such as large cleanup operations.

  • Com a opção de execução de procedimento, é possível que a execução seja replicada para todos os Assinantes, quer as instruções individuais no procedimento armazenado tenham sido bem-sucedidas ou não.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. Acima de tudo, como é possível que as alterações feitas nos dados pelo procedimento armazenado ocorram em várias transações, talvez os dados dos Assinantes não estejam consistentes com os dados do Publicador.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. Para resolver esses problemas, é necessário que os Assinantes sejam somente leitura e que você use um nível de isolamento superior ao de leitura não confirmado.To address these issues, it is required that Subscribers are read-only and that you use an isolation level greater than read uncommitted. Se você usar a leitura não confirmada, as alterações aos dados em tabelas publicadas serão replicadas como uma série de instruções DML.If you use read uncommitted, changes to data in published tables are replicated as a series of DML statements.

O exemplo a seguir ilustra por que é recomendado definir a replicação de procedimentos como artigos de procedimento serializáveis.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  

No exemplo anterior, supôs-se que SELECT na transação T1 ocorre antes de INSERT na transação T2.In the previous example, it is assumed that the SELECT in transaction T1 happens before the INSERT in transaction T2.

Se o procedimento não for executado na transação serializável (com o nível de isolamento definido como SERIALIZABLE), a transação T2 não terá permissão para inserir uma nova linha no intervalo da instrução SELECT em T1 e será confirmada antes de 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. Isto também significa que ela será aplicada ao Assinante antes de T1.This also means that it will be applied at the Subscriber before T1. Quando T1 é aplicada ao Assinante, SELECT pode retornar potencialmente um valor diferente do valor do Publicador e poderá ser convertido em um resultado diferente de 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 o procedimento for executado em uma transação serializável, a transação T2 não terá permissão para ser inserida no intervalo coberto pela instrução SELECT em 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. Ele será bloqueado até que T1 seja confirmada, assegurando os mesmos resultados no Assinante.It will be blocked until T1 commits ensuring the same results at the Subscriber.

Os bloqueios serão mantidos por mais tempo quando esse procedimento for executado em uma transação serializável e poderá resultar em redução de simultaneidade.Locks will be held longer when you execute the procedure within a serializable transaction and may result in reduced concurrency.

A configuração XACT_ABORTThe XACT_ABORT Setting

Ao replicar a execução de procedimento armazenado, a configuração da sessão que executa o procedimento armazenado deve especificar XACT_ABORT ON.When replicating stored procedure execution, the setting for the session executing the stored procedure should specify XACT_ABORT ON. Se XACT_ABORT estiver definida como OFF e ocorrer um erro na execução do procedimento, no Publicador, o mesmo erro ocorrerá no Assinante, causando falha do Agente de Distribuição.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. Especificar XACT_ABORT ON assegura que nenhum erro encontrado durante a execução, no Publicador, cause a reversão total da execução, evitando falha do Agente de Distribuição.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. Para obter mais informações sobre como configurar XACT_ABORT, consulte SET XACT_ABORT (Transact-SQL).For more information about setting XACT_ABORT, see SET XACT_ABORT (Transact-SQL).

Se a configuração de XACT_ABORT OFF for necessária, especifique o parâmetro -SkipErrors do Agente de Distribuição.If you require a setting of XACT_ABORT OFF, specify the -SkipErrors parameter for the Distribution Agent. Isto permitirá que o agente continue a aplicar alterações no Assinante, ainda que um erro seja encontrado.This allows the agent to continue applying changes at the Subscriber even if an error is encountered.

Consulte TambémSee Also

Article Options for Transactional ReplicationArticle Options for Transactional Replication