SQL Server 2005 고가용성 및 재난 복구 김영건 필라넷 / DB 사업부 / 책임 컨설턴트 SQL Server Specialist Member PASS Korea Member

목차 MS SQL Server 2005 의 고가용성 백업 및 복원 복제

Session 1 MS SQL Server 2005 의 고가용성

고가용성 ? 재난 복구 ? 사고는 언제 어디서 터질지 모른다. 누가 ? 언제 ? 어떻게 ? 무엇을 ? Disaster

재난 유형 데이터베이스 장애 – 장비 문제 – 사용자 실수 – 자연 재해 Application 장애 – 잘못 만들어진 Application –Stored Procedure – 잘못된 Connection – 외부 공격

재난 시 손해 ? 재난 혹은 System Down 시 손해는 ? – 24 * 7 * 365 필요 ? – 가용성 비율 99.99% => 연간 53 분의 down time % => 연간 5 분의 down time 그럼 대비는 어떻게 대비해야 하나 ?

MS SQL Server 2005 의 고가용성 클러스터링 복제 로그 전달 데이터베이스 미러링 (2005 에서 추가된 기능 ) 온라인 인덱스 온라인 복원 Snapshot Isolation

클러스터링 가상 서버 공유 디스크 Active Passive Active

미러링 주 서버 미러 서버

복제 게시자 구독자 Data

로그 전달 주 서버 대기 서버 Transaction Log Restore

대기 유형 HOT Standby – 주 서버와 대기 서버의 데이터가 동일 – 클러스터링, 미러링 (Safety Full) Warm Standby – 주 서버와 대기 서버가 주기적으로 데이터를 동기화 – 복제, 로그 전달, 미러링 (Safety Off) Cool Standby – 데이터의 동기화가 일어나지 않는다 – 백업 / 복원, Attach/detach

고가용성 비교 가용성 특징 Hot StandbyWarm Standby 데이터베이스 미러링 장애조치 클러스터링 로그 전달트랜잭션 복제 데이터 손실없음 (HA) 없음있을 수 있음 자동 장애 조치 Yes(HA)YesNo 클라이언트 리디렉션 Yes ADO.NET, SQL Native Client 사용 Yes 가상 IP 사용 No NLB 필요 사용자 코딩 No NLB 필요 사용자 코딩 장애 조치 시간 10 초 미만 (HA)30 초 + 대기 서버 접근스냅샷으로 가능 No 복원 시에는 불가능 가능 가용성 범위데이터베이스서버데이터베이스개체

고가용성 비교 cont. 가용성 특징 Hot StandbyWarm Standby 데이터베이스 미러링 장애조치 클러스터링 로그 전달트랜잭션 복제 데이터 이중화 YesNoYes 하드웨어표준 서버인증 서버표준 서버 거리 제한없음 100 마일없음 관리 복잡도낮음높음낮음중간 여러 보조 No Yes

Session 2 백업 및 복원

새로운 백업 기능 복사 전용 (COPY_ONLY) 백업 부분 백업 백업 미디어 미러링 Checksum 사용

복사 전용 (COPY_ONLY) 백업 다른 백업과 복원 시퀀스에 영향을 주지 않는 백업 모든 복구 모델에서 사용 가능 모든 백업 타입에서 사용 가능 RESTORE HEADERONLY 에서 IsCopyOnly 컬럼 msdb.dbo.backupset 의 is_copy_only 컬럼 WITH COPY_ONLY 옵션 사용

부분 백업 주파일 그룹과 모든 읽기 쓰기 파일 그룹이 백업됨 필요에 따라서 읽기 전용 파일 그룹을 지정 가능 부분 차등 백업도 가능 모든 복구모델에서 사용가능 단순 복구 모델에서의 사용 편리 READ_WRITE_FILEGROUP 옵션 사용

백업 미디어 미러링 백업 미디어에 대한 이중화 동일한 미디어에 백업을 받아야 한다 ( 장치까지 동일 ) MIRROR TO DISK = …

Checksum 사용 백업 시 데이터베이스의 검증과 에러 탐색 복원 시에도 checksum 을 사용 가능 일반 백업보다는 부하 가중 WITH CHECKSUM 옵션 사용

새로운 복원 기능 온라인 복원 – 파일 온라인 복원 – 페이지 온라인 복원 증분 (Piecemeal) 복원 데이터베이스 스냅숏 복원

파일 온라인 복원 Enterprise Edition 에서 가능 모든 복구모델에서 사용 가능 복원 중인 파일 이 외의 파일 그룹은 접근 가능 최소한 주 파일 그룹은 온라인 상태여야 함

페이지 온라인 복원 Enterprise Edition 에서 가능 페이지 온라인 복원은 전체, 대량 복구모델에서 가능 복원 중에 해당 페이지가 있는 파일 그룹은 접근 불가 나머지 파일 그룹은 접근 가능 최소한 주 파일 그룹은 온라인 상태여야 함 RESTORE DATABASE dbname PAGE=‘8:2145‘ FROM…

증분 (Piecemeal) 복원 파일 그룹 단위로 복원 진행 복원되는 그룹 단위로 사용 가능 2000 의 Partial 옵션을 개선한 것 모든 복구 모델에서 사용가능 Enterprise Edition 을 사용하면 온라인 복원 가능

데이터베이스 스냅숏 복원 데이터베이스의 스냅숏으로 스냅숏을 생성한 시점으로 복원 가능 불완전한 복사본 메타데이터는 스냅숏 당시로 변경됨 전체 텍스트 카탈로그가 모두 삭제됨 원본 데이터베이스와 스냅숏 모두 접근 불가 RESTORE DATABE dbname FROM DATABASE_SNAPSHOT = …

Session 3 복제

향상된 복제 기능 ( 일반 ) 관리 기능 – 새 복제 모니터 – 간단한 사용자 인터페이스 – 향상된 ID 범위 관리 – 병렬 스냅숏 준비 가용성 – 스키마 변경 내용 복제 – 스냅숏 배달 재개 가능 – 피어 투 피어 트랜잭션 복제 – 복제 에이전트 지원 향상 – 논리적 레코드 복제

향상된 복제 기능 ( 일반 ) cont. 다른 유형의 복제 –Oracle 데이터베이스로부터 SQL Server 2005 에 데이터를 게시 – 비 -SQL Server(Non-SQL Server) 구독자로 데이터를 게시 프로그래밍 기능 –RMO( 복제 관리 개체 ) – 병합 복제를 위한 비즈니스 논리 처리기 복제 이동성 –HTTPS 를 통한 웹 동기화 보안 – 에이전트 간의 보안 세분화

향상된 복제 기능 cont. 트랜잭션 복제 – 트랜잭션 게시를 위한 추적 프로그램 토큰 – 트랜잭션 구독을 백업에서 초기화 – 재초기화 없이 트랜잭션 아티클의 호출 형식 수정 가능 – 트랜잭션 게시에서 동시 스냅숏 기본 사용 – 트랜잭션 게시에서 허용되는 열 개수 증가 – 업데이트 구독에 대한 blob 지원

향상된 복제 기능 cont. 병합 복제 – 병합 구독에 대한 통계 모니터링 기능 향상 – 매개 변수가 있는 필터의 병합 게시에 대한 향상된 스냅숏 – 병합 게시의 아티클에 대한 선언적 정렬 – 병합 게시의 아티클에 대한 조건적 삭제 처리 – 향상된 오류 메시지 – 병합 에이전트 및 배포 에이전트에 대한 병렬 처리 – 필터링된 병합 게시에 대한 파티션 미리 계산 – 필터링된 병합 게시에 대한 새로운 분할 옵션 – 병합 게시에 대한 새로운 구독자 업로드 옵션 – 병합 복제의 향상된 BLOB 배달 기능

복제 모니터 다음과 같은 내용을 모니터링 – 복제 시스템이 정상적으로 작동 중인가 ? – 트랜잭션 구독이 얼마나 오래 되었나 ? – 에이전트가 실행되지 않는 이유는 무엇인가 ? – 트랜잭션 복제 시 지금 커밋된 트랜잭션이 구독자에 도달하기까지 얼마나 걸리는가 ? – 내 병합 구독이 왜 느린가 ? – 어떤 구독이 느린가 ?

복제 모니터 cont.

경고 및 임계값 설정 성능 조건에 대한 경고 설정 가능 임계값에 도달하면 복제 모니터에 경고, 알림 신호를 트리거 할 수 있다 다음 성능 조건에 대해서 경고 설정 가능 – 지정한 대기 시간이 초과된 경우 ( 트랜잭션 복제 ) – 지정된 동기화 시간이 초과된 경우 ( 병합 복제 ) – 지정된 시간 내에 지정된 수의 행을 처리하지 못한 경우 ( 병합 복제 )

경고 및 임계값 설정 cont.

추적 프로그램 토큰 연결 유효성 검사 트랜잭션 복제를 사용하는 시스템의 대기 시간을 측정 – 게시자에서 커밋된 트랜잭션과 배포자의 배포 데이터베이스에서 삽입된 해당 명령 사이의 경과 시간 – 배포 데이터베이스에 삽입된 명령과 구독자에서 커밋된 해당 트랜잭션 사이의 경과 시간

추적 프로그램 토큰 cont.

복제 Topology 병합, 양방향, P2P 복제 병합, P2P 복제 Bus Topology Ring Topology 모든 복제 Directed Topology 즉시 업데이트를 제외한 모든 복제 Republishing Topology 복제의 결합 Mixed Topology

양방향 복제 (Bus Topology) Log Reader Agent Distribution Agent Log Log Reader Agent Distribution Agent 전달 안됨 게시자 A 배포자 B 구독자 C 배포자 D Originator = '' Originator = ' A ' Originator = Publisher

Log Reader Agent 양방향 복제 (Ring Topology) Log Reader Agent Distribution Agent Log Log Reader Agent Distribution Agent 게시자 A 배포자 B 구독자 C 배포자 D Originator = '' Originator = ' A ' 구독자 E Distribution Agent 배포자 F Originator = ‘ C ' 무한 루프 Originator != Publisher

Log Reader Agent Peer-to-Peer 복제 (Ring Topology) Log Reader Agent Distribution Agent Log Log Reader Agent Distribution Agent 게시자 A 배포자 B 구독자 C 배포자 D Originator = '' Originator = ' A ' 구독자 E Distribution Agent 배포자 F Originator = ‘ A ' 전달 안됨 Originator = Publisher

Peer-to-Peer 복제 개념 – 각각의 노드가 게시자이자 구독자 – 서로 다른 노드끼리 1:1 복제 구성 –Peer-to-Peer 네트워크 토폴로지와 유사 – 다른 서버들은 “ 그들의 ” 변경된 내용을 수집해서 받음 –" 일반적인 " 트랜잭션 복제처럼 계층이 아님 – 데이터 “ 주인 ” 은 전적으로 로컬에 있음 ; 충돌을 방지하지 않음 ( 디자인 시 충돌에 대한 고려 필요 ) –SQL Server 는 양방향으로의 변경을 방지함 –Enterprise Edition 에서만 사용 가능

Peer-to-Peer 복제 cont. 장점 – 데이터베이스 어플리케이션에 대한 고 가용성과 수평 확장 능력 제공 – 단일 실패 지점의 문제 해결 – 온라인 상태에서의 유지 관리 작업 – 쿼리에 대한 데이터베이스 간 확장 기능 제공

Peer-to-Peer 복제 구성도 서울 뉴욕 파리 데이터 복제 Read/Write

일치하지 않는 두 테이블의 데이터 비교 명령줄 유틸리티 행 개수와 스키마만 비교 여러 대상 서버의 테이블을 동시에 비교 가능 Tablediff 유틸리티

유용한 SP sp_requestpeerresponse – 노드를 하나씩 걸러서 응답을 요청 sp_helppeerresponses – 피어에게서 받은 특정 상태 요청에 대한 모든 응답을 반환 sp_helpsubscriptionerrors – 지정된 구독에 대한 모든 트랜잭션 복제 오류를 반환 sp_setsubscriptionxactseqno – 문제 해결 도중 구독자의 배포 에이전트에 의해 적용될 다음 트랜잭션의 LSN 을 지정하는 데 사용 – 에이전트에서 실패한 트랜잭션을 건너뛸 수 있게 해줌

참고 자료 Microsoft SQL Server 2005 Books Online Microsoft SQL Server Homepage – Microsoft TechNet – 『 Microsoft SQL Server 2005 포켓 컨설턴트 관리자용』 William R. Stanek, 필라넷 DB 사업부 | 정보문화사

감사합니다