• 아티클

SQL Q&A백업 압축, 데이터베이스 미러링을 사용한 클라이언트 리디렉션 및 기타 정보

Paul S. Randal

Q: 현재 우리 회사에서는 대부분의 서버를 SQL Server 2008로 업그레이드하고 있는데 가장 기대하고 있는 기능 중 하나가 백업 압축입니다. 각 서버의 모든 데이터베이스에서 기본적으로 이 기능을 설정할 수는 있지만 그렇게 하지 않는 편이 좋다는 이야기도 들었습니다. 손해 볼 부분이 없을 것 같은데 이 기능을 기본적으로 설정하지 않는 편이 좋다는 이유가 무엇인지 궁금합니다. 그 이유를 설명해 주시면 감사하겠습니다.

A: 대답은 제가 늘 하는 말이지만 상황에 따라 다릅니다. 이에 대한 약간의 배경 정보부터 살펴보겠습니다.

고려할 핵심적인 사항은 백업 압축을 설정했을 때 각 데이터베이스 백업에 적용되는 압축률입니다. 알고리즘을 통해 압축되는 대상의 압축률은 압축되는 실제 데이터에 따라 다릅니다.

임의 데이터(예: 작은 정수)는 압축 효율이 그리 좋지 않습니다. 따라서 대부분의 경우에는 데이터베이스의 인덱스와 테이블의 내용에 따라 압축률이 결정됩니다.

다음은 높은 압축률을 얻을 수 없는 백업 압축의 몇 가지 예입니다.

  • 데이터베이스에 투명한 데이터 암호화가 설정되어 있는 경우 압축되는 데이터가 임의의 작은 값이므로 압축률이 매우 낮습니다.
  • 데이터베이스의 데이터 대부분이 열 수준에서 암호화된 경우 열 암호화에 의해 데이터가 불규칙화되므로 압축률이 낮습니다.
  • 데이터베이스의 테이블 대부분에 데이터 압축이 설정되어 있는 경우 압축률이 낮습니다. 이미 압축되어 있는 데이터를 다시 압축하면 효과가 거의 없기 때문입니다.

압축률이 낮을 때의 문제는 낮은 압축률 자체가 아니라 별다른 이득 없이 압축 알고리즘을 실행하는 데 사용되는 CPU 리소스입니다. 데이터 청크가 아무리 잘 압축되더라도 압축과 압축 풀기 알고리즘을 실행하는 데는 항상 CPU 리소스가 사용됩니다.

이것은 데이터베이스에서 백업 압축을 사용할지 결정하기 전에 항상 백업에서 각 데이터베이스 압축이 얼마나 잘 수행되는지 확인해야 함을 의미합니다. 그렇지 않으면 CPU 리소스가 낭비될 수 있습니다. 이것이 알고 있어야 할 기본적인 내용입니다.

요약하면 데이터베이스 대부분이 백업 압축으로 혜택을 볼 수 있다면 서버 수준에서 백업 압축을 설정하고 WITH NO_COMPRESSION 옵션을 사용하여 일부 백업 작업을 수동으로 변경하는 것이 좋습니다. 반대로 데이터베이스 대부분이 백업 압축으로 혜택을 볼 수 없다면 서버 수준에서 백업 압축을 해제하고 WITH COMPRESSION 옵션을 사용하여 일부 백업 작업을 수동으로 변경하는 것이 좋습니다.

Q: 작년에 우리 회사에서는 데이터베이스 미러링을 사용하도록 데이터베이스를 업그레이드하여 오류가 발생하더라도 미러로 장애 조치를 수행하고 응용 프로그램을 계속 실행할 수 있도록 했습니다. 시스템을 디자인하는 동안 실제로 데이터베이스 장애 조치를 실행해 보았으며 모든 것이 올바르게 작동했습니다. 그런데 저번 주에 실제로 오류가 발생했고 데이터베이스 장애 조치가 수행되었지만 모든 응용 프로그램 트랜잭션이 중단되었고 응용 프로그램은 장애 조치 서버에 연결되지 않았습니다. 앞으로 장애 조치 중에 응용 프로그램 연결이 끊어지지 않고 트랜잭션이 계속될 수 있도록 SQL Server를 설정하려면 어떻게 해야 합니까?

A: 우선은 응용 프로그램이 장애 조치에 대응하는 방법과 데이터베이스 미러링을 사용한 클라이언트 리디렉션 관리라는 두 가지 내용으로 나누어 보겠습니다.

SQL Server에서 제공되는 고가용성 기술 중 하나를 사용하여 장애 조치를 수행하면 오류가 발생한 서버에 대한 클라이언트 연결이 끊어지고 진행 중이었던 트랜잭션이 모두 손실됩니다. 장애 조치 상황인지 여부에 관계없이 서버 간에 진행 중이었던 트랜잭션을 마이그레이션하는 것은 불가능합니다. 진행 중이었던 트랜잭션은 고가용성 기술에 따라 장애 조치 서버에 아예 존재하지 않게 되거나 진행 중인 트랜잭션으로 존재하지만 장애 조치 서버의 데이터베이스를 온라인으로 설정하는 프로세스의 일부로 롤백됩니다.

지속적으로 트랜잭션 로그를 주 서버에서 미러 서버로 전송하는 데이터베이스 미러링의 경우에는 일반적으로 후자의 상황이 적용됩니다. 즉, 진행 중이었던 모든 트랜잭션은 미러 데이터베이스를 주 데이터베이스로 전환하는 과정에 롤백됩니다.

따라서 다른 서버로 장애 조치를 수행할 가능성이 있는 서버에서 실행되는 응용 프로그램이 정상적으로 작동하려면 두 가지 사항이 필요합니다.

  1. 서버 연결이 끊어지더라도 오류를 일으키지 않고 짧은 시간 내에 다시 연결을 시도할 수 있어야 합니다.
  2. 트랜잭션이 중단되더라도 오류를 일으키지 않고 장애 조치 서버와 연결된 후 트랜잭션을 다시 시도할 수 있어야 합니다(중간 계층 트랜잭션 관리자를 사용하는 등의 방법으로).

여기에서 클라이언트 변경 없이도 장애 조치 후에 클라이언트 연결 리디렉션을 허용하는 유일한 고가용성 기술은 장애 조치 클러스터링입니다. 클라이언트는 가상 서버 이름에 연결하며 활성화된 실제 클러스터 노드로 투명하게 리디렉션됩니다.

로그 전달 및 복제와 같은 고가용성 기술의 경우에는 장애 조치 서버의 서버 이름이 다르므로 장애 조치 후에 클라이언트 연결을 수동으로 리디렉션해야 합니다. 이 수동 리디렉션을 수행하는 데는 몇 가지 방법이 있습니다.

  • 장애 조치 서버에 대한 다시 연결을 시도하도록 클라이언트에 장애 조치 서버 이름을 하드코드할 수 있습니다.
  • 100/0—0/100 구성으로 네트워크 부하 분산을 사용할 수 있습니다. 이렇게 하면 연결이 장애 조치 서버로 전환될 수 있습니다.
  • 서버 이름 별칭이나 DNS 테이블의 전환 항목과 같은 방법을 사용할 수 있습니다.

데이터베이스 미러링의 경우에는 이러한 선택 사항 중 어떤 것이라도 사용이 가능합니다. 그러나 데이터베이스 미러링에도 기본 제공 클라이언트 리디렉션 기능이 있습니다. 클라이언트 연결 문자열은 명시적으로 미러 서버의 이름을 지정할 수 있으며 주 서버에 연결할 수 없는 경우 자동으로 미러에 대한 연결을 시도합니다. 이 프로세스를 명시적 리디렉션이라고 합니다.

클라이언트 연결 문자열을 변경할 수 없는 경우 오류가 발생한 서버가 현재 미러 서버로 실행 중이면 암시적 리디렉션이 가능할 수 있습니다. 이에 대한 모든 연결은 자동으로 새로운 주 서버로 리디렉션되지만 이 방법은 미러 서버가 실행 중인 경우에만 작동합니다.

이러한 선택 사항에 대한 자세한 내용은 SQL Server 2005 백서 "데이터베이스 미러링으로 응용 프로그램 장애 조치 구현"을 참조하십시오.

Q: 우리 회사에서는 SQL Server 2005로 업그레이드할 때 분할 관리와 슬라이딩 윈도우 메커니즘을 활용하기 위해 큰 테이블을 분할 가능하도록 다시 디자인했습니다. 이 작업에는 2008년 8월 기사("분할, 일관성 확인 및 기타")의 내용을 참조했습니다. 그런데 약간의 문제가 발생했습니다. 쿼리가 같은 파티션을 액세스하고 있지도 않은데도 동시 응용 프로그램 쿼리가 전체 테이블에 대해 차단되는 문제가 종종 발생합니다. SQL Server 2008에서는 이 문제가 해결되었다는 이야기를 들었습니다. 이 문제를 해결하려면 어떻게 해야 합니까?

fig01.gif

그림 1 분할된 테이블에서 잠금 검토

A: 이 문제는 잠금 에스컬레이션이라고 하는 메커니즘에 의해 발생합니다. SQL Server는 쿼리가 데이터를 읽거나 쓰는 동안 데이터를 보호하기 위해 잠금을 수행합니다. 전체 테이블, 데이터 파일 페이지 또는 개별 테이블/인덱스 행에 대한 잠금을 수행할 수 있으며 모든 잠금에는 약간의 메모리가 소비됩니다.

쿼리가 지나치게 많은 잠금을 유발하는 경우 SQL Server는 테이블의 행이나 페이지에 대한 모든 잠금을 전체 테이블에 대한 단일 잠금으로 대체하도록 결정할 수 있습니다. 이러한 대체가 발생할 때의 임계값은 대략 잠금 5000개이지만 정확한 알고리즘은 복잡하며 구성 가능합니다. 이 프로세스를 잠금 에스컬레이션이라고 합니다.

SQL Server 2005에서는 예를 들어 쿼리 A가 테이블의 단일 파티션에 대한 작업을 수행할 때 잠금 에스컬레이션을 트리거할 만큼 많은 잠금을 수행하는 경우 전체 테이블이 잠깁니다. 이에 따라 같은 테이블의 다른 파티션에 대한 쿼리 B의 작업이 차단될 수 있습니다. 따라서 쿼리 A가 작업을 완료하고 해당 잠금을 삭제할 때까지 쿼리 B는 차단됩니다.

SQL Server 2008에서는 테이블이 파티션 수준 잠금 에스컬레이션을 허용하도록 잠금 에스컬레이션 메커니즘이 향상되었습니다. 앞에서 살펴본 예의 경우에는 쿼리 A에 의한 잠금 에스컬레이션이 전체 테이블이 아닌 쿼리 A가 사용하는 단일 파티션만 잠근다는 것을 의미합니다.

그러면 쿼리 B는 차단되지 않고 다른 파티션에 대해 작업을 수행할 수 있습니다. 쿼리 B 역시 잠금 에스컬레이션을 트리거할 수 있지만 이 경우에도 전체 테이블이 아니라 쿼리 B가 사용하는 파티션에 대해서만 잠금이 수행됩니다.

다음과 같은 구문을 사용하여 이 잠금 에스컬레이션 모델을 설정할 수 있습니다.

ALTER TABLE MyTable SET (LOCK_ESCALATION = AUTO);
GO

이 구문은 테이블이 분할되어 있으면 파티션 수준 잠금 에스컬레이션을 사용하고 테이블이 분할되어 있지 않으면 보통 테이블 수준 잠금 에스컬레이션을 사용하도록 SQL Server 잠금 관리자에 지시합니다. 기본 동작은 테이블 수준 잠금 에스컬레이션을 사용하는 것입니다. 사용하는 쿼리의 동작에 따라서는 교착 상태를 일으킬 수 있으므로 이 옵션을 설정할 때는 주의가 필요합니다.

예를 들어 쿼리 A와 쿼리 B가 테이블에서 서로 다른 파티션에 대한 잠금 에스컬레이션을 유발한 후 각각 상대방 쿼리에 의해 잠긴 파티션에 액세스를 시도하는 경우, 두 쿼리 중 하나는 교착 상태 모니터 프로세스에 의해 중단됩니다.

그림에는 파티션 수준 잠금 에스컬레이션이 발생한 분활된 테이블에 대해 보유 중인 잠금을 검토하기 위해 sys.partitions 시스템 카탈로그 뷰(첫 번째 결과 집합)와 sys.dm_os_lock DMV(두 번째 결과 집합)를 쿼리하는 예가 나와 있습니다. 이 경우에 두 개의 파티션 수준 배타적 잠금(출력의 HOBT 잠금)이 있지만 테이블 잠금(출력의 OBJECT 잠금)은 배타적이지 않으므로 잠금 에스컬레이션이 수행되더라도 여러 쿼리가 파티션에 액세스할 수 있습니다. 이러한 두 파티션 잠금의 리소스 ID는 sys.partitions 출력에 있는 테이블의 처음 두 파티션에 대한 파티션 ID와 일치합니다.

필자는 올해 초반에 파티션 수준 잠금 에스컬레이션의 작동 방법과 잠재적인 교착 상태에 대한 블로그 게시물을 올렸습니다. SQL Server 2008 온라인 설명서의 "데이터베이스 엔진에서의 잠금" 항목에는 SQL Server 2008에서 사용되는 잠금의 모든 측면에 대한 자세한 내용이 포함되어 있습니다.

Q: 사용 중인 서버 중 하나에서 데이터베이스에 대한 트랜잭션 로그가 포함된 디스크와 관련된 몇 가지 문제가 발생하여 데이터베이스가 주의 대상이 되었습니다. 최근 전체 백업은 5주 전에 수행한 것이었고 모든 로그 백업을 복원하는 데는 상당한 시간이 걸릴 것으로 생각되었습니다. 문제가 발생했을 때는 업무가 끝난 시간이었기 때문에 가동 중지가 발생하지 않도록 손상된 트랜잭션 로그를 다시 작성했습니다. 이렇게 하면 상황에 따라서 문제가 발생할 수 있다는 것은 알고 있었지만 데이터를 액세스하는 것이 없다면 안전하다고 생각했습니다. 우리가 잘 대처한 것일까요?

A: 간단하게 말해서 저라면 백업에서 복구가 불가능한 경우에만 트랜잭션 로그를 다시 작성했을 것입니다. 트랜잭션 로그를 다시 작성하는 데 따르는 위험성은 알고 계시겠지만(그렇지 않은 독자는 필자의 블로그 게시물 "최후에 써야 할 방법을 미리 사용하는 사람들"을 참조할 것) 데이터베이스가 주의 대상이 되었다는 것은 크래시 복구 실행 중 또는 트랜잭션을 롤백하는 중에 복구가 실패했음을 의미합니다. 즉, 데이터베이스에 데이터 손상이 있을 가능성이 있습니다.

사용량이 많지 않은 동안 문제가 일어나기는 했지만 예약된 작업이나 백그라운드 작업에 대해서도 고려해 보셨습니까? 로그가 손상되었을 때 클러스터형 인덱스를 다시 작성하거나 재구성하는 관리 작업이 실행 중이었을 수 있으며, 힙이나 클러스터형 인덱스의 페이지에 고스트 정리 작업을 수행하는 백그라운드 작업이 실행 중이었을 수도 있습니다. 이러한 두 경우 모두 클러스터형 인덱스 구조에 변화를 일으킬 수 있으며 올바르게 롤백하지 않으면 데이터베이스에 손상을 초래하고 데이터도 손실될 수 있습니다.

결론적으로 말해 트랜잭션 로그 재작성은 추가적인 손상과 데이터 손실을 야기할 우려가 매우 높기 때문에 모든 재해 복구 시나리오에서 반드시 최후의 수단으로만 사용해야 합니다. 최소한 해당 데이터베이스에 완전한 DBCC CHECKDB를 실행하여 손상된 부분이 있는지 여부를 확인해야 합니다.

원론적인 이야기로 돌아가서 트랜잭션 로그 재작성과 같은 극단적인 방법에 의존할 필요 없이 시기 적절한 복원을 수행할 수 있도록 백업 전략을 변경해야 합니다. 백업 전략을 디자인하는 단계는 이 칼럼의 범위를 벗어나지만 올해 안에 이에 대한 기사를 다룰 계획입니다. 기대하십시오.

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