안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 13-4. 백업과 복원 - 유지 관리 계획 수립을 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

 

이번에 진행할 강좌는 백업과 복원 - 유지 관리 계획 수립입니다.

 

 

 

TL;DR

데이터베이스 유지관리 계획(Database Maintenance Plan)은 데이터베이스에서 수행해야 하는 유지관리 작업을 주기적으로 자동 실행하기 위한 SQL Server의 관리 기능입니다. 유지 관리 계획 구성과 전체 백업, 로그 백업 태스크 내용도 진행됩니다.

 

데이터베이스 유지관리 계획(Database Maintenance Plan)은 종합적으로 데이터베이스를 유지 관리하기 위한 기능입니다. 백업 / 인덱스 관리 등 주기적으로 진행해야 하는 작업을 자동적으로 수행할 수 있도록 설정하는 매우 유용한 SQL Server의 관리 기능입니다.
유지 관리 계획 수립 전에 아래 두 가지 내용을 미리 체크합니다.

 

 

DBCC CHECKDB - 데이터베이스 개체의 무결성 검사

이름부터 뭔가 DB를 체크할 것 같은 느낌이 드는 명령입니다. 수행하면 지정한 데이터베이스에서 모든 개체의 무결성을 검사하게 됩니다.

참조 무결성 검사인가요? 왜 이걸 유지 관리 작업으로 수행하나요?

데이터베이스는 바이너리 형식으로 디스크에 저장됩니다. 디스크는 물리적인 저장소로, 일정 시간 사용하게 되면 배드섹터(Bad sector)나 손상이 발생하는 소비재입니다. 디스크 액세스(Access)가 많은 데이터베이스는 디스크 소모가 극심하고, 손상되면 치명적인 사태가 발생할 수 있습니다. 즉, 주기적으로 데이터베이스의 물리적/논리적 무결성을 검사해야 합니다. 이 무결성 검사 명령이 DBCC CHECKDB입니다.(참조 무결성 검사 아닙니다.)

 

CHECKDB 명령은 다음과 같이 수행할 수 있습니다.

USE master;
GO

DBCC CHECKDB('AdventureWorks');
GO

결과
DBCC results for 'AdventureWorks'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
DBCC results for 'sys.sysrscols'.
There are 2445 rows in 28 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
…
CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdventureWorks'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

클라우드 데이터베이스 - PaaS를 이용한다면, DBCC CHECKDB를 실행할 일이 많지 않습니다. 만약, On-Premise의 SQL Server에서 DBCC CHECKDB 오류를 만나면 다음 절차를 고려합니다.

 

• DBCC CHECKDB(‘DB명’, REPAIR_REBUILD)로 데이터 손실 가능성이 없는 복원을 시도
• REPAIR_REBUILD로 복원 불가하다면, SQL Server를 잠시 중지하고 백업 파일 또는 데이터파일과 로그 파일(mdf / ldf)을 복사하고 다른 디스크나 시스템에서 복원이나 Attach 해 데이터 복원 시도

이렇게 복원을 시도합니다. 더 좋은 방법은 데이터베이스 디스크 등의 장비에 RAID1나 RAID5 등 미러링이나 패리티 체크 장비를 이용하는 방법이며, 점차 클라우드로 이전하는 방안입니다.

위의 시도로 복원이 안된다면, 이제부터 데이터 손실 가능성이 있습니다.

 

• DBCC CHECKDB - REPAIR_ALLOW_DATA_LOSS로 일부 데이터 손실을 감수하고 복원 시도
• 손실이 크다면 최근 정상 백업본에서 복원 수행
  비즈니스 결정으로 손상이 커서 복원이 어렵다면, 최근 정상 백업으로 디스크 등의 하드웨어 등을 교체하고 복원 수행

이런 상황을 예방하는 방법은 곧 진행될 유지 관리 계획의 일부로 DBCC CHECKDB를 수행해 미리 예방하는 방안입니다.

☑️ 챗GPT 활용: RAID1, RAID5, 패리티 체크 장비(Parity Check Device)에 대해서 알려줘
☑️ 챗GPT 활용: 클라우드 환경에 디스크 장애가 거의 없는 이유에 대해서 알려줘

 

 

SQL Server 에이전트(Agent)

SQL Server에서 주기적으로 실행하는 이런 자동화 작업은 어떤 원리로 실행되는 것일까요? SQL Server는 데이터베이스 엔진 프로세스와 SQL Server Agent 스케줄러 서비스 프로세스로 분리되어 있습니다. 이 SQL Server Agent는 자동화와 관련된, 주기적으로 실행하는 작업을 트리거하거나 SQL Mail 등의 설정으로 알림을 보내는 작업을 수행하는 프로세스입니다. 리눅스의 Systemd(또는 Cron)이나 윈도의 작업 스케줄러와 유사하지만, SQL Server 작업에 특화되어 있습니다.

☑️ 챗GPT 활용: 리눅스 Systemd와 Cron 스케줄러에 대해서 알려줘

 

SSMS 개체 탐색기의 아래쪽에서 SQL Server Agent 서비스를 확인 가능합니다.

이미지 - SQL Server Agent

 

만약, SQL Server Agent가 기본 중지 상태라고 놀라지 마세요. 윈도일 경우, 수동 시작으로 기본 설정되며, 윈도의 “서비스”에서 자동으로 변경해야 시스템이 재시작되어도 백그라운드에서 자동으로 시작됩니다.

윈도키+R 키를 눌러 실행 창 시작 - services.msc 실행 - SQL Server Agent 서비스 설정을 자동 시작 유형으로 변경하고 확인

 

이미지 - SQL Server Agent Service

 

 

데이터베이스 유지 관리 계획 수립

데이터베이스 유지 관리 계획은 SQL Server의 여러 관리 작업을 자동화하는 기능입니다. 백업 / 인덱스 재구축 등, 관리자의 작업이 개입되어야 하는 다양한 업무를 자동적으로 수행하는 매우 유용한 기능입니다.

유지 관리 계획은 아래의 작업으로 구성됩니다.

 

이미지 - 유지 관리 계획 태스크

1. 데이터베이스 무결성 검사

DBCC CHECKDB 명령이 실행되며 무결성 검사가 진행됩니다. 전체 백업 전에 수행하는 것이 좋습니다.

2. 데이터베이스 축소

데이터베이스 축소 작업으로 DBCC SHRINKDATABASE 작업을 수행합니다. 

3. 인덱스 다시 구성

인덱스 REORGANIZE 작업입니다. 

4. 인덱스 다시 작성

인덱스 REBUILD 작업입니다.

5. 통계 업데이트

UPDATE STATISTICS 작업을 수행합니다.

6. 기록 정리

설정한 기간보다 오래된 작업 히스토리(job history)나 유지 관리 계획 기록을 msdb에서 주기적으로 삭제하는 유지 관리 계획을 생성합니다.

7. SQL Server 에이전트 작업 실행

SQL Server Agent 작업 중 실행할 작업을 유지 관리 계획으로 생성합니다.

8. 데이터베이스 백업(전체)

전체 백업을 수행합니다.

9. 데이터베이스 백업(차등)

차등 백업을 수행합니다.

10. 데이터베이스 백업(트랜잭션 로그)

트랜잭션 로그 백업을 수행합니다.

11. 유지 관리 정리 태스크

지정한 기간보다 오래된 유지 관리 계획에서 생성된 텍스트 보고서나 백업 파일을 삭제하는 유지 관리 계획을 생성합니다.

 

유지 관리 계획 패턴은 비슷합니다. 실행할 작업과 스케줄을 정의하면 해당 스케줄에 맞춰 작업이 실행됩니다. 다음 작업을 묶어서 수행하는 것이 좋습니다.

 

전체 백업 추천 태스크

- 데이터베이스 무결성 검사(전체)
- 인덱스 다시 작성
- 데이터베이스 백업(전체)

 

로그 백업 추천 태스크

- 데이터베이스 무결성 검사(물리적 전용)
- 인덱스 다시 구성
- 데이터베이스 백업(트랜잭션 로그)

 

 

유지 관리 계획 마법사 실행 - 전체 백업

이제, 유지 관리 계획 마법사를 실행하고 관리 계획을 생성하겠습니다. 
매일 주기적으로 전체 백업을 수행하고, 매 시간 트랜잭션 로그를 백업하는 유지 관리 계획을 생성합니다. 
아래와 같이 SSMS의 관리 - 유지 관리 계획에서 유지 관리 계획 마법사를 실행합니다.

 

이미지 - 유지 관리 계획 실행

유지관리 계획 마법사를 실행하면 간단한 소개가 진행됩니다. 다음을 진행합니다.

이미지 - 유지 관리 계획 마법사

 

계획 속성을 선택합니다.

이미지 - 유지 관리 계획 속성

 

“다음 계정으로 실행”에서 기본적으로 SQL Agent 프로세스로 스케쥴링하고 SQL Agent 계정의 권한으로 작업을 실행합니다. 

“일정”에서는 다양한 일정 패턴을 구성할 수 있습니다. 매시간, 매일, 매주 특정 요일 작업을 설정할 수 있으며, 이번 강좌에서는 다음과 같이 일정을 설정합니다.

이미지 - 유지 관리 계획 - 작업스케줄

 

- 매일, 오전 12시 5분 진행
5분으로 지정하는 이유는, 다른 관리자에 의해 이미 생성된 정시 실행 스케줄 패턴이 있을 수 있으며, 이를 피하기 위해 약간 시간을 딜레이 시켜 진행합니다. 

 

유지 관리 태스크 선택

간단히 소개해 드렸던 다음 세 가지 작업을 전체 백업을 위해 수행합니다.

 

이미지 - 유지 관리 계획 -태스크 선택

 

• 데이터베이스 무결성 검사(전체)
• 인덱스 다시 작성
• 데이터베이스 백업(전체)

 

유지 관리 태스크 작업 순서를 설정합니다. 무결성 검사, 인덱스 다시 작성, 전체 백업 순서로 설정합니다.

 

이미지 - 유지 관리 계획 - 태스크순서

 

유지 관리 계획 - 무결성 검사 태스크

이미지 - 유지 관리 계획 - 무결성 검사 태스크

무결성 검사를 진행할 데이터베이스를 선택합니다. 사용자 데이터베이스를 선택하면 되며, 이번 강좌에서 진행한 bkup_test 데이터베이스를 선택합니다.

설정 중에 “물리적 전용” 체크를 제거합니다. 물리적 전용(Physical only)은 페이지와 헤더의 물리적 구조 무결성과 일관성만 검사합니다. DBCC CHECKDB 실행 시간은 줄어들지만, 전체 백업 과정의 일부로 진행하므로, 체크를 제거해 데이터베이스의 모든 무결성을 검사합니다.

Tablock은 다음 강좌에서 진행하는 잠금 설정입니다. 무결성 검사 세션에게 테이블 단위 잠금을 적용해 다른 세션이 테이블에 접근해 작업할 경우 대기하도록 설정합니다. 무결성 검사에 우선순위를 두고 작업할 경우 설정 가능합니다.

최대 병렬 수준 - DOP(Degree of Parallelism)는 병렬 작업 설정입니다. 무결성 검사에서 사용할 최대 병렬 CPU를 제한하는 설정입니다. 

위의 이미지와 같이 “인덱스 포함”만 체크하고 다음 과정을 진행합니다.

 

유지 관리 계획 - 인덱스 다시 작성 태스크

인덱스 다시 작성은 이전 12-8. 인덱스 생성과 관리 - 인덱스 재구성/재구축 강좌에서 진행한 인덱스 재구축(Rebuild) 작업입니다. 

 

이미지 - 유지 관리 계획 - 인덱스 다시 작성 태스크

 

지난 인덱스 강좌와 인덱스 재구성 작업을 다시 떠올려 보면 어렵지 않습니다. 

데이터베이스와 개체를 선택합니다. 개체는 테이블만 / 뷰만 / 테이블과 뷰 모두 작업 중 선택합니다. 

사용 가능한 공간 옵션은 인덱스의 채우기 비율(FILLFACTOR)을 설정합니다. 인덱스를 생성할 때 지정한 FILLFACTOR 대로 인덱스 다시 작성을 수행하거나, 지정한 FILLFACTOR로 설정합니다.

고급옵션에서 tempdb 결과 정렬은 인덱스 정렬 시 임시 공간으로 tempdb를 사용하는 설정이며, “인덱스 패딩”은 12-7. 인덱스 생성과 관리 - 인덱스 옵션에서 소개한 PAD_INDEX 설정이며 인덱스의 중간 레벨(Non-leaf) 레벨까지 여유를 두게 됩니다. 

인덱스를 온라인으로 유지하는 설정은 인덱스 재구축 작업 중에 인덱스에 액세스 가능한 옵션입니다.(SQL Server Enterprise 에디션에서만 제공되는 기능)

인덱스 검색 유형은 통계 수집 옵션으로 “빠르게” 설정을 수행해 인덱스 통계를 수집합니다. 

마지막 인덱스 최적화 조건은 조각화(Fragmentation)가 30% 일 경우에만 인덱스를 재작성하고, 페이지 수가 최소 1천 개 = 8KB * 1천 개면 약 8M 이상일 경우에만(최소 8M 이상) 재작성하도록 설정합니다. 

필요에 따라 인덱스 다시 작성 태스크를 설정하고, 다음 과정을 진행합니다.

 

유지 관리 계획 - 전체 백업

마지막 단계인 전체 백업 단계입니다. 마찬가지로, 전체 백업 과정과 설정을 그대로 유지 관리 계획에서 선택합니다.

 

이미지 - 유지 관리 계획 - 전체백업-일반

 

백업할 데이터베이스를 선택합니다. 데이터베이스를 여러 개의 파일 및 파일 그룹으로 생성했을 경우 “파일 및 파일 그룹” 단위 백업도 가능합니다.

백업할 위치는 “디스크”, “테이프”, “URL”이 가능하며 URL은 클라우드 서비스 제공자(CSP)의 blob 저장소에 백업을 보관할 수 있습니다. 예를 들어, Microsoft Azure Blob Storage에 저장 가능합니다. 

☑️ 챗GPT 활용: 클라우드 서비스 제공자(CSP)의 blob 저장소 종류에 대해서 알려줘

 

이미지 - 유지 관리 계획 - 전체백업 대상

 

전체 백업 - 대상 탭에서는 백업 파일 타깃을 설정합니다. “모든 데이터베이스에 대한 백업 파일 만들기” 설정은 지정한 폴더에 백업 파일을 생성하고, 데이터베이스 당 하나의 파일을 생성합니다. 테이프나 디스크 디바이스를 이용할 경우 해당 장치에 백업할 수 있습니다. 백업 대상은 반드시 데이터베이스와 물리적으로 다른 시스템/디스크에 저장하세요. 가능하다면, 클라우드 저장소에 백업하는 것도 좋은 선택입니다.

 

이미지 - 유지 관리 계획 - 전체백업 옵션

백업을 압축하거나, 백업 세트 만료 기간을 설정할 수 있습니다. 

백업 암호화 옵션은 백업 파일을 보호하는 설정입니다. 만약 백업 대상 위치에 대한 보안 설정이 어려워 백업본 접근 제한이 어렵다면, 암호화를 이용해 백업하고, 반드시 설정한 암호키를 안전한 장소에 잘 기록해 두세요.

☑️ 챗GPT 활용: AES 암호화 알고리즘에 대해서 알려줘

 

이미지 - 유지 관리 계획 - 전체백업 보고서

 

다음 과정은 보고서 작성입니다.

이미지 - 유지 관리 계획 - 전체백업 완료

 

모든 과정을 완료했습니다. 설정을 리뷰하고 유지 관리 계획을 생성합니다.

이미지 - 유지 관리 계획 - 전체백업 진행

 

이렇게 전체 백업 과정을 완료합니다. 생성이 완료되면 유지 관리 계획에서 리프레시하고 생성한 계획을 확인합니다. 

 

이미지 - 유지 관리 계획 확인

 

이렇게 전체 백업 과정 - 유지 관리 계획이 잘 생성되었습니다. 주기적으로 유지 관리 계획에서 수립한 과정들이 잘 수행되는지 확인해 보시길 바랍니다.

 

유지 관리 계획 마법사 실행 - 로그 백업

다음으로, 로그 백업 과정을 유지 관리 계획으로 생성합니다.

 

유지 관리 태스크에서 다음 세 가지 작업을 선택합니다.

 

• 데이터베이스 무결성 검사(물리적 전용)
• 인덱스 다시 구성
• 데이터베이스 백업(트랜잭션 로그)

 

이미지 - 유지 관리 계획 - 로그백업 스케줄

 

계획 속성 선택에서 일정을 매 시간으로 설정합니다.

유지 관리 계획 -무결성 검사

이미지 - 유지 관리 계획 - 로그백업 무결성 검사

 

매 시간 실행되는 작업이기 때문에 전체 검사는 필요하지 않습니다. “물리적 전용”을 체크하고 검사합니다.

 

인덱스 다시 구성 태스크

이미지 - 유지 관리 계획 - 로그백업 - 인덱스 재구성

다시 구성은 인덱스 REORGANIZE 작업입니다. 기본 설정으로 “빠르게” 통계를 수집하고, 조각화가 15% 이상, 페이지 수가 1천 개 이상일 경우에 동작하도록 설정합니다.

 

데이터베이스 백업(트랜잭션 로그) 태스크

이미지 - 유지 관리 계획 - 로그백업

 

트랜잭션 로그 설정이 기본 선택되고, 백업할 데이터베이스만 선택합니다. 나머지 대상 및 옵션 탭은 기본으로 설정하며, 특정 옵션이 필요할 경우 변경해 실행하세요.

위의 과정을 수행해 트랜잭션 로그 백업 유지 관리 계획을 생성할 수 있습니다.

유지 관리 태스크에서 “기록 정리”, “유지 관리 정리 태스크”는 강좌에서 진행하지 않았지만 이전 기록들을 특정 기간 후 삭제하는 과정이므로, 직접 진행해 보시길 바랍니다.

아울러, SQL Server 시스템 데이터베이스 역시 주기적으로 백업해야 합니다. 유지 관리 계획을 수립해 시스템 데이터베이스 역시 주기적으로 전체 백업을 수행해 안전한 장소에 저장 하시기 바랍니다.
 

 

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022