1 / 56

이 장 래 MCDBA / MCT 웹타임 교육센터 전임강사 sqlworld.pe.kr bestmct@sqlworld.pe.kr

안정적인 데이터베이스 구축과 장애 복구 전략. 이 장 래 MCDBA / MCT 웹타임 교육센터 전임강사 http://www.sqlworld.pe.kr bestmct@sqlworld.pe.kr. ▣ 목차. 데이터베이스에 대한 이해 데이터베이스 구축 및 관리 장애 발생시 데이터베이스 복구. Session 1 데이터베이스에 대한 이해. ▣ 다룰 내용. 데이터베이스 생성 원리 시스템 데이터베이스 파일과 파일 그룹에 대한 이해 파일 그룹 사용 목적. 데이터베이스 생성 원리. Model 데이터베이스.

tahir
Download Presentation

이 장 래 MCDBA / MCT 웹타임 교육센터 전임강사 sqlworld.pe.kr bestmct@sqlworld.pe.kr

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 안정적인 데이터베이스 구축과장애 복구 전략 이 장 래MCDBA / MCT웹타임 교육센터 전임강사http://www.sqlworld.pe.kr bestmct@sqlworld.pe.kr

  2. ▣ 목차 • 데이터베이스에 대한 이해 • 데이터베이스 구축 및 관리 • 장애 발생시 데이터베이스 복구

  3. Session1 데이터베이스에 대한 이해

  4. ▣ 다룰 내용 • 데이터베이스 생성 원리 • 시스템 데이터베이스 • 파일과 파일 그룹에 대한 이해 • 파일 그룹 사용 목적

  5. 데이터베이스 생성 원리 Model 데이터베이스 로그 파일: .ldf 데이터 파일:.mdf또는 .ndf 익스텐트(Extent): 8개의 연속적인 8KB 페이지 페이지(Page): 8KB

  6. 시스템 데이터베이스 • 시스템 데이터베이스 • master • model • tempdb • msdb • mssqlsystemresource • mssqlsystemresource데이터베이스 내용 확인 CREATE DATABASE [mssqlsystemresource_Copy] ON ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ Data\mssqlsystemresource_Copy.mdf' ), ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ Data\mssqlsystemresource_Copy.ldf' ) FOR ATTACH 

  7. 파일과 파일 그룹에 대한 이해(1/9) • 물리적으로 저장되는 데이터 파일과 로그 파일 파일그룹1(Default) 파일그룹2 파일그룹3 데이터파일1 로그파일1 데이터파일3 데이터파일4 데이터파일2 로그파일2 DISK4 DISK1 DISK2 DISK3

  8. 파일과 파일 그룹에 대한 이해(2/9) • 테이블을 만들 때 파일 그룹을 지정 파일그룹1(Default) 파일그룹2 파일그룹3 데이터파일1 로그파일1 데이터파일3 데이터파일4 데이터파일2 로그파일2 • CREATE TABLE T1( col1 int, • col2 int • ) ON FG01 • GO 테이블들 테이블들 테이블들

  9. 파일과 파일 그룹에 대한 이해(3/9) • 기본적인 형태 MyDB CREATE DATABASE MyDB GO PRIMARY(Default) MyDB_Log.LDF MyDB.MDF DISK1(C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA)

  10. 파일과 파일 그룹에 대한 이해(4/9) • 효과적인 형태 MyDB PRIMARY TRAN_FG01(Default) HIST_FG02 MyDB_Log.LDF MyDB1.MDF MyDB2.NDF MyDB3.NDF DISK1(D:\Data) DISK3(F:\Log) DISK2(E:\Data)

  11. 파일과 파일 그룹에 대한 이해(5/9) • 데이터베이스파일 • 주 데이터 파일(Primary Data Files) • 모든데이터베이스는 하나의 주 데이터 파일을 가짐 • 데이터 저장 외에 나머지 데이터 파일에 대한 정보를 갖고 있음 • 파일 확장자.mdf를 가짐 • 보조 데이터 파일(Secondary Data Files) • 보조 데이터 파일 없이도 데이터베이스 존재 가능 • 여러 개의 보조 데이터 파일을 가질 수 있음 • 파일 확장자.ndf를 가짐 • 로그파일(Log Files) • 모든 데이터베이스는 최소한 하나의 로그 파일을 가짐 • 트랜잭션 복구를 위한 정보를 가짐 • 파일 확장자.ldf를 가짐

  12. 파일과 파일 그룹에 대한 이해(6/9) • 데이터베이스파일 생성 시 지정 가능한 속성 • 논리적 파일 이름(Logical Filename) • 물리적 파일 이름(Physical Filename) • 초기/최대/증가 사이즈(Initial/Maximum/Increment Size) • 데이터베이스 기본 생성 폴더 지정 • [서버 속성]– [데이터베이스 설정]에서 변경 • 레지스트리 값 변경(아래 쿼리 수행 가능) EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'C:\Data‘ EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'C:\Data‘

  13. 파일과 파일 그룹에 대한 이해(7/9) • sys.database_files를 사용해 파일 속성 확인 가능 • type: 0=Rows, 1=Log, 4=Full-text • type_desc: ROWS/LOG/FULLTEXT • data_space_id: 파일이 속한 파일 그룹,0=Log File • name: 논리적인 파일 이름 • physical_name: 물리적인 파일 이름(운영 체제 파일 이름) • state: 상태(0=ONLINE,1=RESTORING,6=OFFLINE 등) • state_desc: 상태에 대한 설명 • size: 파일의 현재 사이즈(Page 단위) • max_size: 최대 사이즈(Page 단위,0=증가 불허,-1=무제한)

  14. 파일과 파일 그룹에 대한 이해(8/9) • growth: 0=고정사이즈, >0=자동 증가 • Is_percent_growth=0의 경우 Page 단위의 증가 값 • is_percent_growth=1의 경우 증가 % 값 • is_media_read_only: 1=읽기전용 미디어, 0=읽기/쓰기 미디어 • is_read_only: 1=읽기 전용 파일, 0=읽기/쓰기 파일 • is_sparse: 1=sparse 파일, 0=sparse 파일 아님 • is_percent_growth: 1=%단위 증가, 0=%단위 증가 아님

  15. 파일과 파일 그룹에 대한 이해(9/9) • 파일 그룹 • 주 파일 그룹(Primary Filegroup) • 주 파일 그룹은 오직 하나만 존재 • 주 데이터 파일(Primary Data File)을 포함함 • 데이터 파일의 위치를 명시적으로 지정하지 않으면 모든 데이터 파일이 주 파일 그룹에 위치하게 됨 • 사용자 정의 파일 그룹(User-defined Filegroup) • 하나또는 여러 개의 사용자 정의 파일 그룹을 만들 수 있음 • 기본 파일 그룹(Default Filegroup) • 하나의파일 그룹만이 DEFAULT 속성을 가짐 • ALTER DATABASE 문을 사용해 변경 가능 • 명시적으로 파일 그룹이 정의되지 않은 테이블과 인덱스에 대한 페이지를 포함하게 됨

  16. 파일 그룹 사용 목적(1/2) • 파일 그룹 사용 목적 • 성능 향상 • 여러 디스크에 존재하는 파일을 하나의 파일 그룹에 만듦 • 테이블의 데이터가 여러 디스크에 분산되어 기록 및 검색됨 • 운영 효율 • 비슷한 형태의 유지관리가 필요한 테이블을 그룹으로 묶음 • 파일 그룹 별 백업 및 복원이 가능함 • 자주 백업 받을 테이블을 같은 파일 그룹에 위치 시킴 • 변경되지 않는 테이블은 별도의 파일 그룹에 위치 시킴 • 안정성 확보 • 계란을 바구니 하나에 다 담지 말라. • SQL Server 2005의 온라인 복원 기능을 활용 할 수 있음 • SQL Server 2005의 증분 복원 기능을 활용 할 수 있음

  17. 파일 그룹 사용 목적(2/2) • 하나의 물리적인 드라이브에 여러 파일 사용 목적 • 만일 100GB 사이즈의 데이터 파일 하나만을 갖는 데이터베이스를 다른 위치에 복원해야 한다면… • 100GB 사이즈의 데이터 파일을 특정 위치에 복원해야 함 • 100GB의 빈 공간이 있는 하나의 드라이브가 필요함 • 복원 작업이 용이하지 못함 • 만일 여러 데이터 파일로 분산된 경우라면… • 각각의 데이터 파일 사이즈 만큼의 빈 공간이 있는 드라이브 필요 • 데이터 파일의 사이즈가 크지 않아 빈 공간 확보가 용이함 • 복원 작업이 용이함 • 데이터 파일을 다른 여러 디스크로 분산하기가 쉬움 • 부분 복원, 증분 복원 기능을 활용한 효율적인 복원 가능

  18. Session2 데이터베이스 구축 및 관리

  19. ▣ 다룰 내용 • 데이터베이스 구축 시 고려 사항 • 데이터베이스 구축 방법 • 데이터베이스 파일 관리 • 중요 데이터베이스 옵션 이해 • 데이터베이스 분리와 연결

  20. 데이터베이스 구축 시 고려 사항 • 데이터베이스 사용 목적 • OLTP와 OLAP 데이터베이스는 서로 다른 목적을 가짐 • 서로 다른 디자인이 요구됨 • 트랜잭션 처리량 • 요구되는 트랜잭션 처리량에 따라 정규화, 인덱스, 파티션 관련 전략을 수립해야 함 • 데이터 사이즈 증가 가능성 • 향후 증가될 데이터 처리를 위한 CPU, 메모리 및 디스크 공간 확보 및 계획 필요 • 파일의 위치 • 데이터 및 로그 파일의 위치가 성능에 영향을 미침

  21. 데이터베이스 구축 방법 • Management Studio 사용 • GUI를 사용하므로 손쉽게 만들 수 있음 • 데이터베이스를 반복해서 만들기가 번거로움 • CREATE DATABASE문 사용 • 구문에 대한 정확한 이해가 필요함 • 스크립트를 이용한 반복적인 작업이 용이함 • 데이터베이스스냅샷을 만드는 경우 필히 사용해야 함 • Management Studio를 사용해 만들고 관련 스크립트를 생성해 보관 및 향후에 활용함 • 데이터베이스 만드는 과정에서 스크립트 생성 • 데이터베이스 만든 후 스크립트 생성

  22. DEMO 데이터베이스 만들기

  23. 데이터베이스 파일 관리(1/5) • 데이터 파일 추가 • 새로운 데이터 파일 추가는 별 다른 어려움이 없음 • 파일의 위치와 사이즈를 설정하여 데이터 파일 추가 USE Master GOALTER DATABASE MyDB ADD FILE ( NAME = 'MyDB_03', FILENAME = 'C:\Data\MyDB_03.ndf’, SIZE = 3MB, FILEGROWTH = 1MB ) TO FILEGROUP FG01 GO

  24. 데이터베이스 파일 관리(2/5) • 데이터 파일 제거 • 비어있는 데이터 파일은 쉽게 제거 가능 • 데이터를 포함하고 있는 데이터 파일은 제거 불가 메시지 5042, 수준 16, 상태 1, 줄 1 파일 'MyDB_02'은(는) 비어 있지 않으므로 제거할 수 없습니다. • 데이터를 같은 파일 그룹 내의 다른 파일로 옮긴 후 제거 USE Master GO ALTER DATABASE MyDB REMOVE FILE MyDB_03 GO

  25. 데이터베이스 파일 관리(3/5) • 데이터 파일 제거 - 계속 -- 데이터 파일 비우기USE MyDB GODBCC SHRINKFILE ('MyDB_02' ,EMPTYFILE) GO -- 다시 제거USE Master GOALTER DATABASE MyDB REMOVE FILE MyDB_02 GO

  26. 데이터베이스 파일 관리(4/5) • 데이터 파일을 새 위치로 이동 • ALTER DATABASE 문을 사용해 파일 위치 변경 • ALTER DATABASE 문을 사용해 데이터베이스 오프라인 • 데이터 파일을 새 위치로 이동 • ALTER DATABASE 문을 사용해 데이터베이스 온라인 ALTER DATABASE MyDB MODIFY FILE (NAME = MyDB_01, FILENAME = 'D:\Data\MyDB_01.ndf’) GOALTER DATABASE MyDB SET OFFLINE GOALTER DATABASE MyDB SET ONLINE – 데이터파일 위치 이동 후 GO

  27. 데이터베이스 파일 관리(5/5) • tempdb데이터베이스 파일을 새 위치로 이동 USE Master GO -- 1) 파일 위치 변경 ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\Data\tempdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'D:\Data\templog.ldf'); GO -- 2) SQL Server 인스턴스 재 시작 -- 3) 파일 위치 확인 SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb') GO

  28. DEMO 데이터베이스 파일 관리

  29. 중요 데이터베이스 옵션 이해(1/4) • 데이터베이스 액세스 제어 • SINGLE_USER • 한번에 하나의연결 만을 허용함 • 옵션 설정한 사용자가 로그오프해도 SINGLE_USER 모드로 유지됨 • 이 때 다른 한 명의 사용자만 데이터베이스에 연결할 수 있음 • RESTRICTED_USER • db_owner, dbcreator, sysadmin 역할의 멤버만 연결이 허용됨 • 연결되는 수는 제한하지 않음 • MULTI_USER (기본 값) ALTER DATABASE MyDB SET SINGLE_USER GO SELECT USER_ACCESS_DESC FROM sys.databases WHERE name = ‘MyDB' GO

  30. 중요 데이터베이스 옵션 이해(2/4) • 데이터베이스 상태 제어 • OFFLINE • 데이터베이스가 닫히고 완전히 종료되어 오프라인 상태로 표시 • 더 이상 데이터베이스 변경 불가 • 데이터베이스 스냅샷이 있다면 자동으로 제거됨 • ONLINE(기본 값) • EMERGENCY • 로깅(Logging)이 해제되고 액세스가 sysadmin 역할의 멤버로 제한 • 시스템 관리자는 읽기 전용으로 데이터베이스에 액세스할 수 있음 • 주로 문제 해결에 사용 ALTER DATABASE MyDB SET OFFLINE GO

  31. 중요 데이터베이스 옵션 이해(3/4) • 데이터베이스 변경 가능성 제어 • READ_ONLY • INSERT, UPDATE 또는 DELETE 수행 불가 • SQL Server 시작 과정에서 자동 북구 프로세스 진행 안됨 • SELECT문 수행 시 잠금 불필요 • 데이터베이스 사이즈 축소 작업 수행 불가 • READ_WRITE(기본 값) ALTER DATABASE MyDB SET READ_ONLY GO SELECT name, is_read_only FROM sys.databases WHERE name = 'MyDB' GO

  32. 중요 데이터베이스 옵션 이해(4/4) • Termination 절 • 상태를변경 시 완료되지 않은 트랜잭션의 롤백 시점 지정 • Termination 절을 생략하면 데이터베이스에 잠금이 있는 경우 ALTER DATABASE 문이 무기한 기다리게됨 • 지정 가능한 옵션 • ROLLBACK AFTER integer [SECONDS] / ROLLBACK IMMEDIATE • NO_WAIT 메시지 5070, 수준 16, 상태 2, 줄 1 다른 사용자가 데이터베이스 ‘MyDB'을(를) 사용하는 중에는 데이터베이스 상태를 변경할 수 없습니다. 메시지 5069, 수준 16, 상태 1, 줄 1 ALTER DATABASE 문이 실패했습니다 ALTER DATABASE MyDB SET READ_ONLY WITH NO_WAIT

  33. 데이터베이스 분리와 연결(1/2) • 데이터베이스 분리 • 다음의 데이터베이스는 분리 불가 • 복제를 위해 게시 중인 데이터베이스 • 데이터베이스 스냅숏이 존재하는 원본 데이터베이스 • 데이터베이스 스냅숏 자체 • 데이터베이스 미러링에 포함된 데이터베이스 • 주의 대상 데이터베이스 • sp_detach_db저장프로시저 사용 • 만일 다른 연결이 있어 분리 작업이 불가능한 경우에는 SINGLE_USER 모드로 변경 후 진행(Termination절 사용) • Management Studio 사용 EXEC sp_detach_db 'AdventureWorks', 'true'

  34. 데이터베이스 분리와 연결(2/2) • 데이터베이스 연결 • sp_attach_db저장프로시저 사용 • 16개의 파일만 지정 가능 • 대신 CREATE DATABASE 문을 사용하도록 권장 • CREATE DATABASE … FOR ATTACH문 사용 • Management Studio 사용 CREATE DATABASE database_name ON <filespec> [,…n] FOR { ATTACH | ATTACH_REBUILD_LOG }

  35. DEMO 데이터베이스 속성변경 / 분리와 연결

  36. Session3 장애 발생 시 데이터베이스 복구

  37. ▣ 다룰 내용 • 로그 백업의 중요성 • 특정 시점으로 데이터베이스 복원하기 • 파일 그룹과 데이터베이스 복원 • 데이터 파일 손상 시 최선의 복구 • 로그파일 손상 시 Emergency 모드 활용 • 온라인 복원 작업을 통한 다운타임 최소화 • 증분 복원을 사용한 효율적인 복원 방법

  38. 로그 백업의 중요성(1/2) • 데이터베이스 복구 모델 • 전체(FULL) • 모든 데이터 행에 대한 변경 내용이 로그에 기록됨 • 장애 발생 시점까지또는특정 시점의 복원이 가능함 • 로그 파일 사이즈가 상당히 커질 수 있음 • 정기적인 로그 백업을 통해 로그가 꽉 차지 않도록 해야 함 • 대량로그(BULK_LOGGED) • 대량 INSERT 작업에 대한 로그 기록을 최소화 함 • 단순(SIMPLE) • 가장 단순한 백업과 복원 전략이 가능하게 함 • Checkpoint 발생 시 트랜잭션 로그가 지워짐 • 로그 백업을 수행 할 수 없음 • 테스트용, 개발용 또는 읽기 전용 데이터베이스에 사용 권장

  39. 로그 백업의 중요성(2/2) • 로그 백업은 모든 변경에 대한 순차적인 기록을 포함함으로 특정 시점으로 복원을 할 수 있게 해줌 • 데이터베이스와 관련된 미디어가 손상되었다 해도 로그 파일 자체에 손상이 없다면 로그 백업 가능 (로그를 다른 물리적 위치에 두는 이유 중 하나) • 데이터베이스에 손상이 생긴 후 수행되는 최종 로그 백업(Tail of the log)은 BACKUP LOG 문 수행 시 WITH NO_TRUNCATE 옵션을 필히 지정해야 함 BACKUP LOG MyDB TO DISK = 'C:\Backup\MyDB_TailLog.bak' WITH NO_TRUNCATE, NORECOVERY GO

  40. DEMO 로그 꼬리 백업을 사용한 완벽한 복원

  41. 특정 시점으로 데이터베이스 복원하기 • 트랜잭션 로그 백업을 사용하여 특정 시점으로 복원 • 잘못 처리된 T-SQL문에 의해 손상된 데이터 복원 • 일부 테이블의 복원의 경우는 다른 데이터베이스로 복원 후 해당 테이블의 데이터 복원함 • RESTORE LOG문에 STOPAT 절을 사용하거나 또는 Management Studio를사용하여 수행 가능 RESTORE LOG MyDB2 FROM DISK = 'C:\Backup\MyDB_TailLog.bak' WITH RECOVERY, STOPAT = '2008-06-30 19:04:00' GO

  42. DEMO 특정 시점으로 데이터베이스 복원

  43. 파일 그룹과 데이터베이스 복원(1/4) • 데이터 파일이 손상되면 손상된 데이터 파일을 포함하는 파일 그룹에 속한 개체 액세스 불가 • 파일 그룹이 PRIMARY 뿐이라면 데이터 파일이 손상되면 모든 개체에 대한 액세스 불가 • 데이터의 안정성을 위해서 여러 파일 그룹을 만들어 테이블을 분산시켜 두는 것이 바람직함

  44. 파일 그룹과 데이터베이스 복원(2/4) • 단일 파일 그룹의 예 MyDB PRIMARY MyDB_Log.LDF MyDB.NDF MyDB.MDF X X X 모든 개체 액세스 불가

  45. 파일 그룹과 데이터베이스 복원(3/4) • 다중 파일 그룹의 예 MyDB TRAN_GRP PRIMARY HIST_GRP MyDB_Log.LDF MyDB_03.NDF MyDB_02.NDF MyDB_01.MDF MyDB_04.NDF O X O 액세스 불가 액세스 가능 액세스 가능

  46. 파일 그룹과 데이터베이스 복원(4/4) • 특정 파일 그룹의 데이터 파일이 손상되면… • 데이터 파일이 손상되어 데이터베이스 액세스가 불가한 경우 손상된 데이터 파일 오프라인 시키기 • 데이터베이스를 오프라인 시킨 후 다시 온라인 시키면 다른 파일 그룹의 테이블 액세스 가능 ALTER DATABASE MyDB MODIFY FILE (Name = MyDB_02, OFFLINE) GO ALTER DATABASE MyDB SET OFFLINE GO ALTER DATABASE MyDB SET ONLINE GO

  47. DEMO 손상된 데이터 파일 오프라인 시키기

  48. 데이터 파일 손상 시 최선의 복구 • 데이터 파일이 손상되면… • 데이터베이스 액세스가 안 되는 경우라면 손상된 데이터 파일을 오프라인 시킨 후 데이터베이스를 오프라인 시킨 후 온라인 시켜 업무가 진행 되도록 함 • 현재의 로그의 꼬리를 백업 • 손상된 데이터 파일을 전체 백업으로부터 복원 • 전체 백업 이후의 차등 백업과 로그 백업 복원 • 앞 과정에서 백업 받은 로그의 꼬리 복원으로 마무리 RESTORE DATABASE MyDB FILE = 'MyDB_02' FROM DISK = 'C:\Backup\MyDB_Full.bak' WITH NORECOVERY

  49. DEMO 데이터 파일 손상 시 최선의 복구

  50. 로그 파일 손상 시 Emergency 모드 활용 • 로그 파일이 손상되어 데이터베이스 액세스가 불가능한 경우 상태를 Emergency로 설정 하면… • INSERT, UPDATE 및 DELETE문 수행은 불가능함 • SELECT문 수행은 가능함 • 데이터베이스 백업 불가능함 • 기존 백업으로 다른 위치에 데이터베이스를 복원 한 후 원본 데이터베이스(응급 상태)의 데이터를 가져가 필요한 데이터를 복원하도록 함 ALTER DATABASE MyDB SET EMERGENCY GO

More Related