1 SQL Server 2012에서의백업, 복원및 DRA를활용한복원 ( 복원을품은백업 )
2 강사소개 신대경 dkshin@adconsulting.co.kr 현 ) 에이디컨설팅 전 ) 엑셈 전 ) 예스이십사
3 목차 SQL Server 버전별백업신기능소개 2012 의새로운기능 DRA (Database Recovery Advisor) 2008 의새로운기능 2005 의새로운기능 복구모델 백업 비상로그백업 백업관련유용한팁 백업권고사항 복원 복원단계 파일즉시초기화 복원종류 특정지점으로복원 복원관련유용한구문 다른서버로의복원팁 일반적인백업복원예 부분백업, 증분복원 요약
이건뭘까요? 4
불편한진실???!!! 백업전략은있지만, 복원전략은없다 백업의주된목적 : 데이터베이스손상시백업을복원하여데이터베이스를복구할수있도록하기위함 백업전략수립시복원전략을고려하지않음 백업을수행하기는하지만, 복원테스트는하지않는다 복원이필요한시점에서야복원방법을모색함 백업을수행하기는하지만, 정작필요할때백업으로복구할수없는경우가발생한다 믿기어렵겠지만, 실제로다양한이유에의해발생함 백업을수행하기는하지만, 백업정책이적합하지않다 트랜잭션로그파일의비정상적인자동확장으로서비스장애가발생하거나, 백업본의관리또는복원에어려움이발생할수있음 백업의다양한기능에대한이해부족으로비효율적인백업정책을고수 (?) 하고있다. 5
6 SQL Server 버전별 신기능소개
7 SQL Server 2012 에서의신기능 DRA(Database Recovery Advisor) Windows Azure Platform 으로스냅샷백업지원
8 DRA(Database Recovery Advisor) 데이터베이스백업기록과사용자들의데이터베이스복구가능시점을나타내는시각적타임라인제공 데이터베이스를특정시간으로되돌리기위해최적의백업미디어집합을파악하는작업을간소화하는알고리즘적용 페이지수준의데이터베이스백업을수행하기위한 SSMS 내페이지복구대화상자제공
9 DRA(Database Recovery Advisor) (1) 데이터베이스 -> 태스크 -> 복원 -> 데이터베이스 복원가능한백업세트보여줌 해당복원세트로복원가능한시간을보여줌
10 DRA(Database Recovery Advisor) (2) 특정시간설정기능 필요한경우비상로그백업자동수행
11 DRA(Database Recovery Advisor) (3) 비상로그백업이자동으로추가된모습
12 DRA(Database Recovery Advisor) (4) 단일사용자모드설정 비상로그백업정보확인 기존과동일한복원옵션사용가능
13 DRA(Database Recovery Advisor) (5) 데이터베이스 -> 태스크 -> 복원 -> 페이지 손상된페이지가있는경우자동으로표시해줌
Windows Azure Platform 으로스냅샷백업지원 14 SSMS 에서 SQL Server 데이터베이스사본을 Windows Azure Storage 로손쉽게이동시키거나 SQL Azure Database 로직접이동시킬수있음 온 - 프레미스 SQL 서버데이터베이스용클라우드기반스토리지솔루션을제공함 SSMS 에서 SQL Azure 데이터베이스정보를확인할수있음
DRA 데모 15
16 SQL Server 2008 에서의신기능 백업압축 백업압축과 TDE
17 백업압축 백업압축은버전에따라지원되는 EDITION 의차이가있음 2008 : Standard Edition 은지원하지않으며 Enterprise Edition 에서만지원됨 2008 R2 : Standard Edition 과 Enterprise Edition 에서모두지원됨 백업압축은모든 Edition 에서복원할수있음 특징 백업공간감소 백업및복원시간단축 I/O 자원사용량감소 CPU 자원사용량증가
백업압축사례 국내적용사례 백업압축실행시간및파일크기 백업압축적용전에비해백업실행시간이각각 32%, 33% 단축됨 백업파일크기는적용전에비해 66%, 69% 감소됨 CPU 사용 백업압축수행시 CPU 사용률이약 8% 증가됨 서버전체의 CPU 자원에여유가있으며, 전체백업은업무부하가작은야간시간대에수행되므로서비스에영향을미치지않음 [ 사례 #1] 백업압축후크기 66% 감소 [ 사례 #2] 백업압축후크기 69% 감소 압축백업의성능개선정도와리소스사용변화는시스템환경및데이터성격에따라결과가달라질수있음 18
19 백업압축사례 해외적용사례 고객사 백업압축적용후공간감소율 참고사항 NASDAQ 80% Redprairie WMS 84% 백업시간도 43% 단축됨 GE Healthcare 71% 환자건강기록데이터베이스 SAP ERP 80% Caregroup 85% 압축전백업크기 = 110GB 압축후백업크기 = 26GB * 출처 : 2010 Microsoft SQL Enterprise Architect Summit 자료
20 백업압축과 TDE 암호화된데이터는압축률이좋지않음 TDE 기능을사용하는 DB 백업의경우압축되지않은백업사용권장시스템환경및데이터성격에따라결과가달라질수있음
21 SQL Server 2005 에서의신기능 부분백업 (Partial Backup) 주파일그룹과읽기 / 쓰기파일그룹만백업 복사전용백업 (Copy-only Backup) 백업전체에영향을주지않는특별한용도의백업 미러백업 (Mirror Backup) 백업복사본을여러개생성하는백업 데이터베이스스냅숏 (Database Snapshot) 원본데이터베이스의읽기전용의정적인뷰를제공
22 복구모델 복구모델의구분 트랜잭션로그의유지관리제어방식의차이 복구모델종류 전체 (FULL) 대량로그 (BULK_LOGGED) 단순 (SIMPLE)
23 복구모델 - 전체 모든트랜잭션을전체로깅 트랜잭션로그가백업될때까지유지 로그백업을하지않으면지속적으로로그파일이증가 데이터손실가능성이가장적음 트랜잭션로그를이용하여특정시간, 트랜잭션으로복구가능 Create Index 작업이포함된경우복구시간이빠름 ( 인덱스생성작업을다시수행할필요없음 )
24 복구모델 - 대량로그 전체복구모델과동일하게작동하지만대량복사작업에대해서는최소로깅 최소로깅가능한대량로그작업 BULK INSERT, SELECT INTO, BCP 등 수행사실및공간할당정보만기록 BCM(Bulk Changed Map) 마지막로그백업이후로최소로깅된대량작업에의해변경된익스텐트를기록 로그백업수행후모든 bit 를 0 으로변경 대량작업이없으면지정된시간으로복구가능
25 복구모델 - 단순 완료된트랜잭션에대한트랜잭션로그폐기 체크포인트가발생할때마다로그를비움 데이터베이스가손상될경우상당한작업이손실될가능성있음 마지막백업이후에변경된내용은보호되지않음 로그백업이필요하지않음 일반적으로운영데이터베이스에대해서는 전체 또는 대량로그 복구모델을설정하는것이적절 단순 복구모델은전체백업이후에발생된데이터를복원할필요성이없는테스트데이터베이스와같은경우에적합
복구모델 - 요약 복구모델장점데이터손실지정시간으로복원 전체 모든트랜잭션을로그에기록. 가장안전한상태유지. 손상시점까지복원가능. 로그파일손상시최근백업된이후손실. 지정시간으로복원가능. 대량로그 대량로그작업시성능향상. 손상시점까지복원가능. 대량로그된변경내용이백업되지않은경우최근백업된이후손실. 로그백업에대량로그된변경내용이있는경우불가. 단순 대량로그작업시성능향상. 로그파일크기를최소로유지. 최근에백업된이후손실. 지정시간으로복원불가. 26
복구모델에대한오해와진실 단순복구모델 단순복구모델은로깅을하지않는다 로깅을한다. 하지만, 트랜잭션이완료되면해당트랜잭션로그를폐기한다 트랜잭션로그파일은매우작아도된다 완료되지않은트랜잭션에대한트랜잭션로그는삭제되지않는다. 그러므로, 대량트랜잭션이발생하는경우가있다면그에맞게로그파일크기를크게고려해야한다 단순에서전체혹은대량로그로변경한경우 로그관리가저절로된다 그렇지않다. 전체백업을수행하지않으면트랜잭션로그를관리하지않는다 27
28 백업일반사항 (1) 무엇을백업할것인가? 사용자데이터베이스 시스템데이터베이스 이름설명백업필요여부 Master 인스턴스에대한모든시스템수준정보저장필요 Msdb 에이전트에서사용하는작업정보저장필요 Model 데이터베이스생성템플릿필요 Distribution 복제배포자로구성된경우사용 필요 Resource Tempdb 시스템개체정보를저장하는읽기전용데이터베이스 임시개체나중간결과집합을저장하기위한작업영역 불가능 ( 파일복사만가능 ) 불필요
29 백업일반사항 (2) 백업의종류 백업종류 설명 전체백업 차등백업 전체데이터베이스백업 마지막전체백업이후에변경된데이터분에대한백업 부분백업 주파일그룹, 모든읽기 / 쓰기파일그룹및필요에따라지정된읽기전용파일그룹에있는모든데이터의백업 파일백업 트랜잭션로그백업 복사전용백업 데이터베이스의파일이나파일그룹을개별적으로백업 트랜잭션로그에대한백업 백업시퀀스에영향을주지않으면서백업하고자하는경우사용하는방식
30 백업일반사항 (3) 언제백업할것인가? 사용자데이터베이스 : 정책에따라 시스템데이터베이스 이름 Master Msdb Model Resource 백업시기 - 데이터베이스생성, 삭제 - 서버또는데이터베이스구성값변경 - 로그인계정추가, 수정, 삭제 - 기타 master 데이터베이스변경이일어나는작업후 - 에이전트일정추가, 수정, 삭제 - 기타 msdb 데이터베이스변경이일어나는작업후 - Model 데이터베이스변경이일어나는작업후 - 서비스팩설치등의작업을앞두고이전버전의파일을복사하여백업 - mssqlsystemresource.mdf, mssqlsystemresource.ldf 파일을운영체제에서복사방식으로백업
31 비상로그백업 일반적으로데이터베이스를복원하기전에비상로그백업을수행 작업손실을방지하고로그체인을그대로유지 데이터베이스를실패지점으로복구할때는비상로그백업이복구계획의마지막백업 비상로그백업이불필요한경우 복구지점이이전로그백업에포함된경우 데이터베이스를이동또는교체 ( 덮어쓰기 ) 하는중인경우 최신백업이후의시점으로이를복원할필요가없을경우 비상로그백업수행방법 데이터베이스가온라인인경우, 복원시작전에수행 BACKUP LOG database_name TO <backup_device> WITH NORECOVERY 데이터베이스가오프라인이고, 데이터베이스가손상된경우 BACKUP LOG database_name TO <backup_device> [WITH { CONTINUE_AFTER_ERROR NO_TRUNCATE }]
32 잘못된사례 트랜잭션로그파일백업, 관리 시스템오픈이후에주기적인로그백업을수행하지않아서로그파일의크기가지속적으로확장되어디스크공간이가득차게됨 데이터베이스의데이터변경이중단되는장애발생 자동증가속성으로인해가상로그파일개수가많아져복구시간이길어짐 백업파일의위치 mdf, ldf 파일과동일한디스크에위치 디스크손상으로인해데이터베이스파일손상시백업파일도함께손상되므로, 백업파일을사용하여데이터베이스를복구할수없음
33 백업에활용할수있는유용한팁 분할파일백업 미러백업
34 분할파일백업 (Split File Backup) 백업을여러파일로분할하는기능 별도의백업파일이서로다른디스크상에병렬로작성되므로단일파일백업보다빠르게실행될수있음 예 ) 전체데이터베이스크기 : 90GB 디스크여유공간 : D(50GB), E(50GB), F(40GB) BACKUP DATABASE AdventureWorks TO DISK = D:\Backup\MultiFile\AdventureWorks1.bak', DISK = E:\Backup\MultiFile\AdventureWorks2.bak', DISK = F:\Backup\MultiFile\AdventureWorks3.bak' GO
35 미러백업 (Mirror Backup) 백업복사본을여러개 (2 ~ 4 개 ) 생성하는백업 백업세트나백업파일손상을대비할수있음 BACKUP DATABASE AdventureWorks TO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak', DISK = D:\Backup\MultiFile\AdventureWorks2.bak', DISK = E:\Backup\MultiFile\AdventureWorks3.bak' MIRROR TO DISK = F:\Backup\MirrorFile\AdventureWorks1.bak', DISK = G:\Backup\MirrorFile\AdventureWorks2.bak', DISK = H:\Backup\MirrorFile\AdventureWorks3.bak' WITH FORMAT GO
36 백업권고사항 백업파일은물리적으로별도의디스크에저장할것을권고함 주기적인트랜잭션로그파일백업을권고함 백업파일에대한복원테스트를실시하여검증할것을권고함 복원테스트를자주할수없을경우백업후 RESTORE VERIFYONLY 문옵션활용 정기적인데이터베이스백업체계수립을권고함
37 복원의사전적의미 復元 [ 복원 ] 부서지거나없어진사물 ( 事物 ) 을원래 ( 原來 ) 의모습이나상태 ( 狀態 ) 로되돌려놓는것 [IT 용어 ] 변경된기기의상태, 기억장치나레지스터의내용을원상태로돌려놓는작업 시간을거슬러 ~~~
복원단계 파일생성및초기화 기존데이터베이스가존재하면이단계는건너뜀 파일생성후 0 으로초기화작업수행 별도계정에권한부여하면파일즉시초기화가가능 데이터 / 트랜잭션로그복사 데이터베이스의백업미디어에서모든데이터페이지, 로그페이지, 인덱스페이지를데이터베이스파일로복사하는작업 REDO( 롤포워드 ) 기록된트랜잭션을백업에서복사한데이터에적용하여데이터를복구지점으로롤포워드 이지점에서데이터베이스는일반적으로커밋되지않은트랜잭션을가지며사용할수없는상태 UNDO( 롤백 ) 커밋되지않은트랜잭션을롤백하고사용자가데이터베이스를사용할수있게온라인상태로변경 롤백단계이후의백업은복원할수없음 38
즉시파일초기화 데이터및로그파일초기화 기본적으로는 0 으로초기화작업수행 즉시파일초기화는사용된디스크공간을 0 으로채우지않고기존데이터를덮어쓰는방식으로재사용함 설정방법 SQL Server 서비스계정을 Windows 계정으로설정 해당 Windows 계정에 Windows SE_MANAGE_VOLUME_NAME 특수권한할당 이권한은 Administrators 그룹에기본적으로할당됨 볼륨관리작업실행보안정책에 Windows 계정을추가 로그파일, TDE 적용데이터베이스는즉시초기화불가 39
40 복구옵션 WITH NORECOVERY UNDO 단계를생략하여커밋되지않은트랜잭션을유지 UNDO 단계를생략하면추가적인다른백업을복원하여데이터베이스를더많이 REDO( 롤포워드 ) 할수있음 데이터베이스는복구중상태로접근이불가능 WITH RECOVERY REDO 단계와 UNDO 단계를모두포함하여데이터베이스가복구되며데이터베이스를온라인상태로복원 추가백업에대한복원은진행할수없음 ( 기본값 )
41 수준별복원종류 수준복원종류설명 데이터베이스 데이터파일 데이터페이지 전체데이터베이스복원 파일복원증분복원 페이지복원 - 전체데이터베이스의복원및복구 - 복원및복구작업기간동안데이터베이스는오프라인상태 - 데이터파일또는파일그룹의복원및복구 - 복원기간동안해당파일을포함한파일그룹은오프라인상태 - 오프라인파일그룹에액세스하면오류발생 - 전체및대량로그복구모델에서개별페이지복원가능 - 온라인페이지복원은 SQL Server 2005 Enterprise Edition 이상에서가능 - 손상된페이지는 msdb 의 suspect_pages 테이블에서조회가능
특정지점으로복원 특정시간으로복원 STOPAT 옵션을사용하여특정시간으로복원 RESTORE LOG WITH STOPAT = 'Apr 15, 2020 12:00 AM'; 표시된트랜잭션으로복구 SQL Server 에서는트랜잭션로그에명명된표시를삽입하여특정표시위치로복구가가능 트랜잭션표시 BEGIN TRANSACTION Tx1 WITH MARK 'description'; 표시위치로복구 RESTORE LOG WITH STOPATMARK = Tx1'; LSN( 로그시퀀스번호 ) 으로복구 트랜잭션로그의모든레코드는 LSN 으로고유하게식별되며 LSN 은변경이발생한순서에따라번호가매겨짐 RESTORE LOG WITH STOPATMARK = 'lsn:<lsn_number> ; 42
43 복원관련유용한구문 RESTORE FILELISTONLY 백업세트에포함된데이터베이스와로그파일의목록을포함하는결과집합을반환 RESTORE HEADERONLY 특정백업장치의모든백업세트에대한백업헤더정보를모두포함하는결과집합을반환 RESTORE VERIFYONLY 백업파일을확인, 복원하지는않음 백업세트의완성여부와전체백업을읽을수있는지를확인
44 다른서버로의복원팁 백업파일을다른서버에복원하는경우 로그인과사용자의매핑확인 USE AdventureWorks2008R2; GO EXEC sp_change_users_login 'Report' 매핑되어있지않은경우, 매핑작업수행 EXEC sp_change_users_login 'Update_One', 'User1', 'Login1'; SQL Server 2012 인경우 Contained Database 활용시로그인과사용자매핑이필요하지않음
45 일반적인백업및복원예 (1) 트랜잭션로그백업 일요일월요일화요일수요일목요일금요일토요일 전체백업차등백업차등백업
46 일반적인백업및복원예 (2) 1. 비상로그백업 4. 트랜잭션로그복원 2. 전체백업복원 3. 차등백업복원 5. 비상로그복원 일요일월요일화요일수요일목요일금요일토요일
47 부분백업, 증분복원 대용량데이터베이스에적용가능 Big Database Primary Filegroup1 (ReadOnly) Filegroup2 (ReadOnly) Filegroup3 백업전략 Primary 를포함한 READ_WRITE Filegroup 백업 Filegroup1 백업 Filegroup2 백업 주기적인트랜잭션로그백업 비상로그백업수행 복원전략 Primary 를포함한 READ_WRITE Filegroup 복원 순차적인트랜잭션로그복원 비상로그백업복원 Filegroup1 복원 Filegroup2 복원
48 데모 전체백업후증분복원 부분백업후증분복원
백업, 복원프로세스 0. 복구모델결정 1. 백업정책수립 2. 백업정의, 일정수립 - 전체 - 대량로그 - 단순 5. 복원테스트및복원전략수립 4. 백업파일검증 49 3. 백업수행, 모니터링
50 요약 백업도설계가필요하다 시스템특성과리소스현황에적합한백업전략설계필요 데이터베이스현황, 사용패턴, 하드웨어, 백업미디어등을고려한최적의전략수립필요 백업과복원은일심동체이다 백업전략과더불어복원전략도함께수립 백업을받는것이백업의끝이아니다 백업파일에대한유효성검사필요 민방위훈련하듯이, 복원전략에기반한복원테스트필요 사용하고자하는모든유형의백업에대해서테스트!!! 백업이필요할때백업의진가를발휘할수있어야한다 데이터베이스와백업은서로다른장치에배치 시스템의요건에부합하는복원절차가가능하도록백업설계 백업에대한보안도고려해야한다 시스템환경은변화한다. 시스템여건의변화에맞추어백업및복원전략도진화해야한다
51