SQL Server Replication for High Availability SQL Support Seung Wan Choi

개관 SQL Server 복제 이해 복제 솔루션 계획 HA 를 위한 복제 구성 데모(optional)

미리보기 : Microsoft SQL Server Replication 스냅샷 복제(Snapshot Replication) 트랜잭션 복제(Transactional Replication) 병합 복제(Merge Replication)

Lesson: SQL Server 복제 이해 복제 모델 및 용어 복제는 어떻게 동작하는가? 복제란 무었? 복제의 요소 복제의 유형 스냅샷 복제(Snapshot Replication) 트랜잭션 복제(Transactional Replication) 병합 복제(Merge Replication) 복제 선택 시 지침

복제 모델 출판 업계의 용어로 설명 복제 모델 구독복제 프로세스 게시자 스냅샷 에이전트 배포자 배포 에이전트 구독자 게시 아티클 구독 구독복제 프로세스 스냅샷 에이전트 배포 에이전트 로그 판독기 에이전트 대기열 판독기 에이전트 병합 에이전트

복제 용어 (1 of 2) 게시자 복제를 위해 데이터를 다른 서버에 사용 가능하게 하는 서버이다. 게시자는 게시를 하나 이상 가질 수 있다. 어떤 데이터를 복제할 것인지 지정하는 서버로서의 기능 이외에도 게시자는 트랜잭션 복제 중 어떤 데이터가 변경되었는지를 검색하며 해당 사이트에서 모든 게시에 관한 정보를 유지관리. 배포자 배포 데이터베이스를 호스트하고 history 데이터, 트랜잭션과 메타데이터를 저장하는 서버이다. 배포자의 역할은 구현하는 복제의 유형에 따라 달라진다. 구독자 복제된 데이터를 받는 서버이다. 구독자는 게시(Publication) 내의 개별 아티클(Article)이 아닌 게시를 구독하며, 게시자에서 사용할 수 있는 모든 게시가 아니라 필요한 게시만 구독한다. 복제 유형에 따라 구독자는 데이터 변경 내용을 게시자에 전파하거나 데이터를 다른 구독자로 다시 게시할 수도 있다.

복제 용어 (2 of 2) 게시(Publication) 하나의 데이터베이스에서 하나 이상의 아티클을 모은 것. 아티클을 그룹화하면 함께 복제하려는 논리적으로 관련된 데이터 및 데이터베이스를 더 쉽게 지정할 수 있음. 아티클 복제를 위해 지정한 데이터 테이블, 데이터 파티션 또는 데이터베이스 개체이다. 아티클은 전체 테이블, 열 필터를 사용하는 특정 열, 행 필터를 사용하는 특정 행, 저장 프로시저 또는 뷰 정의, 저장 프로시저 실행, 뷰, 인덱스된 뷰, 사용자 정의 함수 등이 될 수 있다. 구독 복제할 데이터베이스 개체 또는 데이터 복사본에 대한 요청이다. 구독은 어떤 게시를 언제 어디서 받을 것인지를 정의한다. 게시자(구독 밀어넣기 : Push Subscription) 또는 구독자(구독 끌어오기 : Pull Subscription)가 구독의 데이터 배포나 동기화를 요청할 수 있다. 게시는 밀어넣기 및 끌어오기 구독이 혼합된 형태를 지원할 수 있다.

복제 동작 (1 of 3) Receives data changes Maintains source databases 게시자(Publisher) 구독자(Subscriber) Maintains source databases Makes data available for replication Receives data changes Holds copy of data 배포자(Distributor) Stores metadata, history, and receives and stores changes May forward changes to Subscribers

복제 동작 (2 of 3) Subscription Articles Table 데이터의 전체 혹은 부분 뷰 저장 프로시져 사용자정의 함수 Publications 한 개 혹은 여러 개 article 구독의 기초 데이타베이스 당 하나 혹은 이상 Subscription Publication Articles

복제 동작 (3 of 3) Subscriptions Push subscription Push subscription Publication A Push subscription Push subscription Publication B Pull subscription Push subscription

복제 에이전트 Snapshot Transactional Merge Snapshot Agent Distribution Agent Log Reader Agent Queue Reader Agent Merge Merge Agent

복제 에이전트 스냅샷 에이전트(Snapshot Agent) 모든 복제 유형에서 사용된다. 이것은 게시된 테이블과 저장 프로시저의 스키마 및 초기 데이터 파일을 준비하고 배포 데이터베이스에서 동기화에 관한 정보를 기록한다. 스냅샷 에이전트는 일반적으로 배포자의 SQL Server 에이전트에서 실행되며 SQL Server 엔터프라이즈 관리자를 사용해서 관리할 수 있다. 로그 판독기 에이전트(Log Reader Agent) 트랜잭션 복제와 함께 사용된다. 이것은 복제 대상이라고 표시된 트랜잭션을 게시자 상의 트랜잭션 로그에서 배포 데이터베이스로 이동한다. 트랜잭션 복제를 사용해서 게시된 각 데이터베이스는 배포자에서 실행되고, 게시자로 연결되는 자체 로그 판독기 에이전트를 가진다. 배포 에이전트(Distribution Agent) 스냅샷 복제 및 트랜잭션 복제와 함께 사용된다. 이것은 게시 데이터베이스에 있는 스냅샷 작업 및 트랜잭션을 구독자로 이동한다. 배포 에이전트는 일반적으로 밀어넣기 구독을 위한 배포자, 또는 끌어오기 구독을 위한 구독자에서 실행된다.

복제 에이전트 병합 에이전트(Merge Agent) 병합 복제와 함께 사용. 병합 에이전트는 초기 스냅샷을 구독자에 적용하고 발생한 증분 데이터 변경 내용을 이동 및 조정한다. 각 병합 구독에는 게시자 및 구독자 모두에 연결되고 모두를 업데이트하는 자체 병합 에이전트가 있다. 병합 에이전트는 일반적으로 밀어넣기 구독을 위한 배포자 또는 끌어오기 구독을 위한 구독자에서 실행된다. 또한 구독자에서 게시자로 변경 내용을 업로드한 다음 일반적인 양방향 병합 도중에 게시자에서 구독자로 변경 내용을 다운로드한다. 에이전트의 교환 유형을 구성하여 변경 내용을 단일 방향으로 이동할 수도 있다. 11 대기열 판독기 에이전트(Queue Reader Agent) 지연 업데이트(queued update) 옵션이 있는 스냅샷 복제 또는 트랜잭션 복제와 함께 사용.또는 장애 조치(failover)로 지연 업데이트를 사용하는 즉시 업데이트 옵션을 활성화할 경우 사용. 배포자에서 실행하는 멀티스레드 에이전트. 이 에이전트는 대기열에서 메시지를 가져와 적합한 게시에 적용. 배포 및 병합 에이전트와는 달리, 하나의 대기열 판독기 에이전트 인스턴스만 존재하여 특정 배포자에 대한 모든 게시자 및 게시를 서비스한다.

스냅샷(Snapshot) 복제는? 특정 시점의 데이터 및 데이터베이스 개체 상태 그대로를 복사 및 배포 특정 시점의 데이터 및 데이터베이스 개체 상태 그대로를 복사 및 배포 데이터에 대한 갱신을 탐지 하지 않음. 데이터 수정을 전파하는데 오래 걸릴수 있음. A point in time snapshot image

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

병합(Merge) 복제? 사이트들이 자율적으로 동작하게 해줌. 사이트들 간 연결이 이루어졌을때 데이터 갱신이 병합됨. 사이트들이 자율적으로 동작하게 해줌. 사이트들 간 연결이 이루어졌을때 데이터 갱신이 병합됨. 게시자, 구독자에서 동일 데이터에 대해 업데이트시 충돌 발생 충돌 해결 : 기본 및 사용자 지정 가능

병합 복제가 유용한 경우 여러 구독자가 다양한 시간에 데이터를 업데이트하고 그 변경 내용을 게시자 및 다른 구독자에 전파해야 할 경우 구독자는 데이터를 받고 오프라인으로 변경을 하며 게시자 및 다른 구독자로 변경 내용을 동기화해야 할 경우 응용 프로그램 대기 시간(Latency : 게시자의 변경 내용이 구독자에게 반영되어 나타나기 까지의 시간) 요구 사항이 높거나 낮을 경우 사이트 자율성이 중요한 경우

트랜잭션(Transactional) 복제? 데이터 수정에 대해 즉각적인 복제를 가능하게 함. 게시되는 데이터의 필터링 및 변환 가능 다음의 단계를 따름: 초기 snapshot 전파 게시자 에서 데이터 변경 개개의 트랜잭션들이 수집됨 구독자 들에 트랜잭션을 전파

트랜잭션 복제가 유용한 경우 데이터를 수정한 후 몇 초 이내에 수정 내용을 구독자로 전파하려는 경우 데이터를 수정한 후 몇 초 이내에 수정 내용을 구독자로 전파하려는 경우 트랜잭션이 구독자에 모두 적용되거나 전혀 적용되지 않는 원자성을 지녀야 하는 경우 구독자가 대부분 게시자에 연결되어 있는 경우 응용 프로그램에서 구독자가 변경 내용을 받기 위한 대기 시간을 많이 허용하지 않는 경우

이기종 게시자 및 구독자 유형이 다른 구독자 (게시자에서 OLE DB 또는 ODBC 구독자로 밀어넣기 구독 ) Microsoft Access 데이터베이스 Oracle 데이터베이스 SQL Server ODBC 또는 OLE DB 구독자 요구 사항에 부합되는, 유형이 다른 구독자의 기타 데이터베이스 IBM DB2/AS400 and IBM DB2/NT and IBM DB2/6000 ( Microsoft Host Integration Server2000 필수) 유형이 다른 게시자 SQL Server 2000은 Oracle, DB2, Access, 기타 데이터 원본 등으로부터 복제된 스냅샷 데이터나 트랜잭션 데이터를 구독 가능. SQL Server 2000 응용 프로그램으로 게시한 스냅샷 복제나 트랜잭션 복제를 구현하려면, 타사의 소프트웨어를 사용하거나 SQL-DMO 및 복제 배포자 인터페이스로 작성한 응용 프로그램을 통해 구성.

복제 토폴로지 - 예

복제 토폴로지 - 중앙 게시자(Central Publisher) 특성 가장 일반적 구성 예를 들어, 마스터 데이터, 목록 또는 보고서를 중앙 게시자에서 모든 구독자로 배포하는 경우 Select 가 대분인 예를 들면 인증 시스템의 경우

복제 토폴로지 - 중앙 구독자(Central Subscriber) 구성 예 로컬 웨어하우스의 다수의 서버에서 재고 목록 데이터를 본사의 중앙 구독자로 롤업할 경우 또한 회사의 자치 사업 부서에서 정보를 롤업하거나, 여러 위치의 주문 정보를 통합하는 데 이 복제 구성을 사용할 수 있다.

복제 토폴로지 - 재게시자 ( Republisher ) 특징 게시자가 속도가 느리거나 비용이 많이 드는 통신 연결 연결 끝쪽에 다수의 구독자가 있을 경우, 재게시자를 사용하면 대량의 배포 로드가 해당 연결쪽에서만 발생.

복제 토폴로지 HA 를 위해 권고하는 토폴로지 중앙 게시자 와 배포자 느린 연결을 위해서는 Republisher 기타 토폴로지 분할된 데이터를 가진 중앙 구독자 양방향 분할된 토폴로지 (Bidirectional Partitioned Topology) 양방향의 분할되지 않은 토폴로지 (Bidirectional Non-Partitioned Topology)

중앙 게시자 와 배포자 중앙 게시자와 배포자 중앙 게시자와 배포자 Remote 배포 Moscow Berlin headquarters 중앙 게시자와 배포자 Remote 배포 중앙 게시자와 배포자 London Paris Rome Athens 1 2 3

San Francisco publishes to Seattle, Dallas, and Seoul Republisher 사용 Seoul republishes to Beijing, Hong Kong, and Sydney Seattle Seoul Slow link San Francisco Beijing Dallas San Francisco publishes to Seattle, Dallas, and Seoul Hong Kong Sydney 1 2 3

복제 대상 객체 복제 가능 객체 Stored Procedures Views Tables Triggers

자율성 과 대기 (Autonomy and Latency) Snapshot replication Transactional replication Updatable snapshot replication Updatable transactional replication Lower autonomy Lower latency Higher autonomy Higher latency Merge replication Distributed transactions

복제 와 기타 데이터 분산 기술의 대비 Method Autonomy Latency Replication Distributed Transactions Distributed Queries Back up and Restore Autonomy Variable Low High Data Transformation Services (DTS) Special use only Latency Variable, depending on type

Standby Solution 과 비교 Hot Standby Failover Support Failover Clustering 필요 실패 감지 와 failover 가 자동. Warm Standby Solution 로그 전달 (Log shipping) – 주서버로부터 보조 서버로 백업을 전달. 복제 (Replication) – 보조서버로 동시에 데이터 접근을 가능하게 함.

Lesson : 복제 솔루션 계획 복제 솔루션 선택 복제 솔루션 관리 계획

복제 솔루션 선택 HA 를 위한 권고 트랜잭션 복제 데이터 수정이 짧은 시간안에 전파되고, 트랜잭션이 완전히 이루어 지거나 전혀 이루어 지지 않음. HA 의 요구에 따라 다음도 가능: 스냅샷 복제 데이터가 자주 변경되지 않음. 변경된 데이터가 즉시 배포될 필요가 없을때. 병합 복제 여러 개의 구독자가 다양한 시간에 갱신이 필요할때. 사이트 자율성이 중요할때.

복제 솔루션 관리 계획 토폴로지 선택 복제가 복잡할수록, 관리및 문제 해결이 복잡해 진다. 복제가 복잡할수록, 관리및 문제 해결이 복잡해 진다. 단순하게 구성하라, 관리가 더 쉬워 진다. 수정가능 구독자를 위한 충돌 처리 계획 병합 복제 병합 혹은 고객의 방법에 의해서 처리될수 있다. 양방향 트랜잭션 복제 충돌 해결을 위해 고객의 코드가 필요

Lesson : HA를 위한 복제 구성 HA 를 위한 디스크 구성 Impact depending on topology choice 복제 모니터를 사용하여 복제 솔루션 감시 Schema Design 과 복제 복제시 Schema 변경 HA 와 연관된 Architecture 문제 Scheduling Change Forwarding and Reducing Latency Directing to Replicated Data

HA 를 위한 Disk 구성 복제유형 Transaction Log Disk Space 스냅샷 복제 완료된 이미지를 보내는 것으로, 전혀 염려 없음. Snapshot 크기에 의존, 배포자는 이력 만 저장 트랜잭션 복제 트랜잭션 추적을 필요로 하면 복구모델을 단순으로 하지 마라. DB 와 로그를 위해 적절한 크기가 필요함.–배포자를 포함해 많은 디스크가 소요될수 있음. 병합 복제 트랜잭션 변경에 대해 염려된다면 복구 모델을 ‘단순’으로 하지 마라. No other net impact, 게시되는 데이터의 크기와 보유기간이 클수록 만이 요구됨. 배포자는 이력만 저장.

복제 솔루션을 사용할때 실패 관리 시나리오 게시자 배포자 구독자 A Up Down B C D E F G

복제 모니터를 사용하여 복제 감시하기 성능 기준선 수립 복제 경고 점검 에러 감시

복제 와 스키마 디자인 복제되는 테이블 당 최대 열 숫자: 스냅샷, 트랜잭션 : 255 병합: 245 복제되는 테이블의 최대 행 크기: 스냅샷, 트랜잭션: 8,000 bytes 병합: 6,000 bytes Primary and Foreign Keys TEXT and IMAGE fields Integer columns Timestamp Columns

복제와 스키마 변경 제1의 일반적인 규칙 : 스키마 변경이 자주 일어나는 겨우에는 복제를 사용하지 말라. 제1의 일반적인 규칙 : 스키마 변경이 자주 일어나는 겨우에는 복제를 사용하지 말라. 복제는 잘 동작하기 위해서 객체의 이름에 종속적.– 컬럼이름을 변경한다면 복제는 깨어짐. 복제에서 가능한 몇가지 변경 컬럼 추가 컬럼 삭제 온라인 설명서 ‘게시 데이터베이스의 스키마 변경’ 참조 복제에 의해 성성된 뷰 나 기타 객체의 변경은 복제를 깸.

변경을 전송하는 스케쥴 및 지연시간 줄이기 Continuous 대기 서버를 주서버와 최신 동기화 최소의 지연 Scheduled 디폴트로 SQL Server Agent 의 하부시스템으로 복제 에이전트가 구동됨. SQL Server Agent 가 지원하는 주기로 구동 가능. On-demand 관리자 혹은 애플리케이션의 명시적 요구에 의함. warm standby server 를 사용할때는 적절하지 않음. Schedule continuous logreader and distribution · Minimizes potential for log growth · Minimizes latency and potential loss of data when failure occurs Large command chunking A new “feature” in SQL Server 2000 SP1: you can set a maximum command batch size and the Logreader will automatically break up any transactions exceeding that size into separate but ordered transactions containing commands of a maximum size specified Replication of stored procedures when large commands are executed Sends one command rather than one per row; perhaps not appropriate when partitioning of tables Use Independent Agents for each subscription Each subscriber can be setup to have an independent distribution agent. This allows for decreased latency in moving transactions from the distribution database; however this does require additional resources at the distributor.

복제된 데이터 소스로 Redirect 역할 변경후, 애플리케이션과 클라이언트는 새로운 primary 로 접근. 고려할수 있는 방법: ODBC DSN Network Load Balancing Rename the SQL Server (non-clustered only)

SQL Server 복제 기술의 High-Availability 특징 High Availability Feature Transactional Replication Standby type Warm Failure detection No, but with NLB … Automatic failover Protects against failed server process Yes, but … Protects against failed disk Meta data support Some user schema and data Transactionally consistent Yes Transactionally current No, since last replicated transaction Performance impact Log reader continually running Time to fail over Seconds, more to recover more thoroughly Locations Dispersed Additional complexity More Number of nodes No limit Standby available for reporting, etc. Partitioning of data to standby

데모 (optional) SQL 복제 구성 모의 실험 “Server Down” Switch to the secondary Redirect clients to the replicated data using ODBC