복제(Replication) 구성시 고려 할 점 - Distribution Cleanup에 관하여 -

SQL Server는 참으로 친절 하며 사용자에게 참으로 여러 가지 편의를 제공하여 감동을 준다!!
그중 본인을 가장 크게 감동 시키는건 머니머니 해도 "복제 마법사"가 데시겠다!!ㅇ0ㅇ!!

이건 머 배포 마법사, 게시 마법사, 구독 마법사 차례로 하나씩 들어가서 친절한 설명과 함께 시키느대로
다음~다음 클릭 하다보면 어느덧 복제된 데이터베이스 하나가 뚝딱 완성 되버리신다!!!

그렇게 얼마전 한대의 서버에 5개의 데이터베이스를 복제하여 쓰는 배포 겸 구독 서버를 구성해놓고
복제 구성은 별거 아니라고 생각하며 점점 잊혀져 가는데..

어느날 복제 구성하며 쌓인 정이 생각나 서버에 들어가 보니 이 서버가 팝핀춤을 추며 버벅거리신다ㅠㅠ
메모리 사용량은 명절전 과일값 오르듯이 쭉쭉 올라가고
CPU는 미친x 널뛰기 하듯이 오르락 내리락 하고 있다 -_-;;

왜 이럴까? DL580이 고작 5개 데이터베이스 구독으로 버벅되는 가을바람의 갈대같은 존재였나?

그렇게 애처롭게 모니터링을 하던중 발견된 사실!!

CPU사용률이 일정시간 간격으로 증가했다 감소를 하신다!!
먼가 실마리가 잡히는거 같다!!
일정시간 마다 SQL Server가 하는게 멀까? 머지? Agent 스케쥴?
SQL Agent의 Job목록과 기록을 쭈욱 훝어본다.....

찾았다!!! 이거였다!! 배포구성시 생성되는 그 Job!!!
"배포 정리: distributer"

자 그럼 이게 무엇인지 부터 알아보자!
설명에 앞서 복제 방식중 하나인 트랜잭션 복제에 대해 알아보겠다.

트랜잭션 복제는 게시자 서버의 의 트랜잭션 로그의 내용을 모니터링 하여 변경된 내용만을 구독자 서버로
전송되는 방식이며 크게 4단계로 이뤄진다.

1단계 : 초기 스냅샷 적용으로 구독자 서버와 게시자 서버의 테이블 및 스키마 정보를 스냅샵 폴더에 저장후
          배포 데이터베이스에 기록
2단계 : 배포 데이터베이스에 기록된 초기 스냅샷 작업을 구독자로 전송 하여 적용
3단계 : 로그 판독기 에이전트가 게시자의 트랜잭션 로그를 모니터링 후 복제 표시된 트랜잭션 발생시
          배포 데이터베이스로 복사됨.
4단계 : 배포 에이전트가 배포 데이터베이스에 보유한 트랜잭션을 구독자로 전송

위와 같은 4단계로 트랜잭션 복제는 구현되지만 실제 구성 초기 스냅샷은 성능 저하 요인으로
작용하기 때문에 일반적으로 하루에 한두번 씩만 이뤄지게 설정하므로 대부분의 시간동안 3,4단계가
반복된다고 볼 수 있다.

위의 3단계에서 로그 판독기가 모니터링된 트랜잭션 명령들은 1차적으로 배포자로 설정된
배포 데이터 베이스에 복사 되는데 이 정보들이 저장되는 배포 데이터베이스의 대표적인 테이블이
MSrepl_transactions 과 MSrepl_commands 이다

MSrepl_transactions는 복제된 트랜잭션에 대한 트랜잭션 ID와 시퀀스 번호를 저장하며
MSrepl_commands는 복제된 명령에 대한 아티클ID, 명령ID, 명령값등이 저장된다.

로그 판독기는 모니터링된 트랜잭션 명령들에 대해 지속적으로 위의 두 테이블에 저장되기 때문에
시간이 흐르게 되면 상당히 많은 양의 데이터가 저장될 것이다.
그래서 주기적으로 배포 데이터 베이스의 정리가 필요한데 이것이 바로 Distribution Cleanup이며
REPL-Distribution Cleanup 범주에 속하는 Agent Job인 "배포 정리: distribution" 에 의해 주기적으로
보존기간이 만료된 데이터는 삭제 된다.(보존기간은 변경 가능)


자!! 다시 내가 야심차게 구성한 5개의 데이터베이스를 구독하는 배포자겸 구독자 서버가 
왜 그렇게 힘들어 하였는지 보자.

1. 복제 마법사를 통해 만든 배포 데이터 베이스의 트랜잭션 보존기간을 기본값으로 설정(72시간)
2. 로그 판독기가 5개의 데이터베이로 부터 전송된 트랜잭션 정보가 배포 데이터베이스에 과도하게 쌓임
3. Distribution Cleanup 실행 주기가 데이터양에 비해 길다(30분)
4. Distribution Cleanup 실행시 대량의 데이터를 정리해야 하는 부담으로 OS의 과도한 리소스 발생
   (실행시간 평균 10분 이상소요)

위와 같은 원인에 대한 해결은 간단하다
Job Agent에 등록된 목록중 Distribution Cleanup Job(배포 생성시 "배포정리 : distribution"으로 생성됨)의
작업 단계 속성을 클릭 하여 아래의 실행 명령을 수정하면 된다.

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = ?

위의 쿼리는 배포자 속성의 트랜잭션 보존시간을 설정하는 것으로써 물음표 부분에 최대 보관 시간을
의미하며 로그 판독기가 모니터링한 트랜잭션 명령을 배포 데이터베이스에 보관하는 최대 시간을 설정
하는 것이다.
여기의 최대 보관 시간을 특수한 상황이 아니라면 1~2시간 내외로 짧게 설정하는 것이 시스템 성능에
안정적이며 Distribution Cleanup Job의 실행 주기도 역시 짧게하는 것이 성능 향상에 도움이 될것이다.