Transcript SQL2k5-미러링.ppt

SQL Server 2005
미러링
황순영
필라넷 / DB 사업부 / 책임 컨설턴트
KNUG 부시삽
목표
•
•
•
•
•
SQL Server 2005의 고가용성 이해
미러링 이해 및 구현
미러링 사용 응용 프로그램 방법 이해
미러링 모니터링 및 유지보수 방법 이해
DB 스냅숏 이해 및 구현
목차
•
I.
II.
III.
IV.
SQL Server 2005의 고가용성 비교
미러링
모니터링
DB 스냅숏
미러링 지원 프로그래밍
SQL Server 2005의 고가용성 비교
복제
로그 전달
장애방지
클러스터링
데이터베이스
미러링
데이터 유실
가능성
Y
(복제 주기에 종속)
Y
(마지막 트랜잭션)
N
N
자동 장애 조치
N
(사용자 조치필요)
N
(사용자 조치 필요)
Y
Y
다운 시간
로그 복원 + α
로그 복원 + α
< 30 초 + 로그 복원
<3초
대기 서버
읽기 작업
Y
Y
N
Y
(스냅숏 사용)
장애 방지의
범위
사용자 DB
사용자 DB
시스템 & 사용자 DB
사용자 DB
클라이언트에
대한 투명성
N
N
Y
(동일한 IP로 다시 연결)
Y
(자동 리디렉션)
서버간의 거리
무제한
무제한
약 160 Km
무제한
하드웨어
요구사항
표준서버
표준서버
클러스터 인증서버
및 저장
표준서버
비고
주 서버로
자동 전환
I. 미러링(Mirroring)
미러링 이란?
• 데이터베이스 미러링은 가용성을 증가시키기 위한 SQL
Server 2005의 새로운 기술이다.
• 트랜잭션 로그 레코드를 한 서버에서 다른 서버로 직접
전송하고, 장애 발생시 신속하게 장애 조치가 이루어 진다.
• 응용 프로그램에서는 연결 정보를 자동으로 재전송하고,
장애 발생시 미러 서버와 데이터베이스에 자동으로 연결할
수 있는 기술이다.
• 장애 발생시 데이터 손실이 적다.
• 전용 하드웨어가 필요 없다.
• 응용 프로그램 수정이 적다.
• 설치 및 관리가 용이하다.
SQL Server Edition 별 지원
Edition
Enterprise
Developer
Standard
파트너
●
●
●
감시서버
●
●
●
안전성(FULL)
●
●
●
안전성(OFF)
●
●
장애조치 후 UNDO
동안 사용여부
●
●
병렬 재실행(REDO)*
●
●
데이터베이스 스냅숏
●
●
특징
Workgroup
Express
●
●
●
*병렬 재실행(REDO) : 미러 데이터베이스에서 트랜잭션 로그를 재생하는데 여러 스레드 사용
대상 버전
• SQL Server 2005 Service Pack 1
– 별도 시작 옵션 없이 사용 가능
• Service Pack 1 미적용 시
– T1400 옵션을 사용해서 서비스 시작
– 기능 평가용으로만 사용 가능
미러링 종류
보안수준
(SAFETY)
감시서버
비동기
FULL
OFF
필수
없음
없음
가용성 우선
보호 우선
성능 우선
(High Availability)
(High Protection)
(High Performance)
자동
지원
없음
없음
수동
지원
지원
강제
작동 모드
장애
조치
동기
미러링 종류
- 계속
설명
가용성 우선
(High Availability)
보호 우선
(High Protection)
성능 우선
(High Performance)
장점
단점
•주서버 장애 발생시 미러 서버로
자동으로 장애 조치하여 최대한의
가용성을 지원
•쿼럼 구성
•자동 조치
•미러서버에 대한
응답시간 필요
•감시 서버가 없는 구조
•쿼럼 구성
•수동 장애 조치
•가용성 우선에서의 전환 상태 이외에는
사용하지 않는 것이 좋음
•미러 서버의 응답을 기다리지
않음
•쿼럼은 필요 없음
•수동 강제 장애 조치
•장거리 구성 시 유리
•주 서버에서 일부
트랜잭션 로그 블록을
받지 못한 경우
복구되면 미러 서버에
데이터 손실이 발생할
수 있다.
미러링 프로세스
• 동기
감시 서버
(Witness
Server)
Commit
미러링을 이용한
트랜잭션 처리 방법
(SAFETY = FULL)
Acknowledge
Acknowledge
미러 서버
(Mirror Server)
주 서버
(Principal
Server)
Transmit to Mirror
Write to Local Log
DB
Committed in Log
Log
Write to Remote Log
Log
DB
※ 쿼럼 : 현재 서로 통신하고 있는(CONNECTED 상태) 서버 인스턴스 사이에 존재하는 관계
미러링 프로세스
- 계속
• 비동기
미러링을 이용한
트랜잭션 처리 방법
(SAFETY = OFF)
Commit
Acknowledge
미러 서버
(Mirror Server)
주 서버
(Principal
Server)
Transmit to Mirror
Write to Local Log
DB
Committed in Log
Log
Write to Remote Log
Log
DB
SQL Server 서비스 설정
• SQL Server 서비스 시작 계정 설정
– 대부분의 개발 및 운영 서버의 경우 “로컬 시스템”으로 설정되어져
있는 경우가 많음
– 이 경우 동일 서버 인스턴스간의 미러링 구성에는 문제가 없으나,
외부 서버간 미러링 구성 시 로그인 문제가 발생
• 설정 방법
–
–
–
–
동일한 서비스 구동용 계정 생성
계정에 대한 시스템 권한 부여
서비스 시작 계정으로 등록
미러링 구성용 서버에 동일하게 구성
• 차후 미러링 구성 시 계정을 추가로 생성해야 하는 단계 생략 가능
SQL Server 서비스 설정
– 계속
• SQL Server Agent
– 주 서버, 미러 서버에 서비스 실행
– 미러 모니터를 위한 로그 저장
• 기본 매 1분 간격
– Agent 가 정지 중인데도 정상적으로 기록이 저장되고 있음
SQL Server 서비스 설정
– 계속
• 파트너 서버는 동일하게 구성
– CPU, 메모리, 스토리지, 네트워크
Windows와 SQL Server에 대한 동일한 Service Pack, 패치
파트너간 동일한 Edition 사용
추적 플래그, 시작 옵션, 메모리 세팅 등 동일 구성
로그인, 권한 동일 구성
Agent, 경고, SSIS 패키지, 관련 DB, 연결된 서버 정의, 백업
장치, 유지보수계획 등 동일 구성
• 디스크 추가 시 동일하게 추가
• 서버간 디렉터리 동일하게 구성
•
•
•
•
•
장비 주의 사항
• 미러링 구성 서버간에 네트워크 트래픽이 많이 발생하게 됨
• 가용성 우선 동기화 구성의 경우 네트워크 비중이 크게 작용
• 미러링 구성의 최상의 성능을 위해서 미러링 전용
NIC(Network Interface Card)를 사용
방화벽 설정
• 대부분의 DB 시스템은 Private Network안에 구성
– 앞단에 방화벽 설치
• 윈도우 자체 방화벽 설정
• 동일 구역 안에서 구성할 경우에는 방화벽 문제가 없으나,
외부 장비간의 미러링 구성의 경우 방화벽에서 EndPoint에서
사용하고 있는 포트에 대한 방화벽 설정이 필요함
EndPoint
• 통신하기 위한 서비스
• 소켓?
• 미러링 정보 통신은 일반 DB 포트가 아닌 EndPoint을
이용해서 수행
• 미러링 구성을 위한 EndPoint는 인스턴스 당 하나만 설정
가능
• 동일 시스템에서 동일한 포트 번호 사용 안됨
– 인스턴스 구현 시 주의
EndPoint
- 계속
• DMV를 이용해서 설정되어져 있는 미러링 정보를 조회
SELECT *
FROM sys.endpoints
WHERE type_desc = 'DATABASE_MIRRORING'
;
보안주체 ID
이름
ID
프로토콜
프로토콜 설명
Payload 유형
Payload 설명
상태
상태 설명
관리자용
인지 여부
EndPoint
- 계속
• Netstat를 이용해서 확인해 보면 대기 상태의 해당 포트 확인
C:\>NETSTAT –an –p TCP
시스템 적용 순서
•
•
시스템 초기에 적용하는 것이 좋음
운영 시스템 적용 순서
1. 서버에 EndPoint 및 계정 생성
2. DB 복구 모델 “전체(FULL)”로 변경
3. 운영중 : 원본 DB 전체 백업
4. 운영중 : 대상 DB 전체 복원(WITH NORECOVERY)
5. 운영중 : 차등 or 트랜잭션 백업
6. 운영중 : 대상 DB 차등 or 트랜잭션 복원(WITH NORECOVERY)
7. …
8. 서비스 차단
9. 원본 DB 트랜잭션 백업
10. 대상 DB 트랜잭션 복원(WITH NORECOVERY)
11. 미러링 구성
12. 장애 조치 작동 테스트
13. 서비스 시작
※ 두 DB의 기준이 동일하지 않을 경우 미러링이 안될 수 있음
장애 조치
ALTER DATABASE Test SET PARTNER FAILOVER
• FULL 안전모드, 수동, 주서버에서 구동
ALTER DATABASE Test SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
• 주서버 다운, 동기화 되지 않은 상태에서 강제 복구
– 미러서버에서 실행, 감시 서버 정지
– 미러/감시 서버 쿼럼 구성시
– 강제 복구 이후 주서버 정상 복원 시 주서버가 미러 상태 전환되고,
일시 정지 상태 유지
– 상태 정상화를 위해서 구문 실행
ALTER DATABASE Test SET PARTNER RESUME
• SSMS
장애 조치
•
•
•
•
•
•
•
- 계속
수동 장애조치(ALTER DATABASE …)
SHUTDOWN WITH NOWAIT
주서버 시스템 종료
주서버 서비스 정지
주서버 전원 차단
네트워크 분리
…
성능
• 미러링 구성 시 주서버에 10%정도의 CPU 추가 부하 발생
• 주 서버와 미러 서버간에 데이터 전송을 위한 네트워크
트래픽 추가 발생
• 네트워크 속도 제약이 없을 경우 응답 시간은 거의 차이가
나지 않음
미러링 C P U 부하량
100
90
80
미러링
적용
P ro cess(% )
70
60
50
미러링
미적용
40
30
20
10
0
1
2
3
4
5
6
7
8
9
10
11
소요시간
12
13
14
15
16
17
18
Demo
• 미러링 구성
– T-SQL
– UI
• 장애 조치
구성
• 거리 제약이 없기 때문에 대륙간 구성도 가능
– 지진 등의 자연 재해 대비 가능
– 스냅숏 이용 시 읽기 전용에 대한 현지 로컬 서비스 가능
P
M
W
구성
- 계속
• 추가적 구성 가능
– 복제
– 장애조치 클러스터링
– 로그 전달
• 추가 구성 작업 시 사전에 충분한 테스트가 필요
• 원거리 구성 시 서버를 직접 보기 위해서 hosts 파일에 등록
– C:\windows\system32\drivers\etc\hosts
192.168.1.100
Pserver
192.168.1.101
Mserver
192.168.1.102
Wserver
여러 DB 미러링
• 동일한 미러링 구성에서 여러 개의 DB에 대해 설정 가능
• EndPoint는 동일하게 이용
– 하나의 설정이 구성되면 이를 이용해서 DB만 설정 가능
• 각 DB의 장애조치는 별도로 조작됨
장애조치
미러링 제거
• 설정되어져 있는 미러링을 제거한다
– 제거 순서는 생성의 반대 순서로…
• 작업 순서
–
–
–
–
장애조치를 사용해서 주서버로 전환
주서버에서 미러링 제거
미러 서버의 DB 제거
주서버/미러서버/감시서버 EndPoint 제거
미러링 제거
– 계속
• SSMS
• T-SQL
ALTER DATABASE [Test] SET SAFETY OFF;
ALTER DATABASE [Test] SET WITNESS OFF;
ALTER DATABASE [Test] SET PARTNER OFF;
DROP ENDPOINT EndPoint_Principal;
II. 모니터링(Monitoring)
미러링 모니터
• SSMS>DB명>작업>데이터 베이스 미러링 모니터 시작(O)
• 미러링 서버 등록 후 사용
DMV
• sys.database_mirroring
– SQL Server 의 인스턴스에 있는 각 데이터베이스에 대해 한 행을
포함합니다. 데이터베이스가 ONLINE이 아니거나 데이터베이스
미러링이 설정되어 있지 않으면 database_id를 제외한 모든 열의
값이 NULL이 됩니다.
SELECT d.name, d.database_id, m.mirroring_role_desc
,m.mirroring_state_desc, m.mirroring_safety_level_desc
,m.mirroring_partner_name, m.mirroring_partner_instance
,m.mirroring_witness_name, m.mirroring_witness_state_desc
FROM sys.database_mirroring AS m
INNER JOIN sys.databases AS d
ON m.database_id = d.database_id
WHERE mirroring_state_desc IS NOT NULL
현재 서버의
미러링 역할
DB명
DB ID
미러링 상태
컴퓨터명 &
인스턴스명
보안수준
파트너
서버 명
감시서버
상태
감시서버 명
DMV
- 계속
• sys.database_mirroring_endpoints
– 서버 인스턴스의 데이터베이스 미러링 끝점에 대해 한 행을
포함합니다.
SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc
,e.state_desc,t.port, e.is_encryption_enabled
,e.encryption_algorithm_desc,e.connection_auth_desc
FROM sys.database_mirroring_endpoints AS e
INNER JOIN sys.tcp_endpoints AS t
ON e.endpoint_id = t.endpoint_id
미러링
역할
EndPoint
프로토콜
EndPoint
페이로드 유형
암호화
알고리즘
EndPoint
포트번호
EndPoint
상태
암호화여
부
EndPoint
연결
인증유형
DMV
- 계속
• sys.database_mirroring_witnesses
– 서버가 데이터베이스 미러링 파트너 역할을 하는 각 모니터 역할에
대한 행을 포함합니다.
SELECT principal_server_name, mirror_server_name
,database_name, safety_level_desc
FROM sys.database_mirroring_witnesses
DB 명
주 서버
미러 서버
보안 수준
DMV
- 계속
• sys.dm_db_mirroring_connections
– 각 데이터베이스 미러링 네트워크 연결에 대해 하나의 행을
반환합니다.
SELECT principal_server_name, mirror_server_name
,database_name, safety_level_desc
FROM sys.database_mirroring_witnesses
로그인
성공 일시
연결 시작
일시
데이터 전송
지연 여부
EndPoint에서
수신한 총
바이트 수
EndPoint에서
전송한 총
바이트 수
SQL Server Profiler
• 이벤트 번호 167번 사용
• Database\Database Mirroring State Change 선택
성능 모니터
• SQLServer:Database Mirroring 개체
• 주 서버:
–
–
–
–
–
–
Log Bytes Sent/sec
Log Send Queue KB
Transaction Delay
Transactions/sec
Log Bytes Flushed/sec
Disk Write Bytes/sec
• 미러 서버:
– Redo Bytes/sec
– Redo Queue KB
– Disk Write Bytes/sec
Demo
•
•
•
•
미러링 모니터
DMV
Profiler
성능 모니터
III. DB 스냅숏
DB 스냅숏?
• 해당 DB의 당시 상황을 그대로 구성
• 원본 데이터 변경에도 스냅숏으로 구성된 DB는 스냅숏 구성
당시의 데이터 유지
• 읽기 전용 DB
• 실 사용 공간 절약
– 예로 100GB DB를 스냅숏으로 구성할 경우 100GB의 스냅숏 DB가
만들어 지나, 실재 사용 공간은 수 KB만 유지됨
– 원본 데이터 변경 시 이전 데이터 유지를 위해 이전 정보가 저장됨
• 스냅숏 DB를 이용해서 원본 DB 복원가능
• 반드시 동일 인스턴스에 구성
• 한 DB에 대해서 여러 개의 스냅숏 구성 가능
DB 동작
• 기본적인 데이터는 원본 DB 사용
• UPDATE/DELETE 작동 시 원본의 데이터가 스냅숏 DB로
이동, 이후 스냅숏 DB의 정보를 읽어 들임
원본 DB
스냅숏DB
SELECT …
UPDATE…
or
DELETE …
Copy-On-Write
SELECT …
미러링 적용?
• 미러 서버의 경우 DB가 NORECOVERY 상태이기 때문에
사용할 수 없음
• 미러 서버의 DB를 이용해서 부하 분산 구성시 사용 가능
• 미러 구성된 DB에 대해서 스냅숏을 구성하면 해당 시점의
정보를 그대로 사용 가능
• 단, 스냅숏 작성된 시점 이후의 변경된 내용은 적용 안되기
때문에 계속적인 실시간 정보를 사용하기 위해서는 필요할
때 마다 계속적으로 스냅숏을 구성해야 함
구문
• 스냅숏 작성 구문
• 구문 구성시 트랜잭션 로그를 제외한 모든 파일 그룹 지정
CREATE DATABASE [DB_SS] ON
(
NAME
= DB_Data
,
FILENAME = 'C:\SQLDATA\DB_Data_SS.ss'
),
(
NAME
= DB_Index
,
FILENAME = 'D:\SQLDATA\DB_Index_SS.ss'
)
AS SNAPSHOT OF [DB];
구문
- 계속
• 설정되어져 있는 스냅숏 삭제
DROP DATABASE [DB_SS];
• 스냅숏 DB를 이용한 원본 DB 복원
RESTORE DATABASE [DB]
FROM DATABASE_SNAPSHOT=‘DB_SS';
Demo
• DB 스냅숏 구성
• 읽기 전용 사용
• 미러링 적용
IV. 미러링 지원 프로그래밍
개요
• ADO.NET 2.0에서 제공되는 SQL Native Client을 통해서 만
자동 전환 기능 지원
• 연결 문자열 수정
Data Source=주서버;Failover Partner=미러서버;Initial Catalog=DB명;Integrated Security=True; …
– Failover Partner 부분을 제거 했을 경우 프로그램 구동 시 주 서버에
해당하는 서버가 정지되어져 있을 경우 오류 발생
– Failover Partner 부분이 지정되어져 있으면 프로그램 구동 시
주서버에 해당하는 서버 상태 확인 후 문제가 있을 경우 파트너
서버로 연결 시도
WinForm With C#
• OLEDB 개체 사용
• 연결 문자열 수정
– Provider=SQLNCLI
– Failover Partner=서버명
• 장애 조 치중 연결 시도 시 예외 발생에 대한 대처
장애조치
사용자 계정
• 응용프로그램에서 사용하는 계정 불일치 문제가 발생
– 별도의 서버에서 동일한 이름의 로그인을 만들어도 SID가 다르기
때문에 문제 발생
– 동일한 이름으로 작성 할 경우 이미 이름이 존재한다는 오류 발생
사용자 계정
– 계속
• 주서버의 user 계정의 sid를 사용해서 미러 서버 로그인 직접
생성(쿼럼이 구성되어져 있던 없던 상관 없음)
EXEC sp_addlogin @loginame = 'user'
,@passwd = 'user'
,@sid = 0x49E70AE63B85ED43ADA4949AC47C834C
• 장애 조치 후 로그인 매핑 정보 확인한 결과 주서버에서와
동일하게 설정
Demo
• 사용자 계정 등록
• 클라이언트 실행
• 장애 조치
참고 자료
• Microsoft SQL Server Homepage
http://www.microsoft.com/korea/sql/default.mspx
• Microsoft SQL Server 2005 서비스 팩 1
http://www.microsoft.com/downloads/details.aspx?displaylang=ko&Fa
milyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc
• Microsoft SQL Server 2005 Express Edition 서비스 팩 1
http://www.microsoft.com/downloads/details.aspx?familyid=11350B1F8F44-4DB6-B542-4A4B869C2FF1&displaylang=ko
• SQL Server 2005 누적 핫픽스(빌드 2153)
http://support.microsoft.com/default.aspx/kb/918222
• 『Microsoft SQL Server 2005 포켓 컨설턴트 관리자용』
William R. Stanek, 필라넷 DB 사업부 | 정보문화사
감사합니다…