이 글에서는 Microsoft® SQL Server®에서 데이터베이스 수준에서 발생할 수 있는 손상(corruption)의 유형을 살펴보고, 이를 감지하는 방법과 고급 복원 및 복구 기법을 활용해 문제를 해결하는 방법을 자세히 설명합니다.
소개
SQL Server는 정교한 내부 구조와 뛰어난 안정성 덕분에 현재 가장 널리 사용되는 관계형 데이터베이스 관리 시스템(RDBMS) 중 하나로 자리 잡았습니다. 많은 기업이 핵심 비즈니스 데이터를 저장하고 관리하기 위해 SQL Server 데이터베이스를 선택하고 있습니다.
기업들은 데이터베이스 관리자(DBA)에게 데이터베이스 성능, 유지 관리, 보안의 지속적인 개선을 기대합니다. 그러나 데이터베이스가 손상되어 데이터에 접근할 수 없게 되면 하드웨어 결함, 디스크 문제, 바이러스 공격, 운영체제(OS) 장애 등 다양한 원인을 의심하게 됩니다. 최적의 해결 기법을 모른 채로는 손상된 데이터베이스를 복구하는 것이 결코 쉬운 일이 아닙니다.
이 글에서는 데이터베이스 손상의 주요 원인을 다루고, 페이지 손상을 식별하는 방법을 소개하며, DBCC CHECKDB 명령어를 깊이 있게 탐구하고, 고급 복원 및 복구 기법까지 시연해 드립니다.
데이터베이스 손상이란?
SQL Server는 사용자 데이터를 페이지(page) 단위로 저장하며, 이 페이지들은 .MDF(기본) 데이터 파일에 위치합니다. .MDF 파일이 손상되면 전체 데이터베이스가 손상될 수 있습니다. 데이터 파일의 페이지가 SQL Server의 메모리에서 디스크로 기록될 때는 정상이었지만, 다시 메모리로 읽어 들일 때 손상된 상태가 되는 경우가 있습니다.
데이터베이스 손상의 주요 원인
데이터베이스 손상을 일으키는 요인은 다음과 같습니다.
- I/O 서브시스템 (데이터베이스 손상의 가장 흔한 원인 중 하나)
- Windows 운영체제
- 암호화 또는 백신 프로그램 같은 파일 시스템 드라이버
- SAN 또는 RAID 컨트롤러
- 디스크
- 메모리
- 파일 헤더
- SQL Server 자체 버그
- 사람에 의한 실수(휴먼 에러)
주요 오류 메시지
손상된 데이터베이스에 접근하려고 하면 다음과 같은 오류 메시지를 만날 수 있습니다.
- SQL Server Msg 823
- SQL Server Msg 824
- SQL Server Msg 825 (읽기 재시도, read retry)
- SQL Server 오류 9004
- 메타데이터 손상 오류(Metadata Corruption Error)
- 페이지 수준 손상 오류(Page Level Corruption Error)
페이지 손상 추적하기
SQL Server에는 디스크에 페이지를 읽거나 쓰는 등의 I/O 작업 중 손상이 발생하면 자동으로 이를 식별하고 경고하는 내장 메커니즘이 있습니다.
SQL Server에서 제공하는 페이지 수준 검증 옵션은 다음 두 가지이며, 디스크상의 페이지를 보호하는 데 도움이 됩니다.
- TORN_PAGE_DETECTION
- 체크섬(Checksum)
TORN_PAGE_DETECTION 옵션이 설정된 경우, 8KB 데이터 파일 페이지가 디스크에 기록될 때마다 16개 × 512바이트 디스크 섹터에 대해 특정 비트가 반전됩니다. 이후 페이지가 메모리로 읽혀질 때 이 값들이 비교되며, 비트가 올바르지 않은 상태로 발견되면 페이지가 잘못 기록되었을 가능성이 높습니다. 이 경우 시스템은 오류 메시지 824(찢어진 페이지, torn-page 오류)를 생성합니다.
체크섬(Checksum) 옵션이 설정된 경우, 페이지를 디스크에 기록할 때 페이지 내용 전체에 대한 체크섬 값을 계산하여 페이지 헤더에 저장합니다. 나중에 페이지가 디스크에서 로드되면 체크섬을 다시 계산하여 페이지 헤더에 저장된 값과 비교합니다. 값이 일치하지 않으면 시스템은 오류 메시지 824(체크섬 실패)를 생성합니다.
823 하드 I/O(hard I/O) 오류와 824 소프트 I/O(soft I/O) 오류는 모두 심각도(severity) 24에 해당하며, msdb.dbo.suspect_pages 테이블에 기록됩니다.
msdb.dbo.suspect_pages 테이블은 단일 페이지 복원 작업에 활용되며, SQL Server 오류 로그와 Windows® 이벤트 로그에도 기록될 수 있습니다.
DBCC CHECKDB
DBCC CHECKDB는 데이터베이스 내 모든 개체의 물리적·논리적 무결성을 검사합니다.
이 명령은 리소스 집약적인 작업이며 병렬 처리를 사용하지만, 추적 플래그(trace flag) 2528을 사용하면 단일 스레드로 실행할 수 있습니다.
핵심 시스템 테이블 기초 검사
기초(primitive) 검사는 스토리지 엔진 메타데이터를 담고 있는 핵심 시스템 테이블과, 데이터가 MDF 파일에 저장되는 할당 경로(allocation path)를 대상으로 수행됩니다.
주요 기초 검사 항목은 다음과 같습니다.
DBCC CHECKALLOC: 데이터베이스의 할당 구조 일관성을 검사합니다. 할당 구조가 유효한지, 하나의 데이터 페이지가 두 개의 테이블에 동시에 할당되지 않았는지 확인합니다.
DBCC CHECKTABLE: 테이블과 인덱스의 일관성을 검사합니다. 테이블 구조와 관련 인덱스를 검증하고, 인덱스 데이터가 테이블의 행과 일치하는지 확인하며 인덱스 순서 키를 점검합니다. 테이블에 FILESTREAM이 사용된 경우 링크의 존재 여부도 검증합니다.
DBCC CHECKCATALOG: 시스템 카탈로그 간의 일관성을 검사합니다.
DBCC CHECKFILEGROUP: DBCC CHECKDB와 유사하게, 특정 파일 그룹에 대한 일관성 검사와 데이터베이스에 대한 할당 검사를 수행합니다.
샘플 코드
DBCC CHECKDB
[ ( database_name | database_id | 0
[ , NOINDEX
| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
) ]
[ WITH
{
[ ALL_ERRORMSGS ]
[ , EXTENDED_LOGICAL_CHECKS ]
[ , NO_INFOMSGS ]
[ , TABLOCK ]
[ , ESTIMATEONLY ]
[ , { PHYSICAL_ONLY | DATA_PURITY } ]
[ , MAXDOP = number_of_processors ]
}
]
]
내부 데이터베이스 스냅샷
CHECKDB 실행 시 내부 데이터베이스 스냅샷을 함께 사용하면 검사를 수행하면서 트랜잭션 일관성을 유지할 수 있어, 차단(blocking) 및 동시성 문제를 방지할 수 있습니다. 데이터베이스 스냅샷을 생성할 수 없는 경우에는 데이터베이스에 대한 배타적 잠금(exclusive lock)과 공유 테이블 잠금을 확보해야 합니다. 이는 테이블 수준 검사를 수행하는 데 필수적입니다. 스냅샷이 생성되지 않으면 master 데이터베이스에서 CHECKDB는 실패합니다.
DBCC CHECKDB가 수행하는 내부 검사 항목은 다음과 같습니다.
- 핵심 시스템 테이블 검사
- 핵심 시스템 테이블 논리 검사
- 기타 테이블 논리 검사
- 할당 검사
- 메타데이터 검사
- Service Broker 유효성 검사
- 인덱싱된 뷰 및 공간(spatial) 인덱스 검사
CHECKDB 오류 유형
다음은 대표적인 DBCC CHECKDB 오류들입니다.
모범 사례
대규모 데이터베이스(VLDB) 환경에서 CHECKDB 실행 시간이 문제가 된다면, 프로덕션 VLDB 데이터베이스의 실행 시간을 줄이기 위해 PHYSICAL_ONLY 옵션을 자주 활용하는 것이 좋습니다. 다만 일반적으로는 옵션 없이 DBCC CHECKDB를 실행하는 것이 권장됩니다. CHECKDB 실행 일정은 각자의 프로덕션 환경에 맞게 조정하시기 바랍니다.
고급 복원 옵션
대부분의 사람들은 손상 문제를 해결할 때 단순한 데이터베이스 복원 기법을 사용하지만, 다음과 같은 고급 복원 기법도 활용할 수 있습니다.
페이지 복원(Page Restore)
이 기법을 사용하면 하나 이상의 페이지를 복원할 수 있습니다. 페이지 수준 복원은 Enterprise Edition에서는 온라인 작업으로 수행되며, 그 외 에디션에서는 오프라인 작업으로 수행해야 합니다. 즉, 복원 과정 동안 데이터베이스가 오프라인 상태여야 할 수 있습니다.
T-SQL 스크립트
RESTORE DATABASE <database_name>
PAGE = '<file: page> [ ,... n ] ' [ ,... n ]
FROM <backup_device> [ ,... n ]
WITH NORECOVERY
페이지 ID를 확인하려면 오류 로그, 이벤트 추적(Event Traces), DBCC, 손상된 페이지와 해당 ID를 나열해 주는 msdb..suspect_pages 테이블 기록 등 다양한 소스를 활용할 수 있습니다.
참고: 부팅(boot) 페이지, 파일 헤더 페이지, 핵심 시스템 테이블의 일부 페이지, 할당 비트맵(allocation bitmap)은 페이지 복원 방식으로 복원할 수 없습니다.
증분(Piecemeal) 복원 및 부분(Partial) 복원
페이지 복원과 마찬가지로, 여러 파일 또는 파일 그룹을 포함하는 데이터베이스에 대해 Enterprise Edition에서는 온라인으로, 그 외 에디션에서는 오프라인으로 증분 복원과 부분 복원을 수행할 수 있습니다.
모든 증분 복원은 PARTIAL 옵션과 함께 전체 백업을 복원하는 부분 복원 시퀀스, 즉 RESTORE DATABASE 문으로 시작됩니다. 이 복원이 완료되면 데이터베이스는 부분적으로 온라인 상태가 되며, 복구가 연기되었기 때문에 나머지 파일은 '복구 보류(recovery pending)' 상태로 남게 됩니다.
증분 복원은 데이터베이스의 복구 모델(recovery model)과 복구 시퀀스에 따라 달라집니다.
복원 시퀀스 예제
파일 그룹 X와 Z, 그리고 기본(primary) 파일 그룹을 부분 복원하려면 다음 코드를 실행합니다.
RESTORE DATABASE DB_XYZ FILEGROUP='X',FILEGROUP='Z'
FROM partial_backup
WITH PARTIAL, RECOVERY;
이 시점(X 지점)에서 상태는 다음과 같습니다.
- 파일 그룹 Z와 기본 파일 그룹은 온라인 상태입니다.
- 파일 그룹 Y의 파일들은 복구 보류 상태입니다.
- 해당 파일 그룹은 오프라인 상태입니다.
이어서 다음을 실행합니다.
RESTORE DATABASE DB_XYZ FILEGROUP='Y' FROM backup WITH RECOVERY;
이제 모든 파일 그룹이 온라인 상태가 됩니다.
기타 고급 복구 기법
그렇다면 복구(repair)가 항상 데이터 복원을 보장할까요? 정답은 "아니오"입니다.
데이터를 손상시킬 수 있는 조합은 매우 다양하며, 모든 조합을 테스트하는 것은 불가능합니다. 예를 들어 시스템 테이블이 손상된 경우, Boot 페이지나 PFS 페이지 같은 페이지에는 복구가 적용되지 않습니다.
주요 복구 옵션은 다음과 같습니다.
REPAIR_REBUILD
이 옵션은 복구를 수행하지만, 손상된 NC(비클러스터형) 인덱스를 재구축할 때 데이터 손실이 발생할 수 있습니다.
REPAIR_ALLOW_DATA_LOSS
이 옵션은 복구를 수행하지만 데이터 손실이 발생할 수 있습니다.
시스템 테이블 인덱스 재구축
클러스터형 시스템 테이블 인덱스는 복구할 수 없지만, 일부 상황에서는 DBCC CHECKTABLE 옵션을 통해 비클러스터형 인덱스를 복구할 수 있습니다.
참고: 심각한 사태를 피하려면 어떤 종류의 고급 복구 기법이든 반드시 원본 데이터베이스가 아닌 복사본을 대상으로 수행해야 합니다.
비클러스터형 인덱스에서 데이터 재구축
클러스터형 인덱스나 힙(heap)이 손상된 경우, 비클러스터형(NC) 인덱스에서 데이터를 복구하는 유일한 방법은 복구(repair)입니다. 그러나 메타데이터가 손상된 경우에는 복구가 작동하지 않을 수 있습니다.
SELECT 문을 사용하여 손상되지 않은 NC 인덱스 선택을 강제할 수 있지만, 이는 NC 인덱스의 컬럼 수준 커버리지(column-level coverage)에 따라 달라집니다.
DBCC PAGE
DBCC PAGE ... WITH TABLERESULTS를 사용하면 키 범위(key range)를 식별할 수 있습니다. 비클러스터형 인덱스에서 이를 구성하여 손상된 페이지를 검토하고 페이지에서 데이터를 복구해 낼 수 있습니다.
결론
이 글을 통해 데이터베이스 손상에 대한 이해를 높이고, 기본적인 기법과 고급 기법을 모두 활용하여 데이터베이스를 복구하는 방법을 익히셨기를 바랍니다. 또한 이러한 기법들은 손상 발생 후 데이터베이스를 다시 온라인 상태로 전환하는 데에도 도움이 됩니다. 무엇보다 손상으로 인한 서비스 중단을 예방하려면 항상 견고한 백업 계획을 갖추는 것이 중요합니다.
궁금한 점이나 의견이 있다면 피드백 탭을 이용해 남겨 주세요.
전문가의 관리 및 구성 서비스로 환경 최적화하기
Rackspace 애플리케이션 서비스(RAS) 전문가들은 폭넓은 애플리케이션 포트폴리오 전반에서 다음과 같은 전문 및 관리형 서비스를 제공합니다.
- e커머스 및 디지털 경험(Digital Experience) 플랫폼
- 엔터프라이즈 리소스 플래닝(ERP)
- 비즈니스 인텔리전스(BI)
- Salesforce 고객 관계 관리(CRM)
- 데이터베이스
- 이메일 호스팅 및 생산성 솔루션
저희가 제공하는 것:
- 편향 없는 전문성: 즉각적인 가치를 창출하는 역량에 초점을 맞춰 현대화 여정을 단순화하고 안내합니다.
- Fanatical Experience™: '프로세스 우선, 기술 차후(Process first. Technology second.)®' 접근 방식과 전담 기술 지원을 결합하여 포괄적인 솔루션을 제공합니다.
- 독보적인 포트폴리오: 풍부한 클라우드 경험을 바탕으로 적합한 기술을 적합한 클라우드에 선택하고 배포할 수 있도록 돕습니다.
- 애자일한 서비스 제공: 고객의 여정이 어디에 있든 그 자리에서 만나 함께 성공합니다.
지금 바로 채팅으로 시작해 보세요.