트랜잭션 복제에서 저장 프로시저 실행 게시Publishing Stored Procedure Execution in Transactional Replication

게시자에서 실행되고 게시된 테이블에 영향을 주는 하나 이상의 저장 프로시저가 있는 경우 이러한 저장 프로시저를 저장 프로시저 실행 아티클로 게시에 포함해 보십시오.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. 프로시저 정의(CREATE PROCEDURE 문)는 구독이 초기화될 때 구독자로 복제되고 게시자에서 프로시저가 실행되면 복제가 구독자에서 해당하는 프로시저를 실행합니다.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. 이렇게 하면 프로시저 실행만 복제하고 행별 변경 내용은 복제하지 않으므로 대용량 일괄 처리 작업이 수행되는 경우 성능을 크게 향상시킬 수 있습니다.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. 예를 들어 게시 데이터베이스에서 다음 저장 프로시저를 생성한다고 가정합니다.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  

이 프로시저는 회사 내의 직원 10,000명의 급여를 10% 인상합니다.This procedure gives each of the 10,000 employees in your company a 10 percent pay increase. 게시자에서 이 저장 프로시저를 실행하면 각 직원의 급여가 업데이트됩니다.When you execute this stored procedure at the Publisher, it updates the salary for each employee. 저장 프로시저 실행을 복제하지 않으면 업데이트가 여러 단계의 대규모 트랜잭션으로 구독자로 전송됩니다.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'  

10,000개의 업데이트에 대해 이 프로세스가 반복됩니다.And this repeats for 10,000 updates.

저장 프로시저 실행을 복제하면 복제에서는 배포 데이터베이스에 모든 업데이트를 기록한 후 네트워크를 통해 구독자로 보내지 않고 구독자에서 저장 프로시저를 실행하는 명령만 보냅니다.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  
중요

저장 프로시저 복제가 모든 응용 프로그램에 적절한 것은 아닙니다.Stored procedure replication is not appropriate for all applications. 아티클이 수평 분할되어 구독자와는 다른 행 집합이 게시자에 있게 되면 게시자와 구독자 모두에서 같은 저장 프로시저를 실행해도 다른 결과가 나타납니다.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. 마찬가지로 업데이트가 복제되지 않은 다른 테이블의 하위 쿼리를 기반으로 하면 게시자와 구독자 모두에서 같은 저장 프로시저를 실행해도 다른 결과가 반환됩니다.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.

저장 프로시저의 실행을 게시하려면To publish the execution of a stored procedure

구독자에서 프로시저 수정Modifying the Procedure at the Subscriber

기본적으로 게시자의 저장 프로시저 정의는 각 구독자로 전파됩니다.By default, the stored procedure definition at the Publisher is propagated to each Subscriber. 그러나 구독자에서 저장 프로시저를 수정할 수도 있습니다.However, you can also modify the stored procedure at the Subscriber. 이는 게시자와 구독자에서 다른 논리를 실행하려고 할 때 유용합니다.This is useful if you want different logic to be executed at the Publisher and Subscriber. 예를 들어 구독자의 저장 프로시저 sp_big_delete에는 두 가지 기능이 있습니다. 복제된 테이블 big_table1 에서 1,000,000개의 행을 삭제하고 복제되지 않은 테이블 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. 네트워크 리소스에 대한 수요를 줄이려면 sp_big_delete를 게시하여 100만 개의 행 삭제를 저장 프로시저로 전파해야 합니다.To reduce the demand on network resources, you should propagate the 1 million row delete as a stored procedure by publishing sp_big_delete. 구독자에서 100만 개의 행만 삭제하고 big_table2 에 대한 후속 업데이트를 수행하지 않도록 sp_big_delete를 정의할 수 있습니다.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.

참고

기본적으로 게시자에서 ALTER PROCEDURE를 사용하여 적용한 변경 내용은 구독자로 전파됩니다.By default, any changes made using ALTER PROCEDURE at the Publisher are propagated to the Subscriber. 이를 방지하려면 ALTER PROCEDURE를 실행하기 전에 스키마 변경 내용을 전파하는 설정을 해제합니다.To prevent this, disable the propagation of schema changes before executing ALTER PROCEDURE. 스키마 변경에 대한 자세한 내용은 게시 데이터베이스의 스키마 변경을 참조하세요.For information about schema changes, see Make Schema Changes on Publication Databases.

저장 프로시저 실행 아티클의 유형Types of Stored Procedure Execution Articles

직렬화 가능 프로시저 실행 아티클과 프로시저 실행 아티클을 사용하여 저장 프로시저의 실행을 게시할 수 있습니다.There are two different ways in which the execution of a stored procedure can be published: serializable procedure execution article and procedure execution article.

  • 프로시저가 직렬화 가능 트랜잭션의 컨텍스트 내에서 실행되는 경우에만 프로시저 실행을 복제하는 직렬화 가능 옵션을 사용하는 것이 좋습니다.The serializable option is recommended because it replicates the procedure execution only if the procedure is executed within the context of a serializable transaction. 저장 프로시저가 직렬화 가능 트랜잭션 외부에서 실행되면 게시된 테이블의 데이터 변경 내용이 일련의 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. 이 동작을 통해 구독자의 데이터와 게시자의 데이터의 일관성을 기할 수 있습니다.This behavior contributes to making data at the Subscriber consistent with data at the Publisher. 이는 대규모 정리 작업과 같은 일괄 처리 작업에 특히 유용합니다.This is especially useful for batch operations, such as large cleanup operations.

  • 프로시저 실행 옵션을 사용하면 저장 프로시저에 있는 개별 문의 성공 여부에 상관없이 실행을 모든 구독자에 복제할 수 있습니다.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. 또한 여러 트랜잭션 내에서 저장 프로시저에 의해 데이터가 변경될 수 있으므로 구독자 데이터와 게시자 데이터가 일치하지 않을 수 있습니다.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. 이러한 문제를 해결하려면 구독자가 읽기 전용이어야 하고 커밋되지 않은 읽기보다 높은 격리 수준을 사용해야 합니다.To address these issues, it is required that Subscribers are read-only and that you use an isolation level greater than read uncommitted. 커밋되지 않은 읽기를 사용하면 게시된 테이블의 데이터에 대한 변경 내용이 일련의 DML 문으로 복제됩니다.If you use read uncommitted, changes to data in published tables are replicated as a series of DML statements.

    다음 예에서는 프로시저 복제를 직렬화 가능 프로시저 아티클로 설정하는 것을 권장하는 이유를 설명합니다.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  

위의 예제에서 트랜잭션 T1의 SELECT는 트랜잭션 T2의 INSERT 이전에 발생한다고 가정합니다.In the previous example, it is assumed that the SELECT in transaction T1 happens before the INSERT in transaction T2.

프로시저가 직렬화 가능 트랜잭션(격리 수준이 SERIALIZABLE로 설정됨) 내에서 프로시저가 실행되지 않으면 트랜잭션 T2는 T1의 SELECT 문 범위 내에서 새 행을 삽입할 수 있고 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. 이는 T2가 T1 이전에 구독자에서 적용되는 것을 의미합니다.This also means that it will be applied at the Subscriber before T1. T1이 구독자에서 적용되면, SELECT는 게시자에서와 다른 값을 반환할 수 있고 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.

프로시저가 직렬화 가능 트랜잭션 내에서 실행되면, 트랜잭션 T2는 T2의 SELECT 문 범위 내에서 삽입을 할 수 없습니다.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. 구독자에서 같은 결과를 가져오도록 T1이 커밋될 때까지 T2는 차단됩니다.It will be blocked until T1 commits ensuring the same results at the Subscriber.

직렬화 가능 트랜잭션 내에서 프로시저를 실행하면 잠금이 더 오래 유지되어 동시성이 줄어들 수 있습니다.Locks will be held longer when you execute the procedure within a serializable transaction and may result in reduced concurrency.

XACT_ABORT 설정The XACT_ABORT Setting

저장 프로시저 실행을 복제할 때 저장 프로시저를 실행하는 세션에 대해 XACT_ABORT 설정을 ON으로 지정해야 합니다.When replicating stored procedure execution, the setting for the session executing the stored procedure should specify XACT_ABORT ON. XACT_ABORT가 OFF로 설정되면 게시자에서 프로시저 실행 중 오류가 발생하면 구독자에서도 같은 오류가 발생하여 배포 에이전트가 실패합니다.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. XACT_ABORT를 ON으로 지정하면 게시자에서 실행 중 발생한 모든 오류로 인해 전체 실행이 롤백되므로 배포 에이전트 실패를 막을 수 있습니다.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. XACT_ABORT 설정에 대한 자세한 내용은 SET XACT_ABORT(Transact-SQL)를 참조하세요.For more information about setting XACT_ABORT, see SET XACT_ABORT (Transact-SQL).

XACT_ABORT를 OFF로 설정해야 하는 경우에는 배포 에이전트에 대해 -SkipErrors 매개 변수를 지정합니다.If you require a setting of XACT_ABORT OFF, specify the -SkipErrors parameter for the Distribution Agent. 그러면 오류가 발생해도 에이전트가 구독자에서 변경 내용을 계속 적용할 수 있습니다.This allows the agent to continue applying changes at the Subscriber even if an error is encountered.

관련 항목:See Also

Article Options for Transactional ReplicationArticle Options for Transactional Replication