SHBLITZ.NET

지금까지 우리는 데이터베이스 성능관리에 대하여 기존과는 다른 ‘활용’적인 면에 초점을 맞춰 개략적으로 알아보았다.

이번 시간은 단순한 편협한 지식만으로 해결되는 문제가 아닌 데이터베이스 성능에 관해서 필자가 현장에서 컨설팅했던 실전 사례들을 중심으로 데이터베이스 기반의 프로그래밍 사례들을 소개한다.

실제 현장에서 사용되는 프로그램은 크게 온라인 유형과 배치성의 유형으로 나눠지게 된다. 이것은 모든 프로그램의 유형을 대표한다고 하기 보다는 실제 end user(사용자)들이 사용하게 되는 유형으로 개발자들이 프로그래밍을 하는 유형이라고 볼 수 있다.

이렇게 유형을 나누는 이유는 이 유형에 따라 프로그래밍을 하는 방법과 전략이 달라야 하기 때문이다. 실제로 현장에서 많은 성능 문제를 일으키는 이유는 이 두 가지의 단순한(?) 유형의 구분 없이 프로그래밍을 하는 이유가 굉장히 많다.


<그림 1>에서 보듯이 온라인의 프로그래밍은 업무 분석이 선행되어진 상태에서 프로그래밍 기술적인 부분에 들어갈 때 여러 가지 팩터(factor)들을 고려해서 전략을 짜야 한다. 고려해야 할 팩터들은 다음과 같다.

이와 같은 팩터들에 대해 답을 얻었다면 다음과 같은 순서도에 의해 화면 구성과 프로그래밍 전략을 짜야 한다. 이것은 데이터베이스를 기반으로 하는 프로그래밍을 하기 위함이며, 이런 절차를 제대로 밟은 프로그램은 성능 상 전혀 문제를 일으키지 않고 향후 데이터가 많이 쌓이더라도 시스템의 재개발이 필요없게 된다. 따라서 사용자들의 만족도 또한 충족시킬 수 있게 된다.


화면이 복잡해질수록 프로그래밍하기가 어려워지고 SQL이 어렵게 되므로 당연히 성능이 저하되기 마련이지만 <그림 2>와 같은 순서에 입각하여 정리하다 보면 복잡한 프로그램이라도 프로그래밍을 단순하게 짤 수 있는 능력이 길러지게 된다. 온라인 프로그램은 대게 데이터베이스의 조인방식 중 Nested Loops 조인으로 풀리게 짜게 되는데 이것이 전략을 구성하는데 핵심요소가 된다. Nested Loops 조인이란 조인방식 중 가장 전통적이고 기본적인 방식으로 온라인 프로그래밍을 할 때 반드시 확인해야 할 방식이다.

이번 연재에서는 지면 관계상 데이터베이스의 조인 방식에 대하여 자세히 다루지는 않고 관련 자료를 찾아보거나 기회가 되면 자세히 기술하는 시간을 가져보도록 하겠다.

Nested Loops 조인 방식의 가장 핵심은 드라이빙 팩터를 전략적으로 부여해야 하는 것이 핵심이다. 즉, 인덱스와 Random Access로 풀리게 되는 방식인 만큼 드라이빙 팩터의 건수가 적을수록 성능 상에 문제를 일으키지 않는다. 따라서 화면을 구성할 때 단일 조건 또는 조건의 조합이 드라이빙 팩터의 건수를 적게 하기 위한 전략을 구사하면 되는 것이다. 만약 그렇게 불가능하다면 부분범위처리나 배치에 의한 방식으로 전략을 변경해야 성능 상에 문제가 되지 않는다. 실제 여러 가지 사례들을 통해서 데이터베이스 기반의 프로그래밍 방법에 대해 구체적으로 알아본다.

예 1. 효율적인 인덱스를 이용하지 못하고 있는 프로그램


<화면 1>은 어느 ‘봉사자’라는 회원을 조회하는 화면으로 조회 조건에 따라 어떻게 SQL을 구사하는 지를 알아보고자 한다. 간단하게 요약해 보면 다음과 같다.

‘봉사자’ 기본 정보를 조회하는 화면인데 그 조건으로 주민등록번호나 봉사자의 이름이 들어올 수 있다. 해당 테이블의 인덱스 상황에 따라 한 명의 봉사자를 검색하기 위하여 Full Scan을 할 수 있는 부분이다. 따라서 이 부분을 ‘이름’이나 ‘주민번호’가 조건이 들어올 경우로 나누어 SQL을 구성하여 인덱스를 이용할 수 있도록 Access Path를 고정함으로써 효과적인 인덱스 Access를 할 수 있도록 유도한다.



2) 봉사자의 주민번호가 들어올 경우


기존에는 이 검색조건만을 위해 bm_name, bm_jumin의 단일 인덱스가 만들어져 있었으나 이 쿼리는 내부적으로 cent_sigun이라는 상당히 변별력 있는 조건이 항상 같이 들어오게 되므로 단일 인덱스를 drop하고 현재는 cent_code+bm_jumin, cent_code+bm_name 등의 결합 인덱스를 만들었고 이 인덱스를 쓰기 위해서 이와 같이 SQL을 분리하고 힌트를 이용하여 인덱스를 쓸 수 있도록 유도했다.

물론 주민번호 13자리가 전부 조건에 들어오고 bm_jumin이라는 인덱스를 이용하게 되면 성능에는 문제가 없겠지만 기존에는 주민번호와 이름의 자리 수 제약이 전혀 없었고 이름으로만 조회하는 경우 같은 성을 가진 회원(김XX )이 수없이 많기 때문에 화면에 뿌리는 데 어려움이 많았지만 cent_sigun이라는 변별력이 좋은 조건을 함께 결합인덱스를 이용하여 필수 조건화했기 때문에 전혀 문제가 되지 않았다.

예 2. 다양한 검색조건과 많은 양의 결과 수를 뿌릴 경우


이 프로그램은 어느 전자회사의 상품판매 고객을 관리하는 프로그램으로 소위 콜센터에서 주로 이용하는 프로그램이다. 즉 고객의 전화가 올 경우 고객을 응대하기 위해 고객 정보를 조회하는 프로그램이다. 고객 정보를 여러 가지로 입력할 수 있게 하여 다양하게 조회를 할 수 있도록 프로그래밍된 온라인 화면이다. 이 화면을 분석한 결과는 다음과 같다.

문제점 및 원인
이 프로그램은 고객을 조회하는 화면으로 콜센터 업무의 Intro 업무이다. 조회조건이 다양한데 ‘주민등록번호’와 ‘카드번호’가 입력될 경우는 결과 Set이 많지 않을 것이지만 대부분 업무에서 조회할 것으로 예상하는 ‘고객명+휴대폰’이나 ‘고객명+자택전화번호’ 같은 경우는 조건 값에 따라서 많은 양의 결과 Set이 나올 것으로 예상이 된다.

3-티어 구조에서는 조건 값에 맞는 쿼리가 끝나지 않으면 결과로 나타나지 않고 또한 결과 Set이 많으면 그 결과 Set을 네트워크로 끌어오거나 화면에 뿌릴 때 상당한 시간이 소요되므로 업무에 대단한 지장을 초래할 가능성이 있다.

결과 Set이 많을 경우에 첫 페이지의 경우는 빨리 뜰 수 있으나 ‘NEXT’를 누르면 누를수록 속도 저하가 일어나게 되는 것이다. 기존에 되어 있던 프로그램 방식은 흔히 웹 프로그램에서 많이 볼 수 있는 것인데 조건에 따라 SQL을 조합해서 만드는 (if… else if에 의해 SQL을 조합) 조합형 SQL을 구사하고 있다. 이는 데이터베이스가 유일하게 이해할 수 있는 SQL과 일반 로직 부분을 혼용해서 이용하고 있는 것이다.

이렇게 되면 데이터베이스는 조건에 따라 다양한 SQL을 받아들이게 되고 전체적으로 Access Path가 최적화되지 못하여 조건에 따라 성능 차이가 심해지게 된다. 또한 결과 건수에 따른 부분범위 처리 방식의 프로그래밍 또한 불가능하게 되어 데이터베이스 중심의 프로그래밍이 아닌 절차형의 프로그램이 되는 것이다.

개선방안
‘주민등록번호’나 ‘카드번호’가 입력될 경우는 현재 프로그램 방식으로 이용해도 별 무리가 없어 보이고 ‘고객명+주민등록번호’의 경우는 ‘주민등록번호’ 조건으로 대체 이용 가능하므로 크게 문제가 되지 않을 것으로 보이나 ‘고객명+휴대폰’ 또는 ‘고객명+자택전화번호’ 같은 경우는 다음과 같이 부분범위처리 방식으로 프로그램을 변경해야 한다. ‘고객명’이 들어올 때 동명이인의 경우 휴대폰 번호와 Rowid 값을 이용해서 구분하여 페이징을 한다.

‘부분범위처리’ 방식의 프로그램이란 개념은 간단하나 구현에 있어 고도의 기술을 요하는 것으로 웹 페이징 처리 시 필요한 부분만 데이터베이스에서 읽어 올 수 있게 구현하는 방식을 말한다(지면 관계상 세부 내용은 메일이나 다른 지면을 통해 소개하도록 하겠다). 기존 프로그램을 ‘부분범위처리’ 방식으로 필자가 구현한 내용을 소개한다.



이 방식은 다양한 조건을 인덱스를 이용하여 Access Path를 고정시키고 ‘NEXT’를 누를 경우 해당하는 키 값을 넘겨주어 화면의 개수만큼만 DB를 Access하도록 하는 방식이다. 본 내용을 현재 다 이해하지 못하여도 상관이 없다. 이런 방식이 있다고 하는 것만 알게 된다면 독자 여러분은 그 목적을 다했다고 생각한다.

예 3. 굉장히 다양한 검색조건의 화면의 경우


이 프로그램은 처음에 소개한 예제의 상세 검색 화면에 해당된다. 즉, ‘회원’의 가입당시의 여러 조건들을 다양하게 검색할 수 있도록 구성한 화면인 것이다.

이런 화면을 구성할 때 개발자들은 데이터베이스 위주의 프로그램이 불가능하다고 생각하고 있고 로직 위주의 프로그램을 구사하면서 개발자 편의의 프로그램을 짜고 있는 것이다. 하지만 이런 복잡한 프로그램일수록 데이터베이스 위주의 즉, SQL 위주의 프로그램을 구사해야 프로그램이 단순해지고 성능상의 위력을 발휘할 수 있으며 데이터의 정확도와 차후 변경사항의 유지보수 등이 좋아지게 된다.

일단 이렇게 복잡하게 구성해야 하는 프로그램은 다음과 같은 단순한 절차를 생각하면 된다.

이 부분도 ‘부분범위처리’ 방식으로 구현한 것으로 로직으로 밑줄로 되어 있는 부분이 드라이빙 팩터이고 나머지 조건들은 전부 필터링 팩터에 해당된다. 구사했던 부분을 SQL로 구현하는 것은 쉽지 않지만 ‘생각의 전환’을 하게 되면 그리 어려운 부분도 아니다. 필자는 같이 프로젝트를 했던 개발자들이 프로젝트 종료시점에 놀랄만한 SQL 구사 능력 향상을 가져온 것을 여러 번 경험했다.

예 4. 인덱스 전략의 부재로 성능저하가 있는 경우


이 프로그램은 어느 유통회사에서 ‘상품’을 조회하는 간단한 화면으로 회사의 특성상 관리하는 상품의 종류와 수가 많고 대부분의 업무 자체가 이 상품을 관리하는 것이다 보니 이 간단한 프로그램을 제일 많이 사용하면서도 성능 저하가 가장 많이 되고 있는 프로그램이었다. 예를 들어 앞의 화면에서 ‘메이커’와 ‘품목’조건에 둘 중 하나만 들어와도 실행이 가능하다고 해보면, ‘메이커’의 조건에 ‘삼성’이라는 값을 주고 조회를 할 수 있다. 만약 이런 현상이 발생한다면 이 시스템의 문제는 심각해진다. 그런데 이것이 실제 사례이다.


문제점 및 원인
상기 프로그램은 상품조회 화면에서 쓰이는 프로그램으로 상품의 ‘메이커’ 또는 ‘품목’을 조회조건으로 하고 있다. 조건으로 들어온 ‘삼성’이라는 메이커에 해당되는 데이터량은 엄청나다. 즉, ‘삼성의 모든 상품을 조회’하는 결과가 되는 것이다. 따라서 데이터베이스를 엑세스하여 화면에 뿌리는 데는 상당한 시간이 걸리고 있는 것이다.

개선방안
‘메이커’나 ‘품목’ 두 조건을 모두 Madatory 조건으로 변경하고 ‘메이커’ 부분은 데이터베이스에서 ‘=’로 엑세스할 수 있도록 하며 ‘메이커’+ ‘품목’으로 결합 인덱스를 구성하여 결과 Row 수가 최소화되도록 유도하여 빠른 응답시간을 유도한다.


지금까지 필자가 실제로 경험했던 간단한 몇 개의 예제들을 통해 RDBMS에 기반을 두어 성능개선을 한 사례를 살펴 보았다. 데이터베이스 기반의 프로그래밍은 결코 어렵지 않다. 다만 대부분의 개발자들이 접근을 잘못하고 있을 뿐이다. 그리고 앞의 사례들을 현재 100% 설명하기란 지면관계상 불가능한 점을 안타깝게 생각한다. 하지만 이번 연재를 통해 데이터베이스 컨설팅 영역에 대해 공감을 했다면 필자의 목적을 달성한 것이라 생각한다. 자세한 기술적인 문제들은 다음 기회가 되면 자세히 설명하겠다.

이로써 본 연재가 여러 자동화된 툴을 단순히 이용하는 것이 아닌 종합적이고 활용적인 데이터베이스 성능관리 영역의 소개가 되었으면 하는 바람이다. 또한 진정한 데이터베이스 사용 전문가들이 많이 나타나길 기대하는 마음으로 연재를 마친다.

출처 : 마이크로소프트웨어 2005년 4월호

근래에 많은 기업들의 데이터베이스가 대용량화 되면서 이를 효과적으로 관리할 수 있는 방안을 찾는 것이 관리자들의 주요 업무가 됐다. 이를 위한 매우 효과적인 방안 가운데 하나가 파티셔닝이다.

일반적으로 단순한 명령어 위주로만 알려져 있지만 실제 현장에서 접하는 파티셔닝의 효용은 그 이상이다. 익숙한 개념이지만 그동안 제대로 알지 못했던 파티셔닝의 의미와 대표적인 활용 사례를 살펴보자.

필자는 많은 현장 사이트에서 대용량의 가치 있는 데이터들이 놀라운 능력을 보유하고 있는 데이터베이스 안에서 사용자의 무지로 인해 방치돼 있거나 잘못 사용되고 있어 역효과를 일으키는 모습을 많이 보아 왔다. 예를 들어 총 테이블 건수 1억 건이 넘는 상황에서 우리가 어떤 형태로든 건드려야 할 부분이 약 10% 정도라고 할 때 그 테이블 전체를 읽지 않고 1000만 건만 읽을 수 있게 해야 하는 것이 당연하지만 실제로는 그렇지 못한 경우를 많이 보아 왔다.

어떻게 처리해야겠다는 생각도 없이 무조건 명령어(command)부터 날리는 것이다. 그렇다면 필요한 테이블 만을 다루려면 어떻게 해야 할까. 이를 위해 필요한 개념이 바로 테이블 파티셔닝(Table Partitioning)이다.

파티셔닝은 지난 강좌에서 살펴본 사항들과 함께 어떤 자동화된 툴로 절대 해결할 수 없는 부분으로 실제로 어떤 상황에서 파티셔닝이 필요하다고 정형화된 법칙은 없다. 중소 용량의 데이터베이스에서도 상황에 따라 꼭 사용해야 하는 경우가 있고, 초대용량의 경우 파티셔닝을 쓰지 않으면 시스템 자체가 관리되지 않을 수도 있다(필자 역시 컨설팅을 하면서 이 파티셔닝을 이용해 많은 시스템을 효율적으로 운영할 수 있다는 것을 직간접적으로 체험한 바 있다).

그러나 대부분의 파티셔닝 관련 자료들은 형식적으로 파티셔닝의 종류를 나열하고 스크립트 정도를 언급하는 수준이다. 이런 식의 접근은 한계가 명확하다.

오히려 파티셔닝을 올바르게 이용하기 위해서는 먼저 데이터베이스 액세스 방식의 정확한 차이와 장단점 그리고 파티션을 이용한 풀 스캔(full scan)에 대해 정확하게 이해할 필요가 있다. 파티셔닝은 일종의 기능일 뿐이어서 스캔에 대한 정확한 이해없이는 이를 사용할 이유도, 어떻게 사용해야 할지도 전혀 알 수가 없다. 각 스캔 방식의 장단점을 알고 어떤 상황에서 어떤 스캔 방법이 유리한 지를 명확하게 이해해야 그에 대한 보완책으로서 파티셔닝의 개념이 보이기 시작한다.

파티셔닝 세계 입문
대용량 테이블이나 인덱스를 파티셔닝한다는 것은 하나의 Object를 여러 개의 세그먼트로 나눈다는 의미이다. 즉 하나의 테이블이나 인덱스가 동일한 논리적 속성을 가진 여러 개의 단위(partition)로 나누어져 각각이 PCTFREE, PCTUSED, INITRANS, MAXTRANS, TABLESPACE, STORAGE PARAMETER 등 별도의 물리적 속성을 갖는 것이다.

특히 관리해야 할 데이터가 늘어나면 성능과 스토리지 관점에서 문제가 생길 수 있는데, 이를 해결할 수 있는 효율적인 방법 가운데 하나가 곧 파티셔닝이다. 파티셔닝은 보통 다음과 같은 장점을 갖고 있다.

오라클 DBMS에서 제공하는 파티셔닝 방식에는 레인지(range) 파티셔닝, 해시(hash) 파티셔닝, 리스트(list) 파티셔닝, 컴포지트(composite) 파티셔닝(레인지-해시, 레인지-리스트) 등이 있다.

특정 컬럼 값을 기준으로 분할하는 레인지 파티셔닝
레인지 파티셔닝은 어떤 특정 컬럼의 정렬 값을 기준으로 분할하는 것이다. 주로 순차적인(historical) 데이터를 관리하는 테이블에 많이 사용된다. 예를 들면 ‘가입계약’이라는 테이블이 있고 여기에 몇 년 동안의 데이터가 쌓여 있다면, 보통 5년치 데이터만 관리하고 이 가운데 자주 액세스하는 하는 것은 최근 1～2년 정도가 일반적이다.

따라서 이를 년별, 월별로 파티셔닝하고 애플리케이션의 SQL을 조정해 전체 데이터가 아닌 최근 정보를 가지고 있는 파티션만 액세스하도록 하면 전체 데이터베이스의 성능을 향상시킬 수 있다. 일부 사례의 경우 가입계약_1999, 가입계약_2000처럼 월별 또는 년별로 테이블을 따로 만들어 사용하기도 했지만 실제로 쓰는 데 불편한 점이 많고 액세스하는 SQL이 복잡해지는 단점이 있다. 다음은 레인지 파티션을 만드는 DDL(Data Definition Language) 스크립트다.


PARTITION BY RANGE (COLUMN_LIST)는 특정 컬럼을 기준으로 파티셔닝을 할 것인지를 결정하는 것이고, VALUES LESS THAN (VALUE_LIST)는 해당 파티션이 어느 범위에 포함될 것인지 상한을 정하는 것이다. PARTITION BY RANGE에 나타나는 COLUMN_LIST를 파티셔닝 컬럼이라고 하며 이 값이 파티셔닝 키를 형성한다.

파티셔닝 컬럼은 결합 인덱스처럼 최대 16개까지 지정할 수 있다. VALUESS LESS THAN에 나타나는 VALUE_LIST는 파티셔닝 컬럼들의 상한 값으로, 여기 지정된 값보다 작은 값만을 저장하겠다는 의미이다. 이런 스크립트에서 지정한 물리적 속성들은 각 파티션들이 생성될 때 개별적으로 물리적 속성을 지정하지 않으면 각 파티션들은 이러한 속성 값을 적용 받게 된다.

오직 성능 향상, 해시 파티셔닝
해시 파티셔닝은 특정 컬럼 값에 해시 함수를 적용해 분할하는 방식으로, 데이터의 관리 목적보다는 성능 향상에 초점을 맞춘 개념이다. 레인지 파티셔닝은 각 범위에 따라 데이터 양이 일정치 않아 분포도가 일정치 않은 단점이 있는데, 해시 파티셔닝을 이런 단점을 보완해 일정한 분포를 가진 파티션으로 나누고, 균등한 분포도를 가질 수 있도록 조율해 병렬 프로세싱으로 성능을 높인다. 실제로 분포도를 정의하기 어려운 테이블을 파티셔닝을 할 때 많이 이용하고 2의 배수 개수로 파티셔닝하는 것이 일반적이다.

해시 파티셔닝으로 구분된 파티션들은 동일한 논리, 물리적 속성을 가지다(단 테이블스페이스(tablespace)는 유일하게 파티션별로 지정할 수 있다). 또한 레인지 파티션과 달리 각 파티션에 지정된 값들을 DBMS가 결정하므로 각 파티션에 어떤 값들이 들어 있는지를 알 수 없다. 그러나 대용량의 분포도가 일정치 않은 테이블을 마이그레이션할 때는 프로그램 병렬 방식과 함께 유용하게 사용할 수 있다. 다음은 해시 파티션을 만드는 DDL 스크립트이다.


함께 쓰일 때 더욱 강력한 리스트 파티셔닝
리스트 파티셔닝은 특정 컬럼의 특정 값을 기준으로 파티셔닝을 하는 방식이다. 주로 이질적인(distinct) 값이 많지 않고 분포도가 비슷하며 다양한 SQL의 액세스 패스에서 해당 컬럼의 조건이 많이 들어오는 경우 유용하게 사용된다. 예를 들어 ‘서비스 계약’이라는 테이블이 있고 서비스를 최초 가입한 대리점을 ‘가입 대리점’, 변경사항을 처리한 대리점을 ‘처리 대리점’이라고 한다면 모든 서비스의 가입, 해지, 전환 등의 처리 데이터에는 이 두 대리점이 존재한다. 테이블 구조를 보면 다음과 같다.


즉 I_DLR_IND(대리점 구분)라는 컬럼이 존재하고 ‘A’일 때는 ‘가입 대리점’, ‘S’일 때는 ‘처리 대리점“이라고 할 때 대부분의 조회 패턴에는 가입 대리점 또는 처리 대리점에 해당하는 값이 들어오기 마련이다. 이럴 때 I_DLR_IND로 리스트 파티셔닝을 한다면 어떨까. 즉 집합의 서브 타입을 분류할 때 리스트 파티션은 매우 유용하다. 지금 예로 든 것은 단편적인 것에 불과하지만 리스트 파티셔닝의 위력은 강력하다. 특히 컴포지트 파티션에서 레인지 파티션과 함께 사용하면 전체 데이터베이스의 성능을 크게 향상시킬수 있다. 다음은 리스트 파티션을 만드는 DDL 스크립트이다.


PARTITION BY LIST에 나타나는 COLUMN_LIST는 파티셔닝 컬럼으로 파티션 키에 해당하고(단 단일 컬럼만 지정할 수 있다), VALUESS LESS THAN에 나타나는 VALUE_LIST는 파티셔닝 컬럼들의 값이다. 여기에 나타낸 값에 해당하는 행들을 저장하겠다는 의미가 된다.

레인지의 장점을 그대로, 레인지-해시 컴포지트 파티셔닝
레인지-해시 컴포지트 파티셔닝은 레인지 방식을 사용해 데이터를 파티셔닝하고 각각의 파티션 내에서 해시 방식으로 서브 파트셔닝을 하는 방식이다. 서브 파티션이 독립된 세그먼트가 되는 것이 특징으로, 다음과 같은 장점이 있다.

레인지 파티션에서 해당 테이블이 단지 논리적인 구조이고 실제 데이터는 파티셔닝된 세그먼트에 저장됐던 것처럼 컴포지트 파티션에서도 해당 테이블과 파티셔닝된 테이블은 단지 파티셔닝을 위한 논리적인 구조일 뿐이다. 데이터는 가장 하위에 위치한 서브 파티션 영역에 저장된다. 다음은 레인지-해시 컴포지트 파티션을 생성하는 DDL 스크립트이다. PARTITION BY RANGE (I_YYYYMMDD)에 의해 레인지로 파티션을 한 후 SUBPARTITION BY HASH에 의해 서브 파티셔닝을 수행했음을 알 수 있다.


레인지-리스트 컴포지트 파티셔닝
레인지-리스트 컴포지트 파티셔닝은 레인지 방식을 사용해 데이터를 파티셔닝하고 각 파티션 안에서 리스트 방식을 이용해 서브 파티셔닝하는 방식이다(이때 서브 파티션은 독립된 세그먼트가 된다). 레인지-리스트 컴포지트 파티션은 레인지-해시 컴포지트 파티션과 비슷하지만 서브 파티션이 리스트 파티션이라는 점이 다르다. 실제 업무에서는 레인지-해시보다 유용한 면이 많다. 다음은 레인지-리스트 컴포지트 파티션을 생성하는 DDL 스크립트이다.


파티션된 인덱스의 참뜻
‘파티션된 인덱스(partitioned index)’라고 하면 대부분의 개발자들은 로컬 인덱스를 떠올린다. 또한 파티션된 테이블에서만 쓰이는 것으로 생각한다. 그러나 이것은 명백한 오산이다. 파티션된 인덱스는 파티션된 테이블과 별개의 것으로, 단지 많은 상호 연관을 갖고 있을 뿐이다. 파티션된 인덱스는 문자 그대로 인덱스를 파티셔닝한 것으로, 해당 테이블이 파티션된 테이블이든 파티션되지 않은(non-partitioned) 테이블이든 상관없이 만들 수 있다.

예를 들면 ‘EMP’ 테이블의 크기가 상당히 크고 파티션되지 않은 일반 테이블일 경우 다음과 같은 과정을 통해 파티션된 인덱스를 만들 수 있다. 이를 ‘Global Prefixed Partitioned Index’라고 부르는데, 파티션 인덱스와 마찬가지로 대용량 데이터 환경에서 성능을 높이고 관리를 편리하게 하기 위해서다.


파티션된 인덱스가 유용한 이유는, 앞서 파티션의 개념에서 설명한 것처럼 하나의 인덱스를 여러 개의 독립적인 물리 속성을 가진 세그먼트로 나누어 생성, 관리할 수 있기 때문이다. 오라클 DBMS에서 제공하는 인덱스는 글로벌/로컬 인덱스와 Prefixed/Non-Prefixed 인덱스로 분류된다.

파티션된 인덱스와 일반 인덱스 사이의 차이점은 파티션 테이블과 일반 테이블의 그것과 동일하다. 인덱스는 인덱스 컬럼과 Rowid 순으로 값이 정렬되는데, 이런 특성은 파티션 인덱스에서도 동일하다. 많은 개발자들이 파티션된 인덱스는 전체 테이블 값이 정렬되지 않는다고 생각하지 하지만 이것은 사실과 다르다. 글로벌 파티션된 인덱스의 경우 테이블에 대해 값 정렬이 보장돼 있으며, 인덱스도 파티션별로 독립적으로 관리할 수 있다. 두 가지 방식의 차이는 <그림 1>과 같다.


파티션되지 않은 인덱스는 하나의 루트(root) 노드에서 리프(leaf) 노드까지 전체적인 밸런스를 유지하는 구조이고, 파티션 인덱스는 파티션 별로 독립적인 루트 노드와 리프 노드를 갖고 있음을 알 수 있다. 따라서 파티션되지 않으면 대용량 테이블에서는 글로벌 인덱스의 깊이(depth)가 매우 깊어질 수 있는 단점이 있다.

반면 파티션된 인덱스는 각 파티션별 깊이가 일반 인덱스의 깊이보다 얕고 인덱스도 파티션 별로 할 수 있어 병렬 프로세싱을 이용한 인덱스 관리에 매우 효과적이다.

그렇다면 글로벌 인덱스와 로컬 인덱스는 어떤 차이가 있는 것일까? 많은 개발자들이 파티션됐는지 여부로 판단하지만 이것은 잘못된 생각이다. 앞서 설명한 것처럼 글로벌 인덱스도 파티셔닝할 수 있으며, 이를 파티션별로 관리할 수도 있다. 글로벌 인덱스와 로컬 인덱스의 가장 큰 차이는 ‘정렬’이다. 즉 글로벌 인덱스는 테이블 전체에 대해 인덱스된 컬럼과 Rowid 순으로 정렬되고, 로컬 인덱스는 해당 파티션 내에서만 인덱스된 컬럼과 Rowid 순으로 정렬된다.

또한 로컬 인덱스는 ‘Local’이라는 말에서 알 수 있듯이 지역적인 인덱스로, 해당 테이블(base table)의 파티션 키로 파티셔닝된 인덱스다. 일반적으로 로컬 인덱스의 구성 컬럼에 반드시 파티션 키가 포함돼야 가능한 것으로 알려져 있지만 로컬 인덱스에는 파티션 키가 포함되어 있지 않아도 사용할 수 있다. 다음 예제를 보자. PACKAGE_DLR_IDX1 인덱스의 구성 컬럼에 테이블 파티션 키인 I_DLR_IND가 포함되지 않아도 검색조건에 I_DLR_IND = ‘C’라는 검색 조건이 있기 때문에 해당 파티션의 로컬 인덱스를 이용하는 것을 알 수 있다.


글로벌 인덱스는 전역적인 인덱스로, 기본적으로는 파티션되지 않은 인덱스이다. 대부분의 개발자들은 글로벌 인덱스를 파티셔닝해 사용할 생각을 하지 못하는데, 대용량 테이블에서 인덱스 관리의 효율성을 높이고 인덱스 검색 성능을 높이기 위해서는 이를 파티셔닝하는 것이 좋다. 글로벌 인덱스는 기본 테이블의 파티션 키와 무관하게 파티셔닝하는 것으로 설사 기본 테이블의 파티션 키로 글로벌 인덱스를 파티셔닝했다고 해도 로컬 인덱스처럼 동일파티셔닝(equipartitioning)된 개념이 아니므로 테이블 DDL시 전체 인덱스를 다시 생성해야 한다.

그렇다면 글로벌 파티션 인덱스의 인덱스 컬럼 값은 어떻게 전체 테이블에 대해 정렬을 보장하는 것일까. 예를 들어 5000만 건의 파티션되지 않은 EMP 테이블을 부서번호에 따라 파티셔닝했다고 가정하면 다음과 같다.



<그림 2>는 Global Prefixed Partitioned 인덱스의 구조다. Prefixed와 Non-Prefixed는 인덱스 파티셔닝 키가 인덱스의 선두 컬럼으로 오는가 그렇지 않은가의 차이가 있다. <그림 2>에서도 ‘Prefixed’란 인덱스의 파티션 키(DEPTNO)가 인덱스 선두 컬럼(DEPTNO)이 되는 것을 알 수 있다. 글로벌 인덱스의 경우 모든 인덱스 컬럼 값이 정렬돼 있다. 각 인덱스 파티션의 루트 블럭(root block)에 들어가는 값들이 인덱스 파티션에 따라 정렬되기 때문에 자연적으로 리프 블럭(leaf block)에 들어가는 모든 값들도 정렬되는 것이다. 반면 Global Non-Prefixed 인덱스를 파티셔닝하면 레인지 파티셔닝 방식으로만 가능하다. 이것은 정렬 때문인데, 레인지 파티션은 정렬 기능을 이용해 파티셔닝 키 자체를 생성하는데 반해 다른 파티셔닝 방식은 정렬과 상관없이 수행하기 때문이다.

로컬 인덱스는 Prefixed 인덱스와 Non-Prefixed 인덱스를 모두 지원한다. 로컬 인덱스는 기본적으로 현재 테이블의 파티션 키가 인덱스의 파티션 키가 되기 때문에 인덱스 컬럼에 현재 테이블의 파티션 키가 포함되지 않아도 인덱스를 생성할 수 있다. 또한 인덱스 컬럼 값의 정렬이 전체 테이블에 대해 보장된 것도 아니기 때문에 인덱스 파티션 키가 인덱스의 선두 컬럼이 될 필요가 없다. 또한 Non-Partitioned 인덱스이든 파티션 인덱스든 상관없이 인덱스를 이용하고자 할 때는 무조건 인덱스 파티션 키를 조회해야 하는 글로벌 인덱스와 달리 로컬 인덱스는 조회 검색조건에 파티션 키가 들어올 수도 있고 들어오지 않을 수도 있다.

대용량 DB 테이블과 인덱스 전략
파티션 인덱스 전략은 파티션 테이블과 밀접하게 연관되어 수립해야 하지만 여기서는 파티션 인덱스를 위주로 이야기를 풀어본다. 먼저 인덱스 크기에 대한 논의는 기본적으로 테이블보다는 훨씬 작게 생성, 관리하는 것이 원칙이다. 따라서 중소 용량의 데이터베이스 환경에서는 파티션 인덱스의 유용성을 따질 필요가 없다. 단 중소 용량의 데이터 환경일 경우에서도 테이블이 파티셔닝돼 있다면 파티션 인덱스를 고려해야 한다. 또한 기본적으로 파티션되지 않은 인덱스(일반 인덱스) 전략을 기본으로 해 테이블이 파티셔닝 된 경우와 인덱스를 파티셔닝했을 때의 장점을 비교해 보아야 한다.

먼저 테이블 파티션 키가 항상 ‘=’로 들어오는 경우 또는 파티션 범위가 크지 않은 경우에는 로컬 인덱스가 최상이다. 인덱스 컬럼의 순서와 구성은 액세스 패스에 따라 생성하면 되지만 최대한 가볍게 생성하는 것이 좋다. 기본 테이블의 파티션 키는 반드시 포함될 필요가 없으나, 테이블이 레인지 파티션이고 한 파티션 범위 안에서 파티션 키의 분포도가 좋을 경우 이를 포함하는 것을 고려해 볼만하다. 이렇게 하면 각 파티션당 인덱스가 파티션되지 않았을 때보다 가벼워지고 데이터 마이그레이션을 할 때도 테이블 파티션과 인덱스 파티션이 동일하므로 exchange, add, drop, split 등 파티션별 관리도 용이하다.

또한 빠른 응답 시간을 요구하는 환경에서 대용량 파티션 테이블의 조회 조건에 파티션 키가 들어오지 않을 가능성이 있다면 파티션 글로벌 인덱스를 고려해 볼만하다. 이렇게 하면 파티션되지 않은 글로벌 인덱스와 달리 레인지 파티션 별로 인덱스가 가벼워지는 장점이 있고, 레인지 파티션 별로 인덱스 split와 rebuild 명령을 독립적으로 수행할 수 있다. 컬럼 분포도에 따른 파티셔닝이나 민감한(critical)한 상수 레인지에 대해서는 파티션을 독립적으로 생성해 인덱스 크기를 줄임으로써 인덱스 검색 시간을 줄일 수 있는 이점도 있다.

exchange는 파티션된 테이블의 특정 파티션과 파티션되지 않은 일반 테이블 간의 구조를 서로 바꾸는 것으로, 대용량의 파티션된 테이블을 관리하는 데 상당한 효과가 있다. <그림 2>와 같이 데이터가 없는 새로운 데이터 테이블과 데이터가 들어 있는 파티션 2를 exchange하면 파티션 2에 해당하는 디렉토리 정보가 새로운 데이터로 바뀌고 새 테이블 데이터에는 데이터가 들어간다. 이것은 실제 데이터가 이동하는 것이 아니라 데이터를 저장하는 테이블 정보만을 업데이트하는 것이다. 한 가지 주의할 점은 exchange하고자 하는 파티션과 테이블의 구조가 같아야 하고 속성들의 특성도 같아야 한다는 사실이다.
exchange의 기본적인 문법은 다음과 같다.



한편 파티션된 대용량 테이블에 split 함수를 실행하면 많은 시간이 걸린다. 이럴 때 exchange 기능을 이용하면 빠르고 안전하게 작업할 수 있다. <그림 4>에서 보는 것처럼 split를 해야 하는 파티션을 exchange에 의해 빈 공간으로 만든 다음 split을 하고 다시 데이터를 채우기 위해 split하는 것이다. 이렇게 하면 대용량의 데이터라도 매우 빠른 시간내에 split 작업을 수행할 수 있다.


한편 대부분의 DBA들과 개발자들은 동일한 테이블을 생성할 때 create table ～ as select 구문을 이용한다. 대용량의 데이터일 경우 parallel 옵션을 줘 생성하기도 한다. 만약 1억 건의 테이블을 그대로 생성한다고 할 때 어떤 방법이 효과적일까. 이렇게 파티션된 대용량 테이블을 생성할 때는 exchange, program parallel 방법을 사용하는 것이 바람직하다.


<그림 5>는 이 과정을 도식화한 것이다. 먼저 생성할 TB_PART_1 테이블의 빈껍데기를 만든다. 대용량의 파티션된 테이블의 파티션 각각을 create table ～ as select 구문의 parallel 옵션을 이용해 각 테이블로 생성한다. 이후 미리 생성해 놓은 TB_PART_1 테이블의 파티션과 만들어 놓은 테이블들을 exchange하는 것이다. 이때 파티션별로 200105.sql, 200106.sql, 200107.sql…… 형식으로 만들어 놓고 이 프로그램들을 동시에 실행하면(program parallel) 극적인 효과를 볼 수 있다.

이번엔 데이터 마이그레이션에 대해 살펴 보자. 원격으로 데이터를 옮겨야 할 때 보통 database link를 이용한다. 네트워크를 통해 데이터를 옮기면 직렬(serial)로 데이터가 이동되므로 속도가 현저하게 떨어지기 때문이다. 따라서 소스 테이블을 파티셔닝하고 해당 파티션을 액세스하는 프로그램을 각각 띄워 병렬 프로세싱을 하게 되면 매우 빠른 속도로 데이터를 옮길 수 있다.

소스 테이블을 파티셔닝할 수 있는 상황이라면 테이블의 분포를 보고 레인지나 리스트 방식으로 파티셔닝할 수 있고, 일정한 분포가 존재하지 않는 테이블이라면 해시 파티셔닝으로 분포도를 고르게 나눈 다음 해당 파티션을 읽는 뷰를 액세스해 데이터를 옮기는 것이 좋다.

예를 들어 다음은 중대형 정도 크기인 약 2700만 건의 회원 테이블을 옮기는 DDL 스크립트다. 앞서 언급한 대로 이를 바로 database link를 이용해 처리하면 네트워크의 속도가 떨어져 엄청난 시간이 소요된다. 그러나 이것을 일반 테이블을 여러 개로 파티션을 나누어서 파티션과 병렬 처리하면 성능이 크게 향상된다. 작업 순서는 다음과 같다.


이제 다음과 같이 소스 테이블 뷰 생성한 후


다음과 같이 프로그램 패러럴(program parallel) 작업을 동시에 실행한다.


이것은 단적인 예에 지나지 않는다. 활용할 수 있는 사례는 얼마든지 있을 것이다. 한편 인덱스는 전체 데이터에 대해 해당 컬럼의 값으로 정렬하기 때문에 대용량 테이블의 경우 create, rebuild 명령을 실행할 때 많은 시간이 필요하다. 이때 파티션된 인덱스를 만들면 인덱스의 생성과 관리를 더 활용적으로 할 수 있다. 다음은 파티션된 인덱스를 Unusable로 생성한 사례다(로컬/글로벌 파티션된 인덱스).

먼저 파티션 인덱스를 ‘unusable’ 옵션을 이용해 생성한다. 실제 데이터를 정렬해 만드는 것이 아니라 일종의 껍데기를 만드는 과정이다. 이제 앞서 살펴본 병렬 처리를 이용해 여러 파티션을 동시에 rebuild를 하면 대용량 데이터라도 빠른 시간에 인덱스를 생성할 수 있다.


이제 파티션별로 index1.sql, index2.sql 등을 독립적으로 병렬 실행한다.


지금까지 테이블 파티셔닝에 대해 다뤄봤다. 자동화된 성능관리 툴로 커버할 수 없는 영역을 살펴보고 있으나 가장 중요한 것은 데이터베이스 액세스 개념에 대해 정확하게 이해하는 것이다. 많은 사람들이 파티셔닝을 알고 있지만 정확하게 사용하고 있지 못하는 현실이 아타까울 때가 많다. 그러나 이 점은 역설적으로 파티셔닝의 매력이기도 하다. 노력하는 데이터베이스 관리자 만이 도전해 볼 수 있는 영역이 바로 ‘파티셔닝’ 분야이기 때문이다.

출처 : 마이크로소프트웨어 2005년 3월호

데이터베이스 성능관리는 단순한 지식으로 해결할 수 있는 문제가 아니다. 자동화된 툴에서 보여지는 많은 지표의 도움으로 종합적인 판단해야 한다.

이번 글의 출발점은 바로 이 자동화 툴이다. 여기서는 기존의 접근 방법과는 조금 다르게 데이터베이스 활용과 성능관리에 대한 이야기를 시작해 보자.

오늘날에는 거의 모든 기업 활동이 정보 시스템을 통해 이루어지고 있다. 간단한 계산 작업에서부터 복잡한 업무 프로세스까지 정보 시스템을 이용하지 않고는 처리할 수 없는 환경이라고 해도 과언이 아니다. 이같은 상황에서 한 기업의 업무 프로세스를 정보 시스템화한다는 것은 단순히 업무를 전산화한다는 의미뿐만 아니라 회사의 다양한 업무를 표준화, 자동화, 고품질화 해 효율성을 높이고 나아가 회사의 경쟁력을 높이는 원동력이 되는 것이다.

그러나 현재 대부분의 전산 시스템의 상황은 어떠한가? 정보 시스템을 통해 고부가가치를 창출하려는 노력보다는 처음부터 계획성 없이 구축해 놓은 전산 시스템을 유지보수하거나 신뢰할 수 없는 데이터에 대한 보정작업(data cleansing) 등 고급 인력들이 단순 노무자(?)로 전락하고 있는 것은 아닌지 유감스럽다.

기업의 복잡하고 다양한 업무를 전산화하려면 무엇보다 이 수많은 유무형의 작업들을 데이터화하고 이를 관리하는 데이터베이스가 필수적이다. 현재의 모든 전산 시스템이 데이터베이스를 근간으로 만들어지고 있는 것도 이 때문이다. 하지만 실제로 업무 전산화를 담당하는 개발자들은 수많은 개발 언어를 익히는데 치중하는 나머지 업무를 데이터화하는 능력과 데이터베이스를 활용하는 능력을 기르는데 소홀할 수밖에 없다. 데이터베이스를 관리하는 DBA(DataBase Administrator)들은 업무 중 많은 시간을 데이터베이스 운영의 문제점과 이를 해결(trouble shooting)하는 데 허비하고 있다.

필자는 컨설턴트로 활동하면서 이런 데이터베이스 운영상의 문제점들을 수없이 많이 보아왔다. 본래 이러한 문제들의 근본원인을 이해하는 것도 중요하지만, 관리자 입장에서 보면 먼저 이런 문제점들을 발견하고 해결하는 것이 더 중요하다. 실제로 최근 많이 출시돼 있는 데이터베이스 모니터링 자동화 툴도 이런 취지에서 선보인 것들이다.

그러나 이런 툴이 있음에도 불구하고 데이터베이스 운영에 많은 문제점들이 끊임없이 나타나는 것은 데이터베이스란 하나의 물리적인 소프트웨어에 지나지 않지만 실제로는 업무의 논리적인 데이터적 활용이라는 관점에서 사용되기 때문이다.

앞으로 3회에 걸쳐 데이터베이스 운영의 문제점 중 가장 민감하다고 할 수 있는 ‘성능(performance)’ 관리에 대해, 자동화된 툴을 이용한 관리에서 더 논리적이고 데이터베이스 활용 측면의 이야기를 살펴 본다. 데이터베이스는 현재 시중에서 가장 널리 사용되는 오라클 데이터베이스를 예제로 하겠다.

성능관리의 성패를 가르는 요인들
요즘 대부분의 기업에서 이용하고 있는 데이터베이스는 RDBMS(Relational Database Management System)라 부르는 RDBMS이다. RDBMS를 근간으로 구축하는 시스템에서는 실제 대부분의 시스템에서 성능상 문제를 나타내는 원인의 많은 부분이 RDBMS 관련 문제 요소들이다.

4~5년 전만 해도 시스템 구성이나 인프라(infra), 리소스(resource) 부족으로 인한 문제가 많았으나, 하드웨어 기술이 빠르게 발전하고 상대적으로 비용 부담이 줄어들면서 쉽게 하드웨어를 증설할 수 있게 됐다. 실제로 대부분의 기업들이 하드웨어와 메모리 증설이라는 처방을 통해 어느 정도 시스템 부하와 자원부족 문제를 해결해 온 것도 사실이다.

그러나 이러한 시스템 문제들의 내부를 살펴보면 사실상 CPU, 메모리, 디스크, 네트워크 등 하드웨어 리소스 부족으로 인한 문제 상황은 거의 찾아볼 수 없다. 가끔씩 2티어(tier), 3티어 환경 또는 웹 환경에서 네트워크 구성 문제나 네트워크 속도 문제로 인한 문제가 발생하기도 하지만, 필자의 경험에 비추어 보면 거의 절대적으로 시스템 성능의 영향을 미치는 부분은 바로 RDBMS에 맞도록 시스템 설계(데이터모델링/DB Design)가 이루어졌는지 여부와 RDBMS에 적합한 개발기법으로 개발이 이루어졌는 지가 문제의 핵심이었다.

즉 운영업무의 특성과 발생 데이터량 그리고 트랜잭션 부하를 고려해 RDBMS의 특성과 장단점을 데이터베이스 설계에 반영하고, RDBMS 옵티마이저(optimizer) 개념을 이해하고 응용 프로그램을 개발해야 하며, 성능에 가장 직접적인 영향을 미치는 I/O(Input/Output) 성능을 개선하겠다는 설계자와 개발자들의 노력이 매우 중요하다 할 수 있다. 일반적으로 데이터 베이스 성능에 영향을 미치는 부분은 다음과 같이 구분할 수 있다.

물리적인 데이터베이스 설계 측면
RDBMS에 대해 정확하게 이해하지 못한 상황에서 DB 오브젝트 디자인을 해 대부분의 데이터베이스 파라미터를 기본(default)으로 설정해 관리하거나 조정을 하려고 해도 할 수 없는 경우, 혹은 스토리지 설계가 잘못된 경우가 대부분이다.

이런 요소들은 문제 위험요소에 치명적이지는 않지만 RDBMS 성능을 높이기 위해서는 RDBMS의 원리와 DB 스토리지 알고리즘을 이해해 DB 오브젝트 디자인에 반영해야 한다. 특히 PCTFREE, PCTUSED, FREELISTS, INITRANS, INITIAL, NEXT EXTENTS 설계 등 데이터 발생과 액세스(access) 등에 영향을 미칠 수 있는 물리적인 요소들에 대해 적절한 설계가 이루어져야 한다.

또한 대용량의 데이터 환경과 데이터 변환, 마이그레이션(migration) 등의 환경에서는 파티션(partition) 전략이 중요하다. 파티션의 장단점과 그 원리를 정확히 파악하고 현재 데이터 환경에 적용시킬 수 있는 능력이야 말로 관리자가 갖춰야 할 부분 중의 하나다. 많은 자동화 툴들이 이런 기능을 단순히 처리(operation)해 주는 편리한 기능을 많이 제공한다. 보통 단순 작업을 진정한 실력으로 착각하는 경우가 많은데, 적용 능력과 활용 기술 등은 자동화 툴로 대체할 수 있는 것이 아니다.

개발 응용 프로그램 SQL의 품질측면
RDBMS에 맞는 개발 기법은 유일하게 RDBMS가 이해할 수 있는 언어인 SQL을 RDBMS 옵티마이저가 최적화할 수 있게 만드는 것이다. 즉 실력있는 프로그래머는 옵티마이저이며 이것이 최적의 수행속도를 낼 수 있도록 개발자와 설계자가 적절하게 전략적인 최적화 팩터(factor)를 구축해야 한다. 즉 개발 응용 SQL 역시 옵티마이저가 알기 쉽게 집합적인 고급 SQL을 구사하여야 한다.

대부분의 성능상의 문제는 이런 집합적이고 전략적인 SQL을 구사하지 못해 발생한다. SQL은 사용하기는 편리하지만 잘못 사용하면 1초면 가능할 일을 1시간이 넘도록 수행하는 경우도 있다. 즉 SQL은 요구와 결과 밖에 없는 비절차형 언어이기 때문에 누구나 쉽게 배우고 사용할 수 있지만, 최종 사용자 컴퓨팅(End User Computing) 환경이 아닌 패키지 시스템이나 개발시스템에서는 가장 효과적인 고급 SQL을 사용할 수 있는 능력이 요구된다. 이런 능력은 단순하게 툴을 이용하거나 편협한 지식으로 해결될 수 있는 것이 아니다. 원리를 이해하려는 많은 노력으로만 가능하다.

최적화/클러스터링 전략 측면
필자의 경험에 따르면, 대부분의 데이터베이스 인덱스 전략은 한 마디로 주먹구구식으로 만들어 놓고 쓰는 것이 대부분이다. RDBMS를 정확히 이해하지 못하고 사용하는 습관이 만들어 낸 매우 위험한 현상이다. 실제로 개별 SQL의 속도를 빠르게 하기 위해 즉흥적으로 인덱스를 추가하면 오히려 큰 오류를 범하게 된다.

Trace 결과에 근거해 각종 SQL에 어떠한 조건들이 사용될 것인지 그리고 어떤 테이블 간에 연결 조인이 이루어지는지 등을 파악해 모두를 만족할 최소의 역할별 인덱스 또는 클러스터 전략을 수립해야 한다. 상황에 따라 개별 개발자들이 수행속도 향상을 위해 케이스(Case) 별로 인덱스를 추가하기 시작하면 무수히 많은 인덱스가 늘어나 시스템 부하만 가중시키고 정작 의도하는 엑세스 효율은 얻지 못하는 경우가 대부분이다.

반면 아무리 데이터량이 많아도 최적의 데이터베이스 디자인과 효율적이고 전략적인 인덱스 전략 등 최적화 전략이 갖춰져 있으면 전혀 걱정할 필요가 없다. 사실상 대부분은 반복 업무는 온라인 업무이며 이를 처리할 때 수십 수백만 건을 모두 읽어서 처리해야 한다면 이는 업무 정의가 잘못되거나 적절한 데이터베이스 디자인이 되어 있지 않은 것이다.

현재 시스템에 성능상의 문제가 있을 경우 적절하지 못한 데이터 모델링에 기인하는 경우가 많은데 실제로 이런 문제를 데이터 모델링의 문제라고 생각하기 보다는, 단지 개발자들의 능력이 부족하기 때문이라고 속단한다.

그러나 핵심은 원하는 데이터를 가장 효율적으로 최소의 비용으로 처리할 수 있도록 RDBMS에 최적화된 전략을 갖추고 있는가 하는 점이다. 사용자들이 쉽고 빠르게 시스템에 접근해 원하는 바를 얻고, 효율을 높이기 위해서는 전체적인 SQL 액세스 패스를 분석해 최적화 전략을 수립하는 것이 중요한 것이다.

시스템 자원 사용 측면
시스템 자원사용에 관한 부분도 성능상에 문제를 야기할 수 있는 중요한 측면이다. 과거에는 CPU, 메모리, 디스크 등 시스템 자원이 부족해 생기는 부분이 많았으나 현재는 가용한(available) 자원을 제대로 활용하고 있지 못하는데 대부분의 원인이 있다.

하드웨어 자원이 부족하기 때문이 아니라 비효율적인 애플리케이션으로부터 비롯된다는 사실은, 앞서 설명한 설계문제, 최적화 전략 부재, 비효율적인 애플리케이션 등이 복합적으로 작용해 현재 시스템의 자원 사용에 대한 부적절한 스케일링(scaling)이 뒤따를 수 있는 문제가 발생한 것이다. 데이터 중복이 심하고 비정규화로 인해 RDBMS의 기본 개념인 ‘관계’의 데이터 정합(integrity)가 깨지면, 당연히 데이터 액세스 작업이 비효율적으로 되고 불필요한 인덱스들이 양산될 수밖에 없다.

RDBMS 원리에 충실한 DB 디자인과 옵티마이저 원리에 충실한 응용 프로그램 개발, 앞을 내다보는 최적화 전략 등이 성능관리의 성패를 가름하는 것이다.

DB 관리 툴의 활용법과 한계
DBA들은 데이터베이스 운영의 책임을 지고 이를 안정적으로 운영할 의무가 있지만, 실제로 데이터베이스에 문제가 발견되면 그 원인을 찾기 힘든 경우가 많다. 이 때문에 자동화 툴을 이용해 성능상의 문제점을 파악하고 해결하려 한다. 데이터베이스는 매우 복잡한 소프트웨어이고 운영상 많은 지식과 경험 등이 필요해, 관리하는 사람들마다 서로 다를 수밖에 없다.

따라서 아무리 자동화된 툴을 이용한다고 해도 그 지식과 경험의 차이에 따라 툴을 이용하는 수준은 천차만별이다. <화면 1>은 거의 모든 DBA들이 매일같이 확인하는 데이터베이스 세션(Session)에 관한 모니터링 관리화면이다.


이런 종류의 관리화면은 거의 모든 데이터베이스 툴이 제공하는 것으로 데이터베이스 관리자가 데이터베이스에 액세스되고 있는 많은 세션 정보를 편리하게 볼 수 있도록 지원한다. 여기서는 모든 세션에 대한 정보는 물론 그 세션들이 현재 데이터베이스에 어떤 요구를 하고 있는지도 확인할 수 있다.

이런 요구들 가운데에는 데이터베이스에 많은 부하를 주거나 속도 저하를 일으키는 SQL들도 발견할 수 있다. 이를 편리하게 찾아 주는 것이 자동화된 툴의 장점이기도 하다. 실제로 문제가 되는 세션의 SQL을 찾고 싶다면(실제로 이런 요구가 빈번히 발생하게 된다) 대부분의 DBA나 개발자들은 개인적인 경험과 지식을 동원해 문제가 되는 SQL들을 찾아낸다.

Physical I/O, Logical I/O, Lock Wait 등을 보고 툴에서 보내주는 수치가 상대적으로 크거나 또 나름대로의 판단기준을 가지고 속도 저하의 가능성이 있는 SQL이라 판단할 수 있는 것이다. 예를 들어 <리스트 1>과 같은 SQL이 문제 요소로 부각됐다고 가정해 보자.


이때 가장 중요한 것은 어떤 부분이 문제가 되는지를 정확히 판단해 As-Is와 To-Be에 대한 요구 가능성이 충족될 수 있도록 최적화된 튜닝을 하는 것이다. 이를 위해서는 여러 가지 수많은 팩터들 중에서 가장 기본적으로 실행 계획과 트레이스(trace) 정보를 보고 이해할 수 있어야 한다.


<그림 2>와 같은 실행계획을 보고 현재 어떤 문제가 있고 어떻게 튜닝해야 할지 판단하는 것은 쉬운 일이 아니다. 단순히 SQL의 문법지식이나 인덱스 상태만으로 해결되는 것도 아니다. 데이터 상황과 시스템 상황, 인덱스 상황, 다른 SQL들의 액세스 패턴 등을 종합적으로 판단할 수 있어야 한다. 이런 종합적인 판단에 있어 툴은 많은 도움을 줄 수 있지만 ‘최종적인 판단’까지는 대신해 줄 수 없다. 이 부분은 사람만이 가능하다. <리스트 3>과 같은 또다른 사례를 살펴 보자.


<리스트 3>은 특정 게시판 테이블에서 날짜를 이용해 간단한 내용을 조회하는 SQL로, 요즘 대부분의 회사에서 많이 쓰이는 그룹웨어 시스템의 모든 화면에서 조회할 수 있는 SQL이다. BBS 테이블에 많은 데이터가 있지도 않고 특성상 단위 SQL이 수행되는 시간 또한 오래 걸리지 않으며, 언뜻 보기에도 별 문제가 없어 보인다. 어쩌면 실제로 별 문제가 없을 수도 있다.

그러나 <리스트 3>을 자세히 살펴 보면 owner_id 즉 로그인한 ID를 입력 값으로 해 BBS_GET_STATUS 함수를 통해 어떤 상태(status)를 리턴(return) 받는 부분이 있다. 만약 이 시스템을 이용하는 기업의 사용자가 매우 많고 빈번히 조회가 된다면 바인딩되는 owner_id 값의 종류가 기하급수적으로 늘어날 것이다.

이 때 동적으로(dynamic) 바뀌는 값을 데이터베이스에서 바인딩하지 않을 경우 동일한 SQL을 수천만번 파싱(parsing)해야 하는 경우가 생기게 된다. 따라서 단위 SQL은 빠르게 수행이 될 수 있으나 전체적인 데이터베이스의 성능은 시간이 갈수록 저하돼, 마치 시한폭탄과도 같아지게 된다.

만약 자동화된 툴에서 이런 SQL들을 전문적으로 필터링하는 기능이 있다면 이런 정형화된 경우에 한해서는 툴의 자동화 기능을 이용하는 것도 좋은 방법 중 하나가 될 것이다. 하지만 실제 업계에서는 데이터베이스 전문적인 툴이라 하더라도 데이터베이스 자체가 아닌 사용 관점에서 문제점을 발견하고 찾아내는 자동화된 툴은 본 적이 없다.

DBA는 물론 데이터베이스 전문가라고 하는 사람들도 자동화된 툴을 가지고 데이터베이스를 모니터링하는 수준에 불과한 경우도 많았으며, 데이터베이스 문제의 근본 원인을 제대로 파악하는 것이 쉽지 않은 작업임을 여러 차례 확인한 바 있다. 즉 모니터링 툴이 보여주는 물리적인 지표를 이용하면 데이터베이스 자체의 문제를 발견할 수는 있지만, 실질적인 근본 원인과 그 해결책을 찾는 것은 또 다른 이야기가 되는 것이다.


<화면 2>는 많은 자동화 툴에서 보여주고 있는 데이터베이스 모니터 화면이다. 이런 화면에서 보여주고자 하는 것을 제대로 이해하는 것도 쉬운 일은 아니지만, 제대로 이해했다고 해도 그 원인을 해결하는 것은 더욱 어렵다.

예를 들어 SGA Memory Usage에서 Shared Pool의 사용량이 상대적으로 많다면 그 이유가 무엇인지 파악해서 해결할 수 있어야 하지만 대부분의 DBA들은 그 원인을 치유하지 못한 채로 Shared Pool Size만 늘려 해결하려고 한다. 그러나 실제 그 이유를 살펴보면 메모리 크기가 부족한 것인지 아니면 다이나믹 쿼리(dynamic query)에 의한 파싱이 불필요하게 많아서인지 등 여러 가지가 있을 수 있으며, 설사 원인을 파악했다고 해도 이를 어떻게 해결할 수 있는지 알지 못한다.

필자가 경험적으로 느낀 바에 따르면, 대부분의 원인은 데이터베이스 지향적 관점으로 프로그램으로 개발하지 않은, 즉 애플리케이션의 문제가 80~90%를 차지한다. 이 경우 메모리 크기를 늘리면 당장의 해결책은 되겠지만 시간이 흐를수록 더 큰 문제가 야기될 수밖에 없다. 따라서 근본 원인을 찾고 해결하는데 시간을 투자하는 것이 안정적으로 데이터베이스를 운영하는데 필수적이다.

한편 인덱스를 통한 쿼리가 많고 적음에 따라 시스템의 부하를 줄일 수 있는 팩터가 되기도 한다. <그림 5>는 Indexed Queries%를 보여주는데 이 화면으로 데이터베이스에 요구하는 SQL들이 인덱스를 많이 이용하는지 아니면 전체 스캔(Full Scan)을 많이 하는지를 한눈에 알 수 있다.

여기서 독자들에 대한 질문 한 가지! 이때 인덱스를 많이 타면 좋은 것일까? 다양한 답변이 나올 수 있을 것이다. 인덱스에 대해 깊이 파고 들었던 독자는 다양한 상황을 예로 들며 장황하게 설명할 수도 있겠고, 단순하게 ‘그렇다’라고 대답하는 독자도 있을 것이다. 하지만 대부분은 인덱스를 타면 빨라지니 이를 많이 타면 시스템 부하가 내려갈 것이라고 생각할 것이다. 데이터베이스 전문가라고 자부하는 사람들조차 이렇게 생각하고 있으니 일반적인 개발자나 DBA들은 그렇게 생각하는 것이 어쩌면 당연할 수도 있다.

그러나 이것이 항상 옳은 것은 아니다. 인덱스에 대한 기본 구조와 개념, 인덱스를 통한 스캔 방식과 전체 스캔 방식의 차이, 각 스캔 방식의 데이터 상황에 따른 반응 시간(Response Time), 실시간 용인지 데이터 웨어하우스 용인지 등 데이터베이스의 사용목적 등을 심도있게 연구해야 다양한 환경에 따라 목적에 맞는 사용방식을 찾을 수 있다.

이런 관점에서 보면 기본으로 제공되는 자동화된 툴들은 현재 시스템의 전체 윤곽을 잡는데 큰 도움이 되는 것은 분명하다. 그러나 이 툴을 이용하는 사람들이 단순히 툴을 다루는(operation) 오퍼레이터(operator)에 머무른다면 수많은 문제를 올바르게 해결할 수 없다.

앞서 설명했듯 문제점의 원인을 파악하다 보면 대부분은 효율적이지 못한 SQL 문장, 최적화 전략의 부재 등에서 시작된다. 어떤 경우 프로그램 하나가 전체적인 시스템의 성능을 좌지우지 하는 경우도 있다. 필자가 컨설팅했던 한 사이트는 운영시스템이 항상 90% 이상의 CPU 사용률을 기록하고 있었고 시스템의 부하가 너무 심해 CPU와 메모리를 최대로 탑재해서 더 이상 리소스 증설을 할 수 없는 상태였다.

리소스를 최대로 증설한 상태에서도 사용률이 90% 이상을 기록하니 운영 중에 많은 부작용이 나타났고 상품을 판매하는 회사에서 고객을 유치하는 프로모션을 해야 함에도 불구하고 시스템이 이를 지원하지 못해 걱정만 하고 있었다.

분석 결과 이 시스템은 근본원인을 치유하기 보다는 즉흥적인 조치로 인덱스 수가 무수히 늘어나 시간이 갈수록 더욱 더 심각한 상황에 이른 경우였다. 운영시간에 조작되는 프로그램은 많았고 특히 대부분의 사용자들이 빈번하게 사용하면서 시스템의 부하를 크게 높이는 프로그램 한두 개를 찾아 이에 대한 긴급 처방을 하자 CPU 사용률이 30% 정도로 떨어졌다. 튜닝 컨설팅이 끝날 무렵에는 시스템의 과다 탑재된 CPU의 활용 용도를 놓고 행복한 고민에 빠지게 됐다.

회사 측면에서 보면 프로그램 한두 개가 막대한 손해를 끼친 경우로, 옵티마이저의 기본원리를 알고 프로그램을 개발했더라면 그런 현상을 미연에 방지했을 것이다. 이런 것은 특정 툴 하나를 능숙하게 다루고 자동화된 어떤 작업을 잘해서 해결되는 것이 아님을 독자들은 이해하리라 생각한다.

참고로 데이터베이스의 성능관리에 있어서 애플리케이션의 중요성을 강조하는 이유는 앞서 설명했듯, 데이터베이스가 스탠드 얼론(stand alone)으로 운영되는 것이 아니라 사용자의 요구(request)에 의해 운영되는 것이고, 그 사용 자체가 비즈니스이기 때문에 활용적인 면에서 접근할 수밖에 없기 때문이다.


<화면 3>과 <화면 4>는 RDBMS의 특성에 맞는 애플리케이션 개발 방법이 중요하며, 자동화된 툴의 한계가 명확하다는 것을 보여주는 대표적인 사례다. 이것은 모 유통회사에서 문제가 되었던 애플리케이션을 튜닝한 것으로, 대부분의 웹 애플리케이션 개발자가 접하게 되는 프로그램 화면과 프로그램 개발 소스다. 다양한 검색조건에 따라 SQL을 추가(append)해 조건에 맞는 답이 나올 수 있는 SQL을 만들어 데이터베이스에 요구하는 방식으로 짜여져 있다. 그러나 이러한 방식에는 다음과 같은 문제점이 있다.

이런 프로그램들을 과도하게 생성해 사용하면 클러스터링 팩터의 부재로 인해 성능이 저하되고, 파싱 오버헤드에 의해 시스템 리소스 사용이 증가돼 전체적으로 시스템 성능이 떨어지고 심하면 장애가 발생할 수도 있다. 이런 상황들을 모르는 시스템 담당자 또는 DBA들은 문제가 발생하면 원인을 찾기 힘들게 되고 시스템 증설이나 인덱스 추가 등 즉흥적인 대처에 급급해 시스템에 오히려 독이 되는 것이다. <화면 3>과 <화면 4>에서 보이는 SQL의 일부 트레이스 정보를 추출하면 <리스트 4>와 같다.


<리스트 4>는 <화면 3>의 화면에서 ‘상품명’의 조건으로만 조회했을 때의 SQL에 대한 트레이스 정보로, 전체적인 트레이스 정보를 각각 분석해 튜닝하는 방법보다 화면 단위로 전체적인 클러스터링 팩터를 선정해 튜닝하는 것이 효과적이다. 여기서는 화면 단위로 봤을 때 Mandatory 조건을 어떤 것으로 할 것이며 그에 따른 Optional 조건은 어떤 것으로 하고, 또 그에 따른 데이터 상황과 인덱스 전략 등을 수립해 종합적으로 판단해 <리스트 5>와 같이 SQL을 one-set으로 만드는 방법을 사용했다.

이와 같이 튜닝을 하면 모든 조건검색을 안전하고 빠르게 수행할 수 있으며, 동시에 시스템 부하를 줄일 수 있다. RDBMS의 특성과 기본 개념, SQL 등의 올바른 사용법 등을 참조해 데이터베이스 지향적인 프로그램을 개발한 단적인 사례로, 앞서 설명한 여러 자동화된 툴에서 채울 수 없는 ‘2%’이기도 하다.


지금까지 다양한 예제들을 통해 RDBMS의 특성을 이용한 성능 문제 해결 방안에 대해 살펴 봤다. 많은 관리자들이 자동화된 툴의 한계와 적절한 이용법을 익혀 데이터베이스의 문제점을 빨리 파악하고 이를 분석, 해결할 수 있는 데이터베이스 전문가로 성장하기를 기대한다. 다음 글부터는 실제 현장에서 자주 부딪히는 사례를 중심으로 각각의 해결 방법을 상세하게 알아 본다.

출처 : 마이크로소프트웨어 2005년 2월호

텔레비전을 보면 가끔 나오는 개인정보 유출이나 내부 직원의 기업 정보 유출은 드라마만의 내용이 아니라 이미 현실로 다가오고 있다. 도덕적으로 깨끗한 사람만을 채용하기에는 세상이 너무 복잡해지고 다양해졌다. 사고 사실을 알고 충격을 받아 당황하는 것은 이제 더 이상 남의 일이 아니다.


보안 전략 선택

필자는 얼마 전 A 고객사의 팀장으로부터 연락을 받았다. 그 내용은 보안에 관한 컨설팅이 필요하다는 것으로 약속을 정하고 방문을 하니 고객사 팀장은 개인정보보호법이라는 제목의 인쇄물을 필자에게 보여줬다. 개인정보보호법이 시행되면 어떻게 해야 할지에 관한 고민을 하고 있었다. 고객사 팀장은 이 문서에서 요구하는 사항을 만족할 수 있는 솔루션이나 관리 기법을 알고자 했다. 그래서 <그림 1>의 과정 중 3 요구 사항에 대한 솔루션 단계까지를 설명하기로 했다.

기능 요구 사항

필자를 비롯해 많은 사람들이 텔레마케터의 전화를 가끔 받는다. 나의 이름과 전화번호를 어떻게 알았냐고 물어보면 텔레마케터는 정확한 답변을 회피한다. 나의 정보가 어디서 유출되었을까? 인터넷에서 나는 누구일까? 인터넷에서의 나는 나를 대신한 ‘나의 정보’이다. 그런데 누군가가 나의 정보를 알고 있다면 나를 대신해 나쁜 짓을 할 수도 있을 것이다. 전자정부를 표방하고 있는 정부에서 이러한 개인정보를 보호하기 위해 법을 제정하는 것은 당연한 흐름일 것이다.

데이터베이스 관리자의 필요성

개인정보보호법 중 데이터베이스 관리 부분에는 인증, 계정 생성과 폐기, 계정 운영, 패스워드 관리에 대한 기본적인 요구 조건을 정의한다. 그밖에 추가적으로 잘못된 로그인 횟수에 대한 제한 설정, 일정시간 작업을 하지 않았을 때 자동 로그아웃되도록 한다는 내용 등이 있다. 접근 수준과 권한부여에 관한 항목도 눈에 띈다. 이와 같은 사항들은 데이터베이스 관리자가 처음 배우는 보안의 기본적인 내용이다. 단지 정책이 없기 때문에 전사적으로 적용한 적은 없더라도 교육을 통해 흔히 배우던 부분이다. SQL 서버 관리자에 의해 쉽게 정의될 수 있다. 예를 들어 <표 1>과 같이 사용자와 작업들을 정의하고 DB 서버에 적용하면 된다.

개인정보보호법을 위한 정책과 SQL DBA가 알아야 할 사항을 모두 이 지면에서 설명할 수는 없을 것이다. 다음과 같은 한 가지 예를 들어 설명해 보겠다. 다음 과정을 통해 정책과 솔루션 파악하고 적용하는 과정을 살펴보자.

⑴ 정책정의 : 정책 프로그램, 유틸리티, 명령어 등의 데이터에 대한 접근을 통제해야 한다.

⑵ 해결책 찾기 : 특정 프로그램을 어떻게 막을 수 있을까? SQL 서버에 이러한 기능이 내장되어 있느냐는 것인데, 당연히 포함되어 있다. 애플리케이션 역할을 이용하면 된다.

⑶ 관리자가 알아야 할 개념 : 애플리케이션 역할도 데이터베이스 역할의 일부분이다. 애플리케이션 역할은 그룹의 의미는 없고 오직 ‘권한의 집합이라는 의미만을 갖는다. 즉 애플리케이션 역할 안에는 데이터베이스 사용자가 포함되지 않는다. 애플리케이션 역할은 특정 업무가 있는데 다른 클라이언트(액세스, 엑셀 등)에서는 해당 데이터 접근을 금지하고, 오직 해당 업무를 위해 개발된 애플리케이션을 통해서만 데이터에 접근하도록 통제하기 위해서 사용된다.

⑷ 정책적용 : 다음에 살펴볼 ‘roleApp’라는 사용자정의 응용 프로그램 역할을 만드는 예제처럼 정책을 적용한다.

여기서 주의할 것이 있는데, 응용 프로그램 역할은 애플리케이션에서 사용하는 세션이 끝날 때까지만 유효하게 설정된다는 것이다. 세션이 종료되면 해당 애플리케이션을 사용하고 있던 사용자가 가지고 있는 모든 권한이나 역할도 모두 무효화된다.

사용자정의 응용 프로그램 역할을 만드는 방법은 엔터프라이즈 관리자를 사용하는 방법과 쿼리 분석기를 사용하는 방법이 있다. 먼저 엔터프라이즈 관리자를 사용하는 방법에 대해 알아보겠다. 엔터프라이즈 관리자를 사용하는 방법은 사용자정의 데이터베이스 역할을 만들 대상 데이터베이스의 ‘역할’을 선택하고, 오른쪽 클릭한 다음, ‘새 데이터베이스 역할’ 메뉴를 선택한다. 새 데이터베이스 역할 이름을 지정한 후 데이터베이스 역할 유형을 ‘응용 프로그램 역할’로 선택하면 된다. 마지막으로 응용 프로그램 역할에서 사용할 암호를 지정한 다음 ‘확인’ 버튼을 클릭하면 된다.

퀴리 분석기를 사용해서 사용자정의 응용 프로그램 역할을 추가하는 방법은 다음과 같다.


참고로 응용 프로그램 역할에 대한 암호를 암호화하여 네트워크상에서 보안을 강화하고자 할 때는 다음과 같이 ODBC Encrypt 기능을 활용해서 암호화할 수 있다.



<화면 1> 응용 프로그램 역할 만들기

<그림 1> 보안 전략 선택의 절차


<표 1> 사용자와 작업에 대한 정의 예

사용자 계정	작업
feelanet	모든 데이터베이스 액세스
F_backup	백업 작업 수행
F_personnel	인사 DB에 대한 액세스
F_security	기밀 데이터에 대한 액세스
F_product	제품 정보 읽기 전용

정책 프로그램, 유틸리티, 명령어 등의 데이터에 대한 접근을 통제해야 한다는 정책을 이해하고 애플리케이션 역할을 적용하는 관리자가 있는가? 정보보호법에 대비를 하고 회사의 보안 수준을 한 단계 높이고 싶은가? 이 모든 것은 물론 사람이 직접 해야 할 일이다. 좋은 관리자가 있다면 쉬운 일이겠지만, 반대로 얘기하면 좋은 관리자가 없기 때문에 문제가 발생한다는 것이다.

현실에서 반영하기 어려운 문제

기능 요구 사항에 맞게끔 SQL 서버의 보안을 설명하던 중 A사 팀장은 필자에게 바로 적용하기 힘들 것 같다는 의견을 주었다. 개발자들이 운영중인 데이터베이스에 개체를 만들지 못하고 테스트 서버에서 만들면 DBA가 직접 적용하는 형태로 진행하기에는 현실적으로 어렵다는 것이다. 수많은 개발자들이 db_owner가 될 수밖에 없는 형편이라 적용하려는 정책 중 지금 적용할 수 없는 일들이 발생한다는 설명이었다. 필자는 항상 고객사에게 컨설팅할 때 만약 개발자 중 악의를 품고 데이터를 삭제한다거나 고객 정보를 나쁜 의도로 사용하는 것에 대한 대책은 있냐고 물어보면 대부분 고객사들에게서 현재는 별다른 방법이 없다는 답변을 듣는다. 개인정보보호법이나 회사의 정책을 적용하기 위해서는 통제된 환경에서 오는 불편함을 기꺼이 받아들여야 하고 새로운 투자가 필요하다는 딜레마에 빠진다. 현실에서의 데이터베이스 보안은 개발의 편리성과 그와 상충되는 운영 환경에서 통제되고 문서화된 관리 절차를 어떻게 접목하느냐의 선택일 것이다.

요구 사항에 대한 솔루션 검토

기능 요구 조건이 정의가 되면 어떻게 조건을 만족시켜야 하는지 선택해야 한다. 사람이 모든 일을 하기에는 많은 시간이 들고 반복적으로 일을 해야 하며 능력에 따라 다른 결과를 가져오기도 한다. 많은 솔루션 업체들이 이런 문제를 해결하기 위해 고민을 했고 그 결과 훌륭한 제품을 만들게 되었다. 관리자가 해야 할 일은 이 제품들 중 어느 것이 가장 적은 비용으로 가장 많은 이익을 얻을지 선택하면 된다.

뜨거운 감자가 된 암호화

중요 정보에 대한 침해 사고와 악용 사례가 증가하고 있다. 고객정보 유출로 인해 심각한 피해가 발생하고 기업의 이미지가 실추된다. 사람들의 악용으로부터 개인정보를 막기 위해 기밀성이 요구되는 DB 시스템 내의 중요 필드에 대해 암호화되어 있어야 한다. 개인정보(주민번호, 계좌번호, 의료보험증번호 등)를 DB에 저장할 때 암호화해서 저장해야 할 것을 의무화하고 있다. 주민번호를 대체하는 방식이 논의되고 있는 등 아직 완전한 스펙이 정의된 것은 아니다.

암호화를 위한 솔루션으로는 소프트웨어 방식과 하드웨어 방식이 있는데, 항상 그렇듯이 소프트웨어 방식은 하드웨어 방식에 성능이 떨어진다는 단점을 지적받고 있다. SQL 서버 2005 또한 자체적으로 데이터 암호화가 탑재되어 있어 개발자나 관리자가 공부해야 할 영역이 늘어났다. 그리고 많은 소스코드가 공개되어 있어 개발자들이 특정 컬럼을 암호화하는 것은 어려운 일이 아니다. www.codeproject. com/database/xp_md5.asp에서는 SQL 서버 2000에서도 확장 프로시저를 사용하여 암호화하는 방법을 보여준다.

손쉬운 암호화 기술로 아무 문제도 없다는 결론이면 행복한 사람(관리자), 불행한 사람(솔루션 제공자)들이 생기겠지만 현실은 그 반대의 결론으로 흘러가고 있다. 암호화 자체가 기술적으로 문제되는 것은 아니지만 데이터베이스의 특정 컬럼을 암호화하면 인덱스를 사용할 수 없어 성능에 큰 문제를 야기한다. 그리고 법의 내용과 시행이 어떻게 진행될지에 대한 정확한 정보가 없는 상태다. 데이터베이스 디자인이 변경되거나 추가 장비로 인해 비용, 데이터베이스와 통합된 기술 등이 문제될 것이다. 법으로 강요해도 중소기업들의 인프라 구축과 기술 부족으로 인해 지킬 수 없는 법이 될지도 모른다.

감사 시스템

최근 실시된 CSI/FBI(Computer Security Institute/Federal Bureau of Investigation) 컴퓨터 범죄와 보안 관련 설문 조사 결과를 보면 정보 도난과 DoS(Denial-of-Service, 서비스 거부) 공격으로 인한 손실이 가장 크다는 것을 알 수 있다. 여기서 주목해야 할 내용은 내부 사용자의 정보 도난이다. 인가된 내부 사용자들은 권한이 있음으로 데이터에 접속할 수 있는 것은 당연한 일이다. 인가된 내부 사용자에 의한 사고에 대비한 데이터베이스 감사 솔루션 필요성이 제기된다.

SQL 서버의 감사 기능

SQL 서버 2005는 C2 audit 설정을 통해 SQL 서버에서 일어난 모든 명령문과 개체 액세스에 성공하거나 성공하지 않은 시도를 검토할 수 있다. 이런 모든 내용은 추적 파일에 기록되며 이것은 프로필러를 사용하여 살펴볼 수 있다. 뜻하지 않은 데이터의 삭제, 테이블 삭제 등의 원인들도 찾을 수 있다. 이를 위해서는 다음과 같은 설정이 필요하다. 단 주의할 점은 서버에 약간의 부하가 걸리며, 추적 파일을 쓸 수 없으면 서버는 자동으로 중지된다는 것이다. 참고로 서버를 재시작해야만 C2 보안 감사가 시작된다.

감사 파일은 SQL 서버 데이터 폴더(보통 C:\Program Files\Microsoft SQL Server\MSSQL\Data)에 ‘audit trace_yyyymmddhhmmss.trc’로 저장되며 200MB 분량이 다 차거나 서비스가 중지되어야 파일로 남게 된다. 그리고 나서 새로운 파일을 또 기록하게 된다. 해당 폴더에 공간이 없으면 SQL 서버 서비스가 시작되지 않는다. 감사를 중지할 때는 앞의 스크립트에서 다음과 같이 바꾸어 실행하고 SQL 서버 서비스를 재시작하면 된다.

참고로 써드파티에서는 보안을 위한 제품을 만들어 판매하고 있다. SQL 서버가 기본적으로 제공하는 이상의 보안 감사를 원한다면 이런 제품들을 고려해 볼 수도 있다. 써드파티 보안감사 제품을 보고 싶다면 www. microsoft.com/sql/partners/technologysolutions/ security.asp를 참고하면 된다. 국산 제품은 아직 이곳에서 소개되지 않고 있다.

써드파티 솔루션 소개

여러 제품이 있겠지만 필자가 사용해 본 경험이 있는 IAuditor라는 제품을 잠깐 살펴보면 서버에 전혀 부하를 주지 않고도 SQL 서버에 들어오는 모든 패킷을 손실 없이 잡아낸다. 작은 규모의 사이트라면 약간의 부하가 문제가 되지 않겠지만 대용량 트랜잭션이 빈번한 사이트라면 이야기가 달라진다. SQL 서버의 감사 기능으로 충분한가? 그렇지 않다면 감사 솔루션의 구입이 필연적일 수 있다. 항상 기억해야 할 것은 소프트웨어 방식은 시스템에 많은 부하를 주고 하드웨어 방식은 시스템에 부하를 주지 않는 장점이 있다는 것이다.

<그림 2>는 IAudit에서 제공하는 기능을 설명하고 있으며, <그림 3>은 스위치 장비에 들어오는 패킷을 TAP나 포트 미러를 통해 감사 서버로 전달함으로서 프로덕션 서버에 영향을 주지 않는 구조하는 IAudit 아키텍처를 설명하고 있다. 대부분의 감사 솔루션들이 이와 비슷한 구조를 가지고 있다.

보안이 취약한 이유

사고 관리는 가능한 빨리 사용자에게 서비스를 복원하는 것이고 문제 관리는 문제에 대한 영구 해결책을 제시하는 것이다. 많은 중소기업에서 문제가 발생하면 전문 업체로부터 컨설팅을 받거나 자체적으로 해결하는 것으로 사고 관리가 부분적으로 이뤄지고 있지만 문제에 대한 근본적인 해결책이 이뤄지지 않고 있다. 근본적인 이유는 돈과 관계가 있는 것으로 보안에 따로 투자할 여력이 없다는 것이다. 그러나 반드시 알아야 할 사항은 보안 시스템을 구현하는 데에도 상당한 비용이 들지만 이 비용은 보안 사고에 따른 손상을 줄이기 위한 비용에 비하면 극히 미미하다는 것이다.

<그림 2> IAuditor 기능 소개

<그림 3> IAuditor 아키텍처


출처 : 마이크로소프트웨어 2006년 2월호

얼마 전에 SQL 서버 2005가 출시되었다. 많은 회사에서 SQL 서버를 2005 버전으로 업그레이드할 것이고, 이에 따라 관리자는 새로운 기능을 익혀야 하며 알아야 할 사항도 많아졌다. 새로운 기능, 보안, 가용성, 성능 등에서 5년의 세월동안 SQL 서버가 진화했기 때문이다. 개인정보보호법에 DB시스템 내의 중요 컬럼을 암호화해야 하고 기밀성 데이터를 데이터베이스에 전송할 때도 암호화해야 한다고 되어 있다. 그래서 SQL 서버 2005로 암호화하는 방법과 기능을 미리 파악해야 할 필요성이 있다. SQL 서버가 제공하는 보안 모델을 살펴보고 어떻게 적용할 건지를 고민해야 할 시기가 왔다.


서버와의 연결은 안전한가?

SQL 서버 2005에 접속하기 위한 문과 열쇠를 이해할 필요가 있다. 문을 통해 정상적인 접속이 가능하지만 문을 악용해서 비정상적인 접속을 시도할 수 있다는 것도 염두에 둬야 한다. 이 문은 열쇠가 없이는 들어올 수 없을 정도로 최신의 기술을 갖추었다. 하지만 열쇠를 문 입구의 화단 밑에 숨겨 놓았다면 약간의 짐작으로 열쇠를 획득 할 수 있을 것이다. 기본적인 액세스 컨트롤은 문과 열쇠에 대해 이해하고 서버와의 연결을 제어하는 것이다.

엔드포인트 기반 인증

엔드포인트(Endpoint)는 인스턴스에 대한 엔트리 포인트로서 HTTP, 서비스 브로코, 데이터베이스 미러링 등에 사용된다. 어떤 목적으로 사용할지 명시적으로 만들지 않는다면, 존재하지 않고 기본적으로 어떠한 퍼미션도 없다. 생소하게 느껴질 수도 있으나, 외부에서 SQL 서버로 들어오기 위한 문이라고 생각하면 쉽게 이해할 수 있을 것이다. 이 문을 구성하기 위해서는 다음과 같이 사용 목적과 사용할 프로토콜, 접속 퍼미션 등을 부여해야 한다.

◆ SOAP, SSB, 데이터베이스 미러링 등에 사용된다.
◆ TCP, HTTP 등의 프로토콜을 사용한다.
◆ 허락된 사용자에게 접속 퍼미션을 부여한다.

사용 목적과, 프로토콜, 퍼미션 등을 부여하는 문법을 이용해 상품을 조회하는 엔드포인트를 다음과 같이 만들어 보자.

기본적으로 엔드포인트에 접속할 수 있는 사용자는 아무도 없다. 예를 들어 철수가 SOAP를 통해 SQL 서버 2005에 접속해 상품조회를 하고 싶다면 다음과 같은 조치를 취해야 한다.


다음 예제는 영희가 SOAP를 통해 상품조회와 가격 수정을 해야 하는 경우이다.


엔드포인트에 대한 이해는 SQL 서버 2005의 여러 가지 기능들이 사용될 때 필수적이며 관리자의 중요한 보안 포인트가 될 것이다. 엔드포인트를 통해 지구촌 여러 곳에서 SQL 서버를 사용하는 환경이 만들어지고 편리성과 필요성은 많은 부분에서 충족되겠지만 보안에 틈이 생긴다면 무용지물이 될 수도 있다.

<화면 1> 윈도우의 보안 설정


<화면 2> 정책 위반으로 계정 만들기 실패


<화면 3> 계정 만들기

강화된 암호 정책

암호 정책이 없다는 것은 열쇠를 방치하고 있다는 것과 유사하다. 2005 이전 버전에서는 회사 정책에 의해 관리자가 수동으로 암호를 바꾸거나 복잡한 암호를 사용해야 했지만 시스템이 강요할 수는 없었다. 지키지 않아도 무방했다는 뜻이다. 하지만 SQL 서버 2005에서는 다음과 같이 강화된 암호 정책을 지원한다. 지키지 않을 수 없다는 것을 의미한다.

◆ 로그인 정책 강화 : 암호 길이, 암호 만료기간, 계정 잠금
◆ 로컬 윈도우 암호 정책 계승 : 지속적인 기업 광범위 정책 지원
◆ 접근 : 새로운 API 암호 정책 점검, 윈도우 서버 2003 이상만 사용가능, 이전 버전에 대한 초보적인 복잡성 점검

SQL 서버 2005는 윈도우 서버 2003의 암호 정책을 그대로 상속받는다. 암호는 최소 6자리 이상으로 하고 단순한 암호는 사용할 수 없다는 정책을 윈도우 서버 2003에서 설정했다면, SQL 서버 2005에 그대로 상속되어 계정의 암호 정책에 반영된다.

윈도우 서버 2003의 암호 정책을 따르지 않는다면 <화면 2>와 같은 결과가 나온다. 암호의 복잡성을 만족시키지 못하기 때문에 계정이 만들어지지 않는 단순한 데모다. 그래서 <화면 3>과 같이 정책을 위반하지 않도록 암호를 복잡하게 만들었고 반드시 다음에 로그인시 암호를 바꾸도록 설정했다.

예전에 경찰 보안 담당자들에게 집에 설치된 번호키를 몇 자리의 번호로 설정하고 얼마나 자주 바꾸는 것이 좋은지 물어본 적이 있다. 그 보안 담당자의 대답은 7자리 이상의 번호와 한 달에 한 번씩은 바꿔야 한다는 것이었다. 하지만 과연 현실이 그럴까? 보통은 1년에 한 번도 바꾸지 않는 것이 현실로 알고 있다.

또한 집에 초등학생이 있다면 따를 수 있는 정책이겠는가. 필자는 최소한의 원칙을 만들고 지켜나가는 것이 보안의 시작이라 생각한다. 적어도 자신이 관리하는 서버 개체들은 안전한가와 최소한의 특권 원칙을 지키고 있는지는 생각해봐야 한다.

데이터베이스는 모두 SA(System Administrator) 계정으로 접근하는 것이 일반적이고 당연하게 생각되고 있다. 윈도우는 Administrator로, SQL 서버는 SA로 로그인하는 이상한 관습들이 많은 회사를 지배하고 있다. 누구나 실수를 할 수도 있고 접근하지 말아야 하는 곳에 접근하고 싶은 욕심이 생길 수도 있다. 기본을 지키지 않아서 발생하는 많은 문제들을 경험할 수도 있다.


데이터베이스 개체 보안

데이터베이스 개체가 어디에 존재하는지를 생각해보자. 우물에서 숭늉을 찾을 수는 없는 노릇이다. 개체에 퍼미션을 설정하는 기본 작업과 함께 개체들을 논리적으로 나눠서 저장하는 것은 중요하다. 이전 버전에서는 데이터베이스 단위로만 나눌 수 있었으나 SQL 서버 2005에서는 스키마를 지원으로 세분화하고 있다. 또 하나의 중요 특징은 실행 문맥 제어를 통해 직접적인 권한 대신 대리인의 문맥으로 실행할 수 있다는 것이다.

새로운 등장, 스키마

보안의 세부 단위가 새롭게 등장했다. 스키마는 데이터베이스를 보안 단위나 논리적 단위(응용 프로그램)로 나눌 수 있는 개념이다. 책상에 서랍이 여러 개 있는 개념이라고 할 수 있다. 윗 서랍의 관리자와 아랫 서랍 관리자 그리고 사용자를 각각 분리해서 처리할 수 있다. 그래서 이제 개체를 명명할 때는 서버, 데이터베이스, 스키마, 개체로 불러야 한다. SQL 서버 2000에서는 서버, 데이터베이스, 소유자, 개체의 이름을 가졌다. 그래서 소유자를 삭제하고 싶어도 개체의 소유주이기에 지울 수 없거나 개체의 소유자를 다 바꾸고 삭제하는 무식한(?) 행동을 해야 했다. 하지만 2005에서는 스키마로 대처되어 이런 불편함이 사라지게 되었다. 사용자 스키마를 제한하면 다른 스키마의 접근이 차단되어 향상된 보안 관리를 할 수 있다. 그리고 응용 프로그램에게 향상된 네임스페이스 관리 계획을 제공한다. <리스트 1>은 스키마 수준의 개체 보안에 관해 보여주고 있으며, <그림 1>은 데이터베이스 스키마 개체의 계층 구조를 설명하고 있다.

실행 문맥 제어

기본적인 실행 문맥은 호출한 사용자의 권한으로 실행하면 된다. 데이터를 지우는 업무를 하기 위해서는 지울 수 있는 권한이 있어야 한다는 의미이다. 업무의 단위로 권한을 부여하다 보면 최소한의 권한이 아니라 어쩔 수 없이 많은 권한의 줘야 하는 경우가 생기기도 한다. 예를 들어 특정 테이블만 truncate할 수 있는 권한은 없다. 그래서 truncate 권한을 주면 다른 테이블에 대한 권한이 생겨 문제가 야기될 수 있다는 것이다. 그래서 특정 테이블을 지울 수 있는 개체를 만들고 개체를 실행할 수 있는 권한을 줘서 그 개체에서 수행하는 업무를 다른 사용자의 문맥으로 수행해야 할 필요성이 존재한다. 최소한의 권한 부여라는 보안의 기본원칙을 실행 문맥 제어를 통해 세밀하게 정의할 수 있게 되었다.

<그림 1> 스키마의 계층 구조


<표 1> 퍼미션을 조회하는 카탈로그 뷰

카탈로그 뷰	내용
Sys.Server_permissions	서버 레벨 퍼미션
Sys.database_permissions	데이터베이스 레벨 퍼미션
Sys.securable_permissions	모든 보호된 객체 리스트
Sys.fn_builtin_permissions	모든 수여 가능한 퍼미션 목록

EXECUTE AS와 SETUSER

저장 프로시저를 만들 때 누구의 문맥으로 실행할지를 지정할 수 있다. 사용자들은 저장 프로시저를 호출한 권한만 있으면 저장 프로시저에서 어떤 일을 하는가에 따른 퍼미션을 신경 쓰지 않아도 된다. Execute As는 「{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | ‘user_name’ } 」와 같이 사용한다. 여기서 CALLER는 호출한 사용자(기본 값), SELF는 저장 프로시저나 함수를 만든 사용자를 말한다. 또 OWNER는 소유자 문맥, user_name에는 특정 사용자를 지정한다. 그리고 SETUSER는 「SETUSER [ ‘username’ [ WITH NORESET ] ] 」와 같이 사용한다. EXECUTE AS는 저장 프로시저가 동작하는 순간에만 유효하지만 SETUSER는 다른 사용자로 가장해서 다른 업무를 수행하다가 다시 자신의 계정으로 돌아온다.

퍼미션 검사

관리자가 조회해야 할 기본적인 카탈로그 뷰이다. 누가 어떤 권한을 가지고 있는지를 보고 싶다면 <표 1>과 같은 뷰들과 친숙해야 한다. 필자가 SQL 서버 2000을 강의할 때 가장 많아 듣던 질문 중 하나가 어떻게 보안이 설정되었는지 보고 싶다는 것이었다.

데이터 암호화

보안 강화를 위해 민감한 데이터는 암호화되어야 한다. 아무리 관리자라도 고객들의 민감한 데이터를 볼 권한은 없다. 고객의 비밀번호나 주민등록번호 등과 같은 데이터를 생각해보자. 요즘은 데이터베이스에서 암호화는 필수적으로 필요한 시대에 왔다. 또한 법률 규정이 강화되고 있어 회사의 정책이나 법률 규정에 따라서도 데이터를 암호화해야 한다. 2005 이전 버전에서는 암호화된 모듈을 개발하거나 전문 업체의 제품을 구입하는 것으로 암호화가 이뤄졌다. SQL 서버에서 직접 지원하지 못했기 때문에 벤더 스스로가 해결책을 찾아야만 했다. 그러나 SQL 서버 2005는 데이터 암호화가 탑재되어 있다. 이전 버전보다 진보되었고 시대의 흐름을 반영하고 있다는 증거가 될 것이다.

키 이해하기

서비스 마스터 키는 연결된 서버의 암호, 데이터베이스 마스터 키 보호 등 시스템 데이터 보호 역할을 담당한다. 서비스 마스터 키는 현관 열쇠로 비유할 수 있는 것으로 연결된 서버의 암호와 연결 문자를 암호화하는데 사용된다. 그리고 데이터베이스 마스터 키를 암호화할 때도 사용될 수 있다. 방마다 열쇠(데이터베이스 마스터 키)가 있고 이 열쇠의 목적은 방안의 귀중품을 지키는 것이다. 그럼 이 열쇠는 누가 보호하고 있는 가를 생각해보면 이해가 될 것이다. 현관 열쇠를 이용해서 현관에 들어온 이후에야 방 열쇠를 사용할 수 있다.

데이터베이스 마스터 키는 디지털 증명서와 비대칭 키를 암호화하는데 사용되며, 디지털 증명서와 비대칭 키는 대칭키를 암호화하는데 사용된다. 방안에 있는 책상 열쇠가 최종적인 데이터를 보호할 것이다. 대칭키를 책상 열쇠로 생각하면 이해가 빠를 것이다. <그림 2>는 암호화 계층 구조를 보여주고 있다. 가장 상위에는 서비스 마스터 키가 있고, 윈도우의 DPAPI에 의해 암호화되어 있다.

디지털 증명서

디지털 증명서는 크게 다음 3가지 용도로 사용된다.

◆ 서비스 브로커에서 커뮤니케이션 인증이나 메시지를 암호화하는데 사용된다.
◆ 저장 프로시저 등의 개체에 서명하여 코드 인증을 한다.
◆ 디지털 증명서를 이용해 데이터를 암호화하고 복호화한다.

디지털 증명서를 사용하기 위해서는 새로운 T-Sql문을 익혀야 한다. <리스트 2>는 디지털 증명서를 만드는 문법을 보여주고 있고, <리스트 3>에는 특정 컬럼을 암호화하는 데모이다. 데모를 따라하면 <화면 4>와 같이 암호화된 결과를 확인할 수 있다.

SQL 서버 감사

데이터베이스 관련 분야에는 많은 솔루션들이 있다. 그 중에서 최근에 많은 관심을 받고 있는 솔루션은 감사와 관계되는 솔루션들이다. 네트워크에서 들어오는 모든 쿼리문을 데이터 손실 없이 잡아내는 제품부터 Agent 방식의 제품까지 정말 다양하다. 일반적인 회사의 업무에서 사용할 목적이라면 SQL 서버 2005에서 제공하는 감사 기능으로 충분할 수 있다. 그러나 특수한 목적이 있다면 SQL 서버 2005와 함께 추가적인 솔루션 도입을 고려해야 한다. 다음은 SQL 서버 2005의 감사 기능이다.

◆ 데이터 접근 감사 : SQL 서버에는 프로필러라는 모니터링 도구가 있다. 이벤트를 선택하고 데이터를 필터링하게 구성하면 쿼리문을 실시간으로 감사할 수 있다. 그러나 클라이언트에서 이 도구를 이용해서 실시간으로 보게 되면 서버에 부하를 줄 수 있다. 그래서 가급적이면 저장 프로시저를 이용해 파일로 남겨두고 나중에 프로필러에 불러오거나 테이블로 저장해두고 서버에 어떤 일이 발생했는지 감사할 수 있다. 감사 도구라기보다는 모니터링 도구에 가깝지만 특정 시간대에 일어나는 일을 보기 위한 용도라면 충분할 것이다. 서버가 다운되었는데, 블랙박스가 없다면 정확한 원인을 알기 힘들다. SQL 서버에도 블랙박스를 만들 수 있다.

◆ 로그인 감사 : 누가 언제 접속했는지를 알 필요성이 있다. 누군가 서버로 침입하기 위한 기록을 남겼다면 그런 공격에 대한 대비를 할 수 있다. SQL 서버 2005는 기본적으로 실패한 로그인 시도에 대해 감사하도록 설정되어 있다. 관리자는 정기적으로 감사 이벤트를 확인해야 한다.

◆ 사용자 정의 감사 : 누가 데이터를 삭제했는지 테이블을 수정했는지를 알고 싶다면 트리거나 이벤트 알림을 사용해야 한다. 외부의 침입뿐만 아니라 내부 운영에 관해서도 누가 어떤 일을 했는지 알아야 한다. 트리거나 이벤트 알림 서비스를 사용해서 구현하는데 목적에 맞는 감사 테이블을 만들고 특정 행위가 일어나면 감사 테이블로 적재하는 형태로 구현된다.

<그림 2> SQL 서버 2005 암호화 계층 구조



<화면 4> 암호화된 데이터 조회 결과

서버 구성은 안전한가?

Surface Area Configuration은 새로운 설치의 기본 보안을 지원하기 위한 도구이다. 윈도우를 설치하면 기본적으로 많은 서비스가 시작된다. 사용하지 않는 서비스가 동작하면 서버 리소스(메모리 등)가 그 만큼 낭비되고 보안의 틈은 그 만큼 커지게 된다. 좀 더 쉽게 SQL 서버를 구성하자는 단순하지만 직관적인 생각으로는 새로운 도구를 만들 수 없을 것이다. 처음 이 단어를 접했을 때 당황스러웠지만 한번만 실행해 보면 모든 것이 이해될 것이다.

<화면 5>는 Surface Area Configuration의 기본 화면 구성을 보여 준다. 화면에서 몇 번 클릭해보면 이 도구를 쉽게 이해 할 수 있을 것이지만, 간단히 정리하면 다음과 같은 일을 한다.

◆ 설치 시 서비스를 선택할 수 있다(가급적 필요 없는 서비스는 멈추는 것이 좋다).
◆ 기능 레벨에서 특정 기능의 ON/OFF 스위칭이 가능하다.
◆ 특정 통신 프로토콜에 대한 ON/OFF 스위칭이 가능하다.

보안 진단 체크 리스트

보안 진단 체크 리스트는 사람에 따라 평가가 달라지면 안되기 때문에 템플릿이 필요하다. <표 2>를 간단히 보자. 11가지 중 Yes라는 대답이 6가지 이상이 안되면 서버의 보안 상태는 아주 낮은 수준일 것이다. MS에서는 운영자 가이드, 보안 가이드를 통해 많은 정보를 주고 있다. 이러한 정보를 이용해서 단순하지만 적용 가능한 체크리스트를 만들어 활용해야 한다.

이론에 대한 실천이 중요

지금까지 SQL 서버 2005가 제공하는 보안 모델에 대해 살펴봤다. SQL 서버 2005에 관한 정보는 아직 부족하지만 점점 많아질 것이다. 아무리 많은 이론을 알아도 실천하지 못하는 지식은 반쪽이라고 생각한다. 어디서부터 시작하면 좋을까? SQL 서버 2005를 테스트 장비에 설치하고 시뮬레이션을 해보는 것도 좋은 시작일 것이다. 짧은 지면이라 SQL 서버 2005의 모든 보안 기술을 나열하지는 못했지만 전체를 간단히 살펴 볼 수 있는 기회가 되었으면 좋겠다.


<화면 5> 서비스와 연결 구성


<화면 6> 응용 프로그램에서 요구하는 기능 제어 구성

<표 2> 간단한 보안 체크 리스트

다음을 수행하고 있나?	Yes or No
주기적으로 MBSA 실행
정책 점검과 널 값 암호이 삭제 또는 검색
사용되지 않은 로그인 삭제
Public에 대한 퍼미션 객체 검색
로그인-사용자 맵핑 확인
붙이기/분리하기 방법
Sp_change_users_login
역할에 소속된 멤버십 관리
트러스트된 개별 맴버십 확인
서버 시작 시 프로시저의 안정성 확인
주기적 검색 필요 -the surface area

출처 : 마이크로소프트웨어 2006년 1월호

SQL 서버 2005가 나오면서 개인적으로 가장 크게 관심을 보인 부분이 바로 테이블 파티셔닝이다.

현재 롯데칠성음료에서도 매달 100만 건의 거래명세표 내역이 쌓이면서 이를 처리하기 위한 대용량 데이터베이스에 대해 높은 관심을 기울이고 있다. 이번 글에서는 대용량 데이터베이스를 위한 테이블 파티셔닝과 가용성을 높이기 위한 스냅샷 그리고 미러링에 대해 알아 볼 것이다.

지난 3회에 걸쳐 SQL 서버 2005의 새로운 특징들에 대해 알아봤다. 이번 글에서는 마지막으로 대용량 데이터베이스를 위한 기존의 파티션뷰를 대체하는 테이블 파티셔닝과 데이터베이스 이력 관리를 위한 스냅샷, 그리고 가용성을 높이기 위한 클러스터링에 견줄 수 있는 미러링에 대해 알아 볼 것이다.

기업의 데이터는 시간이 지날수록 점점 많아지고 있다. 분석을 위한 데이터는 점점 더 쌓여만 가고 더 이상 하나의 테이블에 이 모든 정보를 담아 두는 것이 비효율적일 때가 있다. 보통 기가나 테라 단위의 데이터를 하나의 테이블에 담아 두게 되면 테이블 유지 보수가 힘들며 성능 또한 느려지게 된다.

이러한 데이터는 대부분 과거의 데이터가 함께 있어서 그러는데, 아마 몇 년 전의 데이터는 거의 사용하지 않을 것이다. 이럴 때에는 테이블을 나누어서 최근의 데이터는 높은 성능을 내는 I/O에 담아 두고, 예전의 데이터는 비교적 낮은 성능의 저렴한 I/O 장치에 담아 두는 것이 효율적일 것이다. 이럴 때 사용하는 것이 바로 파티셔닝이다.


SQL 서버 7.0/2000에서 분할된 뷰

파티셔닝을 위한 전략은 SQL 서버 7.0에서부터 지원했다. 분할된 뷰(partitioned view)를 이용하여 각각의 테이블을 UNION으로 묶어서 마치 하나의 테이블로 볼 수 있도록 했다.


이와 같이 2003년 9월의 테이블과 2003년 10월의 테이블, 2003년 11월의 테이블을 UNION으로 결합함으로써 분할된 뷰를 만들 수 있다. 이 때 각 테이블은 파티셔닝 컬럼을 CHECK 조건을 이용하여 미리 제한해둬야 한다. 예를 들면 앞의 각 테이블에 TransactionDate라는 날짜 컬럼이 있다면 제한 조건으로 다음과 같이 줘야 한다.


이렇게 파티셔닝 컬럼을 정의하고 이 컬럼에 INDEX를 걸어 두면 분할된 뷰를 이용하여 테이블에 접근할 때 다른 날짜의 테이블은 읽지 않게 된다. SQL 서버 2000에서는 분할 뷰를 이용하여 데이터 갱신 작업이 효과적으로 수행하도록 지원하였으며 분산 분할된 뷰(distributed partitioned view)로까지 발전을 하여 각각의 테이블이 한 서버가 아닌 다른 서버에 있어도 가능하도록 발전했다.

하지만 분할된 뷰 방식의 파티셔닝은 여러 테이블을 하나의 뷰로 모았기 때문에 관리상 불편한 점이 많았다. 예를 들면 테이블 구조를 바꾼 다거나 인덱스를 재생성하거나 변경하는 경우 각각의 테이블을 모두 반영해줘야 하기 때문이다.


SQL 서버 2005의 테이블 파티셔닝

SQL 서버 2005에서는 뷰를 통한 파티셔닝이 아닌 테이블 단위의 파티셔닝을 지원한다. 즉 하나의 테이블을 여러 조각으로 쪼개어 관리하는 것이 가능하다. 그러므로 분할된 뷰처럼 각 테이블을 따로 관리할 필요가 없다. 예를 들면 인덱스를 만드는데 있어서 하나의 테이블만 만들면 되므로 관리상 이점이 있다. 또한 성능에 있어서도 더 좋은 성능을 보여준다.

분할된 뷰에서는 각각의 테이블을 보고 나중에 합치는 방식으로 진행되었지만, 테이블 파티셔닝에서는 멀티 CPU 환경이라면 병렬처리(demand parallelism)를 이용하여 보다 빠른 쿼리를 수행할 수도 있다. 쿼리를 컴파일하는데 있어서도 분할된 뷰에서는 테이블이 많을수록 느렸지만, 테이블 파티셔닝에서는 파티션 개수에 상관없이 빠른 속도를 보장한다.


테이블 파티셔닝은 파티셔닝 함수와 스키마를 이용하여 구현을 한다. 파티셔닝 함수로는 경계 영역을 구분하고 스키마로는 실제 물리적인 파일 그룹에 각 파티션을 맵핑한다.


이 예제는 myRangePF1이라는 파티션 함수를 정의하는데 있어 경계 부분을 왼쪽에 포함하는 함수를 만들고 있다. 이와 같이 실행하면 다음과 같이 4개의 파티션 영역을 정의한다.

파티션	1	2	3	4
값	col <= 1	col > 1 and col <= 100	col > 100 AND col <= 1,000	col > 1,000

즉, 경계를 왼쪽 부분에 포함하기 때문에 1,100,1000은 각각 왼쪽 파티션에 포함하게 된다. 만약 LEFT 대신에 RIGHT라고 쓴다면 1,100,1000은 각각 오른쪽 파티션에 포함하게 된다. 파티션 함수를 만들었으면 실제 물리적인 영역에 맵핑할 수 있는 스키마를 정의해야 한다.


이 구문은 앞에서 정의한 파티션 함수를 바탕으로 각각 4개의 파일 그룹에 맵핑하고 있다. 따라서 이런 경우는 각각의 파티션이 별개의 물리적인 공간에 저장되게 된다. 물론 하나의 파일 그룹에 담을 수도 있다. 그럼 이제 SQL 서버 2000의 분할된 뷰와 SQL 서버 2005의 테이블 파티셔닝의 차이점에 대해 알아보자.


분할된 뷰 vs. 테이블 파티셔닝

먼저 분할된 뷰를 만들어보자. 기본적으로 SQL 서버 2005 베타2를 설치하면 AdventureWorks에 TransactionHistory라는 큰 테이블이 존재한다. 이를 먼저 분할된 뷰로 만들기 위해 다음과 같이 여러 개의 테이블로 나누고 각각 CHECK 제약 조간을 주고 인덱스를 생성해보자. 전체 코드는 ‘이달의 디스켓’에 있다.


이제 다 만들었으면 과연 잘 만들었는지 샘플 쿼리를 실행해보자.


이 결과를 보면 예상대로 다른 테이블은 읽기가 없고 해당 테이블에서만 읽기가 일어난 것을 볼 수 있을 것이다. 그럼 실제 실행 계획을 보자.


실행 계획을 보면 다른 테이블을 모두 본 다음에 필터로 걸러서 나중에 결합(concatenation) 연산을 이용하여 합치는 것을 볼 수 있다. 앞에서는 PK(Primary Key)가 없어서 그런 것이고, 만약 파티셔닝 컬럼이 PK라면 다른 테이블은 아예 보지도 않고 필터링도 없어 바로 데이터를 가져온다.

하지만 PK가 있는 테이블도 저장 프로시저를 이용하여 쿼리를 하면 어차피 현재와 똑같이 필터링하여 결합하므로 큰 차이는 없다고 봐도 된다. 읽기 수를 보더라도 다른 테이블은 필터링을 하므로 0이 나온다. 이제는 테이블 파티셔닝을 이용해보자. SQL 서버 2005 베타2에서 엔진 예시(Engine Example)를 설치하여 다음 폴더에 가보면 테이블 파티셔닝 예제가 있다.


이 예제를 실행시키면 TransactionHistory 테이블을 파티셔닝을 하는데, 2003년 10월 이전부터 2004년 8월 이후까지 12개의 파티션으로 나누어서 만든다. 다음은 주요 코드 중에 하나이다.


파티션 함수를 만드는데 있어 월별로 총 12개의 파티션으로 나누고 있다.


여기에서 만든 함수를 스키마를 이용하여 물리적인 공간에 맵핑하는 데 있어 하나의 파일 그룹에 맵핑하고 있다.


테이블을 생성할 때 앞에서 만든 스키마 위에 만들고 있다. 다 만들었으면 잘 만들었는지 예제 쿼리를 실행해보자.


테이블이 하나이므로 하나의 테이블에서 두 번의 스캔이 일어났다. 이제 실행 계획을 보자.


실행 계획을 보면 ‘Nested Loops Join’을 이용하여 해당 테이블을 ‘Index Seek’하여 데이터를 가져오고 있다. 이 쿼리에서는 두 달 치의 데이터를 읽으므로 두 번의 스캔이 일어남을 확인할 수 있다.


슬라이딩 윈도우 구현

파티션된 테이블을 관리하다 보면 오래된 데이터는 거의 사용을 하지 않게 된다. 어쩌다 한 번씩 통계 자료용으로 사용하는 경우가 대부분이다. 이러한 데이터를 계속 고성능의 I/O 장치에 담아 두는 것은 비효율적이다. 따라서 오래된 데이터는 더 이상의 트랜잭션이 일어나지 않으므로 비교적 낮은 성능의 저렴한 I/O 장치로 이식하는 것이 효율적이다.

이러한 과정을 ‘슬라이딩 윈도우(파티션 스위칭)’라고 한다. 슬라이딩 윈도우의 우리말 뜻은 ‘미닫이창’이다. 즉 밀어서 여닫는 창이라는 뜻인데, 오래된 데이터는 밀어서 내보내고 대신 새 데이터를 받아들인다는 의미로 보면 될 것이다.

슬라이딩 윈도우를 구현하는데 있어 대량의 데이터가 이동하므로 느릴 것이라고 생각 할 수 있으나, 실제로는 메타 데이터만 이동하므로 상당히 빠르게 작업할 수 있다. 다음 예제 역시 SQL 서버 2005를 설치한 다음 폴더에 가면 슬라이딩 윈도우 예제가 있다.


이번 예제에서는 2003년도 9월의 데이터를 TransactionHistory 테이블에서 떼어 내어 TransactionHistoryArchive 테이블로 옮기는 작업이다. 초기 상태는 <그림 2>와 같다.


TransactionHistory에는 12개의 파티션이 있고 TransactionHistoryArchive에는 2개의 파티션이 있다. TransactionHistoryArchive는 자주 사용하지 않는 데이터를 모아 두는 곳이므로 두 개의 파티션만을 만들었다. 여기에서 TransactionHistory 테이블에서 2004년도 9월 데이터를 위한 새로운 데이터를 위한 공간을 확보하자.



새로운 공간을 확보하기 위하여 기존 공간을 분할하여 총 13개의 파티션을 만들었다. 이제는 TransactionHistoryArchive에도 역시 새로운 공간을 확보하자.



각각의 테이블에 새로운 공간을 할당하였으니 이제 파티션을 옮겨 보자.



옮겼으면 이제 기존 파티션을 병합하여 초기 상태로 만들어줘야 한다. 먼저 TransactionHistory부터 병합하자. 병합하기 전에 sys.partition_range_values라는 테이블을 조회하여 파티션 정보를 조회해보자.


이제까지 제대로 작업을 했다면 총 14개의 행이 있을 것이다. 이제 다음과 같이 병합을 하자.



병합을 한 후 다시 테이블 파티션 정보를 보자.


병합을 했으므로 총 13개의 행이 생겼다. 이제 TransactionHistoryArchive도 병합을 하자.



이렇게 함으로써 슬라이딩 윈도우 작업을 완료할 수 있다. 작업이 간단하지는 않지만 이런 일련의 작업들이 실제로는 메타 데이터를 가지고 작업을 하기 때문에 상당히 빠르게 수행된다.



간편한 이력 관리를 위한 데이터베이스 스냅샷

SQL 서버 2005에서는 간단하게 데이터베이스에 대한 백업본을 만들 수 있다. 보통 개발자가 어떤 간단한 작업을 할 때 실수를 할까봐 트랜잭션을 걸고 작업을 종종 한다. 그러다가 실수를 하면 롤백하면 되기 때문이다. 그런데 이렇게 작업을 하면 잠금이 걸리기 때문에 다른 사용자들은 대기하고 기다려야 하는 불편이 있다. 하지만 이제는 데이터베이스 스냅샷을 사용하면 현재 데이터베이스의 내용을 간단하게 백업을 할 수 있기 때문에 트랜잭션을 걸지 않아도 된다. 만약 실수를 하게 되면 간단하게 복구를 할 수 있다.

스냅샷은 읽기만 할 수 있는 데이터베이스이다. 만들 때에는 실제 데이터의 복사본을 만드는 것이 아니고 메타 데이터만으로 만들기 때문에 상당히 빠르고 적은 용량으로 만들 수 있다. 실제 구현을 보면 먼저 스냅샷은 현재 데이터베이스와 동일한 저장 공간을 예약하고 원본 데이터베이스에서 변경이 일어나면 먼저 스냅샷 데이터베이스에 복사를 한 후 원본 데이터베이스를 변경한다. 이를 복사-쓰기(copy-on-write) 기술이라고 부른다.


<그림 9>를 보면 원본 데이터베이스의 2라는 값이 10으로 바뀔 때 먼저 2라는 값을 스냅샷 데이터베이스에 복사를 하고 자기 자신의 값을 10으로 바꾸고 있다. 스냅샷 데이터베이스는 결국 원본 데이터베이스에서 바뀌기 전의 상태 값만 가지고 있고, 나머지는 원본 데이터베이스를 참조한다. 그래서 생성 시간이 빠르고 공간도 적게 차지하는 것이다. 그럼 직접 실습을 해보자.



Test라는 데이터베이스를 만들고 Dummy라는 테이블을 만들어서 1,2,34라는 값을 넣고 Test_01이라는 Test 데이터베이스의 스냅샷을 만들었다. 앞에서 만든 Test_01.ss 라는 파일의 실제 크기를 보면 다음과 같다.


크기는 1.56MB를 할당했지만 실제 사용하는 크기는 128KB밖에 안 된다는 것을 확인할 수 있을 것이다. 이제 Test 테이블에서 2라는 값을 10으로 바꾸고 스냅샷에서 제대로 값을 보존하고 있는지 확인해보자.


스냅샷 테이블이 이전 값을 잘 간직하고 있음을 확인할 수 있을 것이다. 다시 Test_01.ss의 파일 크기를 보면 384KB로 그 크기가 커져 있는 것을 확인해 볼 수 있다. 즉 2라는 값을 저장하므로 그만큼의 공간이 늘어난 것이다. 이번에는 원본 데이터베이스를 복구해보자.


제대로 복구된 것을 확인할 수 있다.


멈추지 않는 시스템을 위한 DB 미러링

SQL 서버 2000에는 서버가 도중에 다운되더라도 다른 서버가 대신 작동하게 하는 기능으로 클러스터링을 이용하였다. 그러나 클러스터링을 구축하기 위해서는 공유 디스크와 같은 별도의 하드웨어가 필요했다. 또한 디스크 자체는 공유를 하므로 디스크가 깨지는 경우에는 좋은 해결책이 아니었다. 그리고 광케이블로 서로 연결해야 하므로 100마일이라는 거리의 제한도 있었다. SQL 서버 2005에서는 또 다른 해결책으로 미러링이라는 것을 지원한다. 미러링은 두 대의 SQL 서버를 운영하면서 서로 로그 정보를 주고받으면서 동일한 데이터를 유지한다.

따라서 별도의 공유 디스크가 필요 없으며, 디스크 자체가 깨지더라도 서로 디스크 복사본을 유지하기 때문에 문제가 안 된다. 또한 별도의 광케이블이 아닌 일반 네트워크 선을 사용하므로 거리 제한도 없다. 여기에 클러스터링은 서버에 문제가 생겨 교체되는데 있어 30초 이상의 시간이 걸리지만 미러링은 2～3초면 서버가 교체되어 자동으로 작동한다.

그렇다고 미러링이 클러스터링의 대안은 될 수 없다. 서로 장단점이 있기 때문이다. 미러링은 시스템 데이터베이스에는 사용하지 못한다. 단지 사용자 DB만 사용할 수 있다. 따라서 클러스터링은 전체 시스템을 보호하는 용도로 사용하고 미러링은 중요한 사용자 데이터베이스를 보호하는 용도로 사용하는 것이 적당할 것이다. <그림 10>은 미러링의 동작 방법이다.


미러링은 데이터 자체를 서로 전송하는 것이 아니라 로그만을 서로 전달한다. 애플리케이션으로부터 데이터 수정 작업이 들어오면 이를 먼저 로그에 기록한 다음 미러 서버에게도 그 로그 정보를 전달하여 미러 데이터베이스에도 동일 정보를 유지하도록 해준다. 이러한 동작은 감시 서버(witness server)가 계속 감시하고 있다가 만약 주 서버가 다운이 되면 바로 미러 서버를 주 서버로 바꾸어 동작하게 한다. 그동안 애플리케이션은 별도의 프로그램 수정 없이도 자동으로 미러 서버를 주 서버로 간주하여 접속을 유지한다. 그럼 직접 실습을 해보자.

원래는 제대로 된 실습을 위해서는 주 서버(Principal Server), 미러 서버(Mirror Server), 감시 서버(Witness Server) 이렇게 3대가 있어야만 하지만, 간단한 실습을 위하여 한 서버에 이 세 개의 서버를 인스턴스 이름만 달리하여 설치하면 테스트가 가능하다. 3개의 서버를 모두 설치한 후 다음 같이 종단점을 만들자.


종단점은 외부에서 이 서버에 접근할 수 있는 문을 열어주는 의미이다. TCP 프로토콜을 사용하여 5055포트를 열어 주었다. 마찬가지로 다른 미러 서버와 감시 서버도 종단점을 만든다. 단 이 때 한 서버에서 테스트를 하는 것이므로 서로 다른 포트 번호를 부여해줘야 한다. 미러 서버는 5056, 감시 서버는 5057 이런 식으로 다른 포트 번호를 부여하자. 그런 다음 앞서 스냅샷에서 실습한 Test DB를 주 서버에서 백업하여 미러 서버에 복구를 한다. 따라서 주 서버와 미러 서버는 동일한 Test DB를 가지게 된다. 그런 다음 미러링을 위한 파트너를 아래와 같이 미러 서버에서부터 작업을 한다.


마찬가지로 주 서버에서는 미러 서버와 감시 서버를 연결한다.


이제 주 서버에서 다음과 같은 데이터 작업을 해보자.


이제 미러 서버에서 동일한 데이터가 존재하는지 확인해 보자. 이 때 미러 서버는 항상 읽기 전용의 복구 모드로 동작을 하기 때문에 접근을 할 수가 없다. 접근을 하면 다음과 같은 에러 메시지가 나온다.


따라서 앞서 실습한 스냅샷을 이용하여 접근을 해야 한다. 미러 서버의 스냅샷을 만들고 Dummy 테이블을 조회해 보자.


값이 제대로 들어가 있는 것을 확인해 볼 수 있을 것이다. 이제 주 서버를 한 번 다운시켜 보자. SQL Computer Manager에서 주 서버의 동작을 멈춘다. 그러면 자동으로 미러 서버가 주 서버가 되고 기존의 주 서버는 미러 서버로 서로 스위치가 된다. 새로운 주 서버에서 Dummy 테이블을 조회해 보자. 이전까지는 스냅샷 없이는 조회가 안 되던 것이 이제는 잘 될 것이다.


정식 SQL 서버 2005를 기다리며

지난 4회 동안 SQL 서버 2005의 새로운 모습을 살펴보았다. 지금까지는 베타2를 기존으로 살펴보았고 앞으로는 베타3도 나와야 하고 정식버전도 나와야 한다. 앞으로 새로운 버전이 나오면서 또 어떻게 바뀔지는 모르겠다. 하지만 베타2 정도면 어느 정도 SQL 서버 2005에서 구현하려는 핵심 기능은 대부분 들어 있다고 봐도 될 것이다.

그 핵심 기능을 요약해 보면 개발자의 관점에서는 닷넷과의 통함이 될 수 있을 것이고, 관리자의 관점에서는 향상된 가용성(availability)이라고 할 수 있을 것이다. 이제 올 하반기에 나올 정식 SQL 서버 2005를 기다리면서 또 다른 항해를 준비하려고 한다.

출처 : 마이크로소프트웨어 2005년 5월호

SQL 서버 2005는 5년 만에 나온 제품인 만큼 엔진, 관리 툴, 보안에 많은 변화가 있다. 이번 글에서는 SQL 서버 2005 엔진의 새로운 변화, 그리고 대폭 바뀌고 개선된 관리 툴에 대한 소개와 향상된 보안 기능에 대해 알아볼 것이다.

앞서 두 번의 연재 동안 개발자 관점에서의 SQL 서버 2005의 모습을 살펴보았다. 이번 글부터는 관리자의 관점에서 바뀐 SQL 서버 2005의 새로운 모습을 소개할 것이다.

원래는 이 내용을 첫 회에 연재하려고 하였으나 지루할 것 같아 일단 당장 눈에 보이는 변화인 개발자 부문을 먼저 다뤘다. 여기에서는 SQL 서버 2005의 시스템에 대한 전반적인 부분부터 관리 툴에 대한 소개하고 보안 관련된 변화까지 알아볼 것이다.


4GB 메모리의 한계를 넘는 64비트 컴퓨팅 지원

현재 대부분 쓰이고 있는 32비트 프로세서는 기본적으로 메모리를 최대 4G(232)까지 지원한다. 그런데 DB 서버에서는 프로세서의 속도보다도 더 중요한 것이 바로 메모리 용량이다. 그래서 SQL 서버에서는 AWE(Address Windowing Extensions)를 이용하여 최대 32G까지 지원하고 있다.

AWE는 가상의 메모리 공간을 마련하여 실제 물리적 메모리와 맵핑하는 방식으로 4G 이상의 메모리에 접근한다. 하지만 이는 가상 메모리와 물리적 메모리 사이에 변환이 필요하므로 오버헤드를 유발하기 때문에 직접 접근하는 것보다는 느리다.

64비트 프로세서를 사용하게 되면 이런 제약은 없어진다. 현재 SQL 서버 2005는 인텔 아이태니엄/제온(EMT64), AMD 옵테론/애슬론64와 같은 64비트 프로세서를 지원하기 때문에 이들을 이용하면 현재 상태에서는 512GB까지 메모리 확장이 가능하다. 따라서 CPU를 64비트 프로세서로 바꾸기만 해도 성능 개선 효과를 볼 수 있을 것이다.

최근에 2001OUTLET에서 SQL 서버 2000을 32비트에서 64비트로 마이그레이션한 뒤 성능 향상에 대한 사례 발표를 한 적이 있다. 관심 있는 독자는 참고 사이트(참고자료)에서 확인해 볼 수 있다. 이 발표 내용 중 성능 향상에 대해 한 가지만 소개하면, 110GB의 테이블의 인덱스를 재생성하는 데 있어 기존에는 10시간 이상 걸리던 작업이 64비트 환경에서는 1시간 45분 만에 끝났다고 한다. 이러한 성능 향상에는 CPU를 교체한 것 이외에도 메모리, 스토리지를 업그레이드한 효과도 포함된 것이므로 단순 비교에는 무리가 있다.


효율적인 멀티프로세서 활용을 위한 NUMA 지원

일반적인 멀티프로세서 환경인 SMP(Symmetric MultiProcessing) 환경에서는 CPU와 메모리가 버스라는 통로를 통해 접근하므로 프로세서를 많이 달수록 버스 통로는 바빠지게 된다. 그러므로 프로세스를 많이 장착한다고 해서 반드시 프로세스를 정착한 개수만큼의 성능 개선 효과를 볼 수 없다.

그러나 NUMA(Non-Uniform Memory Access) 방식을 사용하면 이런 문제를 해결할 수 있다. NUMA는 윈도우 서버 2003에서 지원하는데, 이는 메모리와 CPU를 하나의 노드로 묶어서 전용의 로컬 메모리 공간을 확보하는 방식을 말한다. 따라서 각각의 노드들은 각각의 로컬 메모리를 가지고 있어서 로컬 메모리 내에서는 빠른 속도로 메모리 접근을 할 수 있다.
하지만 이 방식의 단점이라면 서로 다른 노드 사이에 메모리 접근을 하는 것은 외부 버스를 통해 접근해야 하므로 느릴 수밖에 없다. 그러므로 성능을 향상시키기 위한 핵심은 바로 이 노드들 사이의 메모리 접근을 줄이는 것이다. 그러기 위해서는 운영체제와 응용 프로그램간의 긴밀한 협조가 있어야만 한다. SQL 서버 2005는 이러한 NUMA를 적극 지원하여 크로스 노드 문제를 완화하고 있다.

하나로 두 개의 CPU 성능을 구현하는 하이퍼쓰레딩 지원

하이퍼쓰레딩(hyper-threading)을 지원하는 인텔 CPU의 경우 하나의 CPU로 마치 두 개의 CPU가 동작하는 것처럼 흉내 낼 수 있다. 이를 이용하면 멀티쓰레드 애플리케이션이나 멀티 애플리케이션을 수행할 때 성능이 개선된다고 알려져 있다. 이를 이용하면 금전적인 면에서 절약을 할 수 있다. SQL 서버 라이선스 1-CPU를 구매하고 하이퍼쓰레딩을 이용하여 마치 두 개의 CPU를 돌리는 것과 같은 흉내를 낼 수 있다. 하지만 리얼 2-CPU보다는 성능이 떨어지므로 그리 권장할 만한 방법은 아니다.


향상된 멀티플 인스턴스 지원

기존에는 최대 16개까지 인스턴스를 지원했지만, SQL 서버 2005에서는 최대 50개까지 인스턴스를 지원한다.


멈추지 않는 운영을 위한 데이터베이스 미러링

데이터베이스의 안정적인 운영을 위해 기존에는 대부분 클러스터링을 구현해 사용했다. 하지만 클러스터링은 데이터베이스 자체 내에서 지원되는 기능이 아닌 외부에서 지원되는 기능이다. 그래서 SQL 서버 2005에서는 자체 내에서 이러한 기능을 지원하기 위해 미러링이라는 기능을 추가했다. 미러링은 클러스터링과 다르게 별도의 하드웨어가 필요 없으며, 별도의 공유 스토리지도 필요 없다. 또한 길이 제한도 없어서 멀리 떨어진 곳에서도 설치가 가능하다.

이는 primary 서버와 mirroring 서버 두 대를 구축하여 서로 트랜잭션 로그 정보를 주고받기 때문에 가능한 것이다. 이 가운데 watch 서버가 추가되어 primary 서버의 동작을 감시하다가 primary 서버가 다운되면 즉시 mirroring 서버로 교체시켜주는 방식으로 동작한다. 자세한 내용은 다음 호에서 다룰 예정이다.


간편한 이력 관리를 위한 데이터베이스 스냅샷

데이터베이스를 운영하다 보면 특정 시점의 데이터를 저장하고 싶을 때가 있다. 백업을 이용하면 되지만 시간이 오래 걸리고 대용량의 저장 공간이 필요하다는 단점이 있다. SQL 서버 2005에서는 이런 불편을 해소하기 위하여 데이터베이스 스냅샷 기능을 지원한다. 이는 특정 시점의 데이터를 쉽게 보관하고 복구하는 기능을 제공한다. 이 때 실제 전체 데이터를 모두 보관하는 것이 아니라 메타 데이터만 보관하기 때문에 부담이 없다. 이 역시 자세한 내용은 다음 호에서 다룰 예정이다.


IIS 없이 HTTP 지원

SQL 서버 2005에서는 웹 서비스와 같은 HTTP 요청을 IIS 없이도 스스로 할 수 있는 기능을 제공한다. 따라서 웹과 연동된 프로그래밍을 할 때 더욱 쉽게 개발할 수 있게 되었다. 이 점은 비주얼 스튜디오 2005에서도 지원하는 기능이기도 하다. 비주얼 스튜디오 2005에서도 ASP.NET 프로그램을 개발하는 데 있어 더 이상 IIS가 없어도 가능하기 때문이다.


근무시간에도 가능한 인덱스 재생성

기존 SQL 서버 2000의 경우 인덱스를 재생성하게 되면 재생성하는 동안에는 데이터를 갱신하지 못했다. 그래서 인덱스를 다시 만드는 경우 대부분 야근을 하는 것이 보통이었다. 하지만 이제는 그러지 않아도 된다. 실시간으로 인덱스를 재생성하면서도 데이터 갱신 작업이 가능하다. 어떻게 이 기능이 가능할까? 그것은 바로 두 개의 인덱스를 SQL 서버가 유지하기 때문이다.

즉, 하나는 기존의 인덱스를 그대로 유지하면서 온라인 작업이 가능하게 하고, 다른 하나의 인덱스는 재생성 작업을 하는 데 이용한다. 그러다가 인덱스 재생성 작업이 끝나면 기존 인덱스는 삭제하고 재생성된 인덱스를 붙이는 방식이다. 그런데 이 방법에는 두 개의 인덱스를 유지하는 데 따른 오버헤드가 있다. 그러므로 사용자는 온라인/오프라인을 선택해서 인덱싱 작업을 할 수 있다.

또한 기존에 클러스터드 인덱스를 재생성하는 경우, 넌클러스터드 인덱스까지 같이 재생성되는 문제점이 있었다. 이는 넌클러스터드 인덱스가 클러스터드 인덱스를 참조하기 때문에 어쩔 수 없는 현상이었다. 그래서 클러스터드 인덱스 한 번 바꾸려면 시간이 많이 걸려서 대용량 테이블의 경우 만만한 작업이 아니었다. 하지만 이제는 클러스터드 인덱스를 재생성한다고 해서 넌클러스터드 인덱스까지 영향을 주지 않는다.

그럼 온라인 인덱싱 기능을 직접 시험해 보자. 다음은 adventureworks 데이터베이스의 SalesOrderDetail 테이블의 인덱스를 재생성하는 구문이다. 이 테이블이 12만행이나 되기 때문에 이러한 작업을 테스트하기에 안성맞춤이다.


이 결과를 보면 SalesOrderDetail 테이블의 인덱스를 재생성하는 데 있어 WITH 옵션에 ON을 주어서 온라인으로 하고 시간은 35초에서 43초까지 약 8초가 걸렸다. 이 작업을 돌리는 것과 동시에 다음 데이터 갱신 작업을 하자.


결과를 보면 39초에 갱신 작업이 끝났음을 알 수 있을 것이다. 인덱스를 재생성하는 동안에도 데이터 갱신 작업을 성공한 것이다. 그런데 만약 여기에서 ONLINE을 OFF로 했을 때의 시간은 얼마나 걸릴까? 실제 2～3초 밖에 걸리지 않는다. 즉, 인덱스를 두 개 만들지 않아도 되므로 그만큼 빠른 것이다.


온라인 복구 기능 지원

SQL 서버 2000에서는 데이터베이스가 복구되는 동안 사용자는 데이터베이스를 사용하지 못했다. 하지만 SQL 서버 2005에서는 부분 복구 기능을 지원한다. 한 예로 데이터베이스의 primary 파일 그룹이 복구되면 primary를 사용하는 데이터베이스는 사용이 가능하다. 나머지는 사용하면서 복구를 한다.


백업 미러링 지원

데이터를 백업할 때 하나의 테이프에만 백업을 했는데 만약 그 테이프에 오류가 생긴다면 난감할 수밖에 없다. 그럴 때에는 두 개의 테이프에 동시에 백업받는 것이 안전하다. SQL 서버 2005에서는 이러한 경우를 위해 백업 미러링을 지원한다. 즉, 테이프 1에 데이터를 백업하면서 동시에 테이프 2에도 백업을 하는 것이다. 그렇다고 시간이 두 배가 걸리는 것은 아니다. 미러링을 하기 때문에 더 추가하더라도 성능에 영향을 미치지 않는다. 단, 이 때 백업 장치는 동일한 장치이어야만 미러링이 가능하다. 다음은 미러링 백업 예제이다.



동시에 하는 데이터베이스 백업과 로그 백업

SQL 서버 2000에서의 로그 백업은 데이터베이스 백업이 끝난 후에나 가능했다. 하지만 SQL 서버 2005에서는 데이터베이스와 로그를 동시에 백업할 수 있다.


다운돼도 접속할 수 있는 관리자 전용 연결 기능

SQL 서버를 운영하다가 가끔 잘못되면 CPU 사용률이 거의 100%가 되는 경우가 발생할 수 있다. 이럴 경우에는 마우스도 움직이기 어렵다. 어떤 조치를 취하고 싶어도 마우스가 움직이지 않으니 어떻게 해 보지도 못하고 발만 동동 구르는 경우가 있다. SQL 서버 2005에서는 이런 경우, 관리자 전용 연결 기능(dedicated administrator connection) 기능을 이용하여 SQL 서버에 접속해 들어가서 문제를 해결할 수 있다. 이는 커맨드라인 유틸리티를 이용하는 것인데, 과거 OSQL을 대체하는 SQLCMD를 이용하면 된다. SQLCMD를 사용할 때 ‘-A’ 옵션을 주면 관리자 전용 연결로 들어 갈 수 있다. 명령 프롬프트에서 다음과 같이 실행해 보자.


이 예제는 로컬 SQL 서버(-S localhost)에 관리자 전용 연결(-A)을 신뢰된 연결(-E)로 접근하여 쿼리를 수행하는 모습이다.


익스체인지나 아웃룩이 필요 없는 메일링 기능

기존 SQLMail의 경우, 사용하려면 익스체인지와 아웃룩이 필요했다. 설치 또한 계정 문제가 얽혀 있어서 간단하지 않았다. 그래서 이번 SQL 서버 2005에서는 좀 더 편리한 SQLiMail을 지원한다. 이는 익스체인지나 아웃룩 없이도 SMTP 서버만 있으면 사용 가능한 메일링 기능이다. 이 기능은 현재는 기본적으로 설치되지 않고 관리자가 추가로 설치해야 한다. 방법은 두 가지가 있는데, 마법사를 이용하는 방법과 쿼리를 직접 이용해서 설치하는 방법이 있다. 쿼리를 이용하려면 다음과 같은 폴더에 스크립트가 있으니 이를 실행해서 설치하고 프로파일과 계정을 만들어서 연결시켜 주면 된다.


마법사를 이용하는 방법은 매니지먼트 스튜디오에서 매니지먼트에 부분에 보면 SQLiMail이라는 아이콘이 있다. 그 아이콘을 더블클릭하면 마법사가 실행된다.


사용 방법은 기존과 비슷하다.


이와 같이 받을 사람을 지정하고 메일을 보내면 된다.



시스템 트레이에서 사라진 SQL 서버 서비스 관리자

SQL 서버 2000에서는 서비스 관리자가 시스템 트레이 아이콘으로 있어서 거기에서 관리했다. 하지만 이는 다른 MS 제품 대부분이 MMC(Microsoft Management Console)를 이용하여 관리하는 것과는 차이점이 있었다. 그래서 MS는 그런 트레이 아이콘을 없애고 MMC에 포함시켰다. 이제는 MMC 내에서 서비스를 시작하고 중지할 수가 있다. [제어판]-[관리도구]-[컴퓨터관리]에 가보면 SQL 컴퓨터 매니저가 있다.


SQL 컴퓨터 매니저에서는 다음과 같은 서비스를 관리한다.

엔터프라이즈 관리자+쿼리 분석기 = SQL 서버 매니지먼트 스튜디오
맨 처음 SQL 서버 2005를 설치하면 쿼리 분석기를 찾지 못해 약간 당황할 수도 있다. SQL 서버 2005에서는 기존 DB 관리를 위한 엔터프라이즈 관리자와 스크립트 수행을 위한 쿼리 분석기가 SQL 서버 매니지먼트 스튜디오라는 이름으로 하나의 도구로 합쳐졌다.


<화면 4>를 보면 다양한 구성이 추가된 것을 볼 수 있다. 마치 비주얼 스튜디오를 연상하게 하는 구조처럼 변했다. 이 매니지먼트 스튜디오는 SQL 서버 2005 뿐만 아니라 SQL 서버 2000, SQL 서버 7까지 붙여서 관리할 수 있다. 이 매니지먼트 스튜디오의 가장 큰 변화라면 아마도 non-modal 기능일 것이다.

기존에는 EM(Enterprise Manager)에서 어떤 작업을 하기 위해서 창을 띄우면 그 창은 modal 창으로 떠서 그 작업이 다 끝날 때까지 기다려야만 했다. 하지만 매니지먼트 스튜디오에서는 non-modal 형식으로 창이 뜨기 때문에 동시에 다른 작업을 수행하는 것이 가능하다.

또 다른 변화로는 매니지먼트 스튜디오에서는 많은 수의 오브젝트를 다를 수 있다는 것이다. 기존 EM에서는 DB에 접속할 때 항상 모든 오브젝트를 한꺼번에 열거하기 때문에 오브젝트가 많을 경우에는 시간이 오래 걸렸다. 하지만 매니지먼트 스튜디오에서는 그 오브젝트를 브라우저에서 열기 전까지는 나열하지 않는다. 즉, 현재 필요한 정보만 읽어보고 필요에 따라 그때그때 정보를 읽어 오기 때문에 DB에 많은 오브젝트가 있더라도 접속하는 데 시간이 오래 걸리지 않는다.

<화면 4>를 보면 가운데 있는 것이 쿼리 편집기(query editor)이다. 쿼리 편집기가 기존 쿼리 분석기와는 달리 다수의 창을 열 경우 상단에 탭으로 표시된다. 기본에 별도의 창이 열려서 관리하기 불편했는데, 상단에 탭으로 표시되니 창을 관리하기가 쉬워졌다. 약간 불편한 점이라면 상단 탭의 제목이 너무 길어서 잘 보이지 않는다는 것이다. 이 쿼리 에디터에서는 T-SQL 뿐만 아니라 MDX, DMX, XMLA 등도 같이 실행이 가능하다.

<화면 4>의 우측에 보면 솔루션 탐색기(solution explorer)가 있는데, 이는 비주얼 스튜디오처럼 프로젝트를 관리할 수 있는 기능을 말한다. 다수의 SQL문을 하나의 프로젝트로 묶어서 관리가 가능하다. 또한 소스세이프도 지원하기 때문에 다수의 개발자가 동시 개발을 해도 소스 관리가 되며, 버전 컨트롤도 되기 때문에 앞으로 쿼리문 관리도 더욱 쉬워질 전망이다.

쿼리문을 이용해서 개발하다 보면 주로 반복되는 패턴들이 있다. 그래서 숙련된 개발자나 관리자들은 이러한 스크립트들을 별도로 모아서 관리하고 있다. 하지만 이제는 매니지먼트 스튜디오의 템플릿 탐색기(template explorer)와 보조 편집기(assisted editor)를 이용하면 이러한 반복되는 패턴들을 쉽게 이용할 수가 있다. 템플릿 탐색기는 자기만의 템플릿을 등록하거나 기존에 등록된 템플릿을 이용할 수 있으며, 보조 편집기는 SP, 트리거, 함수 같은 것들을 만들기 쉽게 도와주는 편집기이다.



튜닝의 조언자, 데이터베이스 튜닝 어드바이저

기존 인덱스 튜닝 마법사는 인덱스만을 튜닝하는 데 도움을 주었다. 하지만 튜닝 어드바이저는 인덱스뿐만 아니라 파티셔닝과 같은 전반적인 데이터베이스 튜닝에 대한 조언을 해준다. 먼저 프로필러로 해당 DB를 추적한 다음에 이를 trc 파일로 저장을 한다. 이를 튜닝 어드바이저에서 불러와서 튜닝을 하면 어떻게 하라는 권고 사항을 알려준다. <화면 7>의 예제를 보면, 튜닝 어드바이저가 해당 테이블의 현재 인덱스를 삭제하라고 조언하고 있다.



소유자와 사용자를 분리하는 스키마

SQL 서버 2000에서는 데이터베이스 오브젝트의 소유자가 사용자였다. 예를 들면 SQL 서버 2000에서 Northwind DB의 Products 테이블의 소유자는 dbo이다.

Northwind뿐만 아니라 아마 대부분의 테이블 소유자는 모두 dbo로 되어 있을 것이다. 그 이유는 테이블의 소유자를 어떤 한 사용자로 두었다가 만약 그 사용자를 교체해야 한다면, 모든 데이터베이스 오브젝트의 소유자를 다 바꿔줘야 하는 불편이 있기 때문이다. 이는 애플리케이션 프로그램의 변경에도 영향을 미치는데 애플리케이션에서 해당 오브젝트를 사용하는 코드를 기술할 때 대부분 소유자를 명시하기 때문이다. 예를 들어


이런 식으로 저장 프로시저를 호출해야 하기 때문에 소유자의 변경은 프로그램 전체를 다 변경해야 한다는 심각한 문제점이 발생한다. 그래서 대부분 그냥 소유자는 dbo로 통일해서 쓰는 경우가 많았다. SQL 서버 2005에서는 이러한 문제점을 개선하고자 스키마라는 개념을 확장했다. 데이터베이스의 오브젝트들을 묶어서 스키마라고 하고 사용자는 이 스키마를 소유할 수 있는 것이다.



그러므로 이제는 소유자가 바뀌더라도 해당 오브젝트들의 소유자를 모두 바꾸어 줄 필요가 없다. 단지 스키마의 소유자를 바꾸어 주면 되는 것이다. 직접 실습을 해보자. 먼저 3명의 로그인을 생성한다.


그 다음 각각의 로그인에 맞는 사용자를 생성한다.


이 때 UserA에만 기본 스키마로 Schema1이라는 것을 할당했다. 나머지는 명시를 하지 않았는데, 그러면 기본 스키마로 dbo가 할당된다. 이제 UserA에는 테이블 생성 권한을 주고, UserB에는 Schema1 스키마의 조회 권한을 주자.


Schema1 스키마의 소유자를 UserA로 정하자.


사용자 UserA로 변환한 다음 테이블을 생성한다.


사용자 UserB로 변환한 다음 조회를 해보자. 잘된다.


이제 Schema1의 소유자를 바꿔보자.


다시 UserB에 조회 권한을 주고 조회해 보면 잘된다. 즉, 스키마의 소유자가 변하더라도 다른 곳을 수정하지 않아도 되는 것이다.



끊어진 소유권 체인도 연결 가능?

SQL 서버 2000에서 테이블과 저장 프로시저의 소유자가 같은 경우에는 전혀 권한 체크를 하지 않는다. 예를 들어 Table1과 저장 프로시저 Proc1(Proc1에서 Table1을 참조)의 소유자가 UserC라면 누구든 Proc1을 실행할 수 있는 사람이면 비록 Table1에 권한이 없더라도 Proc1을 통해 실행이 가능하다. 이를 소유권 체인(ownership chain)이라고 부른다.

하지만 저장 프로시저와 테이블의 소유자가 다른 경우 권한 체크를 하게 되며 권한이 없을 경우 에러를 발생시킨다. 예를 들면 Table2의 소유자가 UserD이고 Proc2(Proc2에서 Table2를 참조)의 소유자가 UserB라면 UserA가 UserB에 실행 권한이 있다고 하더라도 테이블과 저장 프로시저간의 소유자가 다르므로 권한 체크를 한다. 그러므로 Table2에 대해 UserA가 권한이 없다면 에러를 발생시킨다. 이를 끊어진 소유권 체인(broken ownership chain)이라고 한다. 이를 해결하기 위해 SQL 서버 2005에서는 WITH　EXECUTE 구문을 제공한다.


ALTER PROC UserB.Proc2 WITH EXECUTE AS 'UserZ'

이와 같이 실행을 하면 UserB.Proc2는 마치 UserZ가 실행하는 것처럼 가장하게 된다. 따라서 UserZ가 Table2에 대해 권한만 있다면 이 구문은 실행이 잘된다.


데이터를 보호하기 위한 암호화 메커니즘 제공

만약 데이터 중에 사용자 패스워드가 있다면 대부분 암호화하여 저장할 것이다. SQL 서버 2005에서는 이를 위해 인증(certificate), 대칭키(symmetric keys), 비대칭키(asymmetric keys) 등 세 가지 방식의 암호화 메커니즘을 제공한다. 사용자는 이 세 가지 중 한 가지를 선택하여 데이터를 암호화하여 보호할 수 있다.


이 예제를 보면 인증 방식으로 암호화하는데 비밀번호는 123으로 했다. 암호화를 하니 그냥 조회해보면 알아볼 수 없는 값들이 나온다. 하지만 비밀번호를 이용하여 제대로 풀면 원래의 값을 조회할 수 있다.


SQL 서버 2005 관리자가 봐야 할 것들

이번에는 SQL 서버 2005의 관리자라면 한 번쯤 봐야할 만한 내용들을 전체적으로 알아보고, 추가로 보안에 대한 내용을 소개했다. 마지막인 다음 연재에서는 대용량 데이터를 다루기 위한 테이블 파티셔닝과 가용성(availability)을 높이기 위한 미러링과 스냅샷에 대해 소개할 예정이다.

출처 : 마이크로소프트웨어 2005년 4월호

SQL 서버 2005의 가장 큰 변화라고 한다면 아마도 닷넷 프레임웍과의 통합일 것이다. 이제는 쿼리문을 C#을 이용해서 개발할 수 있을 뿐만 아니라 C#을 통해서 T-SQL이 하지 못하는 기능을 마음껏 확장할 수도 있다. 이번 글에서는 CLR에 통합된 SQL 서버 2005의 새로운 모습을 살펴본다.

지난 글에서는 T-SQL의 새로운 모습에 대하여 살펴 보았다. T-SQL은 언어 자체가 집합적 언어이기 때문에 여전히 데이터를 조작하고 접근하는데 있어서는 닷넷 언어 보다 더 좋은 성능을 나타낸다. 하지만 T-SQL은 절차적 언어이기 때문에 객체지향적 프로그래밍을 할 수 없다는 단점이 있다.

그러나 닷넷을 이용하면 더 이상 이 문제로 고민하지 않아도 된다. C#, VB.NET, Managed C++를 이용해서 얼마든지 객체지향적 프로그래밍이 가능하다. 또한 복잡한 로직이나 계산, 외부 자원 연동, 코드 재사용등에 있어서는 T-SQL 보다 더 좋은 접근성과 성능을 보여준다. 한마디로 .NET 프레임웍과의 통합은 T-SQL을 교체하는 개념이 아니라, 더욱 확장하고 강화하기 위하여 도입된 것이라고 보면 된다.

SQL Server 2005가 .NET 프레임웍과 통합되면서 안정성이 대폭 향상되었다. 이전 SQL Server 2000에서 확장 저장 프로시저를 C++를 이용해서 작성을 하는 경우, 간혹 잘못된 코드로 인하여 SQL Server 전체가 다운되어 버리는 경우가 있었다. 그래서 확장 저장 프로시저를 매우 신중하게 만들어야 했으며 만드는 과정 자체도 간단하지가 않았다.

하지만 SQL Server 2005에서는 기본적으로 .NET 프레임웍의 호스팅 모델을 따라간다. SQL Server 2005와 각각의 .NET 코드로 만들어진 확장 저장프로시저는 서로의 독립성을 보장한다. 서로 메모리를 직접적으로 침범할 수 없으며, 서로의 실행 환경을 침해 할 수도 없다. 각각 별도로 운영된다는 것이다.

<그림1>을 보면 .NET 프레임웍의 호스팅 모델이 와 있다. SQL Server와 외부 어셈블리는 서로 다른 도메인을 가지고 있어 자신의 독립적인 실행 환경을 보호한다. 그래서 이제는 확장 저장 프로시저 때문에 더 이상 SQL Server가 다운되는 일은 없다.


SQL Server는 자기 자신만의 특별한 쓰레드 스케줄링, 동기화, 잠금, 메모리 할당 정책을 가지고 있다. SQL Server 자체가 워낙 메모리를 많이 사용하고 성능이 중요한 기업용 애플리케이션이기 때문에 보통의 CLR에서 제공하는 정책을 따르지 않고 자기 자신만의 특별한 방식을 적용해서 운영을 한다.

약 외부 어셈블리가 CPU나 메모리를 과도하게 많이 써서 SQL Server를 운영하는데 지장을 준다면, SQL Server는 이를 즉시 탐지해 내고 해당 사용권을 외부 어셈블리로부터 뺏어온다. 이렇게 함으로써 SQL Server는 더 이상 외부의 간섭에 영향을 받지 않고, 자기 자신을 스스로 지속적으로 안정적으로 운영할 수 있는 능력을 가지게 되었다.


간단한 사용자 정의 함수 만들기

먼저 간단한 사용자 정의 함수를 C#으로 만들어 볼 것이다. 복잡한 표현식이나 계산을 요하는 작업의 경우 C#으로 만드는 것이 더 효율적이므로 이번 예제에서는 우편번호를 체크하는 간단한 정규식 표현 함수를 만들어 보자.

먼저 VS.NET을 시작하고 새로운 프로젝트로 SQL Server Project를 선택한다. CLREx이라는 새로운 프로젝트를 만들고 AdventureWorks DB 서버에 연결한 후 새로운 아이템으로 IsValidZipCode라는 사용자 정의 함수를 추가한다.


그러면 <화면2>와 같은 템플릿 코드가 들어있다. 여기에서 주의해서 보아야 할 것은 함수 위에 있는 속성 [SqlFunction]이다. 이 속성은 아래의 함수가 SQL에서 사용하는 사용자 정의 함수임을 컴파일러에게 알려주는 지시자이다. 이제 기본 코드는 지우고 아래와 같이 코딩을 하자.



간단하게 해당 문자열이 우편번호식인지 검사하여 결과를 리턴해 주고 있다. 이제 이 코드를 컴파일하여 배포까지 하자. 그러면 자동으로 SQL Server에 이 어셈블리가 등록이 된다. 배포를 성공적으로 끝내면 아래와 같이 테스트를 해보자.


잘 작동하는 것을 볼 수 있을 것이다. 사용자 정의 함수를 만들어서 사용해 보았는데, 함수를 만들고 배포 하는 것이 간단하다는 것을 느꼈을 것이다. 그럼 SQL Server 내부에는 어떻게 등록이 되어 있는 것일까?


sys.assemblies라는 뷰를 보면 해당 CLREx 이라는 어셈블리가 등록되어 있는 것을 확인할 수 있을 것이다.


sys.assembly_files에는 실제 어셈블리의 내용이 들어있다. 즉, DLL 바이너리 자체를 SQL Server안에 등록 한 것이다. 그러므로 한번 어셈블리를 SQL Server안에 배포를 하면 해당 DLL 파일은 없어도 무방하다.

위에서는 배포를 VS.NET을 이용해서 자동으로 배포를 하였지만, 수동으로 배포하는 방법도 있다.


위와 같이 먼저 어셈블리를 등록을 하고 해당 함수를 만들어 주면 수동으로도 등록을 할 수 있다.


이제는 저장 프로시저를 C#으로 만든다고?

이번에는 저장 프로시저를 만들어 보자. 저장 프로시저를 만들려면 먼저 SQL문장을 실행해서 결과를 리턴해야 한다. 그러기 위해서는 어셈블리가 DB에 접속을 해서 SQL문장을 보내주어야 한다. 일반적으로 ADO.NET을 이용해서 DB에 접속을 하지만 기존의 연결 방법을 사용할 경우에는 외부에서 접속해 들어오는 것이므로 성능상에 문제가 있다.

따라서 내부 접속을 위한 별도의 Data Provider가 필요한데 그것이 바로 SQL Server Managed Provider이다. 이 프로바이더는 SQL Server 내에서 실행되므로 별도의 접속을 맺을 필요 없이 빠르게 수행을 한다. 따라서 open, close와 같은 절차가 필요없는 Data Provider이다. 사용 방법은 아래와 같이 선언을 하면 된다.


SQL Server Managed Provider에는 효과적인 작업을 위하여 몇가지 타입을 제공한다. SqlCommand, SqlPipe, SqlResultSet, SqlTransaction, SqlTriggerContext 와 같은 타입을 제공한다. 이중 대부분은 SqlClient에 있는 것과 동일하고 SqlPipe와 SqlTiggerContext가 이번에 새로 등장한 타입이다. SqlTiggerContext는 트리거 작성을 위한 타입이고 SqlPipe는 테이블과 같은 데이터를 호출 하는 쪽에 보내 줄때 사용하는 타입이다. 그러면 SqlResultSet과 뭐가 다르냐고 할 수도 있다.

SqlResultSet은 성능 문제로 인하여 사용을 권하지 않는 타입이고(이제는 없어질지도 모른다) SqlPipe가 성능상 더 좋은 타입이다. SqlPipe는 말 그대로 호출자에게 파이프로 물을 보내듯이 데이터를 받는 즉시 바로 보낸다. 성능면에서도 T-SQL의 저장 프로시저와 거의 비슷한 성능을 보여준다. 그러므로 앞으로 테이블 데이터를 리턴 받는 경우에는 SqlPipe를 써야 한다. 또한 .NET 저장 프로시저는 리턴값으로 int형과 void형만을 리턴 할 수 있으므로 어차피 SqlResultSet 형식으로 리턴하지도 못한다.

이번에는 직접 저장 프로시저를 만들어 보자. 이전에 만든 프로젝트에 저장프로시저를 하나 추가하고 아래와 같이 코딩을 한다.


이번 예제는 사용자 테이블에서 특정 부서의 사람들을 추출하는 저장프로시저이다. 이를 컴파일 하고 배포한 다음 아래와 같은 SQL문장으로 테스트 해 보면 결과를 볼 수 있을 것이다.



사용자 정의 데이터 타입을 이용하여 나만의 데이터 타입을 만들자

SQL Server에는 기본적으로 CHAR, INT와 같은 기본 데이터 타입을 지원한다. 여기에 더 확장을 하여 우리가 원하는 데이터 타입을 스스로 만들어서 추가할 수도 있다. 예를 들면, 위도, 경도, 포인트를 나타내는 데이터 타입이라든지 이메일 주소를 나타내는 데이터 타입을 새로 만들어서 추가할 수 있다. 포인트를 보면 10:30 과 같은 표현식을 수용하는 하나의 칼럼을 만들 수도 있다. 그런데 사실 이러한 표현은 기존의 칼럼을 두 개로 나누어서 x, y 좌표 값을 저장해도 된다. 구지 사용자 정의 데이터 타입(User-Defined Data Types)(UDT)을 안 만들어도 할 수는 있다.

하지만, 의미상 하나로 표현하는 것이 더 타당하고, 그 자료형과 관련된 많은 메서드나 행위가 필요할 때에는 하나의 데이터형으로 만드는 것이 바람직하다. 예를 들면 날짜 같은 데이터 타입을 년,월,일로 나누어서 3개의 칼럼에 저장하는 것 보다는 년월일 하나로 만들어서 하나의 칼럼에 저장하는 것이 더 의미상 더 타당하다는 것은 누구나 알고 있다.

또한 날짜와 관련된 많은 메서드와 제약사항들이 있기 때문에 이를 3개의 칼럼으로 나누어서 처리하는 것은 많은 불필요한 코드들을 필요로 한다. 예를 들면 월에 1월을 더하거나 빼는 연산과 같은 것들을 하나의 데이터 타입에 같이 넣어 두면 어디서나 손쉽게 끌어다 쓸 수 있다. SQL Server의 UDT는 데이터 자체뿐만 아니라 메서드도 같이 포함 할 수 있으므로 (사실 UDT는 클래스나 구조체로 정의한다) 이러한 구현이 가능하다.

자 그럼 여기서 이런 생각까지 하는 사람이 있을 수도 있다. “UDT를 클래스의 개념으로 볼 수 있으니, 이제는 객체를 그대로 DB에 저장 할 수 있다는 얘기군. 그럼 아예 사원(Employee) 객체를 통째로 DB에 저장해 볼까?” 여기까지 생각을 하면 “그동안 미들티어에서 했던 OR매핑(Object Relational Mapping)이 더 이상 필요 없는 진정한 객체지향의 DB가 탄생했군!” 이라고 생각하는 사람이 있을 수도 있다.

틀린 얘기는 아니다. 하지만 성능과 용량이 문제가 된다. UDT는 8KB라는 사이즈 제한이 있고, 인덱싱 처리의 제약, 그리고 데이터 업데이트시 부하가 있다. 그러므로 UDT는 그러한 복잡한 객체를 저장하는 데에는 적절하지 않다. 처음에 예를 들었던, 위도, 경도, 포인트와 같이 가벼운 객체를 저장할 때에만 이 UDT를 사용해야 한다.

UDT는 결국 클래스를 하드디스크에 저장하는 것이기 때문에 직렬화를 해야 한다. 직렬화를 위해서는 데이터의 크기가 중요하다. 기본적으로 .NET 환경에서는 값타입(Value Type)과 참조타입(Refernce Type)이라는 두 가지 타입이 있다. 값타입은 int, char과 같이 실제 데이터가 직접 있는 타입이고, 참조 타입은 string과 같이 실제 데이터가 아닌 데이터의 주소가 들어있는 타입을 말한다. 따라서 이들 데이터 타입에 따라 저장하는 방법도 달라진다.

값타입은 대부분 고정된 길이를 가지고 있으므로 컴파일러가 알아서 그 크기를 계산할 수 있지만, 참조 타입의 경우 그 크기가 얼마나 될지 모른다. 그래서 하드디스크에 얼마 정도의 공간을 할당해야 하는지 모르는 것이다. 그래서 참조 타입을 직렬화 하는 경우에는 사용자가 직접 그 방법을 정의를 해줘야 한다. 직렬화 방법을 정리해 보면 아래와 같이 3가지 방법이 있다.

◆ SerializedDataWithMetadata
값타입이나 참조 타입에 관계없이 어떤 데이터 타입도 저장 가능. 하지만 성능 면에서는 가장 느리다. 아마 Beta3에서는 없어질 포맷이다. 한마디로 사용하면 안 되는 포맷이다.
◆ Native
크기가 고정된 값타입의 데이터 형만 저장 가능. 가장 빠르다.
◆ UserDefined
값타입, 참조 타입 모두 사용가능. 하지만, 사용자가 데이터를 읽는 방법과 쓰는 방법을 정의해 주어야 한다.

위 3가지 포맷중 사용자 정의 포맷에서 읽기와 쓰기를 직접 구현하는 것이 간단하지가 않다. 약간 복잡하다. 그래서 이번 예제는 UDT를 소개하는 것이 목적이므로 Native 포맷을 이용하는 간단한 포인트 예제를 보여주려고 한다.

기존 CLREx프로젝트에 새로운 아이템으로 Point라는 사용자 정의 데이터 타입을 추가해 보자. 그러면 기본적인 코드들이 생성되어 있을 것이다. 모두 지우자. 현재 템플릿에서 생성된 코드는 옛날 방식의 코드이다. 기본적인 구조는 아래와 같다.


이 메서드들을 채워주면 포인트 UDT가 완성 된다. 위의 가상코드를 보면 직렬화를 지원하고 Native 포맷으로 정의되어 있는 것을 볼 수 있을 것이다. 그리고 class가 아닌 struct로 선언한 것이 보일 것이다. 구지 class가 아닌 struct를 쓴 이유는 전통적으로 사용자 정의 데이터 타입은 구조체를 썼기 때문이다. 그 이유는 클래스는 힙에 데이터가 저장이 되지만 구조체는 그렇지가 않다. 따라서 클래스는 가비지 콜렉터가 쉽게 수거해 갈 수 있지만, 구조체는 그렇지 않다.

성능면에서 구조체가 약간 더 빠르다는 것이다. 또한 NULL값을 구현하는데 있어 구조체는 별도의 초기화 없이 기본적으로 모든 값을 기본값으로 초기화를 해준다. 예를 들면 숫자형은 모두 0으로 자동 초기화를 해준다. 그래서 데이터 형을 다루는 데에는 아무래도 클래스 보다는 구조체가 약간 더 편하다고 할 수 있다. SQL Server에서는 NULL이라는 값이 존재한다. 따라서 UDT를 만들때에는 NULL이라는 의미를 부여해 주어야 한다. 그래서 INullable 인터페이스를 상속받아서 NULL을 구현하고 있다.

포인트를 저장하기 위해서 X,Y값을 위한 공간을 마련하고 널값 체크를 위한 공간도 마련하였다. 그런데 사실 널값 체크를 위해서 위와 같이 별도의 저장 공간을 사용하는 것은 하드디스크 낭비가 될 수 있다. 그래서 어떤 사람들은 위와 같은 경우 Int32.MinValue를 널값 대신으로 사용하기도 한다. 즉 Int32의 최소값을 널값으로 대신 하는 것이다. 만약 포인트의 데이터형이 string형이면 이러한 불편이 없다. string형은 참조 타입이기 때문에 null이라는 값을 수용할 수 있기 때문이다.

Int32라는 데이터형은 값타입이기 때문에 NULL을 수용할 수가 없어 위와 같은 방법을 사용하였다. 어떤 방법을 사용하던 그것은 개발자의 몫이니 상황에 따라 적절한 방법을 사용하면 된다. 이번 예제에서는 하드디스크의 공간을 걱정 안해도 되므로 그냥 따로 널값 체크를 위한 데이터형을 따로 만들었다. 기본적인 메서드의 설명은 아래와 같다.


실제 완성된 코드는 지면관계상 이달의 디스켓에 있으니 참고하기 바란다.
이제 이 UDT를 컴파일 하고 배포 하면 아래와 같이 테스트 할 수 있다.



SUM, MAX와 같은 집합 함수만으로는 더 이상 충분하지 않다.

이번에 SQL Server의 CLR 통합 기능 중에서 제일 반가운 기능이 바로 이 기능이다. 기존에 MIN, MAX, SUM, COUNT, AVG 같은 집합 함수를 쓰다 보면 부족함을 느끼는 경우가 많다. 이러한 집합 함수가 있으면 좋은데... 하고 많은 사람들이 원했던 것이 사실이다. 이제는 이러한 집합 함수를 직접 만들어 쓸 수 있다. 만드는 방법은 UDT와 상당히 유사하다. 이번 예제에서는 최대 변이값을 구하는 함수를 만들 것이다. 즉, 최대값-최소값을 구하는 MaxVariance라는 함수 이다. 기존 프로젝트에 새로운 아이템으로 Aggregate를 추가하고 이미 있는 템플릿 코드는 역시 옛날 방식이므로 지운다. 기본적인 구조는 아래와 같다.


데이터형이 값타입 밖에 없으므로 Native 포맷으로 했으며, 최대값과 최소값을 저장하는 별도의 변수를 만들었다. 각 메소드별 설명은 아래와 같다.


자세한 코드는 이달의 디스켓에 있으니 참고하기 바란다. 위의 사용자 정의 집합(User-Defined Aggregate)(UDA)을 컴파일 하고 배포한 후 아래와 같은 코드로 테스트 해 보자. 아래 코드는 전체 사원중에서 휴가시간이 가장 많은 사람과 가장 적은 사람의 차이를 나타낸 것이다.


위와 아래의 쿼리문을 대조해 보면 제대로 된 결과가 나왔음을 확인해 볼 수 있다.


클라이언트 ADO.NET의 개선점

이번에 ADO.NET 2.0으로 나오면서 SQL Server와 관련해서 크게 주목할 부분은 두가지가 있다. 하나는 비동기 호출기능과 하나의 연결로 다수의 커맨드를 실행하는 기능(Multiple Active Result Sets)(MARS)이다. 지난호에서 ADO.NET에서도 페이징 처리가 가능하다고 했는데, 그 기능이 이젠 없어질 예정이라서 이번에 제외했다.


더 이상 기다릴 필요 없는 비동기 호출

비동기 호출 기능은 기존에 쿼리 문장을 수행 시키고 결과가 올 때까지 기다려야 했단 불편을 없애고, 클라이언트는 결과가 올 때까지 나름대로의 작업을 할 수 있다. 그러므로 사용자는 쿼리 문장을 날리고 모레시계의 아이콘을 기다릴 필요 없이 다른 작업을 수행 할 수도 있다. 이때 처음 DB에 연결을 맺을 때 비동기 호출을 쓴다는 표시를 “Asynchronous Processing=true” 이와 같이 해주어야 한다. 간단한 예제를 보자. 전체 예제는 이달의 디스켓에 있다.


위 예제는 고객 데이터를 조회 하는데 있어 비동기 호출을 이용하고 있다. 먼저 비동기 호출의 장점을 보려면 DB에서 시간이 오래 걸리는 작업을 돌려주어야 그 효과를 확실히 볼 수 있다. 그래서 2초간 딜레이를 주는 문장을 삽입하여 강제로 시간이 오래 걸리도록 하였다. 그리고 클라이언트는 결과가 올 때까지 계속 별을 찍다가 결과가 오면 끝내는 예제이다.

그런데 이번 예제에서는 간단히 하기 위해서 끝났는지 안 끝났는지를 알아보기 위하여 WHILE문에서 계속 체크를 하였지만, 실제 사용할 때에는 이렇게 할 필요 없이 비동기 콜백 함수를 만들어서 다 끝나면 저절로 그 함수가 호출되게 하는 것이 더 좋은 방법이 될 것이다.


하나의 연결로 다수의 쿼리 실행

기존 ADO.NET에서는 하나의 연결을 맺으면 하나의 커맨드만 실행가능 하였다. 그래서 다른 커맨드를 실행하려면 별도의 연결을 다시 맺어야만 했다. 하지만 이제는 하나의 연결로 다수의 커맨드를 실행 할 수 있다. 이렇게 함으로써 매번 새로운 연결을 안 맺어도 되므로 성능 향상이 있는 것이다. 구현하는 방법은 어렵지 않다. 그냥 쓰면 된다. 아래 예제를 보자.


cmd1과 cmd2가 하나의 cnn이라는 연결을 공유해서 쓰고 있다. 전체 예제는 이달의 디스켓에 있다.


SQL Server의 변신은 무죄?

처음에 SQL Server가 닷넷 프레임웍(CLR)에 통합된다고 하였을 때 많은 사람들이 궁금증을 가지고 지켜보았다. 이제는 C#을 공부해야 하는가 하고 걱정하는 사람들도 있었다. 하지만 막상 뚜껑을 열어보니 CLR통합 이라는 기능은 T-SQL을 대체하는 기능이 아닌 좀더 확장하고 보강하기 위한 기능으로 보는 것이 좋다는 결과가 나왔다. SQL Server를 개발하는데 있어 기본은 T-SQL이다. 하지만 거기서 멈추지 않고 더욱 새로운 기능을 추가하고 확장하고 싶다면 .NET을 이용하면 된다. 다음 글에서는 DB 관리툴과 보안에 대해 소개할 것이다.

출처 : 마이크로소프트웨어 2005년 3월호

그동안 유콘(Yucon)이라는 코드명으로 불렸던 차기 SQL 서버가 드디어 명칭을 SQL 서버 2005로 확정을 지으면서 올 하반기 출시를 앞두고 있다. SQL 서버 2005에 대한 전체적인 소개 글이 게재된 바 있지만 이번에는 실제 예를 통해 과연 SQL 서버 2005에서는 어떤 변화가 있는지 살펴본다.

SQL 서버 2005가 서서히 모습을 드러내고 있다. 현재 베타 2까지 발표된 상태이며, 올 하반기에 정식 버전이 나올 예정이다. SQL 서버 2005 출시 소식이 전해지면서 심지어 “지금까지의 SQL 서버는 다 잊어라”, “T-SQL을 쓰지 않고 닷넷으로 전부 통합되기 때문에 처음부터 다시 배워야 한다”는 이야기까지 있었다. 하지만 SQL 서버 2005가 실체를 드러내면서 그러한 소문은 사실이 아니었음이 드러났다.

우선 닷넷에서는 SQL 서버의 저장 프로시저, 사용자 정의 데이터 타입, 그룹 함수, 사용자 정의 함수, 트리거 등을 만들 수 있다. 하지만 닷넷의 C#, VB.NET은 객체지향적 언어이지만, 집합적 언어는 아니다. 그래서 닷넷 언어를 이용하면 테이블의 행마다 어떤 일을 처리해 주어야 한다. 그러나 SQL문은 언어 자체가 집합적이므로, SQL문을 이용하면 테이블 전체를 핸들링할 수 있고, 전체를 핸들링하는 것이 속도면에서도 더 빠르다.

결국 닷넷은 T-SQL의 기능을 확장하기 위한 도구이지 T-SQL을 대체할 수는 없다. 대부분의 DML 구문(SELECT, INSERT 등)에서는 T-SQL이 닷넷보다 빠르다. 단 닷넷으로 작성하면 좋은 경우가 있는데, 그것은 CPU 작업을 많이 하는 작업(수학적 계산, 암호화 계산 등)이나, T-SQL로는 할 수 없는 시스템 외부와의 연동 작업 등이다. 이제 T-SQL의 변화된 모습을 직접 확인해 보자.



TOP 구문의 개선

SQL 서버 2000에서는 TOP 문장에 변수를 쓸 수 없었다. 굳이 쓰려고 한다면, 동적 SQL문을 이용하여 EXEC 구문으로 수행하는 방법이 있었다. 하지만 이 방법은 많이 불편해 왔던 것이 사실이다. SQL문 자체를 매번 동적 SQL 구문으로 만드는 것은 디버깅을 어렵게 하고, 가독성을 낮추는 결과를 초래하기 때문이다. SQL 서버 2005에서는 이런 문제를 해결하여 변수 사용이 가능하다. 다음 예제를 보자.


여기서 한 가지 주의할 것은 ()이다. 상수일 때에는 없어도 무방하지만(SQL 서버 2000과의 호환성을 위해), 상수가 아니거나 데이터를 변경하는 구문에서는 반드시 괄호를 써주어야 한다. 괄호 안에 변수 뿐만 아니라 표현식이 들어올 수 있다. SQL문도 좋고, 함수도 좋다. 다음 예제는 부서의 개수만큼 사원의 정보를 읽어오는 예제이다. 부서가 총 16개 있으므로 16명의 사원 정보를 읽어 왔다.


TOP문의 또 다른 변화는 INSERT, DELETE, UPDATE와 같은 DML 구문에서도 쓸 수 있다는 것이다. 다음 예제는 첫 번째에 있는 사원의 성별을 바꾸는 구문이다.



새로운 순위 함수

일반적으로 페이징 처리를 할 때에는 두 가지 방법이 있다. 먼저 클라이언트 쪽에서 모든 데이터를 읽어서 페이징 처리를 하는 방법이 있고, DB 쪽에서 페이징 처리를 해서 해당 페이지만을 읽어오는 방법이 있다. 첫 번째 방법은 모든 데이터를 읽어야 하므로, 데이터가 많을 때에는 사용하지 못한다. 두 번째 방법 또한 DB 쪽에서 처리를 하려면 동적 SQL문을 이용하여 복잡한 쿼리문을 작성해야 하므로 쉽게 구현하기가 힘들었다. 이러한 불편을 개선하기 위해 SQL 서버 2005에서는 두 가지 해결책을 내 놓았다.

첫 번째는 ADO.NET에서 페이징 처리를 하는 것이고, 두 번째는 T-SQL문을 이용하는 것이다. ADO.NET에서 하는 방법은 다음에 다룰 것이고, 이번 연재에서는 T-SQL문에서 하는 방법에 대해 알아보자. 다음 예제는 사원을 생년월일 순으로 정렬한 예제이다.


이제는 Row_Number()라는 함수를 이용하면 행 번호를 출력할 수 있다. 과거 SQL 서버 2000에서는 행 번호를 출력하는 함수가 없어 무척 불편했는데, 이제는 편하게 행 번호를 출력할 수 있다. 그런데 여기서 한 가지 주의할 것이 있다. 앞에서 OVER 다음에 오는 ORDER BY 문장은 행 번호를 어떤 순서로 매길 것인지를 정하는 구문이다. 만약 이 문장 FROM 절 다음에 또 다른 ORDER BY절이 온다면, 이는 행 번호를 다 매긴 후에 ORDER BY 구문에 의해서 행을 정렬하라는 의미가 된다.

따라서 FROM 이후에 ORDER BY EmpolyeeID라는 문장을 넣어 준다면, RowNum는 EmployeeID에 의해 정렬이 되므로 흐트러지게 된다. 직접 해보면 이해가 빠를 것이다. 지면 관계상 예제는 '이달의 디스켓'으로 대신한다. 이제 행 번호를 이용하여 페이징 처리를 해보자.


간단하게 페이징 처리하는 것을 볼 수 있을 것이다. 여기서 한 가지 단점이 있는데, Row_Number()라는 함수는 WHERE절 이후에 판단한다는 것이다. 즉 WHERE에 의해 SELECT를 범위를 정한 후에 행 번호를 구할 수 있다. 따라서 WHERE절에는 Row_Number()라는 함수를 쓸 수가 없다. 그러므로 이와 같이 전체를 읽은 후에 페이징 처리를 해야 하는 단점이 있다.

그밖에 새로운 순위 함수로는 Rank(), Dense_Rank(), NTile() 등이 있다. Rank는 같은 순위가 있을 경우 다음에는 그 만큼 순위를 건너뛰는 것이고, DenseRank는 순위를 건너뛰지 않는다. NTile은 전체를 NTile의 개수로 나눈 후 공평하게 순위를 배정하는 방법이다. 자세한 예제는 ‘이달의 디스켓’을 참고하기 바란다.


데이터를 조작할 때 유용한 OUTPUT 구문

SQL로 프로그래밍을 하다보면 삽입, 삭제, 업데이트시 이들 연산에 의해 일어난 결과 값을 알고 싶을 때가 있다. 예를 들면 데이터를 삭제할 때 무슨 데이터를 삭제했는지 알고 싶을 때가 있을 것이다. 이때에는 보통 삭제하기 전에 삭제할 값을 다른 테이블에 따로 저장해 두고, 삭제한 다음에 다른 테이블에 잠시 넣어둔 데이터를 조회함으로써 그 결과 값을 알 수 있었다. 이제는 이렇게 하지 않고도, 자신이 삭제한 값을 바로 알 수가 있다.

삽입의 경우도 마찬가지이다. IDENTITY 컬럼이 있는 테이블의 경우, 데이터를 삽입을 한 후 다시 그 테이블을 조회해야만 자신이 삽입한 행의 IDENTITY 값을 알 수가 있었다. 이제는 삽입을 할 때 바로 알 수가 있다. 다음 예제를 보자.


첫 번째는 삽입을 할 때 OUTPUT 구문을 이용하여 그 삽입한 값을 받아왔고, 두 번째는 삭제를 할 때 삭제한 값을 OUTPUT 구문을 이용하여 받아왔다. 이를 이용하면 데이터를 조작하는 구문에서 쉽게 결과 값을 받아 올 수 있다.


CTE의 재귀 기능

테이블을 디자인하다 보면, 간혹 자기 자신을 참조하는 테이블을 디자인하는 경우가 있다. 예를 들면 사원 테이블의 경우 관리자 또한 사원이기 때문에 그 안에 포함하여 디자인하는 경우가 있다. 한 예로 AdventureWorks의 Employee 테이블을 보면 다음과 같이 사원과 관리자의 컬럼이 있다.


109의 관리자는 없으므로 사장이 될 것이고, 4번의 관리자는 3번이다. 이런 식으로 이루어진 테이블의 값을 조회하는 데 있어 3번의 부하 직원을 모두 조회하는 경우를 보자.


3번은 158번을 관리하고 있고 79번은 158의 관리를 받으므로, 결국 3번의 관리를 받는 직원으로 간주할 수 있다. 그런데 이러한 쿼리는 몇 가지 문제점을 가지고 있다. 조직의 깊이가 어느 레벨까지 내려갈지도 모르는 것이고, 조직의 변동에 따라 JOIN문을 추가해야 하므로 소스코드의 수정이 있어야 한다. 또한 불필요한 NULL 정보를 리턴하고 있어 정보의 전달 과정 또한 매끄럽지 못하다. 이를 좀 더 유연성있고 쉽게 표현해 보자. 다음은 SQL 서버 2005에서 가능한 구문이다.


좀 더 유연성 있는 결과가 나왔다. 조직이 어떻게 변하든, 레벨이 얼마다 더 깊어지든 상관없이 소스코드를 고치지 않고서도 좋은 결과를 낼 수 있다. 이 구문은 CTE(Common Table Expression)라는 SQL 서버 2005에서 새로 소개된 기능을 사용한 것이다. 그런데 그중에서도 재귀 CTE 구문을 이용한 것이다. 사실 CTE라는 것은 Derived Table, 뷰, 임시 테이블 등 어떤 것으로도 대체할 수 있는 구문이다. 그러므로 이와 같이 재귀 구문으로 쓰지 않는 한 새로운 점이 없는 구문이다. 사실 마이크로소프트에서도 이 재귀 기능 때문에 CTE라는 구문을 도입한 것이다.

CTE는 일종의 임시적인 가상 뷰로 보면 된다. 왜 임시적이냐 하면 CTE는 DML 구문(예, SELECT)과 같은 구문에 붙여서 사용하기 때문이다. 단독으로는 쓸 수 없다. CTE는 정의할 때 생기는 것이 아니라 실제로 구현할 때 그 구문이 실행되는 구문이다. 앞에서 보면 EmpCTE라는 것을 정의하고 그 밑에 있는 SELECT문에서 사용하고 있다.

앞의 CTE 구문의 보면 UNION ALL 구문을 기준으로 상단의 Anchor 멤버와 하단의 recursive 멤버로 나눌 수 있다. 상단 구문은 재귀 호출의 기준이 되는 구문으로 재귀 호출되는 구문이 없는 표현이 있어야 한다. 그래서 이 구문에서는 사장은 관리자가 없으므로 사장을 조회하도록 하였다. 하단의 재귀 구문에서는 자기 자신을 참조하여 재귀 구문을 수행하는 부분이므로 사원-관리자 관계를 조인하고 있다. 이제 이를 이용하면 재귀 구문도 쉽게 구현할 수 있을 것이다.


CASE문을 대체하는 PIVOT과 UNPIVOT

SQL 서버 2000에서 관계형 데이터에 행별로 저장된 값을, 가로 테이블로 된 형식으로 보기 위해서는 CASE문을 써야만 그렇게 볼 수 있었다. 하지만 SQL 서버 2005에서는 PIVOT 연산자를 이용하여 간단히 구현할 수 있다. 자세한 내용은 본지 12월호에 소개되었기 때문에 예제만 보도록 하자. 한 예로 년도별 판매사원의 매출을 구하는 예제를 보도록 하자. 다음은 SQL 서버 2000 방식으로 구현한 예제이다.


이번에는 2005 방식으로 구현한 예이다.



대용량 데이터 타입

SQL 서버 2005에서는 기존의 대용량 데이터를 다루는 데 사용했던 text, ntext, image 데이터 타입 대신에 새로운 데이터 타입을 소개하고 있다(사실 앞의 3가지 데이터 타입은 이제 사라질 예정이라고 한다). 그것은 text->varchar(max), ntext->nvarchar(max), image->varbinary(max)이다. 이들은 최대 2GB까지 데이터를 저장할 수 있다. 이들 데이터 타입은 마치 문자열 데이터를 다루듯이 다룰 수 있기 때문에 대부분의 문자열 함수를 지원한다. 또한 이전에는 이러한 대용량 데이터를 처리하기 위해서는 별도의 구문이 필요했지만 이제는 그냥 보통 데이터 타입을 쓰듯이 그대로 쓸 수 있다. 다음 예는 varchar(max) 데이터 타입을 선언한 예이다.



XML 데이터 타입

SQL 서버 2000에서는 XML 데이터는 SQL 서버 엔진 안에 속하지 못하고 변방에서 맴돌았다. SQL 서버 2000에서는 XML 데이터를 마치 문자열 데이터처럼 다뤘기 때문에 별도의 함수나 구문을 사용해야만 했다. 하지만 SQL 서버 2005에서는 XML도 당당히 기본 데이터 타입으로 자리잡고 있다.

XML 데이터 타입에는 크게 두 가지 형태가 있다. typed와 untyped이다. type는 well formed XML 형식을 지원한다. XSD로 만든 스키마를 연결해 주면 스키마에 맞는 XML 데이터만 저장할 수 있다. 반면 untyped는 이러한 스키마 없이 그냥 생성한 XML 데이터 타입을 말한다. 아무래도 typed XML 데이터 타입을 쓰는 것이 속도나 기능면에서 여러 가지로 유리한 점이 있다. 다음 구문은 스키마를 정의하는 구문이다.


스키마는 비주얼 스튜디오에서 만든 후 쿼리문에 붙여 넣었다. 앞를 보면 ID, Name이라는 두 가지 요소가 있고, ID는 INT형, Name은 문자형으로 정의하고 있다. 이제 이 스키마를 이용하는 테이블을 만들어 보자.


간단히 데이터 타입을 선언하고 스키마도 같이 기술해 주고 있다. 여기에 한번 데이터를 삽입해 보자.


결과를 보면 처음 두 데이터는 잘 들어갔는데, 마지막 데이터는 오류를 내고 들어가지 못했다. 이유는 스키마 규칙을 어겼기 때문이다. ID에 문자열이 있기 때문에 에러가 나면서 데이터 삽입을 거부하고 있다. 이제 결과를 조회해 보자.


예상대로 2행만 삽입이 되었다. 이제 이 Typed XML 데이터형을 가지고 XML 데이터 검색에 강한 X쿼리를 쓸 수 있다. 다음 예제는 ID가 10인 데이터를 검색하는 구문이다.


EXIST 함수의 경우 존재하면 1을 반환하고 없으면 0을 반환한다. 따라서 ID가 10인 데이터가 있으므로 하나의 행을 반환했다. 이제 이 X쿼리문을 이용하면 좀 더 쉽게 XML 데이터를 검색할 수 있을 것이다.


예외처리

SQL 서버 2000에서의 오류 처리 기능은 @@ERROR 변수 값을 확인하면 됐다. 하지만 이 변수 값은 단 하나의 SQL문에서만 생명력이 있기 때문에 에러가 날 만한 구문이 많이 있다면 모두 그 부분에서 @@ERROR 변수를 확인해야만 했다. 하지만 이제는 간단하게 TRY-CATCH 구문으로 묶어 줌으로써 이를 간단하게 해결할 수 있다. 이 구문은 이미 다른 언어에서는 많이 사용하는 구문이기 때문에 이해하기 어렵지는 않을 것이다.


이 예제는 외래키 위반 사례를 TRY-CATCH 구문으로 묶어본 것이다. 하지만 이러한 예외처리는 심각하지 않는 구문에서만 유효하다. 다음과 같은 경우를 보자.


PPP라는 테이블은 존재하지 않는다. 이러한 심각한 오류의 경우 CATCH 문에서 걸리지 않는다. 그래서 롤백되지 않는 분리된 트랜잭션이 남아 있게 된다. SQL 서버 2000에서도 이 경우는 마찬가지이므로 주의해야 한다.


DDL 트리거

SQL 서버 2000에서의 트리거는 데이터를 조작하는 구문(INSERT, UPDATE 등)에서만 사용이 가능했다. 하지만 이제는 DDL 이벤트(테이블, 뷰, 프로시저 등을 생성하거나 삭제)에서도 사용이 가능하다. 다음 예제를 보자.


현재 테이블 삭제와 변경시 발생하는 트리거를 정의하고 테이블 삭제와 변경을 못하도록 막아 놓았다. 그래서 실제로 테이블 삭제 테스트를 해보면 에러가 발생하면서 테이블 삭제가 되지 않는다. 이를 응용하면 관리자가 테이블이나 SP를 관리하는 데 도움을 줄 수 있다. DB를 관리하다 보면 잘 되던 프로그램이 갑자기 이상한 결과 값을 반환하거나 심한 경우 전체 DB가 다운되는 경우가 있다. 여러 가지 원인이 있을 수 있지만, 그중에서도 SP를 변경해서 생기는 경우가 종종 있다. 이럴 때 SP 변경 트리거를 걸어서 로그 관리를 한다면 원인이 되는 SP를 쉽게 찾을 수 있을 것이다. 또한 테이블 변경의 경우 매우 중요한 이슈이므로 테이블 변경 트리거를 걸어서 바로 알림 메시지(이메일, SMS 등)를 받을 수도 있다.


이것이 전부는 아니다

이것으로 T-SQL의 변화에 관해 짧게 소개를 해보았다. 하지만 지면상 많은 부분을 소개하지 못해 아쉬울 따름이다. T-SQL은 현재 소개한 것 말고도 많은 변화가 있다. 대략 나열해 보면 다음과 같다.

특히 문장 단위의 재컴파일 기능은 성능 면에서 좋은 효과를 줄 것으로 기대하고 있다. 다음 글에는 닷넷과 연동하는 부분에 대해 다룰 예정이다.

출처 : 마이크로소프트웨어 2005년 2월호