SQL Q&A: 분할, 일관성 확인 및 기타

아티클
08/24/2016

SQL Q&A분할, 일관성 확인 및 기타

Paul S. Randal

Q. 실수로 프로덕션 데이터베이스를 SQL Server® 2005 서버에 연결했는데, SQL Server 2000을 실행 중인 올바른 서버에 연결하려고 합니다. 단순히 데이터베이스를 분리한 다음 SQL Server 2000 서버에 연결하려고 시도했으며 백업 및 복원을 사용해서도 동일한 작업을 시도했지만 모두 제대로 되지 않았습니다. SQL Server 2000 서버에서 작동하지 않는 이유가 무엇입니까? 이 데이터베이스가 현재 가지고 있는 유일한 사본입니다.

A. 여기서 주의해야 할 첫 번째는 백업의 중요성입니다. DBA는 대개 손상이나 기타 심각한 재해로부터 복구하기 위한 백업의 필요성을 생각합니다. 하지만 이번 경우는 실수로 인한 업그레이드라 하더라도 업그레이드 중 무엇인가 잘못된 흔치 않은 사고입니다. 그래도 데이터베이스에 무슨 일이 발생할 경우를 대비하여 항상 최근의 전체 데이터베이스 백업을 가지고 있어야 합니다.

의도적이든 실수로 인한 업그레이드든 간에 업그레이드는 일방향 작업이며 되돌리기가 매우 어렵습니다. SQL Server 버전 간에 업그레이드할 경우 일련의 업그레이드 단계가 데이터베이스에서 수행됩니다. 각 단계에는 대개 데이터베이스에 대한 몇 가지 물리적 변경이 포함되고, 각 단계는 데이터베이스의 버전 번호를 높입니다.

예를 들어, SQL Server 2000에서 SQL Server 2005로 데이터베이스를 업그레이드할 경우 수행되는 주요 변경 사항 중 하나는 테이블, 인덱스, 열, 할당 및 데이터베이스의 관계 및 물리적 구조 관련 기타 세부 사항에 대한 다양한 메타데이터를 포함하는 데이터베이스의 시스템 카탈로그(시스템 tablesor 데이터베이스 메타데이터라고도 함)의 구조 변경입니다.

이러한 각 업그레이드 단계가 수행되면서 데이터베이스 버전 번호가 높아집니다. 예를 들어, SQL Server 7.0 데이터베이스의 버전 번호는 515, SQL Server 2000 데이터베이스의 버전 번호는 539, 그리고 SQL Server 2005 데이터베이스의 버전 번호는 611(또는 vardecimal 기능이 사용된 경우 612)입니다. 이를 통해 SQL Server에서 데이터베이스에 대해 수행된 마지막 업그레이드 단계를 알 수 있습니다.

SQL Server 릴리스는 더 최근의 SQL Server 릴리스로 업그레이드된 데이터베이스를 읽을 수 없습니다. 예를 들어, SQL Server 2000은 SQL Server 2005로 업그레이드된 데이터베이스를 읽을 수 없습니다. 이전 릴리스에는 업그레이드된 구조 및 데이터베이스 레이아웃을 해석하는 데 필요한 코드가 없기 때문입니다. 그리고 이로 인해 SQL Server 2005로 업그레이드된 데이터베이스를 SQL Server 2000에 다시 연결할 수 없게 되는 문제가 발생합니다.

전체 데이터베이스 백업이 없으므로 업그레이드된 데이터베이스에서 모든 데이터를 내보내고 수동으로 새로운 SQL Server 2000 데이터베이스로 다시 전송할 수밖에 없습니다. SQL Server 2005에서 아직 새로운 기능을 사용하지 않았다면 데이터베이스 구조를 스크립팅하고 SQL Server 2000에서 데이터베이스를 만든 다음 데이터 내보내기/가져오기 작업을 수행할 수 있습니다.

SQL Server 2005에서 데이터베이스를 스크립팅하려면 SQL Server Management Studio에서 개체 탐색기를 사용할 수 있습니다(데이터베이스를 마우스 오른쪽 단추로 클릭하고 작업을 선택한 다음 스크립트 생성 옵션 선택). 마법사는 이해하기 쉬우며 모든 개체, 인덱스, 제약 조건, 트리거 등을 만들기 위한 스크립트를 생성합니다.

Q. 최근 기본 테이블에서 테이블 분할을 사용하도록 스키마를 다시 설계했습니다. 이렇게 하면 성능 향상에 도움이 된다고 들었습니다. 데이터베이스는 단일 120GB 드라이브에 저장되고 테이블은 단일 파일 그룹에 포함되어 있습니다. 수행되는 슬라이딩 윈도우는 없고 단지 새 파티션만 매주 추가되며, 모든 데이터를 온라인에서 사용할 수 있어야 합니다. 대부분의 쿼리는 한 주 내의 데이터를 처리하고 지난 해의 데이터로 작업하는 경우는 가끔씩 있습니다. 단일 파일 그룹에서 작업하는 것이 더 쉬워 보입니다. 이 방법이 올바릅니까? 아니면 더 수행할 작업이 있습니까?

A. 단일 파일 그룹을 사용하는 것이 더 쉬울 수 있지만, 이 경우 분할 사용의 이점을 활용하지 못할 수 있습니다. 분할을 사용하는 주요 이유는 더욱 효율적인 데이터베이스 유지 관리 및 재해 발생 시 데이터 가용성 증가와 성능 향상을 제공하는 스키마를 생성할 수 있다는 것입니다.

분할의 전형적인 예는 각 파티션이 지난 해 한 달의 매출 데이터를 나타내는 12개의 파티션이 있는 매출 테이블입니다. 월말에 가장 오래된 파티션은 교체되어 나가고(및 보관 또는 삭제) 새 파티션이 교체되어 들어옵니다. 이것이 위에서 언급한 슬라이딩 윈도우 시나리오입니다. 현재 월의 파티션은 읽기/쓰기로 설정되고 이전 월은 읽기 전용입니다. 각 파티션은 별도의 파일 그룹에 저장됩니다. 이 스키마를 통해 분할의 모든 이점을 얻을 수 있지만 모든 환경에서 가장 최적은 아닙니다.

제 아내인 Kimberly는 더욱 효율적인 인덱싱이 가능하도록 위에서 설명한 스키마에 대한 변형을 생각해 냈습니다. 매출 테이블을 두 개의 테이블로 나누는 경우를 생각해 보겠습니다. 한 테이블에는 단일 읽기/쓰기 파티션이 있고 다른 테이블에는 11개의 읽기 전용 파티션이 있으며 두 테이블에 대한 분할된 뷰가 있습니다.

이렇게 되면 읽기/쓰기 테이블에는 적은 수의 인덱스가 허용되고, 읽기 전용 테이블에는 보고 쿼리를 지원하기 위한 더 많은 인덱스가 허용됩니다. 결과적으로 클러스터링되지 않은 인덱스를 그렇게 많이 유지 관리할 필요가 없어지므로 읽기/쓰기 데이터에 대한 DML(데이터 조작 언어) 작업 효율성이 크게 높아집니다.

또한 읽기/쓰기 데이터에 대한 쿼리에서 읽기 전용 데이터를 처리할 필요가 없습니다. 쿼리 계획에서 파티션 제거는 SQL Server 2005에서(특히, 복합 조건자가 있는 경우) 완벽하지 않지만, SQL Server 2008에서는 크게 향상되었습니다. 자세한 내용은 sqlskills.com/blogs/kimberly/default,month,2007-10.aspx#a71c70243-3d57-4940-9af7-a802b73f2f93에서 Kimberly의 블로그 게시물을 참조하십시오.

이와 관련하여 여러 파일 그룹에 대한 분할을 통해 사용할 수 있게 되는 몇 가지 기능을 설명하겠습니다.

부분적 데이터베이스 가용성 주 파일 그룹이 온라인 상태이면 재해 복구 중에 데이터베이스를 온라인 상태로 만들어 액세스 가능하도록 할 수 있는 기능입니다. 단일 파일 그룹만 있다면 복원 중 전체 데이터베이스를 사용할 수 없습니다. 데이터가 여러 파일 그룹에 분산되어 있다면 손상된 파일 그룹만 복원 중에 오프라인 상태가 되고 응용 프로그램은 계속해서 사용할 수 있습니다.

증분 복원 이 방식은 부분적 데이터베이스 가용성과 유사합니다. 단일 파일 그룹을 사용하는 경우 복원 단위는 단일 페이지 또는 전체 데이터베이스입니다. 여러 파일 그룹을 사용하는 경우 단일 파일 그룹만 복원할 수 있으므로 부분적 데이터베이스 가용성이 허용됩니다.

분할된 데이터베이스 유지 관리 위에서 설명한 두 분할 방식의 경우 모든 파티션이 단일 파일 그룹에 있더라도 파티션별 인덱스 조각 제거를 수행할 수 있습니다. 그러나 단일 파일 그룹을 사용하는 경우 데이터베이스 일관성 확인(DBCC)에서 처리해야 하는 데이터의 양(및 사용되는 CPU 및 I/O 리소스의 양)을 크게 줄일 수 있는 파일 그룹별 일관성 확인을 수행할 수 없습니다.

간단히 말해서 동일 파일 그룹 내에 여러 파티션을 포함할 수 있지만, 파티션과 파일 그룹 간에 1-1 매핑 관계가 있으면 많은 이점이 있습니다.

Q. 최근 하이엔드 데이터베이스 서버 중 하나에서 불량 메모리 보드로 인한 손상이 발생했습니다. 임의 데이터가 응용 프로그램에 나타나기 시작했을 때 이러한 사실을 알았습니다. DBCC CHECKDB를 실행했으며 모든 종류의 손상을 발견했습니다. 불행하게도 이러한 손상은 백업에서도 발견되어 잘못된 데이터를 수동으로 제거해야 했습니다.

간단히 말하면 결국 불량 하드웨어를 교체하고 페이지 체크섬을 설정했습니다. 정기적인 일관성 확인을 실행하고 싶지만, 유지 관리 기간이 충분하지 않고 2.4TB 데이터베이스는 확인하는 데 오랜 시간이 소요됩니다. 어떻게 하면 좋겠습니까?

A. VLDB(초대형 데이터베이스)에 대한 일관성 확인 및 기타 유지 관리 수행 방법의 문제는 점점 더 일반화되고 있습니다. 많은 DBA는 허용되는 유지 관리 기간보다 DBCC CHECKDB를 실행하는 데 오래 걸린다는 것을 발견하고 포기합니다. 어떤 경우에는 데이터베이스가 24x7 동안 사용되어 장기간 동안 DBCC CHECKDB에 필요한 CPU 및 I/O 오버헤드를 할당할 유효한 시간이 없습니다.

일관성 확인을 포기하고 실행하지 않는 방법(매우 권장되지 않음) 이외에 사용 가능한 4가지 방법이 있습니다. 개인적으로 고객이 이 4가지 방법을 모두 사용하도록 도와준 적이 있습니다.

DBCC CHECKDB의 WITH PHYSICAL_ONLY 옵션 사용 정기적인 DBCC CHECKDB에서는 많은 수의 논리 일관성 확인을 실행하고 많은 CPU(및 기본적으로 CPU 바인딩 작업)를 사용하게 됩니다. WITH PHYSICAL_ONLY 옵션을 사용하면 매우 빠른 DBCC CHECKALLOC 할당 비트맵 일관성 확인 실행으로 확인이 제한되고, 데이터베이스에서 할당된 모든 페이지를 읽고 감사하여 페이지에 존재하는 모든 페이지 체크섬을 테스트하게 됩니다. 그러면 DBCC CHECKDB가 매우 짧은 실행 시간의 I/O 바인딩 작업으로 전환됩니다. 사실, 전체 DBCC CHECKDB보다 더 크고 훨씬 빠른 경우가 많아 시간 절약에 많은 도움이 됩니다.

일관성 확인 작업 부하 나누기 이 방법에서는 데이터베이스의 테이블을 동일 크기의 그룹으로 분리(이를 수행하는 가장 쉬운 방법은 페이지 수별 분리)한 다음 DBCC CHECKTABLE 명령을 사용하여 매일 밤 단일 그룹의 모든 테이블에 대한 일관성 확인을 수행합니다. 따라서 예를 들어, 7개의 그룹을 하루에 하나씩 확인하고 DBCC CHECKALLOC 및 DBCC CHECKCATALOG를 1주에 1회씩 수행하면 1주일에 걸쳐 분산되기는 하지만 DBCC CHECKDB와 동일한 효과를 얻을 수 있습니다.

여러 파일 그룹으로 테이블 분할 사용 VLDB에서 가장 큰 테이블을 여러 파일 그룹으로 나눌 수 있습니다. 예를 들어, 읽기/쓰기 파티션을 포함하는 파일 그룹에 대해 매일 DBCC CHECKFILEGROUP을 실행하고, 읽기 전용 파티션을 포함하는 파일 그룹에 대해 매주 DBCC CHECKFILEGROUP를 실행하여 일관성 확인을 수행할 수 있습니다. 이 논리는 읽기 전용 데이터는 전체적으로 백업되고 일상적인 처리에 사용되지 않는다는 것입니다. 결과적으로 이 데이터에 대해서는 손상이 그렇게 치명적이지 않으므로 자주 일관성 확인이 필요하지 않습니다.

다른 서버로 일관성 확인 부하 이전 이 옵션에서는 정기적인 전체 데이터베이스 백업을 다른 서버로 복원하고 해당 서버에서 DBCC CHECKDB를 실행합니다. 그러면 일관성 확인 작업 부하가 프로덕션 서버에서 완전히 없어집니다. 그러나 손상이 발견될 경우 일관성 확인을 프로덕션 서버에서 실행해야 하는 단점이 있지만 이러한 경우는 매우 드뭅니다.

이와 같이, DBA가 VLDB에 대해 일관성 확인을 수행하고 전체 DBCC CHECKDB를 실행할 때 필요한 추가 리소스 부하를 줄이는 데 사용할 수 있는 많은 옵션이 있습니다. 앞에서 언급한 대로 저는 개인적으로 고객이 이 4가지 방법을 모두 사용하도록 도와준 적이 있습니다. 이러한 옵션은 분명 여러분에게도 도움이 될 것으로 생각합니다.

팁: 트리거를 사용하여 서버 쪽 논리 구현

일부 시나리오에서는 트리거로 서버 쪽 논리를 구현해야 합니다. 그러나 알아 두어야 할 유의 사항이 있습니다. 다음은 염두에 두어야 하는 몇 가지 중요한 사항입니다.

트리거는 행별이 아닌 문별로 실행됩니다. 따라서 문의 영향을 받는 여러 행이 있거나 행이 없는 시나리오를 처리하도록 트리거 논리 내에 추가 논리를 두어야 합니다. 영향을 받는 행이 없더라도 트리거는 문별로 실행됩니다. 영향을 받는 데이터는 DML(데이터 조작 언어) 문의 가상 테이블 내에 포함됩니다. 이러한 테이블은 조인할 수 있으므로 데이터 작업이 가능합니다.
트리거는 트랜잭션 내에서 동기적으로 실행됩니다. 응답 시간 또는 적정 시간 내에 반환될지 확실치 않은 외부 응용 프로그램을 호출하거나 외부 리소스에 액세스할 때마다 이 사항을 기억해야 합니다. 예를 들어, 테이블에 대해 Update 문을 실행하고 트리거가 해당 작업 내에서 실행될 경우 트랜잭션(Update 문의 암시적 트랜잭션)은 트리거의 모든 논리가 완료될 때까지 완료되지 않습니다. 외부 응용 프로그램이나 프로세스에서 오류 코드를 반환할 경우 SQL Server는 트랜잭션을 취소하고 롤백할 수 있습니다(구현된 오류 처리 및 오류 코드에 따라). 따라서 트리거 내에서 외부 작업을 수행해야 하고 이 작업이 트랜잭션에 중요하지 않거나 동일 범위 내에서 실행할 필요가 없는 경우 다른 프로세스로 확장하여 비동기 방식으로 데이터를 선택해야 합니다. SQL Server 2005에서는 이러한 작업을 비동기적으로 수행할 수 있는 SQL Server Service Broker를 도입했습니다.
트리거 내의 문으로 인해 발생한 오류는 찾기가 매우 어렵습니다. 트랜잭션 내에 여러 테이블이 포함되어 있는 경우 오류 발생 시 트리거를 검사하고 적절한 오류 처리를 구현해야 합니다. 또한 데이터베이스 내에서 스키마를 변경할 경우에는 트리거 논리도 추적해야 합니다. 그렇지 않으면 작은 트리거라도 전체의 성능과 안정성에 큰 영향을 줄 수 있습니다. 스키마 변경과 관련된 대부분의 영향은 프로젝트 편집 중 자동 스키마 확인을 수행하고 정적 코드 분석으로 데이터 유형 불일치를 확인하는 Visual Studio® for Database Professionals를 사용하여 확인할 수 있습니다.

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

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입니다.