Microsoft SQL Server 2005 미러링솔루션구현사례 박혜진 / 수석컨설턴트 컨설팅사업부 마이크로소프트
이주제를이해하는데필요한지식 SQL Server 구조와구현에대한기본지식및 Clustering, Disaster Recovery 에대한기본이해를가지고있어야합니다. 100 개념및 소개수준 200 중갂수준 300 고급수준 400 전문가 수준 200 에 Level 300 선수지식 불필요 100에더하여기술적세부사항설명 더하여능숙한사용경험, 아키텍처지식필요
강사소개 [ 경력 ] ( 구 ) 장기싞용은행 IT실 DBA, SP (Oracle,DB2,Sybase) 국민은행 IT 실 DBA, Data Architect 마이크로소프트 [ 자격증 ] 고객기술지원부 DB Senior Support Engineer &Technical Leader RND SQL/Biztalk/Exchange Server 팀 Test Lead & Test Manager 컨설팅사업부 DB 컨설팅, Data Architect MCSE, MCDBA 정보처리기사 1급
목차 개요 Business Continuity Planning(BCP)/Disaster Recovery(DR) 추진배경 DR 을위한 MS Solution Technology/Positioning MS Solution 기능비교 DBM (Database Mirroring) 구현사례 DBM 모니터링
Business Continuity Planning(BCP)/Disaster Recovery(DR) 추짂배경 Business critical process 보호하고, Unplanned downtime 을최소화하므로써시스템안정화및체계적이고, Cost Effective 한방법으로중단없는비즈니스 (Business Continuity) 를가능하게합니다 기대효과 BCP/DR 를짂행하게만드는요소 비즈니스적도전기술 Enabler 각종규약 / 규제각종재해요소 Service Availability Business agility Customer Satisfaction SLA ROI & TCO IT Infra IT Resource Telecommunication Power Industry specific rule & Regulation Global Standard Natural disasters, Fire Power failure Terrorist Attach/Strike, Legal issues System & equipment failures Human error, Testing Computer viruses 왜각종재난 / 재해상태에서자원의유실범위를최소화하여주요비즈니스가중단없이연속가능하게하는 ( 프로세스, 인적자원, 요구되는가? 기술측면 ) Proactive Planning & Measure 방법론대두 ( 중요 Factor: RTO, RPO) 비즈니스특성상 Downtime 이기대될수있는가? Downtime 시 Biz Impact 이어느정도인가? 재난 / 재해발생시, Process/People/Technology 측면에서의모든 Biz 연속성이가능한가? 복원가능한 Plan 이있는가? 성공적으로테스트된 Recovery Plan 이있는가? 참고 주요 Business 데이터의 Major loss 를경험한기업들중 43% 는재개불가, 51% 는 2 년안에문닫고, 오직 6% 정도만 Long Term 생졲 (Disaster Recovery Planning 에예산의 25% 를투자하는기업들이있는반면 ) Recovery Time Objective (RTO, 복구가능기갂 /Downtime 기갂 : online & available at 2 nd site) Recovery Point Objective (RPO, 데이터유실가능범위 : Data 동기화 ) 1. A recent Gartner Study says ~ 70% of companies that suffer a major IT disaster without a valid recovery plan in place fail within the next year 2. Cost/hourOfDowntime: $78,000, Average incidents/year: 9, Hours/incident: 4.2 hours, Downtime Cost/year : $2,970,000 based on a survey of 450 of the Fortune 1000 companies [Source:ContingencyPlanningResearch (Jericho,NY)] 3. The costs of disasters are 15 times greater than the costs of preparing for disasters. [Source : FEMA (Federal Emergency Management Agency) ]
Disaster Recovery 를위한 MS Solution Technology/Positioning Recovery Point Option Zero - Seconds - Minutes - Hours - Days - Weeks Failover Clustering Geo-dispur. Clustering Sync Database Mirroring Sync Remote Mirroring Microsoft SQL 2005 DB Mirroring Peer-to-Peer Replication Async Database Mirroring Async Remote Mirroring H/W 미러링 Log Shipping Backup / Restore Detach / Copy / Attach Recovery Time Option Second to Minutes to Hours to Days No mirroring Safety OFF Safety FULL Performance characteristic % CPU 4.0 6.0 6.4 Data disk write rate (KB / sec) 4,500 4,500 4,500 Log disk write rate (KB / sec) 720 720 709 Transactions / sec 241 241 238 Transaction response time (seconds) 0.13 0.13 0.19 미러적용에따른성능차이는크지않음. 그러나, TX/Log 량및 N/W 시스템상황에따라차이발생하므로사전테스트권장 참조. http://www.microsoft.com/technet/prodtechnol/sql/2005/t echnologies/dbm_best_pract.mspx
MS Solution 기능비교 SQL Server 2005 고가용성솔루션으로, 장애복구클러스터링, 데이터베이스미러링, 로그쉬핑및복제구성이가능하며, 장애유형및대처방안에따라이들갂에단독또는복합적용이가능합니다. ( 차이점요약 ) 데이터베이스미러링클러스터링로그쉬핑 0 복제 1 2 3 동기식 모니터서버 A Node Single Instance Virtual Server B Node 주서버 미러서버 A B 선택기준 : 1.Remote 구현측면 2. 자동 Failover 3. Downtime 4. 복구단위 ( 서버 Instance/Database) 5. H/W 비용측면 표 1. 요약. DBM vs Clustering vs Log Shipping Feature 데이터베이스미러링 ( 동기 / 비동기 ) Failover Clustering (MSCS 기반 ) 로그쉬핑 Data Loss No data loss option (Async 에서 Force service 인경우, 전달되지않은마지막로그유실가능성 ) No data loss Some data loss possible Automatic Failover Yes Yes No ( 수동진행 ) Transparent to Client Yes, Auto-Redirect (ADO.NET 2.0 혹은 SQL Native Client) Yes, Reconnect to same IP No, NLB helps Downtime < 10 Seconds ( 가장빠름 ) 30 Sec + DB Recovery Seconds + DB Recovery Standby Read Access Continuously accessible Snapshot (License 필요 ) No Intermittently accessible Data Granularity 데이터베이스수준, < 10 Databases (10 개이상인경우 MSCS 권장 ) All System & User Databases ( 서버수준 ) 데이터베이스수준 Distant Limit Virtually Unlimited. Remote 구현가능 100 miles. GeoCluster(with H/W) WLAN. Remote 구현가능 Special Hardware Needed No, Dup. system needed. (H/W 및 Disk 구성동일권장 ) Specialized Hardware from Cluster HCL No, Dup. system needed 특이사항 SQL Server 2005 SP1 이상. 동기식인경우, 모니터서버필요 최소 2 ~ 8 Node 까지 (EE 64bit only). Single Instance/Multi Instance 마지막로그백업을복원해야데이터유실최소화 8
목차 개요 DBM (Database Mirroring) 구현사례 Best Practice L 사사례 구현고려사항 DBM 모니터링
DBM Best Practice Memory 및 Threads 자원사용측면에서, 한개의인스턴스당하나의데이터베이스를미러로구성하는것이가장이상적이고, ( 인스턴스당 10 개이상의데이터베이스가미러링되어야하는경우, MSCS 구성을권장 ), H/W 및 Disk 구성은동일하며, 초기데이터베이스복원시, 로그복원은반드시병행되어야합니다 (Full recovery model). 고객사 L 사 ERP, K 사 POC, Microsoft SAP.. 용도 성능 고려사항 Disaster Recovery, 백업시스템 (Patch..) Remote 구현. Async 방식 -> 장애시나리오고려필요 ( 서버가비정상 Down 된다면? N/W 이단절된다면?) No mirroring vs Async 미러미적용보다 overhead 는있으나, Async 적용시가시적인성능차이는매우미약함. Sync vs Async Sync 와 Async 방식에따라성능차이는발생하나, TX volume 와 N/W 상황, Log 발생량, H/W 성능에따라차이는각각다름. L 사의경우, Syn 와 Async 갂에경미한차이가발생했으나 ( 약 1,2 배 ), K 사의경우 2.6 배 Async 방식이느림. (INSERT/UPDATE/DELTE) Async 적용시고려사항 Manual Failover (Automatic Failover 보다약갂느림 ) Force Service - Data Loss 발생 ( 장애발생시점의로그전달미짂행될가능성 ) 권장사항 반드시고객사홖경에서의사전테스트작업이필요함. (Deploy 전, 실제상황에서의 Stress test) Impact 분석 : Log Generation Rate, Network latency/bandwidth 사전짂단 장애시나리오및 Requirement 가명확해야함. Remote N/W 홖경인경우는 Async 방식권장 DR 와 DC 센터갂의 N/W Bandwidth 확장, 전용선적용을권장 (N/W 안정성 ) H/W 및 Disk 구성동일 Endpoint 암호화 (Default) 권장, Alert 기능홗용 <L 사장애발생시 > - DB 서버 : 수동 Failover (Async), Force Service - Application Server: DNS 변경 ( 수동변경 ) - Application : 연결문자열에 Failover Partner 명시 * DC: Data Center, DR : Disaster Recovery 10
L 사 SQL2005 미러링적용 DR 사례 국내최초 SQL2005 기반의 Database Mirroring (Async 방식 ) 이 EKP 시스템에도입된사례로, 클러스터링과함께 DR 시스템가동에 DBM (Database Mirroring) 이운영되고있습니다. 주서버 (A 지역 ) 클러스터홖경 Failover 미러서버 (B 지역 ) DBM (Async 방식 ) 장애유형주서버 (DC) N/W (DC <-> DR) DB 장애대응방안 ( 미러서버의주서버전홖방법 ) Case1 Online Down - 미러제거. 주서버비상로그백업 / 복원 -> 서비스가동. - N/W 복원후새주서버기준으로미러재구성 Case2 Down Down Force Service Case3 Down On Force Service Case4 Online On Manual Failover DC : Data Center DR : Disaster Recovery L 사사례 : 1 일평균 200M log 생성, N/W Fail 시복원후 200M log 1 분내전송. (200Miles, 100M N/W bandwidth). SQL2k5 SP1 기반 11
구현고려사항 I 성능및관리측면, 장애시나리오측면에서다음의사항들이고려되어야합니다. 1 NETWORK 성능및안정성 ( 데이터유실최소화 ) 측면의가장큰 factor 임. Dedicated Line / 이중화 N/W Bandwidth : Log generation rate * 3 ( 클수록좋음 ). 테스트팅짂단 : workload 를수행하여 N/W 모니터측정. 먼거리일수록 Latency 증가및 banwidth 감소 -> Sync 방식인경우성능이떨어짐. High Latency -> timeout 옵션증가설정가능 : Alter the database endpoint timeout # (Default : 10 초 ) Clustering 과함께적용시, Clustering 이미러보다우선 Failover 되기위해서는 timeout 옵션을 60 초이상설정 ( 그러나, 권장사항아님 ) Ex. L 사사례 : 1 일평균 200M log 생성, N/W 복원후 200M log 1 분내전송. (200Miles, 100M N/W) 2 장애시나리오 Sync 방식 - Witness 서버필요. ( 주서버및미러서버와독립된 3 rd 위치배치권장하나, 비용측면에서미러 N/W zone 에일반적으로배치. 고객사홖경고려필요 ) - Automatic Failover. Clustering 과함꼐구성되는경우, Failover 우선순위결정필요. -> Default 설정은미러의 Failover 가 Clustering 보다우선함. ( 미러의 Timeout 옵션증가로 Clustering Failover 를선행적용가능하나, 권장사항아님. Network 부분참조 ) => Clustering 과접목시 Manual failover(async) 권장. Async 방식 - Manual Failover -> 장애자동감지안됨. 따라서, 주서버비정상 Down 및 N/W 단절시, 미러서버의주서버전홖을위해 Force service 필요. ( 일부 Data Loss 발생가능성 ) : ALTER DATABASE db 명 SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS - 데이터유실허용범위는어느정도가능한가? -> N/W 성능중요 - 데이터유실범위최소화 3 전제사항 : ADO.NET 2.0 SQLClient, SQL Native Client (OLEDB 또는 ODBC) Application ADO.NET connection string 예 : Data Source=SQLA\INST1;Failover Partner=SQLB\INST1;Initial Catalog=DBTest; Integrated Security=True Failover Partner, Default DB명은반드시기입. Application Retry logic 삽입 (Error handling) 권장 12
구현고려사항 II 4 Failover/ 미러재구성 Failover 시갂이 10 초이상소요되는경우 : 초기데이터베이스생성시갂이 Failover 시갂영향 ( 디스크문제및 Cache 부족가능성 ) 이롞상 10 초이내여야함. - Workaround : 초기데이터베이스 size 를 100 M 정도로설정한뒤 (Log size 가작아야함.), 미러구성후파일추가및파일사이즈증가 ( 수초내에완성 ) -> 데이터량에상관없이 10 초내에 Failover. 데이터복원시허용가능한 Downtime 기갂은? ( 미러재구성시갂단축필요 ) - 로그또는데이터복원시미러를제거해야함 ( 일시정지아님 ) -> 로그복원 ( 또는데이터 ) -> 미러재구성필요. - 미러구성시갂은작으나, 복원및미러적용후 Synchronizing 에소요되는시갂고려대상. ( 데이터사이즈가큰경우 ) -> 복원시갂최소화필요. 로그사이즈작아야함. (Full Recovery Mode) - 백업파일전달시갂고려대상 : 주서버백업본빠른시갂안에전달가능한방법고려 (Remote N/W 홖경인경우 ) 5 관리 Patch 및 Upgrade 작업 : 미러서버선행적용후 Failover, 주서버적용후 FailBack 로그삭제 : BACKUP LOG Database to DISK = nul: (null 이아니라, nul: ) Failover 시갂 파일증가 / 축소, N/W 교체 미러일시정지 (Pause) 후작업권장 로그파일축소 Mirror on 상태에서짂행 (Pause 상태에서축소경우, synchronizing 상태오래소요가능성 ) 주서버와미러서버의 H/W 홖경 ( 디스크 / 드라이브문자, CPU, 메모리 ) 동일할것권장 로그 FULL 미러일시정지경우, 로그삭제되지않음. 일시정지시갂이길지않도록조절. 데이터정합성측정 tablediff 또는 Vaildation tool (replication 제공 ) 사용. 6 보안 SQL 서비스계정권한 - 도메인계정 (Endpoint 에대한 Connect 권한필요 ) : 모든 endpoint 는동일한계정을사용하도록한다 (Best Practice) 사용자계정의 SID 일치 (http://support.microsoft.com/default.aspx?scid=kb;en-us;918992). 암호화된데이터베이스사용 ( 인증서, Symmetric key 사용하는경우 ) 시미러데이터베이스에마스터키에대한자격증명필요 ( 상세사항 : ms-help://ms.sqlcc.v9/ms.sqlsvr.v9.ko/udb9/html/7329a575-be29-46e0- abc6-1344db37920c.htm) 13
목차 개요 DBM (Database Mirroring) 구현사례 DBM 모니터링 개요 Tool
Time DBM 모니터링 : 개요 주서버와미러서버갂의로그전달관계를설명하고, 전달성능은 SQL Management Studio 의 DBM Monitor tool 을사용하여모니터가능하며, 동시에성능카운터를통해집계가능합니다. 미러링성능 : SQL Server : Database - Log Bytes Flushed/second ( 성능카운터 ) Key status monitoring procedure: sp_dbmmonitorresults 주서버와미러서버갂의로그전달관계 Principal Mirror Amount of log sent to mirror Log to redo on mirror Impacts failover time Redo Queue Unrecovered Log 15 Amount of log not sent to mirror Represents possible data loss Send Queue (unsent log) < SQL Server : Database Mirroring 성능카운터 > Bytes Received/sec, Bytes Sent/sec Log Bytes Sent/sec, Log Bytes Received/sec Pages Sent/sec Log Send Queue KB -- 주서버주측정항목 Redo Bytes/sec Redo Queue KB 미러서버주측정항목 Send/Receive Ack Time Receives/sec, Sends/sec Transaction Delay
DBM 모니터링 : Tool - Management Studio 2 3 1 Send Queue Redo Queue Note: Ensure 16 that the Database Mirroring Monitoring Job is running.
DBM 모니터 : 미러서버 (Disconnected 상태및해결사례 ) DBM Disconnected 상태 SQL Errorlog 주서버에서미러서버로보내지못한로그량 (Disconnected) -> 조치후 -> 0. 현상 : 미러상태가 Disconnected 됨. ( 미러링중단상태 ) 원인 : N/W 교체작업후 5022 port 에대한 garbage connection 이존재. 해결방법 : 주서버의 Endpoint 를재시작 Step1. 머싞에 open 되어있는모든 endpoint 에서미러링과관렦된 endpoint 의 name 을확인 SELECT * FROM sys.endpoints [ 참고 ]. 미러링상태 Disconnected: 연결안됨 Suspended: 일시중지 Synchronized: 미러가주서버와 100% 동기화 Synchronizing: 로그적용중 (TX 진행가능하나, failover 안됨 ) Step2. Mirroring endpoing 를정지 ALTER ENDPOINT Mirroring STATE = STOPPED -> Netstat ano 결과기존에있던좀비 connection 이사라졌음을확인 Step3. Mirroring endpoing 를다시시작 ALTER ENDPOINT Mirroring STATE = STARTED -> 다음과같이양쪽머싞에서정상적으로 5022 port 로 connection 이되어있음을확인. TCP 10.132.21.11:1233 10.135.1.14:5022 ESTABLISHED 1056 TCP 10.132.21.11:5022 10.135.1.11:2036 ESTABLISHED 1056 Step4. 미러링상태가 disconnected -> suspend 상태로변경되어있음확인. Step5. Suspend 상태의미러링을재시작해서 (Resume) 두 DB 를동기화시킴. Error: 1474, Severity: 16, State: 1. Database mirroring connection error 4 '64(error not found)' for 'TCP://ServerXX:5022'. Database mirroring connection error 2 'Connection attempt failed with error: '10060(error not found)'.' for 'TCP://ServerXX:5022'. 17
DBM 모니터 : 데이터 Validation Out of Sync 테이블확인방법 Step1. 미러제거 Step2. 주서버와미러서버갂에 TX Replication 구성 Step3. Table Validation 실행 (Out of Synch) Step4. Tablediff tool 실행 Step3-1. Request validation Step3-2. Launch monitor Step3-3. View mismatching tables 18
세션요약 비즈니스요건이반영된장애복구계획준비 ( 장애시나리오, Procedure/Policy, communication, staffing, decision strategy) 및 People/Process/Technology 총체적측면에서고객사홖경에적합한기술솔루션선정 SQL2005 미러링적용시, 성능및관리, 장애시나리오측면에서 Best practice 적용 H/W, N/W 성능, 보안, 어플리케이션특성고려 사전테스트및검증테스트, 주기적인 DR 테스트진행
참고자료 : SQL Server 홗용리소스 - 도움받을수있는자료는?
참고자료 DBM Best Practices http://www.microsoft.com/technet/prodtechnol/sql/20 05/technologies/dbm_best_pract.mspx SQL Server Best Practices http://technet.microsoft.com/enus/sqlserver/bb331794.aspx
SQL Server 관렦유용한웹사이트 SQL Server Product Homepage SQL Server Tech Center SQL Server Developer Center SQL Server Support Center SQL Server Download Center SQL Server Resource Center SQL Server Newsgroup SQL Server Community Sites http://www.microsoft.com/korea/sql http://www.microsoft.com/korea/technet/prodtechnol/sql http://www.microsoft.com/korea/msdn/sql http://www.support.microsoft.com/ph/2855 http://www.microsoft.com/downloads/browse.aspx?displaylang=ko&pro ductid=261ba873-f3ab-420e-96d6-e3004596a551 http://www.microsoft.com/korea/sql/prodinfo/sql2005_resources.mspx http://www.microsoft.com/technet/community/newsgroups/server/sql. mspx http://www.microsoft.com/korea/communities/related/windows_server_ community.mspx#server Microsoft Partner Portal http://partner.microsoft.co.kr/pds/mp_pds.asp
IT 전문가를위한마이크로소프트리소스 TechNet 웹사이트 : http://www.microsoft.com/korea/technet TechNet 세미나및웹캐스트 : http://www.microsoft.com/korea/technet/events TechNet 뉴스레터 : http://www.microsoft.com/korea/technet/flash TechNet Plus Subscription : http://www.microsoft.com/korea/technet/subscriptions TechNet 커뮤니티 : http://www.microsoft.com/korea/technet/membership TechNet 매거진 : http://www.microsoft.com/technet/technetmag TechNet Virtual Lab : http://www.microsoft.com/technet/virtuallab 고객지원포털 : http://support.microsoft.com/default.aspx/gp/supportportal/ko 묻고답하기 (Q&A 게시판 ) : http://www.microsoft.com/korea/communities/webforum/webforum2.mspx 기술지원및계약문의 : 1577-9700