SQL Server 2016 향상된 복제 기능 – 배포 데이터베이스 클린업 향상

·      Version : SQL Server 2016 SP2, 2012 SP4 이후

SQL Server 2016 SP2 에서 복제 기능 향상으로 배포 데이터베이스를 클린업 할때 사용자가 일괄 삭제에 대한 입력 값을 정의할 수 있으며 대량 삭제 작업시 시스템이 자동으로 삭제 비율을 조정 할 수 있도록 변경되었다.

복제의 배포 데이터베이스는 전체 복제 토폴로지에서 매우 중요한 구성요소이다. 트랜잭션 복제의 경우 배포 에이전트는 복제 에이전트에 대한 복제 메타 데이터와 기록을 저장하는 것 외에도 구독자에게 전달 될 트랜잭션과 명령에 대한 중간 저장소를 제공한다.  이러한 트랜잭션과 명령은 MSRepl_Transactions및 MSRepl_Commands 테이블에 저장된다. 트랜잭션수가 매우 많은 게시자를 포함하는 트랜잭션 복제 토폴로지에서는 배포 데이터베이스의 MSRepl_Commands및  MSRepl_Transactions 테이블이 상당히 커질 수 있다.

배포 서버에서 10분마다 실행되는 배포 클린업 작업은 지정된 트랜잭션 보존기간(기본 72시간)을 기준으로 오래된 트랜잭션 및 명령을 삭제한다. 또한 배포 클린업 작업은 배포 서버에서 만료된 구독을 삭제한다.

배포 데이터베이스 클린업에서 사용자가 오해하는 부분이 있는데 동일한 게시 데이터베이스에 여러 트랜잭션 또는 스냅샷 게시가 있고 이러한 게시에 대한 클린업 작업을  배포 에이전트가 서로 다른 간격으로 실행되도록 구성하는 경우 클린업은 72시간 보다 오래된 데이터를 삭제하지 않을 수 있다. 그 이유는 배포 에이전트는 개별 게시자 데이터베이스에 대한 개별 배포 에이전트가 아닌 게시자 데이터베이스 기반으로 @max_cleanup_xact_seqno를 계산하기 때문이다.

트랜잭션이 많은 환경에서 배포 클린업 작업은 많은 양의 만료된 트랜잭션 및 명령을 삭제해야한다. 클린업 작업은 while 루프를 사용하여 MSRepl_Commands 및 MSRepl_Transactions의 항목을 각 2000 및 5000행씩 일괄 삭제 한다. MSRepl_Commands및 MSRepl_Transactions 테이블 크기에 따라 삭제 시간이 오래 걸리수 있으므로 잠금 및 차단 또는 복제 에에전트 실패와 같은 여러가지 성능 문제가 발생할 수 있다.  복제 테이블의 크기가 작을 때는 문제 없지만 약 200~ 300만 행 이상의 큰 테이블 경우 성능 문제가 발생할 수 있다.

향상된 배포 클린업 기능으로는 삭제 프로시저에서 사용할 일괄 처리 행수를 사용자가 정의할 수 있다. 만약 매개 변수를 명시적으로 정의하지 않으면 기본값(batch size = 2000, row = 5000)을 사용한다.   또한 각 반복마다 일괄 처리 크기를 이전의 반복 성능에 따라 배치 크기를 늘리거나 줄인다. 삭제 쿼리가 수행한 시간이 이전 실행과 비교하여 50% 향상되면 배치 크기 값을 20% 까지 증가하여 최대 50000행까지 증가한다. 만약 이전 실행과 비교하여  20% 감소하면 배치 크기 값을 50% 감소하고 기본값 2000/5000 으로  MSRepl_Commands 및 MSRepl_Transactions 각각에 대해 일괄 처리 비율을 조절한다.

또한 향상된 기능으로 만료된 구독을 클린업 하는 작업을 기존의 배포 클린업 작업과 분리하였다. 하루에 한 번 실행되도록 등록된 구독 만료 클린업 작업은 배포 데이터베이스 서버에 연결하여 구독 클린업 저장 프로시저를 실행 한다. 클린업 작업은 삭제된 행의 누적값이 아닌 while루프의 마지막 반복을 기반으로 행 계수를 보고한다. 또한 메시지에서 표현되는 값을row/sec 에서row/ms로 변경되어 보다 정확한 비율을 제공한다.

사용자 정의 파라메터를 지원하기 위해 sp_adddistributiondb 저장 프로시저에 두 개의 새로운 매개변수 @deletebtchsize_xact 및 @deletebatchsize_cmd가 추가 되었다. 이 매개 변수는 MSRepl_Transactions 및 MSRepl_Commands 테이블의 일괄 삭제 처리를 제어한다. 또한 SSMS는 변경 사항을 지원하도록 업데이트 되었다.

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/replication-enhancement-improved-distribution-database-cleanup/

2018-06-08 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

SQL Server, MS SQL, SQL replication, Replication Enhancement, SQL 복제, 배포 데이터베이스, SQL Server AG, Availability Group, HA