• 아티클

SQL Q&AI/O 오류, 데이터베이스 미러링 및 기타

Paul S. Randal

Q. 제가 관리하는 데이터베이스에서 정기적으로 일관성 확인을 실행하기로 하고 사용자 쿼리에서 발생하는 I/O 오류를 잡아내기 위해 몇 가지 SQL 에이전트 경고도 추가했습니다. 그런데 손상 문제가 발생한 데이터베이스가 없어서 확인 및 경고를 위해 구현한 논리가 제대로 작동하고 있는지 알 수가 없습니다. 제가 설정한 대로 올바르게 작동하는지 확인하기 위해 테스트 데이터베이스에서 손상을 일으킬 방법이 있습니까? 또한 I/O 오류를 감지하려면 무엇을 더 해야 하는지도 알려 주십시오.

A. SQL Server® 2000에서 테스트를 위해 손상된 데이터베이스를 만들 때는 주로 테스트 데이터베이스의 sysindexes 테이블에서 한 행을 수동으로 삭제하는 방법을 썼습니다. 그러나 SQL Server 2005에서는 이 방법으로 시스템 테이블을 손상시키기가 매우 어렵습니다. 테스트 데이터베이스를 직접 손상시키는 가장 좋은 방법은 데이터베이스를 종료한 상태에서 16진 편집기를 사용하여 데이터 파일을 수정하는 것입니다. 방법은 다음과 같습니다.

  • 데이터 파일이 잠기지 않도록 데이터베이스를 종료합니다. (데이터베이스가 분리되지 않도록 주의하십시오. 잘못된 페이지를 손상시키면 다시 연결하지 못하게 될 수 있습니다.)
  • 파일에 대해 100페이지 이상의 오프셋(819200바이트 이상)을 선택하되 8192바이트 경계(페이지 경계)에 맞춥니다. 그러면 치명적인 메타데이터 페이지 및 할당 비트맵을 피하여 데이터베이스를 시작하고 DBCC CHECKDB를 실행할 수 있습니다.
  • 선택한 오프셋에서 몇 바이트의 0을 파일에 기록합니다. 이렇게 하면 거의 확실히 페이지 헤더 손상 오류가 일어납니다.

한편, 손상된 테스트 데이터베이스를 얻는 가장 빠른 방법은 다른 사람이 이미 만들어 둔 손상된 데이터베이스를 사용하는 것입니다. 제 블로그(go.microsoft.com/fwlink/?LinkId=115151)에도 손상된 SQL Server 2000 및 SQL Server 2005 데이터베이스 샘플(설명 포함)이 있습니다.

I/O 오류를 감지하기 위해 필요한 조치를 묻는 두 번째 질문에 대한 답변은 페이지 체크섬을 사용하시라는 것입니다. 이 기능은 I/O 하위 시스템에서 발생한 오류로부터 전체 데이터베이스 페이지를 보호하기 위한 수단으로 SQL Server 2005에 도입되었습니다.

기본적으로, 디스크에 페이지를 기록할 때 SQL Server에서 마지막으로 이루어지는 작업은 전체 8KB 페이지에 대한 체크섬을 계산하고 그 체크섬을 해당 페이지에 스탬프로 찍는 것입니다. 디스크에서 페이지를 읽을 때 페이지 체크섬이 있으면 체크섬을 다시 계산하여 해당 페이지에 저장된 것과 비교합니다. 양쪽 값이 일치하지 않으면 SQL Server 외부의 원인으로 페이지가 손상된 것이며 824 오류가 발생합니다. 이 오류는 해당 페이지를 읽도록 한 연결에 표시되고 SQL Server 오류 로그 및 Windows® 응용 프로그램 이벤트 로그에도 기록됩니다.

SQL Server 2005 및 SQL Server 2008에서 만들어진 모든 데이터베이스는 기본적으로 페이지 체크섬을 사용합니다. 그러나 이전 버전의 SQL Server에서 업그레이드한 데이터베이스의 경우에는 페이지 체크섬을 수동으로 설정해야 합니다. 다음 코드로 페이지 체크섬을 사용하도록 설정할 수 있습니다.

ALTER DATABASE mydb SET PAGE_VERIFY CHECKSUM;

팁: 기본 SQL Server 포트 변경

기본적으로 SQL Server 인스턴스용으로 구성된 포트는 1433입니다. 한 인스턴스에서 이 포트를 사용하고 있을 때는 다른 인스턴스에서 사용할 수 없습니다. 따라서 tcp를 사용하여 net에서 수신하는 두 번째(명명된) 인스턴스를 설치하려면 다른 포트가 필요합니다. 또한 관리자가 모호화를 위해 포트를 변경하고자 하는 경우도 있습니다(이러한 모호화 방법은 부수적이며 포트 검색기로 쉽게 감지됨). 물론 이 경우에는 클라이언트가 다른 포트를 사용하도록 구성해야 합니다. 주로 사용하는 방법은 다음 세 가지입니다.

첫째, 만일 관리자가 인스턴스의 포트를 5555로 변경했다면 MyServername,5555 구문을 사용하여 연결하려는 컴퓨터 이름 안에 해당 인스턴스의 포트 번호를 지정하면 됩니다. 포트가 다시 변경되면 클라이언트에서도 connectionStrings를 다시 변경해야 합니다.

두 번째 방법은 클라이언트에 구성된 SQL Server 별칭을 사용하는 것입니다. 별칭 이름을 지정하는 동시에 서버 이름, 포트 이름 및 프로토콜도 지정해야 합니다. 이렇게 구성하면 이 별칭을 서버 이름처럼 사용하여 데이터베이스 인스턴스에 연결할 수 있습니다. 이 방법의 장점은 설정이 레지스트리에 저장되므로 서버 구성의 변경을 도메인 관리자가 배포할 수 있다는 것입니다.

사용자가 명명된 인스턴스의 이름만 아는 상태에서 MachineName\InstanceName을 사용하여 connectionString 내에 그 이름을 지정하는 세 번째 방법은 SQL Server Browser 서비스를 사용하는 것입니다. 이 서비스는 실행 서비스의 일환으로 SQL Server 2000에서 이미 구현되었습니다. 그러나 SQL Server 2005에는 SQL Server Browser 서비스가 별도의 서비스로 내장되어 있습니다. 이 서비스는 시스템에서 인스턴스를 검색하는 것 외에도 요청된 인스턴스에 대한 적합한 포트 번호로 포트 1434에서 수신 UDP(User Datagram Protocol) 요청에 응답하여 클라이언트 리디렉션을 가능하게 하고 투명한 연결을 지원합니다.

—Jens K. Suessmeyer, Microsoft 데이터베이스 컨설턴트

Q. 데이터베이스에서 조각화를 모두 없애기 위해, SQL Server 2005 Enterprise Edition SP2에서 실행되는 프로덕션 데이터베이스의 모든 인덱스를 다시 구축하는 야간 유지 관리 계획을 설정했습니다. 그랬더니 데이터베이스가 너무 커지고 디스크 공간이 부족해져서 잉여 공간을 모두 축소하는 단계를 추가했습니다. 그러자 이제는 재구축 단계가 작동하지 않는 것 같습니다. 왜 그럴까요?

A. 유지 관리 계획을 수립할 때 자주 발생하는 문제입니다. 축소-증가-축소-증가의 덫에 빠지셨습니다.

인덱스를 재구축할 때는 기존 인덱스를 삭제하기 전에 인덱스 복사본을 새로 만듭니다. 이를 위해 데이터베이스 파일에 여분의 공간이 있어야 합니다. 필요한 공간의 크기는 대개 현재 인덱스에 사용되는 공간과 동일합니다. SQL Server 2000에서는 인덱스 행을 정렬하기 위한 추가 공간(인덱스 크기의 약 20%)도 필요했으나 SQL Server 2005부터는 단순 인덱스 재구축의 경우 필요가 없어졌습니다.

관리자는 인덱스 재구축 중 만들어진 추가 공간을 제거하기 위해 유지 관리 계획에서 재구축 단계 다음에 축소 작업을 추가하기도 합니다. 하지만 이러한 축소가 알고리즘의 특성상 인덱스 조각화를 일으킨다는 사실은 잘 알려져 있지 않습니다. 즉, 새로 재구축 및 조각 모음한 인덱스가 즉시 다시 조각으로 나뉘어 재구축 효과가 전혀 나타나지 않게 됩니다.

다음 번 인덱스 재구축을 수행할 때 데이터베이스 파일이 다시 커진다면 데이터베이스에 추가 공간을 남겨두고 축소 작업은 전적으로 피하는 것이 좋습니다. (한편 데이터베이스 파일의 증가-축소가 반복되면 OS 수준의 파일 조각화가 일어나 인덱스 조각화와 마찬가지로 성능이 저하될 수 있습니다.)

마지막으로 고려해야 할 한 가지는 인덱스 재구축 빈도를 줄이는 것입니다. 제가 SQL Server 2000용으로 작성했던 구형 DBCC INDEXDEFRAG나 SQL Server 2005 및 SQL Server 2008의 새로운 ALTER INDEX REORGANIZE 구문 등 다른 방법을 시도해 볼 수도 있습니다.

인덱스 조각화를 설명하고 조각 제거 시기에 대한 지침을 제시하는 유용한 백서가 있습니다(go.microsoft.com/fwlink/?LinkId=115154). SQL Server 2000용으로 작성된 백서이지만 개념은 같습니다.

Q. 저희 조직에서 재해 복구 전략을 평가하고 있는데, 데이터 미러링이 현재 상황에 가장 적합한 방법이라고 생각됩니다. 이전에 서버 통합 프로젝트를 실시한 결과 보호하려는 서버에 관련 없는 데이터베이스가 많이 생겨났으며, 이들 전부에 대해 데이터베이스 미러링을 사용하고자 합니다. 성능 저하 없이 미러링할 수 있는 데이터베이스의 수는 최대 몇 개나 되는지 알고 싶습니다.

A. 이 질문에 대한 답변은 제가 자주 쓰는 말로 해야겠군요. 그때 그때 다릅니다. 안내서에는 미러링하는 데이터베이스가 인스턴스당 10개를 넘지 않도록 하라고 되어 있지만, 여기서 10개는 대부분의 사용자에게 해당되는 최대값을 추정한 수입니다. 여러분은 하드웨어 상황을 토대로 다음 요소를 고려해야 합니다.

  • 주 인스턴스와 미러 인스턴스의 메모리 용량은 얼마입니까? (동일한 것이 이상적입니다.)
  • 주 인스턴스와 미러 인스턴스의 처리 성능은 얼마입니까? (이것도 동일해야 합니다.)
  • 미러 인스턴스의 I/O 하위 시스템 대역폭은 얼마나 됩니까? (주 인스턴스와 동일해야 합니다.)
  • 각 데이터베이스에서 작업 부하로 생성되는 트랜잭션 로그는 얼마나 됩니까?
  • 주 인스턴스와 미러 인스턴스 간에 사용할 수 있는 네트워크 대역폭은 얼마나 됩니까?

마지막 두 요소가 가장 중요합니다. 두 인스턴스 간에 사용할 수 있는 네트워크 대역폭이 미러링되는 모든 데이터베이스에서 초당 생성되는 트랜잭션 로그를 처리하기에 부족하면 주 데이터베이스의 성능이 저하됩니다. SQL Server 2008에서는 로그 스트림 압축으로 이 부담을 어느 정도 줄일 수 있습니다.

다음으로 중요한 문제는 미러링에 필요한 메모리 및 스레드 요구 사항입니다. 미러링된 각 데이터베이스는 스레드 하나와 약간의 메모리를 사용합니다. 저성능의 서버에 미러링되는 데이터베이스가 많고 여기에 통상적인 작업 부하가 더해지면 서버에 너무 큰 부담이 될 수 있습니다.

또한 데이터베이스 미러링의 실행 방법도 고려해야 합니다. 동기 모드에서는 모든 트랜잭션 로그 레코드가 미러링된 데이터베이스의 트랜잭션 로그에 복사될 때까지 주 데이터베이스에서 트랜잭션을 커밋할 수 없습니다. 따라서 네트워크 과부하로 인한 지연 때문에 주 데이터베이스에서 작업 부하 성능 문제가 발생할 수 있습니다.

비동기 모드에서는 주 데이터베이스에서 기다리지 않고 트랜잭션을 커밋할 수 있지만, 네트워크 지연으로 인해 미러 데이터베이스로 전송 대기 중인 트랜잭션 로그의 양이 증가할 수 있습니다. 이 경우 트랜잭션 로그 크기 문제가 발생할 수 있습니다. 더욱이, 전송되지 않은 트랜잭션 로그는 오류 발생 시 손실되므로 전송되지 않은 트랜잭션 로그가 많아질수록 복구 상황에서 데이터 손실 가능성이 높아집니다.

상황에 따라 매우 다양한 시나리오가 가능하며, 실제 프로덕션 환경에서 흥미로운 상황을 여러 번 목격했습니다. 일례로, 작업량이 매우 적고 동시에 모두 사용하는 일도 없는 데이터베이스 150개로 구성된 환경이 있었습니다. 이 환경에서는 150개의 데이터베이스가 모두 문제 없이 미러링되었습니다.

반대로, 부하가 매우 큰 데이터베이스 3개뿐이지만 네트워크 연결이 좋지 않은 환경도 있었습니다. 이 환경에서는 네트워크 대역폭 부족으로 작업 부하 성능 저하가 발생하여 하나의 데이터베이스도 제대로 미러링할 수 없었습니다.

성공의 관건은 다름 아닌 로그 생성을 계산하는 것입니다. 미러링하려는 데이터베이스 수를 지원하기에 충분한 네트워크 대역폭이 확보된다면 괜찮습니다. 실전에 들어가기 전에 구성을 테스트하여 데이터베이스 유지 관리 작업을 비롯하여 트랜잭션 로그가 생성될 수 있는 모든 작업이 포함되도록 하십시오.

Paul S. Randal은 SQLskills.com의 관리 이사 겸 SQL Server MVP이며, 1999년부터 2007년까지 Microsoft의 SQL Server 저장소 엔진 팀에서 근무했습니다. DBCC CHECKDB/repair for SQL Server 2005를 작성했고 SQL Server 2008 개발 과정에서 핵심 저장소 엔진 부분을 담당했습니다. 재해 복구, 고가용성 및 데이터베이스 유지 관리 분야의 전문가인 Paul 이사는 각종 컨퍼런스에서도 꾸준히 의견을 발표하고 있습니다. 블로그 주소는 SQLskills.com/blogs/paul입니다.

© 2008 Microsoft Corporation 및 CMP Media, LLC. All rights reserved. 이 문서의 전부 또는 일부를 무단으로 복제하는 행위는 금지됩니다..