SQL Server 교육

Size: px

Start display at page:

Download "SQL Server 교육"

승만 강
6 years ago
Views:

1 Database Mirroring 을활용한데이터이중화방안 에이디컨설팅

2 교육내용 고가용성개요 데이터베이스미러링 개요및구조 구축시고려사항 구축방법 장애조치 상태확인 SQL Server 2012 Always-On - 2 -

3 고가용성의목적 다운타임을줄이려는노력 계획되지않은다운타임 하드웨어오류 ( 스토리지오류, 네트워크지연 ) 시스템응답지연 자연재해 사용자실수 계획된다운타임 하드웨어 / 소프트웨어 / 운영체제등의업그레이드 시스템개편 마이그레이션 - 3 -

4 고가용성개요 (1) SQL Server 의주요고가용성기능 SQL Server 에서기본적으로제공하는고가용성기술 1. 장애조치클러스터 2. 로그전달 3. 복제 4. 데이터베이스미러링 5. Always-On (SQL Server 2012에서도입 ) - 4 -

5 고가용성개요 (2) SQL Server 의주요고가용성기능비교 가용성특징 Hot Standby Database Mirroring Warm Standby MSCS Log Shipping Replication 데이터손실 없음 (HA) 없음 발생가능 ( 마지막트랜잭션 ) 자동장애조치 있음 (HA) 있음 없음 ( 사용자조치필요 ) 발생가능 ( 복제주기에종속적임 ) 없음 ( 사용자조치필요 ) 장애조치시간 3초미만 30초 + 로 그복원 로그복원 + α 가용성범위데이터베이스서버데이터베이스개체 로그복원 + α 대기서버접근 데이터베이스스냅샷으로가능 안됨 가능 ( 복원중은불가능 ) 가능 5

6 데이터베이스미러링이란? 데이터베이스미러링 데이터베이스가용성을높이기위한 SQL Server 의주요솔루션 SQL Server 엔진의서로다른서버인스턴스간에특정데이터베이스복사본유지 주서버데이터베이스의트랜잭션을미러서버의데이터베이스로전달 주서버의데이터베이스는클라이언트에게서비스를제공하며미러서버는상시대기 (Hot) 또는웜대기 (Warm) 서비스를제공 - 6 -

7 데이터베이스미러링구성요소 (2) 주데이터베이스 (principal database) 읽기쓰기모드의데이터베이스로써해당데이터베이스의트랜잭션로그가읽기전용모드의미러데이터베이스로전달 미러데이터베이스 (mirror database) 주데이터베이스와동기화된읽기전용의복사본데이터베이스 주 / 미러서버 (principal/mirror server) 주 / 미러데이터베이스를운영하는서버인스턴스 모니터서버 (witness server) 보호우선모드에서자동장애조치 (automatic failover) 가필요할때선택적으로구성하는서버. 클라이언트에데이터베이스를제공하지않음. 끝점 (end point) SQL Server 과네트워크를통해통신할수있도록하는개체 서버인스턴스의모든미러링연결은미러링끝점을통해통신 - 7 -

8 데이터베이스미러링구성 기본구성 - 8 -

9 데이터베이스미러링구성 자동장애조치가있는미러링구성 미러링모니터서버 주서버 미러서버

10 트랜잭션보안및운영모드 트랜잭션보안옵션 ( 데이터베이스속성 ) SAFETY 옵션 FULL 보호우선모드 데이터베이스미러링작업이동기적으로수행됨 트랜잭션이주서버와미러서버에동시에커밋됨 클라이언트대기시간이증가할수있음 모니터링서버구성여부선택가능 SAFETY 옵션 OFF 성능우선모드 데이터베이스미러링작업이비동기적으로수행됨 주서버의트랜잭션커밋은미러서버의커밋을확인하지않음 강제장애조치시데이터손실가능

11 데이터베이스미러링장애조치 안전모드와장애조치옵션 안전모드 모니터링서버 작업모드 지원가능한장애조치 FULL 존재 자동장애조치의보호우선모드 ( 동기 ) 자동 / 수동 FULL 존재하지않음 4 월 12 일에 보호우선모드 ( 동기 ) 수동 OFF 해당사항없음 성능우선모드 ( 비동기 ) 강제서비스수행 ( 데이터손실가능 )

12 데이터베이스미러링끝점 끝점 Microsoft SQL Server 2005 이상의연결관리 SQL Server 가네트워크를통해통신할수있게하는개체 데이터베이스미러링끝점 모든미러링연결은하나의데이터베이스미러링끝점사용 TCP 를사용하여데이터베이스미러링세션의서버간메시지전송및수신

13 데이터베이스미러링인증 데이터베이스미러링끝점간연결에는인증이필요함 인증유형 SQL Server 서비스계정 - 트러스트된도메인의도메인사용자 - 네트워크서비스 - 서버가트러스트된도메인에없는경우 - 로컬서비스 인증방법 Windows 인증 : Windows 사용자계정의자격증명을사용하여연결의양끝인증 인증서인증 : 미러링끝점은로컬에서만든자체인증서로구성

14 데이터베이스미러링의장점 미러된데이터베이스를운영중인데이터베이스에대해동기적또는비동기적으로최신정보를유지시켜줌 자동또는수동으로미러된데이터베이스로의장애조치가능 운영서버와물리적으로떨어진위치에미러링용도의서버를둘수있어 DR(Disaster Recovery) 구성가능 데이터의중복저장으로데이터보호 데이터베이스가용성증가 구성에따라재해발생시장애조치 (Failover) 가능 데이터베이스미러링에참여하는서버업그레이드시서비스중단최소화

15 미러데이터베이스액세스 미러데이터베이스의활용가능한용도 주데이터베이스에부하를주지않는데이터분석및보고작업 미러데이터베이스액세스 클라이언트가직접미러데이터베이스에액세스할수없음 데이터베이스스냅숏 유의사항 미러데이터베이스에서데이터베이스스냅숏생성 데이터베이스스냅숏은클라이언트가직접액세스가능 읽기전용 데이터베이스스냅숏생성시점까지의데이터사용가능 데이터베이스스냅숏의수가너무많으면주데이터베이스의성능이저하될수있음

16 데이터베이스스냅숏 데이터베이스의읽기전용정적뷰 하나의원본데이터베이스에여러개의데이터베이스스냅숏가능 스냅숏을만든시점의원본데이터베이스와동일 데이터베이스페이지수준에서작동 스냅숏생성후최초로원본데이터베이스의변경작업이일어나면 쓰기시복사 작업이이루어지며스냅숏은생성시점의데이터를유지 스냅숏은최초에빈스파스파일을생성하고데이터변경이이루어질수록계속해서파일의크기가증가

17 데이터베이스미러링고려사항 (1) 고려대상구성요소 미러서버와미러링모니터서버는주서버와같은버전의 SQL Server 를실행해야함 비동기데이터베이스미러링은 Enterprise 에디션이상에서지원 미러데이터베이스는 NORECOVERY 옵션을사용하여주데이터베이스와동일한이름으로생성 고려사항 시스템데이터베이스는미러링할수없음 전체복구모델데이터베이스만미러링가능 FILESTREAM 파일그룹이포함된데이터베이스에대해데이터베이스미러링을구성할수없음 32 비트시스템에서는서버당최대 10 개의데이터베이스에대해미러링구성지원가능 지원하지않는트랜잭션 데이터베이스간트랜잭션 분산트랜잭션

18 데이터베이스미러링고려사항 (2) 자동장애조치도입시고려사항 가용한 bandwidth, 주서버와미러서버사이의 latency 가허용범위내에있지않은경우 동기방식 : 주서버에서발생한로그데이터를미러서버에전달하여미러서버의로그드라이브에쓰여진후에비로소주서버에커밋발생 만약주서버에트랜잭션시간에상당한영향을준다면동기방식을사용하기힘드며자동장애조치도사용할수없음 어플리케이션에서사용하는데이터베이스개수가 2 개이상인경우 어플리케이션이정상작동하기위해다수의데이터베이스가필요한경우자동장애조치는적합하지않음 하나의데이터베이스만자동장애조치가되는경우어플리케이션에서커넥션을주서버와미러서버를동시에할수없기에정상적으로작동하지않음

19 데이터베이스미러링고려사항 (3) 자동장애조치도입시고려사항 (Continued) 장애조치가어플리케이션다운타임을수반하는경우 장애조치이후 fail back 하려고하는시점이발생. 이때어플리케이션다운타임이다시한번발생함

20 데이터베이스미러링권장사항 주서버와미러서버는동등한사양의시스템권장 장애조치시동등한서비스품질보장 가능하면주데이터베이스와미러데이터베이스의보관경로는일치시킴 미러링세션의모든서버에서같은마스터코드페이지와데이터정렬사용 장애조치에걸리는시간을예상하여현재시스템구성이필요한성능을제공하는지확인 미러링전용네트워크어댑터사용 보호우선모드로구성시, 비동기성능우선모드로구성을시작해서네트워크및어플리케이션성능을고려해서보호우선모드로전환하는것을고려함

미러링적용시성능 인덱스생성시미러링적용여부의성능 출처 : http://technet.

21 미러링적용시성능 인덱스생성시미러링적용여부의성능 출처 :

22 미러링적용시성능 (2) 인덱스리빌드의미러링적용전후의성능

23 미러링적용시성능 (3) 인덱스생성시미러링 Send, Redo 큐대기 생성완료후큐대기 0KB 로떨어지기까지 3 분 40 초

24 미러링적용시성능 (4) BULK INSERT 작업시에일괄처리에포함될행수 (BATCHSIZE 옵션 ) 조절에따른미러링동기화성능

25 데이터베이스미러링구성

26 데이터베이스미러링구성방법 SQL Server Management Studio 에서데이터베이스미러링을구성하는방법 1) 주서버에연결 2) 개체탐색기에서 [ 데이터베이스 ] 를확장하고미러링할데이터베이스선택 3) 데이터베이스를마우스오른쪽단추로클릭하고 [ 태스크 ] 를선택한다음 [ 미러 ] 클릭 4) [ 보안구성 ] 을클릭하여데이터베이스미러링보안구성마법사시작 5) 데이터베이스미러링보안구성마법사를사용하면자동으로각서버인스턴스에데이터베이스미러링끝점이생성되고 ( 없는경우 ) 서버인스턴스의역할 ( 주서버, 미러서버또는미러링모니터서버 ) 에해당하는필드에서버네트워크주소가입력됨 6) 선택적으로운영모드변경 7) [ 미러링시작 ] 클릭 8) [ 확인 ] 클릭

27 데이터베이스미러링환경설정 사전설정 SQL Server 서비스계정은도메인계정으로수행 데이터베이스미러링에관련된모든서버의 TCP 5022 포트를사용가능한지확인 ( 또는사용하기로결정한포트 ) 예 : 방화벽사용의경우 데이터베이스미러링계정및그룹에 Access this computer from the network 권한설정 주서버의로컬보안정책에서설정 주서버 복구모델확인 전체복구모델 미러서버 미러데이터베이스복원 최소 1 개의로그파일을복원하여로그파일경로확인 주서버와동일한 SQL Server 로그인과사용자를동일한 SID 로생성 끝점생성

28 데이터베이스미러링자동장애조치단계 자동장애조치단계 주데이터베이스에오류발생 오류감지 미러서버및모니터링서버에의해감지 ALTER DATABASE SET PARTNER TIMEOUT 로 timeout 설정가능 오류감지시간은 10 sec 이상의설정을권고 기본적인제한시간 : 10 sec 10 sec 미만일경우무거운쿼리가수행될경우가오류로감지될가능성있음 미러서버의 REDO 큐에남아있는트랜잭션로그적용완료 장애조치여부결정 미러서버가주서버로전환 모든 REDO 큐의트랜잭션로그를적용한후에주서버로전환 전환된주서버로연결하여운영재시작 UNDO 커밋되지않은트랜잭션은롤백됨

29 데이터베이스미러링강제서비스 강제서비스 ( 데이터손실가능 ) 주서버와연결이끊어진경우데이터손실을감수하고보조서버로의 강제서비스 적용 데이터손실을감수하더라도즉시서비스를재개해야하는경우에만적용고려 주서버가미러서버와연결은끊겼지만일부클라이언트와연결이가능한경우양서버모두업데이트에노출 강제서비스적용방법 1 미러서버에연결 2 다음쿼리문실행 ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS; 3 미러서버는주서버로전환되고미러링은일시중단됨

30 미러된데이터베이스환경에서의어플리케이션구성 어플리케이션에서미러된데이터베이스를접근하기위해서필요한연결문자열구성 미러된데이터베이스에대한연결문자열에장애조치파트너지정이필요함 장애조치시 연결다시시도 알고리즘에의해주서버의파트너미러서버로연결시도 연결문자열의예 ( 부분 ) Server=ServerA; Failover_Partner=ServerB; Database=DBname 장애조치파트너특성 API 장애조치파트너특성키워드 OLE DB 공급자 ODBC 드라이버 ADO(ActiveX Data Objects) FailoverPartner Failover_Partner Failover Partner

31 데이터베이스미러링제거방법 SQL Server Management Studio 에서데이터베이스미러링을구성하는방법 1) 주서버에연결 2) 개체탐색기에서 [ 데이터베이스 ] 를확장하고미러링할데이터베이스선택 3) 데이터베이스를마우스오른쪽단추로클릭하고 [ 태스크 ] 를선택한다음 [ 미러 ] 클릭 4) [ 페이지선택 ] 에서 [ 미러링 ] 클릭 5) [ 미러링제거 ] 클릭 6) 확인메시지에서 [ 예 ] 클릭

32 데이터베이스미러링상태모니터링 (1) 데이터미러링상태모니터링도구 데이터베이스미러링모니터 시스템저장프로시저 성능카운터

33 데이터베이스미러링상태모니터링 (2) SQL Server Management Studio 에서데이터베이스미러링모니터를시작하는방법 1) 주서버에연결 2) 개체탐색기에서 [ 데이터베이스 ] 를확장하고모니터링할데이터베이스선택 3) 데이터베이스를마우스오른쪽단추로클릭하고 [ 태스크 ] 를선택한다음 [ 데이터베이스미러링모니터시작 ] 클릭

34 데이터베이스미러링상태모니터링 (3) 시스템저장프로시저 저장프로시저 sp_dbmmonitoraddmonitoring sp_dbmmonitorchangemonitoring sp_dbmmonitorhelpmonitoring sp_dbmmonitorresults sp_dbmmonitordropmonitoring 설명 모든미러된데이터베이스에대한상태정보를정기적으로업데이트하는작업생성 데이터베이스미러링모니터링매개변수값변경 데이터베이스미러링상태테이블에대한업데이트간격 ( 분 ) 을반환 모니터링한데이터베이스에대한상태반환 모든데이터베이스에대한미러링모니터작업을중지하고삭제

35 데이터베이스미러링상태모니터링 (4) 성능카운터 주서버 Log Bytes Sent/sec Log Send Queue KB Transaction Delay Transactions/sec Log Bytes Flushed / sec Logical Disk\Disk Write Bytes/sec 미러서버 Redo Queue KB Redo Bytes/sec Logical Disk\Disk Write Bytes/sec

36 SQL Server 2012 Always-On 가용성그룹도입 미러링 + 클러스터링 + 로그전달의기능을결합 / 향상시킴 복수개의데이터베이스를함께장애조치 읽기가능보조서버 (Active Secondary) 미러링보조서버의데이터베이스읽기전용모드지원 부하분산효과를위한활용 복수개의동기화모드보조서버지원 2 개의동기화모드보조서버지원 ( 자동장애조치는 1 대만가능 ) 멀티사이트장애조치 Subnet 이다른서버의클러스터링지원

37 감사합니다

Windows 8에서 BioStar 1 설치하기

Windows 8에서 BioStar 1 설치하기 / 콘텐츠 테이블... PC에 BioStar 1 설치 방법... Microsoft SQL Server 2012 Express 설치하기... Running SQL 2012 Express Studio... DBSetup.exe 설정하기... BioStar 서버와 클라이언트 시작하기... 1 1 2 2 6 7 1/11 BioStar 1, Windows 8 BioStar