하성희 강사(dbconsultant@empal.com) 스냅샷 복제 하성희 강사(dbconsultant@empal.com)

스냅샷 복제 오브젝트들과 데이터를 복사하여 초기화하는데 사용됨 특정 시점의 데이터를 배포하고 데이터 변경을 모니터링 하지는 않음 수직 필터링 및 수평 필터링 가능 사람이 수작업으로 하는 작업을 대신해 주는 기능 – 오브젝트들의 스크립트를 받고 데이터를 bcp out으로 받은 다음에, 스크립트와 데이터를 구독자에 적용

스냅샷은 언제 필요한가? 스냅샷 복제를 구축하는 경우 트랜잭션 복제나 병합 복제 구축을 위해 구독자 동기화에 사용하는 경우 트랜잭션 복제 – 트랜잭션 로그에 기록되어 있는 데이터 변경을 읽어서 구독자에 적용함 병합 복제 – 트리거를 사용하여 데이터 변경을 알아내어 구독자에 적용함

스냅샷 복제를 구축하는 경우? 스냅샷 복제는 테이블에 Primary Key가 없어도 가능함 자주 변경되지 않는 테이블의 경우에 주기적으로 동기화하면 되는 경우 (코드를 작성하거나 DTS를 사용하는 것보다 단순함) 게시자에서 이루어지는 변경 작업에 대한 제약이 없다

스냅샷 복제가 유용한 경우 데이터가 대부분 정적이고 자주 변경되지 않는 경우 일정 기간 동안 데이터 복사본이 최신이 아니어도 되는 경우 소량의 데이터를 복제하는 경우 사이트의 연결이 종종 끊기며 대기 시간(한 사이트에서 데이터가 변경된 후 다른 사이트에 변경 사항이 반영되기까지의 시간)이 길어도 상관 없는 경우

스냅샷 복제 계획 스냅샷 파일 전송 및 저장 스냅샷 일정 일시적으로 테이블을 추가 또는 업데이트할 수 없는 사용자를 최소화하려면 가능하다면 트랜잭션 복제와 함께 동시 스냅샷 처리 선택 데이터 변경이 적은 시점에 에이전트를 예약 (백업과 마찬가지로 스냅샷 생성에는 많은 리소스가 필요하며, 이 오버헤드로 스냅샷 생성 중 시스템 성능이 저하될 수 있음)

데모 (Optional) 처음에는 마법사를 사용하여 구축한 다음에, 스크립트 생성하여 스크립트 활용 가능 데모 : Northwind 데이터베이스의 테이블들을 동일 컴퓨터에 있는 다른 인스턴스로 복사

사전 점검 사항 파일을 저장하기에 충분한 공간이 디스크에 있는지 사전에 확인해야 함 (전송할 데이터 크기) 스냅샷은 해당 테이블로부터 데이터를 추출하는 동안 테이블에 공유 잠금을 걸기 때문에, 언제 테이블에 잠금이 걸려도 괜찮은지 확인해야 함

성능 튜닝 꼭 필요한 컬럼들과 행들만 복제할 것을 권고함 고성능의 디스크 서브 시스템 사용 게시 당 하나의 스냅샷 폴더 사용 스냅샷 복제에 소요되는 시간이 지나치게 오래 걸리는 경우에는 트랜잭션 복제로 변경하는 것을 고려하기 바람

트랜잭션 복제

목차 복제 계획 복제 토폴로지 디자인 복제 관련 시스템 테이블 복제 관련 시스템 프로시저 복제 관련 매개 변수 복제 점검 유의사항

복제 계획 복제를 신중하게 계획하는 것이 필요  데이터 일관성을 극대화하고 네트워크 리소스에 대한 요구를 최소화하며, 문제를 빠르게 해결 가능 복제 계획 시 고려 사항 복제된 데이터에 업데이트가 필요한지 그리고 누가 업데이트를 해야 하는지의 여부 일관성, 자율성 및 대기 시간에 관한 데이터 배포 요구 기업 사용자, 기술 인프라, 네트워크 및 보안, 데이터 특징 등을 포함한 복제 환경 복제 유형 및 복제 옵션 복제 토폴로지 및 복제 토폴로지를 복제 유형에 맞추는 방법

복제 토폴로지 결정 복제 모델을 선택 중앙 게시자 원격 배포자를 가진 중앙 게시자 게시 구독자 중앙 구독자 게시자와 구독자의 최초 동기화 방법 및 스냅샷 파일이 저장될 위치 결정 로컬 배포자인지 또는 원격 배포자인지 결정 배포 데이터베이스 공유 여부를 결정 여러 게시자가 배포자를 공유한다면, 게시자별로 자체 데이터베이스를 사용할 것인지 아니면 배포 데이터베이스를 공유할 것인지 결정 복제 유형 및 옵션을 결정 밀어넣기 구독을 사용할 것인지 끌어오기 구독을 사용할 것인지 결정

배포 데이터베이스 대부분의 경우, 배포 데이터베이스의 개수는 하나면 충분함. 여러 개의 배포 데이터베이스가 필요한 경우 복제 작업 및 관리를 중앙 집중화하고 하나의 배포 서버를 사용하여 여러 게시자를 수용하고자 하는 경우 관리 목적으로 개별 데이터베이스로 분리하는 경우 성능 측면 : 여러 개의 배포 데이터베이스를 사용하면 경합(배포 데이터베이스에 쓰기 및 읽기)을 줄일 수 있으므로 성능적인 측면에서 이로움.

복제 관련 시스템 테이블 Publisher..Syspublications 하나의 데이터베이스에서 정의된 모든 게시 목록을 저장. Publisher..Sysarticles 하나의 데이터베이스에서 정의된 모든 아티클들의 목록을 저장. 아티클에 정의된 필터링에 대한 정보도 저장. syssubscriptions select artid, status, dest_db from syssubscriptions where srvid = SubscriberSrvID

복제 관련 시스템 테이블 Msrepl_commands Msrepl_transactions Msrepl_subscriptions

MSrepl_commands 구독자로 복제되기 위해 대기중인 command들과 복제는 되었지만 Distribution Cleanup Agent job 이 삭제하지 않은 command들이 저장 배포 데이터베이스에 존재 sp_browsereplcmds 저장 프로시저를 실행하여 조회 가능

MSrepl_transactions 구독자로 복제되기 위해 대기중인 복제 정보 행들과 복제는 되었지만 Distribution Cleanup Agent job 이 삭제하지 않은 복제 정보 행들이 저장 Distribution 데이터베이스에 저장 스냅샷 에이전트도 초기 동기화에 대한 정보를 Msrepl_commands와 Msrepl_transactions 테이블에 저장

Msrepl_subscriptions 생성 시점 배포 에이전트가 시작될 때 MSreplication_subscriptions 테이블이 없으면 테이블을 생성 구독자에 MSreplication_subscriptions 테이블이 아직 없을 경우, sp_addpullsubscription 저장 프로시저가 테이블을 생성

복제 관련 시스템 저장 프로시저 Sp_replcmds Sp_replshowcmds Sp_browsereplcmds Sp_repldone Sp_replflush Sp_replcounters sp_subscription_cleanup

Sp_replshowcmds 현재 배포되지 않은 트랜잭션, 즉 배포자에게 전송되지 않고 트랜잭션 로그에 남아 있는 트랜잭션을 보고자 할 때 사용 sp_replshowcmds는 어떤 클라이언트 연결(현재 연결 포함)도 로그에서 복제된 트랜잭션을 읽지 않을 경우에만 실행 가능  이 프로시저를 실행하려면 로그 판독기 에이전트를 중지해야 함. 게시 데이터베이스에서 실행. 주의> sp_replshowcmds는 복제 관련 문제를 해결하는 경우에만 사용

Sp_repldone 트랜잭션 복제에서, 로그 판독기 에이전트가 어떤 트랜잭션이 배포되었는지 추적하기 위해 사용 주의 : 수동으로 실행하는 경우에는 배포되는 트랜잭션의 순서와 일관성을 손상시킬 수 있음 트랜잭션 보류 중인 복제가 있을 때 트랜잭션 로그를 삭제할 수 있도록 하기 위해 응급 상황에서 사용

Sp_replcounters 게시된 각 데이터베이스에 대한 대기 시간, 처리량 및 트랜잭션 수에 관한 복제 통계를 반환 게시자에서 실행

로그 판독기 에이전트 로그 판독기 에이전트가 sp_replcmds 저장 프로시저를 호출  게시 데이터베이스의 트랜잭션 로그로부터 트랜잭션 복제 대상으로 표시된 트랜잭션 목록 확인 트랜잭션들을 Batch 단위로 MSrepl_commands 테이블과 Msrepl_transactions 테이블로 전달 (배포 데이터베이스에 존재) 트랜잭션들이 배포 데이터베이스에 성공적으로 기록되면, 게시 데이터베이스에서 sp_repldone 저장 프로시저를 호출  트랜잭션 로그에 있는 복제된 트랜잭션들을 복제된 것으로 표시 (복제되었다는 것은 배포자에게 전달되었다는 것을 의미)

배포 에이전트 배포 에이전트가 배포 데이터베이스에서 sp_Msget_repl_commands 저장 프로시저를 수행하여 특정 구독자에게 복제되어야 하는 트랜잭션들을 확보 구독자의 MSreplication_subscriptions 테이블의 transaction_timestamp 컬럼에 배포 에이전트가 구독자에게 전달한 마지막 트랜잭션의 시퀀스 값을 저장 배포 에이전트가 스냅샷 폴더에 대한 완전한 액세스 권한을 가지는지 확인

기타 에이전트 설정 기타 에이전트 설명 디폴트 일정 에이전트 기록 정리: 배포 배포 데이터베이스에서 복제 에이전트 기록 제거 10분마다 실행 배포 정리: 배포 배포 데이터베이스에서 복제된 트랜잭션 제거 만료된 구독 정리 게시 데이터베이스에서 만료된 구독 제거 매일 오전 1시에 실행 데이터 유효성 검사 기능을 가진 구독 다시 초기화 데이터 유효성 검사에 실패한 모든 구독을 다시 초기화 없음(디폴트로 미사용) 복제 에이전트 점검 기록 작업을 로깅하지 않는 복제 에이전트 검색

매개 변수 매개변수 디폴트 값 설명 CommitBatchSize 100 CommitBatchThreshold 1000 COMMIT 문을 실행하기 전에 구독자에게 전달하는 복제 명령 수 QueryTimeOut 300 배포 에이전트의 타임 아웃 시간 (초 단위)

QueryTimeOut 매개 변수들의 값을 변경하고자 하는 경우에는, 한번에 하나씩 변경하고 변경 작업이 배포 에이전트에 어떤 영향을 미쳤는지 확인할 것을 권고함. 맨 먼저 QueryTimeOut 매개 변수를 30,000 초와 같이 더 큰 숫자로 변경 변경사항을 프로필에 저장한 다음 배포 에이전트를 재시작

CommitBatchSize / CommitBatchThreshold 커밋 문을 실행하기 전에 배포 에이전트가 완료해야 하는 작업부하를 감소시키기 위해서, CommitBatchSize 또는 CommitBatchThreashold 매개 변수의 값을 보다 작은 값으로 변경. 하나의 복제 트랜잭션 내에 여러 개의 명령어들이 있을 수 있으므로, CommitBatchSize 값에 트랜잭션 당 예상 명령어 개수를 곱해서 얻은 결과값이 CommitBatchThreshold 값보다 적은지 확인.

배포 에이전트 점검 (1) 게시 ID 확인 : Select pubid from syspublications where name = '게시이름‘ 복제 트랜잭션들이 Msrepl_commands 테이블에 추가되지 않도록 복제 모니터에서 로그 판독기 에이전트를 잠시 중지 배포 정리 에이전트를 수동으로 시작해서 구독자로 복제가 완료된 명령어들과 트랜잭션들을 Msrepl_commands 테이블과 Msrepl_transactions 테이블에서 삭제하는지 확인 (게시 데이터베이스에서 다음 쿼리를 수행하면 특정 구독 데이터베이스로 복제되기 위해 대기 중인 명령어들의 집합을 확인 가능) Select count(*) from [DistributorServerName].distribution.dbo.MSrepl_commands where article_id in (Select artid from sysarticles where pubid = PublicationID) 앞의 쿼리 결과값을 확인 단계 3과 4를 2,3분 동안 반복하고 매번 단계 4에서 확인한 행의 개수를 비교  행의 개수가 감소하면 배포 에이전트가 복제될 명령어들을 구독자에게 복제한다는 것을 의미

배포 에이전트 점검 (2) 구독 데이터베이스에서 MSreplication_subscriptions 테이블을 점검. Transaction_timestamp 컬럼에 이 구독자가 마지막으로 수신한 복제 트랜잭션의 시퀀스 값 저장. 1분 간격으로 transaction_timestamp 컬럼값 확인: Select transaction_timestamp from MSreplication_subscriptions where publisher = Publisher_Name and publisher_db = Publisher_databasename and publication = publication_name 이 값이 계속 변경되면, 구독자가 배포 에이전트가 전달하는 새로운 변경사항들을 수신하고 있다는 것을 의미.

유의사항 IDENTITY 속성 대용량 데이터의 스냅샷 동기화 스키마 변경 백업 및 복원 복제 제거

IDENTITY 속성 복제 전에 소스 테이블에 IDENTITY 속성이 존재하는지를 확인하고, 구독 테이블의 IDENTITY 속성 체계 결정 스키마 : 수작업

대용량 데이터의 스냅샷 동기화 Automatic Sync가 적절한 경우 Identity 항목이 없고 Source Data가 소량(100만건 미만) Manual Sync Manual Shema, Auto Data : Identity 항목이 있고 Source Data가 소량 Manual Schema, Manual Data : Source Data가 대량 (주로 Text/Image)

게시 테이블의 스키마 변경 SQL Server 2000에서는 게시와 구독을 재구축하지 않고 게시된 데이터베이스에서 컬럼을 추가하거나 삭제 가능 (유의사항 : 전제조건 준수) 스냅샷 복제 새 스냅샷을 구독자에서 다시 적용할 때 스키마 변경이 전파됨 트랜잭션 복제와 병합 복제 배포자 에이전트 또는 병합 에이전트가 실행될 때 스키마 변경이 증분 방식으로 전파됨

게시 테이블의 스키마 변경 엔터프라이즈 관리자를 사용하거나 쿼리 분석기에서 ALTER TABLE 명령어를 사용하여 게시된 테이블의 스키마를 변경하면, 스키마의 변경이 구독자에게 전파되지 않음 게시된 테이블에 대한 스키마 변경은 SQL Server 엔터프라이즈 관리자의 복제 게시 속성 대화 상자나 복제 저장 프로시저를 통해 수행해야 함 스키마를 변경하거나 sp_mergecleanupmetadata를 사용한 후에는 게시 데이터베이스를 백업할 것을 권고함  게시자에 오류가 있을 경우 게시 데이터베이스를 정확한 상태로 복구 가능

복제와 스키마 변경 기본 원칙 스키마 변경이 자주 일어나는 경우에는 복제를 사용하지 않는 것이 좋다.

클러스터된 인덱스 변경 클러스터된 인덱스 변경 제약 복제 구축 전에 최적의 클러스터된 인덱스 생성 요망

복제 환경 백업 사용자 데이터베이스 뿐 아니라 master, model, msdb, 배포 데이터베이스들도 백업해야 함 복제 스크립트도 백업해야 함

복제 스크립트 백업 복제 SQL 스크립트 생성 기능 게시자에서 SQL Server 엔터프라이즈 관리자를 열고, 서버 그룹을 확장하고, [복제] 폴더를 마우스 오른쪽 단추로 클릭한 다음, [SQL 스크립트 생성]을 클릭 항상 현재의 복제 설정 스크립트를 안전한 위치에 보관해야 함. 게시자, 배포자 및 구독자의 정기적 백업과 함께 수행.  스크립트를 사용하여 복제를 이전 설정으로 복구 가능

배포 데이터베이스의 복원 배포 데이터베이스를 복원할 때 SQL Server 복제 유틸리티는 실행을 중지해야 함 (복제 유틸리티가 실행 중이면 배포 데이터베이스에 액세스할 수 있기 때문) 온라인 설명서의 “복제 데이터베이스 백업 및 복원” 참조 실행 중지해야 할 복제 복제 로그 판독기 에이전트 유틸리티 복제 배포 에이전트 유틸리티 복제 스냅샷 에이전트 유틸리티 복제 병합 에이전트 유틸리티

복제 제거 게시자에서 복제 제거 방법 엔터프라이즈 관리자에서 게시자 서버를 클릭 [도구] 메뉴에서 [복제]를 선택하고 [게시 해제]를 선택 게시 및 배포 해제 마법사의 지시를 따라 복제 제거

복제 제거 배포 데이터베이스가 사용 가능한지 그리고 아무도 배포 데이터베이스를 사용하지 않는 상태인지 확인해야 함 복제 에이전트 Job이 지속적으로 수행되고 있고 배포 데이터베이스를 사용하고 있다면, 게시 및 배포 해제 마법사를 수행하기 전에 SQL Server 에이전트 중지 배포 데이터베이스를 사용하고 있는 모든 연결 중지 배포 데이터베이스가 손상되지 않았는지 확인 DBCC CHECKDB ('distribution') 게시 및 배포 해제 마법사는 게시의 끌어오기 구독을 삭제하지는 않음 게시 및 배포 해제 마법사를 수행하기 전에, 수작업으로 구독을 삭제해야 함 (엔터프라이즈 관리자를 사용하거나 끌어오기 구독 서버에서 sp_droppullsubscription 저장 프로시저 수행)

복제 제거 게시 및 배포 해제 마법사에서 오류가 발생하는 경우 구독이 삭제되지 않는 경우 : sp_subscription_cleanup 저장 프로시저를 사용하여 구독 삭제 게시 및 배포 해제 마법사를 여러 번 수행해도 계속 오류가 발생하고 복제의 일부분만 제거된 경우 원래의 복제 구축 스크립트를 수행하여 다시 동일한 복제 환경을 구축  SQL Server가 제대로 된 정보를 복제 관련 시스템 테이블에 저장  게시 및 배포 해제 마법사를 사용하여 복제 제거 재시도. 여전히 게시 및 배포 해제 마법사에서 오류가 발생하는 경우에는 수작업으로 복제 제거

배포 데이터베이스 삭제 배포 데이터베이스를 삭제할 때 SQL Server는 실제 파일들을 삭제함. 만약 파일을 삭제할 때 다른 클라이언트에서 배포 데이터베이스를 사용하고 있거나, 공유 위반일 경우, 파일은 실제로 하드 디스크에서 삭제되지 않음. 실제 파일이 제거되지 않은 상태에서 배포를 다시 설치하려고 하면, 새로운 이름의 배포 데이터베이스가 사용됨.

감사합니다 !