적용 대상:
SQL ServerAzure SQL Managed Instance
게시자에서 실행되고 게시된 테이블에 영향을 주는 하나 이상의 저장 프로시저가 있는 경우 이러한 저장 프로시저를 저장 프로시저 실행 아티클로 게시에 포함해 보십시오. 프로시저(CREATE PROCEDURE문)의 정의는 구독이 초기화될 때 구독자에 복제됩니다. Publisher 프로시저가 실행되면 복제는 구독자에서 해당 프로시저를 실행합니다. 이렇게 하면 프로시저 실행만 복제되므로 각 행에 대한 개별 변경 내용을 복제할 필요가 없으므로 대규모 일괄 처리 작업이 수행되는 경우에 훨씬 더 나은 성능을 제공할 수 있습니다. 이에 대한 예로 게시 데이터베이스에 다음 저장 프로시저를 만듭니다.
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
중요
저장 프로시저 복제는 모든 애플리케이션에 적합하지 않습니다. 아티클이 수평 필터링되어 그 결과 게시자와 구독자에 서로 다른 행 집합이 존재하면, 게시자와 구독자 각각에서 동일한 저장 프로시저를 실행할 때 서로 다른 결과가 반환됩니다. 마찬가지로 업데이트가 복제되지 않은 다른 테이블의 하위 쿼리를 기반으로 하는 경우 게시자와 구독자 모두에서 동일한 저장 프로시저를 실행하면 다른 결과가 반환됩니다.
저장 프로시저 실행을 게시하려면
SQL Server Management Studio: 저장 프로시저 실행을 트랜잭션 게시로 게시(SQL Server Management Studio)
복제 Transact-SQL 프로그래밍: sp_addarticle(Transact-SQL)을 실행하고 매개변수
@type에 대해 'serializable proc exec'(권장) 또는 'proc exec' 값을 지정합니다. 아티클을 정의하는 방법은 아티클 정의를 참조하세요.
구독자 측에서의 프로시저 수정
기본적으로 게시자의 저장 프로시저 정의는 각 구독자에 전파됩니다. 그러나 구독자에서 저장 프로시저를 수정할 수도 있습니다. 게시자와 구독자에서 다른 논리를 실행하려는 경우에 유용합니다. 예를 들어 big_table1 복제된 테이블에서 1,000,000개의 행을 삭제하고 big_table2 복제되지 않은 테이블을 업데이트하는 두 개의 함수가 있는 게시의 저장 프로시저인 sp_big_delete을 고려하세요. 네트워크 리소스에 대한 수요를 줄이려면 sp_big_delete를 게시하여 100만 행 삭제를 저장 프로시저로 전파해야 합니다. 구독자에서 sp_big_delete를 수정하여 1백만 개의 행만 삭제하고 big_table2에 후속 업데이트를 수행하지 않을 수 있습니다.
참고
기본적으로 Publisher 사용하여 ALTER PROCEDURE 변경한 내용은 구독자에 전파됩니다. 이를 방지하려면 ALTER PROCEDURE를 실행하기 전에 스키마 변경 내용 전파를 비활성화합니다. 스키마 변경 사항에 대한 자세한 내용은 게시 데이터베이스의 스키마 변경을 참조하세요.
저장 프로시저 실행 아티클의 유형
저장 프로시저 실행을 게시할 수 있는 방법에는 직렬화 가능한 프로시저 실행 문서와 프로시저 실행 문서의 두 가지가 있습니다.
직렬화 가능 옵션은 프로시저가 직렬화 가능한 트랜잭션의 컨텍스트 내에서 실행되는 경우에만 프로시저 실행을 복제하므로 권장됩니다. 직렬화 가능한 트랜잭션 외부에서 저장 프로시저가 실행되는 경우 게시된 테이블의 데이터 변경 사항은 일련의 DML 문으로 복제됩니다. 이 동작은 구독자의 데이터를 게시자의 데이터와 일치하게 만드는 데 기여합니다. 이는 대규모 정리 작업과 같은 일괄 처리 작업에 특히 유용합니다.
프로시저 실행 옵션을 사용하면 저장 프로시저의 개별 문장이 성공했는지와 관계없이 해당 실행이 모든 구독자에게 복제될 수 있습니다. 또한 저장 프로시저에 의한 데이터 변경은 여러 트랜잭션 내에서 발생할 수 있으므로 구독자의 데이터가 게시자의 데이터와 일치하지 않을 수 있습니다. 이러한 문제를 해결하려면 구독자는 읽기 전용이어야 하며, 읽기 커밋 안 함(Read Uncommitted)보다 높은 격리 수준을 사용해야 합니다. 커밋되지 않은 읽기를 사용하는 경우 게시된 테이블의 데이터에 대한 변경 내용이 일련의 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 설정
저장 프로시저 실행을 복제할 때 저장 프로시저를 실행하는 세션에 대한 설정은 ON을 지정 XACT_ABORT 해야 합니다. XACT_ABORT OFF로 설정되고 Publisher 프로시저를 실행하는 동안 오류가 발생하면 구독자에서 동일한 오류가 발생하여 배포 에이전트 실패합니다. ON을 지정하면 XACT_ABORT Publisher 실행 중에 발생한 모든 오류로 인해 전체 실행이 롤백되어 배포 에이전트 오류가 발생하지 않도록 합니다. 설정XACT_ABORT에 대한 자세한 내용은 (Transact-SQL)를 참조SET XACT_ABORT하세요.
XACT_ABORT OFF 설정이 필요한 경우 배포 에이전트에 대해 -SkipErrors 매개 변수를 지정하십시오. 이렇게 하면 오류가 발생하더라도 에이전트가 구독자에서 변경 내용을 계속 적용할 수 있습니다.