[MSSQL/SQL SERVER] 복제 (Replication)

1. 복제 : 복제는 한 데이터베이스에서 다른 데이터베이스로 데이터 또는 데이터베이스 개체를 복사 및 배포한 다음 데이터베이스 간에 동기화를 수행하여 일관성을 유지하는 일련의 기술 (고가용성을 높이기 위한 방법)

 

 

2. 복제의 구성 요소 

복제의 구성요소는 크게 배포, 게시, 구독, 아티클로 나눌 수 있다. 이를 잡지사에 비유하면 잡지를 만들어내는 회사를 게시, 잡지를 나눠주는(판매하는)행위를 하는 곳을 배포, 잡지를 받는 사람을 구독, 잡지 자체는 아티클로 비유할 수 있다.

게시자(원본) : 복제를 통해 데이터를 다른 위치에서 사용할 수 있도록 만드는 데이터베이스

 

배포자 : 하나 이상의 게시자와 연결된 복제별 데이터에 대한 저장소로 게시자에서 구독자로 데이터를 이동합니다. 배포자와 게시자가 동일한 경우는 로컬 배포자 다른 서버인 경우 원격 배포자라고 칭한다.

 

구독자 : 복제된 데이터를 전송받는 데이터베이스

 

 

 

3. 복제의 종류

스냅샷복제 : 데이터가 자주 변경되지는 않지만 변경되는 데이터 양이 많을 때 유리합니다.

트랜잭션복제 : 데이터 변경 내용은 게시자에서 발생한 것과 같은 순서 및 트랜잭션으로 구독자에게 적용되므로 게시 내에서 트랜재션 일관성이 보장됩니다. DML이 빈번하게 일어나며 게시와 구독이 거의 실시간으로 동기화 되어 일관성을 유지해야 할 때 주로 사용

병합복제 : 여러 사이트에서 작업 한 내용이 취합되어 하나의 일관된 데이터로 동기화 된다.

 

 

 

 

참조 :

www.kdata.or.kr/info/info_04_view.html?field=title&keyword=%BA%B9%C1%A6&type=techreport&page=1&dbnum=184067&mode=detail&type=techreportdocs.microsoft.com/ko-kr/sql/relational-databases/replication/sql-server-replication?view=sql-server-ver15

 

 

 

 

복제 구성 예제

스크립트를 통한 복제 생성

 

 게시 만들기

a. 게시자에서 sp_addpublication 실행
b. 게시자에서 sp_addpublication_snapshot 실행
c. sp_addarticle로 아티클을 추가
d. 스냅샷 에이전트 작업을 시작하여 게시에 대한 초기 스냅샷을 생성


sp_addpublication
@publication → 게시이름 
@repl_freq -> 스냅샷(snapshot), 트랜잭션(continuous) 게시 선택, 
@status → active : 구독자가 게시 데이터를 즉시 사용 inactive → 게시가 처음 작성될 때 구독자가 게시 데이터 사용 불가
@allow_push → 밀어넣기 구독 생성 가능 여부
@allow_pull → 끌어오기 구독 생성 가능 여부
@sync_method → 동기화 방법


sp_addpublication_snapshot
@publication → 게시이름
Windows 자격증명 사용:
@snapshot_job_name, @password : 스냅샷 에이전트가 실행되는 지정
SQL Server 인증을 사용
@publisher_security_mode → 0 으로 설정
@publisher_login, @publisher_password에 대해 SQL Server 로그인 정보 지정


sp_addartcile
@publication → 게시이름
@article → 아티클의 이름
@source_object → 게시되는 개체
@filter_clause → where을 생략한 값 ex)@filter_clause = N'col1>3' → col1값이 3보다 큰 경우만 복제






 구독 만들기
a. 게시자에서 sp_addsubscription 실행
b. 게시자에서 sp_addpushsubscription_agent 실행


sp_addsubscription
@publication → 게시이름
@subscriber → 구독자의 이름
@destination_db → 구독의 데이터베이스의 이름
@subscription_type → push(밀어넣기 구독)
@sync_type → automatic (기본값, 초기화) , replication support only (초기화 x)


sp_addpushsubscription_agent
@publication → 게시이름
@subscriber : 구독자의 이름
@subscriber_db → 복제된 데이터를 추가할 대상 데이터베이스의 이름
Windows 인증
@job_login, @job_password 값 지정
SQL Server 인증
@subscriber_security_mode 값 0 지정
@subscriber_login, @subscriber_password 값 지정

 

 

 

복제 케이스

 

C1. 구독에 테이블이 자동으로 생성되도록 설정
  EXEC sp_addsubscription 의 sync_type을 기본값인 automatic으로 설정.

기존 구독DB

복제 과정 후 구독DB

게시DB(좌)에 따라 구독DB(우)의 데이터가 동일하게 생성된다.

C2.  구독에 게시와 같은 형식 ,다른 내용의 테이블이 존재하도록 하는 방식.

기존 게시DB(좌) 구독 DB(우) 테이블 이름은 같으나 내부 데이터가 다름

EXEC sp_addsubscription의 매개변수중 @Sync_type 값을 'replication support only' 설정

복제 설정 후 게시DB에 값 삽입 결과 구독DB에 해당 값이 복제 되었다.

C2-1. 해당 게시에 아티클을 추가 하는 경우.

게시의 매개변수 중 allow_anonymous 와 immediate_sync 값을 false로 변경해준다.
아티클을 추가한 후 sp_addsubscription을 실행한다.

기존의 구독DB

아티클을 추가한 후 복제의 에이전트를 갱신한다.

에이전트가 갱신된 후 구독DB

게시DB(좌)와 동일한 아티클이 구독DB(우)에 생성된다. 

C3. 구독DB의 아티클에 컬럼 추가
게시DB(좌) 구독DB(우)

게시DB에 COL4 추가 후 값 INSERT 결과
ALTER table repl_test add col4 int
insert into repl_test values(200,2000,2000,20000)


C4. 아티클에 필터링 생성
행에 필터링 생성
a. addarticle의 매개변수인 @filter_clause에 값을 지정해준다 (where 생략)

컬럼에 필터링 생성
a. addarticle의 매개변수인 @vertical_partiton 값을 true로 지정 → primary key의 컬럼을 제외하고 drop된다.

b. 아티클에 컬럼을 추가하기 위해선 sp_articlecolumn에 @column에 해당 컬럼명을 넣고 @operation 값을 add로 지정

c. 아티클에 컬럼을 삭제하기 위해선 sp_articlecolumn에 @column에 해당 컬럼명을 넣고 @operation 값을 drop으로 지정

 

 

 

※ 트랜잭션 복제의 최소 단위는 트랜잭션 이하이다. 즉 하나의 트랜잭션의 길이가 긴 경우 잘라서 복제될 수 있다