트랜잭션 복제에서 저장 프로시저 실행 게시

  • 아티클

적용 대상:SQL ServerAzure SQL Managed Instance

게시자에서 실행되고 게시된 테이블에 영향을 주는 하나 이상의 저장 프로시저가 있는 경우 이러한 저장 프로시저를 저장 프로시저 실행 아티클로 게시에 포함해 보십시오. 프로시저 정의(CREATE PROCEDURE 문)는 구독이 초기화될 때 구독자에 복제됩니다. 게시자에서 프로시저가 실행되면 복제는 구독자에서 해당 프로시저를 실행합니다. 이렇게 하면 프로시저 실행만 복제되므로 각 행에 대한 개별 변경 내용을 복제할 필요가 없으므로 대규모 일괄 처리 작업이 수행되는 경우에 훨씬 더 나은 성능을 제공할 수 있습니다. 예를 들어 게시 데이터베이스에서 다음 저장 프로시저를 만든다고 가정합니다.

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

이 절차는 회사의 직원 10,000명 각각에게 10%의 임금 인상을 제공합니다. 게시자에서 이 저장 프로시저를 실행하면 각 직원의 급여가 업데이트됩니다. 저장 프로시저 실행을 복제하지 않으면 업데이트가 대규모 다단계 트랜잭션으로 구독자에게 전송됩니다.

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개 업데이트에 대해 반복됩니다.

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

EXEC give_raise

Important

저장 프로시저 복제는 모든 애플리케이션에 적합하지 않습니다. 아티클이 수평 분할되어 구독자와는 다른 행 집합이 게시자에 있게 되면 게시자와 구독자 모두에서 같은 저장 프로시저를 실행해도 다른 결과가 나타납니다. 마찬가지로 업데이트가 복제되지 않은 다른 테이블의 하위 쿼리를 기반으로 하는 경우 게시자와 구독자 모두에서 동일한 저장 프로시저를 실행하면 다른 결과가 반환됩니다.

저장 프로시저 실행을 게시하려면

구독자에서 프로시저 수정

기본적으로 게시자의 저장 프로시저 정의는 각 구독자에 전파됩니다. 그러나 구독자에서 저장 프로시저를 수정할 수도 있습니다. 게시자와 구독자에서 다른 논리를 실행하려는 경우에 유용합니다. 예를 들어 big_table1 복제된 테이블에서 1,000,000개의 행을 삭제하고 big_table2 복제되지 않은 테이블을 업데이트하는 두 개의 함수가 있는 Publisher의 저장 프로시저인 sp_big_delete 생각해 보세요. 네트워크 리소스에 대한 수요를 줄이려면 sp_big_delete 게시하여 100만 행 삭제를 저장 프로시저로 전파해야 합니다. 구독자에서 sp_big_delete 수정하여 1백만 개의 행만 삭제하고 big_table2 후속 업데이트를 수행하지 않을 수 있습니다.

참고 항목

기본적으로 게시자에서 ALTER PROCEDURE를 사용하여 적용한 변경 내용은 구독자로 전파됩니다. 이를 방지하려면 ALTER PROCEDURE를 실행하기 전에 스키마 변경 내용의 전파를 사용하지 않도록 설정합니다. 스키마 변경에 대한 자세한 내용은 게시 데이터베이스에서 스키마 변경 작업을 참조 하세요.

저장 프로시저 실행 아티클의 유형

저장 프로시저 실행을 게시할 수 있는 방법에는 직렬화 가능한 프로시저 실행 문서와 프로시저 실행 문서의 두 가지가 있습니다.

  • serialize 가능한 옵션은 직렬화 가능한 트랜잭션의 컨텍스트 내에서 프로시저가 실행되는 경우에만 프로시저 실행을 복제하기 때문에 권장됩니다. 저장 프로시저가 직렬화 가능한 트랜잭션 외부에서 실행되는 경우 게시된 테이블의 데이터에 대한 변경 내용은 일련의 DML 문으로 복제됩니다. 이 동작은 구독자의 데이터를 게시자의 데이터와 일치하게 만드는 데 기여합니다. 이는 대규모 정리 작업과 같은 일괄 처리 작업에 특히 유용합니다.

  • 프로시저 실행 옵션을 사용하면 저장 프로시저의 개별 문이 성공했는지 여부에 관계없이 실행을 모든 구독자에 복제할 수 있습니다. 또한 저장 프로시저에 의한 데이터 변경은 여러 트랜잭션 내에서 발생할 수 있으므로 구독자의 데이터가 게시자의 데이터와 일치하지 않을 수 있습니다. 이러한 문제를 해결하려면 구독자가 읽기 전용이고 커밋되지 않은 읽기보다 큰 격리 수준을 사용해야 합니다. 커밋되지 않은 읽기를 사용하는 경우 게시된 테이블의 데이터에 대한 변경 내용이 일련의 DML 문으로 복제됩니다.

다음 예제에서는 프로시저 복제를 직렬화 가능한 프로시저 문서로 설정하는 것이 좋습니다.

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 전에 발생한다고 가정합니다.

직렬화 가능한 트랜잭션 내에서 프로시저가 실행되지 않는 경우(격리 수준이 SERIALIZABLE로 설정된 경우) 트랜잭션 T2는 T1의 SELECT 문 범위 내에 새 행을 삽입할 수 있으며 T1 이전에 커밋됩니다. 이는 T1 이전에 구독자에서 적용된다는 의미이기도 합니다. T1이 구독자에서 적용되면, SELECT는 게시자에서와 다른 값을 반환할 수 있고 UPDATE와 다른 결과를 가져올 수 있습니다.

프로시저가 직렬화 가능 트랜잭션 내에서 실행되면, 트랜잭션 T2는 T2의 SELECT 문 범위 내에서 삽입을 할 수 없습니다. T1이 구독자에서 동일한 결과를 보장하도록 커밋될 때까지 차단됩니다.

직렬화 가능한 트랜잭션 내에서 프로시저를 실행할 때 잠금이 더 오래 유지되고 동시성이 감소할 수 있습니다.

XACT_ABORT 설정

저장 프로시저 실행을 복제할 때 저장 프로시저를 실행하는 세션에 대한 설정은 XACT_ABORT ON을 지정해야 합니다. XACT_ABORT가 OFF로 설정되면 게시자에서 프로시저 실행 중 오류가 발생하면 구독자에서도 같은 오류가 발생하여 배포 에이전트가 실패합니다. XACT_ABORT ON을 지정하면 게시자에서 실행하는 동안 발생한 오류로 인해 배포 에이전트 오류가 발생하지 않도록 전체 실행이 롤백됩니다. XACT_ABORT 설정에 대한 자세한 내용은 SET XACT_ABORT(Transact-SQL)를 참조하세요.

XACT_ABORT를 OFF로 설정해야 하는 경우에는 배포 에이전트에 대해 -SkipErrors 매개 변수를 지정합니다. 이렇게 하면 오류가 발생하더라도 에이전트가 구독자에서 변경 내용을 계속 적용할 수 있습니다.

참고 항목

트랜잭션 복제에 대한 문서 옵션