수안이의 컴퓨터 연구실

한용희 | 롯데정보통신 칠성IS 사업팀

운영체제 │ 윈도우 2000, 윈도우 2003, 윈도우 XP
개발도구 │ MS SQL 서버 2005 베타 2, 비주얼 스튜디오 2005 베타 1
기초지식 │ MS SQL 서버 2000, C#
응용분야 │ MS SQL 서버 2005 관리와 개발

SQL 서버 2005가 나오면서 개인적으로 가장 크게 관심을 보인 부분이 바로 테이블 파티셔닝이다. 현재 롯데칠성음료에서도 매달 100만 건의 거래명세표 내역이 쌓이면서 이를 처리하기 위한 대용량 데이터베이스에 대해 높은 관심을 기울이고 있다. 이번 호에서는 대용량 데이터베이스를 위한 테이블 파티셔닝과 가용성을 높이기 위한 스냅샷 그리고 미러링에 대해 알아 볼 것이다.

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

기업의 데이터는 시간이 지날수록 점점 많아지고 있다. 분석을 위한 데이터는 점점 더 쌓여만 가고 더 이상 하나의 테이블에 이 모든 정보를 담아 두는 것이 비효율적일 때가 있다. 보통 기가나 테라 단위의 데이터를 하나의 테이블에 담아 두게 되면 테이블 유지 보수가 힘들며 성능 또한 느려지게 된다. 이러한 데이터는 대부분 과거의 데이터가 함께 있어서 그러는데, 아마 몇 년 전의 데이터는 거의 사용하지 않을 것이다. 이럴 때에는 테이블을 나누어서 최근의 데이터는 높은 성능을 내는 I/O에 담아 두고, 예전의 데이터는 비교적 낮은 성능의 저렴한 I/O 장치에 담아두는 것이 효율적일 것이다. 이럴 때 사용하는 것이 바로 파티셔닝이다.

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

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

CREATE VIEW Production.vTransaction AS SELECT * FROM Production.Transaction_2003_09 UNION ALL SELECT * FROM Production.Transaction_2003_10 UNION ALL SELECT * FROM Production.Transaction_2003_11

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

CHECK ( TransactionDate between ‘2003-09-01’ AND ‘2003-09-30’)

이렇게 파티셔닝 컬럼을 정의하고 이 컬럼에 INDEX를 걸어 두면 분할된 뷰를 이용하여 테이블에 접근할 때 다른 날짜의 테이블은 읽지 않게 된다. SQL 서버 2000에서는 분할 뷰를 이용하여 데이터 갱신 작업이 효과적으로 수행하도록 지원하였으며 분산 분할된 뷰(distributed partitioned view)로까지 발전을 하여 각각의 테이블이 한 서버가 아닌 다른 서버에 있어도 가능하도록 발전했다. 하지만 분할된 뷰 방식의 파티셔닝은 여러 테이블을 하나의 뷰로 모았기 때문에 관리상 불편한 점이 많았다. 예를 들면 테이블 구조를 바꾼다거나 인덱스를 재생성하거나 변경하는 경우 각각의 테이블을 모두 반영해줘야 하기 때문이다.

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

SQL 서버 2005에서는 뷰를 통한 파티셔닝이 아닌 테이블 단위의 파티셔닝을 지원한다. 즉 하나의 테이블을 여러 조각으로 쪼개어 관리하는 것이 가능하다. 그러므로 분할된 뷰처럼 각 테이블을 따로 관리할 필요가 없다. 예를 들면 인덱스를 만드는데 있어서 하나의 테이블만 만들면 되므로 관리상 이점이 있다. 또한 성능에 있어서도 더 좋은 성능을 보여준다. 분할된 뷰에서는 각각의 테이블을 보고 나중에 합치는 방식으로 진행되었지만, 테이블 파티셔닝에서는 멀티 CPU 환경이라면 병렬처리(demand parallelism)를 이용하여 빠른 쿼리를 수행할 수도 있다. 쿼리를 컴파일하는데 있어서도 분할된 뷰에서는 테이블이 많을수록 느렸지만, 테이블 파티셔닝에서는 파티션 개수에 상관없이 빠른 속도를 보장한다.

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

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000)

이 예제는 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은 각각 오른쪽 파티션에 포함하게 된다. 파티션 함수를 만들었으면 실제 물리적인 영역에 맵핑할 수 있는 스키마를 정의해야 한다.

CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg)

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

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

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

테이블 분할
SELECT * INTO Production.Transaction_2003_09 FROM Production.TransactionHistory WHERE TransactionDate between ‘2003-09-01’ and ‘2003-09-30’; SELECT * INTO Production.Transaction_2003_10 FROM Production.TransactionHistory WHERE TransactionDate between ‘2003-10-01’ and ‘2003-10-31’; ...



체크 제약 조건 삽입
ALTER TABLE [Production].[Transaction_2003_09] WITH CHECK ADD CONSTRAINT [CK_Transaction_2003_09] CHECK (TransactionDate between ‘2003-09-01’ AND ‘2003-09-30’); ALTER TABLE [Production].[Transaction_2003_10] WITH CHECK ADD CONSTRAINT [CK_Transaction_2003_10] CHECK ( TransactionDate between ‘2003-10-01’ AND ‘2003-10-31’); ...

인덱스 만들기
CREATE CLUSTERED INDEX [IX_Transaction_2003_09_TransactionDate] ON [Production].[Transaction_2003_09]([TransactionDate]); CREATE CLUSTERED INDEX [IX_Transaction_2003_10_TransactionDate] ON [Production].[Transaction_2003_10]([TransactionDate]); ...

뷰 만들기
CREATE VIEW Production.vTransaction AS SELECT * FROM Production.Transaction_2003_09 UNION ALL SELECT * FROM Production.Transaction_2003_10 UNION ALL ...

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

SELECT * FROM Production.vTransaction WHERE TransactionDate between ‘2003-09-01’ and ‘2003-10-31’;
-----------------------------------------------------------------------------------------
(20494 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Transaction_2003_10’. Scan count 1, logical reads 74, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Transaction_2003_09’. Scan count 1, logical reads 88, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

이 결과를 보면 예상대로 다른 테이블은 읽기가 없고 해당 테이블에서만 읽기가 일어난 것을 볼 수 있을 것이다. 그럼 실제 실행 계획을 보자. <화면 1>을 보면 다른 테이블을 모두 본 다음에 필터로 걸러서 나중에 결합(concatenation) 연산을 이용하여 합치는 것을 볼 수 있다. 앞에서는 PK(Primary Key)가 없어서 그런 것이고, 만약 파티셔닝 컬럼이 PK라면 다른 테이블은 아예 보지도 않고 필터링도 없어 바로 데이터를 가져온다. 하지만 PK가 있는 테이블도 저장 프로시저를 이용하여 쿼리를 하면 어차피 현재와 똑같이 필터링하여 결합하므로 큰 차이는 없다고 봐도 된다. 읽기 수를 보더라도 다른 테이블은 필터링을 하므로 0이 나온다. 이제는 테이블 파티셔닝을 이용해보자. SQL 서버 2005 베타 2에서 엔진 예시(Engine Example)를 설치하여 다음 폴더에 가보면 테이블 파티셔닝 예제가 있다.

C:\Program Files\Microsoft SQL Server\90\Tools\Samples\1033\Engine\Administration\
Partitioning\Scripts\PartitionAW.sql

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

-- Range partition table TransactionHistory
CREATE PARTITION FUNCTION TransactionRangePF1 (datetime) AS RANGE RIGHT FOR VALUES (‘10/01/2003’, ‘11/01/2003’, ‘12/01/2003’, ‘1/01/2004’, ‘2/01/2004’, ‘3/01/2004’, ‘4/01/2004’, ‘5/01/2004’, ‘6/01/2004’, ‘7/01/2004’, ‘8/01/2004’); GO

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

CREATE PARTITION SCHEME TransactionsPS1 AS PARTITION TransactionRangePF1 TO ([PRIMARY], [PRIMARY], [PRIMARY] , [PRIMARY], [PRIMARY], [PRIMARY] , [PRIMARY], [PRIMARY], [PRIMARY] , [PRIMARY], [PRIMARY], [PRIMARY] , [PRIMARY]);

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

CREATE TABLE [Production].[TransactionHistory]( [TransactionID] [int] IDENTITY (1, 1) NOT NULL, [ProductID] [int] NOT NULL, [ReferenceOrderID] [int] NOT NULL, [ReferenceOrderLineNumber] [smallint] NOT NULL , [TransactionDate] [datetime] NOT NULL , [TransactionType] [nchar](1) NOT NULL, [Quantity] [int] NOT NULL, [ActualCost] [money] NOT NULL, [ModifiedDate] [datetime] NOT NULL ) ON TransactionsPS1 (TransactionDate);

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

SELECT * FROM Production.TransactionHistory WHERE TransactionDate between ‘2003-09-01’ and ‘2003-10-31’;
-----------------------------------------------------------------------------------------
(20494 row(s) affected)
Table ‘TransactionHistory’. Scan count 2, logical reads 162, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

슬라이딩 윈도우 구현

파티션된 테이블을 관리하다 보면 오래된 데이터는 거의 사용을 하지 않게 된다. 어쩌다 한 번씩 통계 자료용으로 사용하는 경우가 대부분이다. 이러한 데이터를 계속 고성능의 I/O 장치에 담아 두는 것은 비효율적이다. 따라서 오래된 데이터는 더 이상의 트랜잭션이 일어나지 않으므로 비교적 낮은 성능의 저렴한 I/O 장치로 이식하는 것이 효율적이다. 이러한 과정을 ‘슬라이딩 윈도우(파티션 스위칭)’라고 한다. 슬라이딩 윈도우의 우리말 뜻은 ‘미닫이창’이다. 즉 밀어서 여닫는 창이라는 뜻인데, 오래된 데이터는 밀어서 내보내고 대신 새 데이터를 받아들인다는 의미로 보면 될 것이다.

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





C:\Program Files\Microsoft SQL Server\90\Tools\Samples\1033\Engine\Administration\
SlidingWindow\Scripts\sliding.sql

이번 예제에서는 2003년도 9월의 데이터를 TransactionHistory 테이블에서 떼어 내어 TransactionHistoryArchive 테이블로 옮기는 작업이다. 초기 상태는 <그림 2>와 같다. TransactionHistory에는 12개의 파티션이 있고 Transaction HistoryArchive에는 2개의 파티션이 있다. TransactionHistoryArchive는 자주 사용하지 않는 데이터를 모아 두는 곳이므로 두 개의 파티션만을 만들었다. 여기에서 TransactionHistory 테이블에서 2004년도 9월 데이터를 위한 새로운 데이터를 위한 공간을 확보하자.

ALTER PARTITION FUNCTION TransactionRangePF1() SPLIT RANGE (‘9/01/2004’);

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

ALTER PARTITION FUNCTION TransactionArchivePF2() SPLIT RANGE (‘10/01/2003’);








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

ALTER TABLE [Production].[TransactionHistory] SWITCH PARTITION 1 to [Production].[TransactionHistoryArchive] PARTITION 2;

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

function_id boundary_id parameter_id value
----------- ----------- ------------
65536 1 1 2003-10-01 00:00:00.000
65536 2 1 2003-11-01 00:00:00.000
65536 3 1 2003-12-01 00:00:00.000
65536 4 1 2004-01-01 00:00:00.000
65536 5 1 2004-02-01 00:00:00.000
65536 6 1 2004-03-01 00:00:00.000
65536 7 1 2004-04-01 00:00:00.000
65536 8 1 2004-05-01 00:00:00.000
65536 9 1 2004-06-01 00:00:00.000
65536 10 1 2004-07-01 00:00:00.000
65536 11 1 2004-08-01 00:00:00.000
65536 12 1 2004-09-01 00:00:00.000
65537 1 1 2003-09-01 00:00:00.000
65537 2 1 2003-10-01 00:00:00.000
(14 row(s) affected)

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

ALTER PARTITION FUNCTION TransactionRangePF1() MERGE RANGE (‘10/01/2003’);
병합을 한 후 다시 테이블 파티션 정보를 보자.

function_id boundary_id parameter_id value
----------- ----------- ------------
65536 1 1 2003-11-01 00:00:00.000
65536 2 1 2003-12-01 00:00:00.000
65536 3 1 2004-01-01 00:00:00.000
65536 4 1 2004-02-01 00:00:00.000
65536 5 1 2004-03-01 00:00:00.000
65536 6 1 2004-04-01 00:00:00.000
65536 7 1 2004-05-01 00:00:00.000
65536 8 1 2004-06-01 00:00:00.000
65536 9 1 2004-07-01 00:00:00.000
65536 10 1 2004-08-01 00:00:00.000
65536 11 1 2004-09-01 00:00:00.000
65537 1 1 2003-09-01 00:00:00.000
65537 2 1 2003-10-01 00:00:00.000
(13 row(s) affected)

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

ALTER PARTITION FUNCTION TransactionArchivePF2() MERGE RANGE (‘9/01/2003’);

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

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

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

스냅샷은 읽기만 할 수 있는 데이터베이스이다. 만들 때에는 실제 데이터의 복사본을 만드는 것이 아니고 메타 데이터만으로 만들기 때문에 상당히 빠르고 적은 용량으로 만들 수 있다. 실제 구현을 보면 먼저 스냅샷은 현재 데이터베이스와 동일한 저장 공간을 예약하고 원본 데이터베이스에서 변경이 일어나면 먼저 스냅샷 데이터베이스에 복사를 한 후 원본 데이터베이스를 변경한다. 이를 복사-쓰기(copy-on-write) 기술이라고 부른다.
<그림 9>를 보면 원본 데이터베이스의 2라는 값이 10으로 바뀔 때 먼저 2라는 값을 스냅샷 데이터베이스에 복사를 하고 자기 자신의 값을 10으로 바꾸고 있다. 스냅샷 데이터베이스는 결국 원본 데이터베이스에서 바뀌기 전의 상태 값만 가지고 있고, 나머지는 원본 데이터베이스를 참조한다. 그래서 생성 시간이 빠르고 공간도 적게 차지하는 것이다. 그럼 직접 실습을 해보자.

CREATE DATABASE Test; USE Test; CREATE TABLE Dummy ( Data int ); INSERT INTO Dummy VALUES (1); INSERT INTO Dummy VALUES (2); INSERT INTO Dummy VALUES (3); INSERT INTO Dummy VALUES (4);

스냅샷 생성
CREATE DATABASE Test_01 ON ( NAME = Test, FILENAME = ‘c:\program files\microsoft sql server\mssql.1\mssql\data\Test_01.ss’ ) AS SNAPSHOT OF Test;










Test라는 데이터베이스를 만들고 Dummy라는 테이블을 만들어 1,2,3,4라는 값을 넣고 Test_01이라는 Test 데이터베이스의 스냅샷을 만들었다. 앞에서 만든 Test_01.ss 라는 파일의 실제 크기를 보면 <화면 3>과 같다.
크기는 1.56MB를 할당했지만 실제 사용하는 크기는 128KB밖에 안 된다는 것을 확인할 수 있을 것이다. 이제 Test 테이블에서 2라는 값을 10으로 바꾸고 스냅샷에서 제대로 값을 보존하고 있는지 확인해보자.

UPDATE Dummy SET Data = 10 WHERE Data = 2; SELECT * FROM dummy; USE Test_01; SELECT * FROM dummy;
------------------------------
Data
------------------------------
1
10
3
4
(4 row(s) affected)
Data
------------------------------
1
2
3
4
(4 row(s) affected)

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

USE master; RESTORE DATABASE Test FROM DATABASE_SNAPSHOT = ‘Test_01’; USE Test; SELECT * FROM dummy;
------------------------------
Data
------------------------------
1
2
3
4
(4 row(s) affected)

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

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

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

SQL 서버 2005에서는 또 다른 해결책으로 미러링이라는 것을 지원한다. 미러링은 두 대의 SQL 서버를 운영하면서 서로 로그 정보를 주고받으면서 동일한 데이터를 유지한다. 따라서 별도의 공유 디스크가 필요 없으며, 디스크 자체가 깨지더라도 서로 디스크 복사본을 유지하기 때문에 문제가 안 된다. 또한 별도의 광케이블이 아닌 일반 네트워크 선을 사용하므로 거리 제한도 없다. 여기에 클러스터링은 서버에 문제가 생겨 교체되는데에 30초 이상의 시간이 걸리지만 미러링은 2~3초면 서버가 교체되어 자동으로 작동한다.

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

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

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

CREATE ENDPOINT EndPoint_Principal STATE=STARTED AS TCP (LISTENER_PORT=5055) FOR DATABASE_MIRRORING (ROLE=ALL);

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

ALTER DATABASE [Test] SET PARTNER = ‘TCP://WIN2003-SQLServer2005:5055’

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

-- mirrored 서버 지정 ALTER DATABASE [Test] SET PARTNER = ‘TCP://WIN2003-SQLServer2005:5056’

-- witness 서버 지정 ALTER DATABASE [Test] SET WITNESS = ‘TCP://WIN2003-SQLServer2005:5057’

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

INSERT INTO Dummy VALUES (11); INSERT INTO Dummy VALUES (12);

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

Database Test cannot be opened - it is acting as a mirror database.

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




SELECT * FROM dummy;
-----------
1
2
3
4
11
12

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

정식 SQL 서버 2005를 기다리며

지난 4개월 동안 SQL 서버 2005의 베타 2 버전을 살펴보았다. 앞으로 새로운 버전이 나오면서 또 어떻게 바뀔지는 모르지만 베타 2 정도면 SQL 서버 2005에서 구현하려는 핵심 기능은 대부분 들어 있다고 봐도 될 것이다. 그 핵심 기능을 요약해 보면 개발자의 관점에서는 닷넷과의 통함이 될 수 있을 것이고, 관리자의 관점에서는 향상된 가용성(availability)이라고 할 수 있을 것이다. 이제 올 하반기에 나올 정식 SQL 서버 2005를 기다리면서 또 다른 항해를 준비하자.



제공 : DB포탈사이트 DBguide.net

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

한용희│롯데정보통신 칠성IS 사업팀

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

운영체제 │ 윈도우 2000, 윈도우 2003, 윈도우 XP
개발도구 │ MS SQL 서버 2005 베타 2, 비주얼 스튜디오 2005 베타 1
기초지식 │ MS SQL 서버 2000, C#
응용분야 │ MS 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만행이나 되기 때문에 이러한 작업을 테스트하기에 안성맞춤이다.

SELECT GETDATE();
ALTER INDEX ALL ON Sales.SalesOrderDetail REBUILD
WITH (ONLINE = ON);
SELECT GETDATE();

-----------------------
2005-03-12 16:06:35.110
(1 row(s) affected)

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

UPDATE Sales.SalesOrderDetail
SET OrderQty = 10000
WHERE SalesOrderID = 43659;
SELECT GETDATE();

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

온라인 복구 기능 지원

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

백업 미러링 지원

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

BACKUP DATABASE AdventureWorks
TO TAPE = '\\.\tape1'
MIRROR TO TAPE = '\\.\tape2'
WITH
FORMAT,
MEDIANAME = 'AdventureWorksSet1'
동시에 하는 데이터베이스 백업과 로그 백업

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

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

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

C:\Documents and Settings\Administrator>sqlcmd -S localhost -E -A
1> USE adventureworks
2> go
Changed database context to 'AdventureWorks'.
1> select Name from Person.AddressType
2> go
Name
--------------------------------------------------
Archive
Billing
Home
Main Office
Primary
Shipping

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

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

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

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\Install_SQLiMail.sql
마법사를 이용하는 방법은 매니지먼트 스튜디오에서 매니지먼트에 부분에 보면 SQLiMail이라는 아이콘이 있다. 그 아이콘을 더블클릭하면 마법사가 실행된다.



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

EXEC dbo.sendimail_sp
@profile_name = 'AdventureWorks Administrator',
@recipients = 'danw@Adventure-Works.com',
@body = '잘 도착했나요?',
@subject = '테스트 메일입니다.' ;
이와 같이 받을 사람을 지정하고 메일을 보내면 된다.

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

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





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

• SQL 서버
• SQL 서버 Agent
• SQL 서버 Analysis Services
• Report Server
• Microsoft Search
• Distributed Transaction Coordinator(DTC)
• Full Text Search

엔터프라이즈 관리자+쿼리 분석기 = 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로 되어 있을 것이다. 그 이유는 테이블의 소유자를 어떤 한 사용자로 두었다가 만약 그 사용자를 교체해야 한다면, 모든 데이터베이스 오브젝트의 소유자를 다 바꿔줘야 하는 불편이 있기 때문이다. 이는 애플리케이션 프로그램의 변경에도 영향을 미치는데 애플리케이션에서 해당 오브젝트를 사용하는 코드를 기술할 때 대부분 소유자를 명시하기 때문이다. 예를 들어

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



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

CREATE LOGIN LoginA WITH PASSWORD = '123';
CREATE LOGIN LoginB WITH PASSWORD = '123';
CREATE LOGIN LoginC WITH PASSWORD = '123';
그 다음 각각의 로그인에 맞는 사용자를 생성한다.

USE AdventureWorks;
CREATE USER UserA FOR LOGIN LoginA WITH DEFAULT_SCHEMA = Schema1;
CREATE USER UserB FOR LOGIN LoginB;
CREATE USER UserC FOR LOGIN LoginC;
이 때 UserA에만 기본 스키마로 Schema1이라는 것을 할당했다. 나머지는 명시를 하지 않았는데, 그러면 기본 스키마로 dbo가 할당된다. 이제 UserA에는 테이블 생성 권한을 주고, UserB에는 Schema1 스키마의 조회 권한을 주자.

GRANT CREATE TABLE to UserA;
GRANT SELECT on Schema::Schema1 TO UserB;
Schema1 스키마의 소유자를 UserA로 정하자.

CREATE SCHEMA Schema1 AUTHORIZATION UserA;
사용자 UserA로 변환한 다음 테이블을 생성한다.

SETUSER 'UserA';
CREATE TABLE Schema1.TestTable(id integer);
사용자 UserB로 변환한 다음 조회를 해보자. 잘된다.

SETUSER 'UserB';
SELECT * FROM Schema1.TestTable;
이제 Schema1의 소유자를 바꿔보자.

SETUSER;
ALTER AUTHORIZATION ON SCHEMA::[Schema1] TO [UserC];
다시 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) 등 세 가지 방식의 암호화 메커니즘을 제공한다. 사용자는 이 세 가지 중 한 가지를 선택하여 데이터를 암호화하여 보호할 수 있다.

CREATE CERTIFICATE Cert1
WITH SUBJECT = 'Test',
ENCRYPTION_PASSWORD = '123',
EXPIRY_DATE = '2010/12/31';

DECLARE @n nvarchar(100);
SET @n = EncryptByCert ( Cert_ID('Cert1'), N'ABC');

SELECT @n;

SELECT CAST ( DecryptByCert( Cert_ID('Cert1'), @n, N'123') as nvarchar);

------------------------------------------------------------------------------
붴?O????使?′졵???啣??얏??돻???손?蚓恩????듊???쟅?????쀁녥??艅?
(1 row(s) affected)

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

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

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

익스체인지, 아웃룩 없이 SQL 서버 2000에서도 메일 보내기

사실 기존 SQL 서버 2000에서도 SQLMail을 사용하지 않고도 단순히 SMTP 서버와 CDOSYS 오브젝트만으로 메일을 보낼 수 있다. 다음 링크를 보면 자세한 내용이 나와 있다.

http://support.microsoft.com/default.as ··· 3B312839

저장 프로시저 소유자를 명시하지 않아 블로킹이 걸리는 경우

이전의 SQL 서버에서는 자신의 소유가 아닌 저장 프로시저를 호출할 때 소유자를 명시하지 않고 호출하는 것이 가능하다. 예를 들면 다음처럼 하는 것이다.

exec MyProc

그런데 이럴 경우 간혹 프로필러로 추적해 보면 캐시 부적중(cache miss)이 발생한다. 즉, 바로 재사용 가능한 실행 계획을 찾지 못하고 한 번 실패를 한 후에 컴파일 잠금을 하고 기존 실행 계획 중 재사용할 수 있는 것이 있는지 찾아본다. 그러다가 기존에 재사용 가능한 실행 계획이 있다는 것을 발견하고 재컴파일을 하지 않고 기존 실행 계획을 재사용하는 것이다. 이런 일련의 과정에서 문제가 되는 것은 바로 컴파일 잠금이 발생한다는 것이다. 대규모 사용자가 동시에 이 SP를 호출한다면 블로킹이 걸릴 수도 있는 것이다. 그러므로 소유자를 명시하는 것이 바람직한 방법이다. 자세한 내용은 다음을 참조하기 바란다.

http://support.microsoft.com/default.as ··· 3B263889
• 『고급 SQL 서버 개발자 가이드』 64쪽～65쪽(켄 헨더슨 저/ 하성희 역)

동의어 기능

SQL 서버 2005에서는 스키마명을 꼭 명시해 주어야 하기 때문에 이름이 길어져서 코딩하는데 약간 불편함이 있을 수 있다. 그럴 때에는 동의어 기능을 이용하면 코딩에 드는 노력을 줄일 수 있다.

CREATE SYNONYM Orders FOR Sales.SalesOrderHeader

이와 같이 지정을 하면 다음부터는 Sales.SalesOrderHeader라고 길게 치지 않아도 Orders라고 치면 된다. 하지만 이 방식은 코딩 노력을 줄여준다는 의미에서는 좋은 반면 가독성 측면에서는 좋지 않은 방법이 될 수도 있다. 왜냐하면 소스코드라는 것은 한군데 있으면 판독하기 쉽지만 여러 군데에 소스코드가 나누어져 있다면 판독하기가 쉽지 않기 때문이다.

참고자료

• http://member.microsoft.co.kr/technet/s ··· page%3D1
• SQL SERVER 2005 FOR DEVELOPERS - Microsoft Press
• A first Look at SQL Server 2005 for developers - Addison Wesley
• SQL Server 2005 New Features - McGrawHill Osborne

제공 : DB포탈사이트 DBguide.net

정수현 | 동명정보대학교 정보기술원

마이크로소프트의 차세대 DBMS인 SQL 서버 2005가 그 모습을 드러내고 있다. SQL 서버 2000이 출시된 지로부터 5년이 지난 후에 출시되는 SQL 서버 2005. 닷넷과 통합된 모습을 보면 IT 엔지니어들도 개발과 관리를 아우르는 컨버전스를 해야 하는 시대란 생각이 든다. IT 컨버전스 시대에 우리도 해야 할 게 갈수록 늘어나고 있다는 것은 기뻐해야 할 일인지 슬퍼해야 할 일인지 모르지만, 이번 기사를 본 독자들은 하나는 느낄 수 있을 것이다. “닷넷을 해야 되겠군” 필수는 아니지만 SQL 서버 2005를 파워풀하게 사용하기 위해서는 어쩔 수 없는 일이 되었다. 누구의 말처럼 SQL 서버 2005를 스포츠카에 비유한다면 닷넷을 사용하지 않으면 스포츠카를 타고 시속 60km로 달리는 것과 마찬가지이기 때문이다.

코드명 ‘유콘(Yucon)’으로 명명됐던 마이크로소프트의 SQL 서버 2000의 차기 버전인 SQL 서버 2005에 대해 어떠한 개선이 이루어졌는지 또한 어떤 기능이 새로 추가되었는지 궁금하게 여기는 사람들이 많다. 현재 SQL 서버 2005는 베타2 이후 Commutiy Technology Preview까지 나와 있으며 필자도 CTP 버전을 가지고 이 기사를 작성하고 있다.
SQL 서버 2005는 CLR(Common Language Runtime) 통합, 새로운 통합 관리도구, 새로운 비즈니스 인텔리전스(이하 BI) 지원도구 등의 기능이 포함된 DBMS이다. 이것은 갈수록 높아지는 기업의 데이터베이스 활용 방향, 즉 데이터웨어하우스와 BI에 대응하기 위함이라고 볼 수 있다. 과연 SQL 서버 2005가 SQL 서버 2000까지 일부 개발자나 DBA에게 인식되던 한계점, 아직까지 대용량의 민감한 기업 환경에서 사용되기에는 약간 부족하다는 인식을 불식시킬 수 있을 것인지 이번 기사를 통하여 조금이나마 알아보기로 한다. 우선 SQL 서버 2005에 대한 설명은 개발적 측면, 관리적 측면 그리고 더욱 강화된 BI 기능으로 분류해서 진행하도록 하겠다.

SQL 서버 매니지먼트 스튜디오

SQL 서버 2005의 새로운 관리도구인 ‘SQL 서버 매니지먼트 스튜디오’는 이전 버전의 관리도구인 엔터프라이즈 매니저, Analysis Services를 통합하였다. 따라서 SQL 서버 매니지먼트 스튜디오는 자연히 많은 도구들의 종합세트처럼 되었는데 먼저 객체 익스플로러(Object Explorer)의 사용을 알아보자.
객체 익스플로러는 OLAP와 DTS, 리포팅 서비스(Reporting services), Notification Service와 보안관리, SQL 서버 에이전트, SQL 메일 등을 사용하게 하는 도구이다. 그리고 이전에 있던 데이터베이스 Maintenance Plan을 더욱 발전시켜 원래 있던 백업, 인덱스 관리 기능뿐만 아니라 DTS 디자이너를 데이터베이스 유지관리에도 포함시켰다.
또한 SQL 서버 매니지먼트 스튜디오 솔루션이라는 새로운 쿼리 실행도구가 추가되었다. 여기서는 마치 비주얼 스튜디오 닷넷을 사용할 때처럼 프로젝트 생성을 통하여 SQL 서버 스크립트나 MDX, DMX, XMLA 등을 생성하고 저장하게 하는 Analysis 스크립트, 그리고 모바일과 연동되는 SQL 쿼리를 생성하는 SQL 모바일 스크립트를 작성할 수 있다.




개발 측면의 개선사항

SQL 서버 2005에서는 개발자를 위한 지원으로 닷넷을 포함시켰다. 또한 SQL의 핵심인 Transact-SQL(이하 T-SQL)의 기능도 개선시켜 개발자들이 더욱 쉽게 데이터베이스 애플리케이션을 개발할 수 있게 하고 있다. 다음은 SQL 서버 2005에서 개선이 이루어지거나 새로이 추가된 개발자적 측면의 기능이다.

◆ SQL 서버 2005의 개선 사항과 개발자 측면의 추가된 기능

1. 닷넷 프레임워크 호스팅 : SQL 서버 2005에서 개발자는 비주얼 C# 닷넷이나 비주얼 베이직 닷넷(이하 VB.NET)과 같은 언어를 통하여 저장 프로시저, 사용자 정의 데이터 유형, 사용자정의 함수와 같은 데이터베이스 객체를 개발할 수 있다.

2. XML 기술 : XML(eXtensible Markup Language)은 네트워크나 인터넷을 통하여 이기종 데이터를 통합할 수 있는 언어이다. SQL 서버 2005는 XML 쿼리와 저장을 지원한다.

3. ADO.NET 버전 2.0 : ADO.NET은 SQL 서버 2005에 데이터세트를 액세스하고 조작하는 기능을 부여해준다.

4. 보안 개선 : SQL 서버 2005는 사용자와 객체를 분리하여 사용할 수 있게 한다. 이를 통하여 개발자는 객체에 대한 보안 관리를 보다 쉽게 할 수 있다.

5. 보안 개선 : SQL 서버 2005는 사용자와 객첼르ㅡ 분리하여 사용할 수 있게 한다. 이를 통하여 개발자는 객체에 대한 보안 관리를 보다 쉽게 할 수 있다.

6. T-SQL 개선 : SQL 서버의 핵심인 T-SQL도 개선이 이루어졌다. 에러 핸들링과 재귀 쿼리, 피봇(Pivot), APPLY, ROW_NUMBER 등의 개선이 이루어졌다.

7. 서비스 브로커 : 서비스 브로커(Service Broker)는 분산되고 비동기화된 애플리케이션간의 메시지 전달을 제공하는 새로운 기능이다.

8. 리포팅 서비스 : SQL 서버 2005의 리포팅 서비스는 비주얼 스튜디오 2005(코드명 Whidvey)와 함께 사용되는 리포팅 솔루션이다.

닷넷과 SQL 서버 2005

SQL 서버 2005에서 가장 중점을 둔 부분은 바로 VB.NET이나 C#과 같은 CLR 기반 언어를 이용하여 T-SQL이 취약했던 프로그램 객체 형성을 통한 복잡한 처리를 할 수 있게 해준다는 점이다. 이는 예전부터 여러 프로그래머들이 계속 요구해왔던 점으로서 SQL 서버 2005에서 이러한 요구를 수용하고 T-SQL을 더욱 발전시켰다고 볼 수 있다. SQL 서버 2005는 CLR과 통합되어서 어떤 데이터베이스 객체, 예를 들어 트리거, 프로시저, 사용자정의 함수, 사용자정의 형식 등을 닷넷 언어로서 정의하고 실행할 수 있다.
간단하게 CLR을 이용하는 닷넷 코드의 배포를 설명한다면 우선 개발자가 VB.NET이나 C#과 같은 언어를 이용하여 코드를 작성하고 나서 닷넷 컴파일러를 통하여 어셈블리를 만들고, 그 후에 Create Assembly와 같은 T-SQL 문장을 이용하여 SQL 서버에 등록하면 되는 것이다.
그러면 지금부터 간단하게 저장 프로시저를 C# 코드를 이용하여 생성하고 이를 적용하는 장면을 보자. 필자는 이를 위하여 SQL 서버 2005 Community Preview 버전과 비주얼 스튜디오 닷넷 2005 Community Preview를 사용했다.




예를 들어 특정 데이터베이스의 쿼리 결과를 XML 형태로 파일에 저장하게 하는 저장 프로시저를 작성해보자. 이를 위해서는 우선 <화면 3>처럼 비주얼 스튜디오 닷넷 2005를 이용하여 저장 프로시저를 작성하는 프로젝트를 작성하기로 한다. C# 코드로서 데이터를 XML에 저장하는 코드를 작성한다.

[SqlProcedure(Name = “SaveXML”)] public static void SaveXML (SqlXml XmlData, SqlString FileName) XmlDocument xmlDoc = new XmlDocument(); xmlDoc.LoadXml(XmlData.Value); xmlDoc.Save(FileName.Value);


이렇게 작성한 다음 이것을 빌드한다. 다음은 SQL 서버 매니지먼트 스튜디오에서 작성하는 것이다. 여기서는 AdventureWorks라는 데이터베이스를 이용하는 것으로 한다. 참고로 AdventureWorks는 SQL 서버 2005를 설치하면 기본으로 생성되는 예제 데이터베이스이다.

USE AdventureWorks GO CREATE ASSEMBLY ManagedCode FROM ‘C:\Program Files\Microsoft SQL 서버\Stored Procedure\ManagedCode.dll’ WITH PERMISSION_SET = EXTERNAL_ACCESS GO CREATE PROCEDURE dbo.SaveXML (@XmlData xml, @FileName nvarchar(20)) AS EXTERNAL NAME ManagedCode.[FileUtilities.StoredProcedures].SaveXML


앞의 코드에서 보는 바와 같이 Create Assembly를 이용하여 VS.NET에서 생성한 어셈블리를 등록하고 이를 프로시저에서 사용하도록 지정해 주면 된다. SaveXML 프로시저를 이용하면 다음과 같이 테스트할 수 있다. 특히 SQL 서버에서 CLR을 사용할 수 있게 하려면 다음과 같이 SQL 서버 옵션을 정해주면 된다.

sp_configure ‘clr_enabled’ , 1 Go DECLARE @productList xml SET @productList = (SELECT ProductID, Name, ListPrice FROM Production.Product Product FOR XML AUTO, ROOT(‘Catalog’), TYPE) EXEC dbo.SaveXML @productList, ‘C:\Catalog.xml’


T-SQL 개선

T-SQL 부분에서는 피봇과 언피봇(Unpivot) 기능 지원, CTE(Common Table Expression), 재귀 쿼리 기능 등이 추가되었다. 이 기사에서 이런 기능들의 상세 부분을 알아볼 수는 없고, 피봇과 언피봇 정도만 살펴보기로 하겠다.



SQL 서버 2005에서는 피봇 연산자를 사용하여 행을 컬럼으로 변환하거나 이에 따른 집계도 할 수 있다. 예를 들어 주문 테이블에서 고객이 주문한 생산자와 주문한 수량을 알고 싶으면 피봇 연산자를 사용하여 원하는 생산자를 지정하면 된다.

SELECT * FROM Sales.Order PIVOT (SUM(Qty) FOR Prod IN ([Bike],[Chain])) PVT


만약 반대로 컬럼들을 행으로 변환시키려면 언피봇을 수행하면 된다.

SELECT Cust, Prod, Qty FROM Sales.PivotedOrder UNPIVOT (Qty FOR Prod IN ([Bike],[Chain])) UnPVT

이전 버전에서는 이러한 결과를 얻기 위해서는 좀 더 복잡한 연산을 해야만 했지만 이제는 한결 간편해졌다는 것을 알 수 있다.

SQL 서버 2005의 관리적 측면

지금부터는 SQL 서버 2005에서 관리 측면에서의 이루어진 개선점과 새로운 기능을 간략히 설명하겠다. 관리적 측면에서는 우선 데이터베이스 미러링과 온라인 복구 기능이 눈에 띈다. 이 기능으로 SQL 서버 2005에서는 한층 가용성이 높아지게 되었다. 또한 SQL 서버 매니지먼트 스튜디오라는 도구를 제공하여 Query Analyzer, 엔터프라이즈 매니저 등으로 분산 사용되던 관리도구를 통합하여 관리자가 더욱 쉽게 데이터베이스 시스템을 관리할 수 있게 하고 있다. 다음은 SQL 서버의 관리적 측면에서의 개선되거나 추가된 기능이다.

◆ SQL 서버 2005의 관리적 측면

1. 데이터 미러링 : SQL 서버 2000의 로그 전달 기능을 더욱 개선시킨 데이터베이스 미러링 기능을 추가했다. 미러링 기능으로 대기 서버의 자동 복구 기능을 활용할 수 있다.

2. 온라인 복구 : SQL 서버 2005에서는 데이터베이스가 온라인 상태에서도 복구 가능하다. SQL 서버 2000에서는 온라인 상태에서의 데이터베이스 복구는 가능하지 않았다.

3. 온라인 인덱싱 작업 : 인덱싱 작업 중에도 업데이트, 삭제, 삽입과 같은 데이터 수정 작업이 가능하다. 예를 들어 클러스터 인덱스를 작성하거나, 재작성하는 중에 데이터의 업데이트가 가능하다는 말이다.

4. 개선된 보안 : SQL 서버 2005는 보안에서의 개선점을 이루어냈다. 예를 들어 로그인 암호정책 사용이나 소유주와 스키마 분리 등의 기능이 추가되었다.

5. SQL 서버 매니지먼트 스튜디오 : SQL 서버 매니지먼트 스튜디오라는 통합 관리 도구가 등장했다. 이 도구를 통해 개발자와 관리자는 T-SQL 작성, 백업, 프로필러 사용, 각종 마법사 사용 등을 할 수 있다.

6. 관리자 전용 연결(Dedicated Administrator Connection) : SQL 서버 2005에서는 관리자에게 전용 관리 연결을 제공한다. 이것은 데이터베이스가 어떤 이유에서 잠겨 있더라도 관리자의 관리 활동을 위해 전용 연결 접속을 계속할 수 있게 하는 기능이다.

7. 스냅샷 격리 : 스냅샷 격리는 데이터베이스 레벨에서 제공하는 새로운 트랜잭션 격리 수준이다. 스냅샷 격리를 통하여 사용자는 쓰기 중인 데이터에도 접근하여 조회할 수 있다.

8. 데이터 파티셔닝 : 데이터 파티셔닝을 통하여 대용량의 테이블이나 인덱스를 효율적으로 관리할 수 있게 한다.

9. 복제 개선 : 분산 데이터를 위한 기능으로 SQL 서버에서는 오라클에서 SQL 서버로 복제하는 데이터를 https를 통하여 가능하게 했다. 추가적으로 일대일 트랜잭션 복제 기능을 더욱 개선시켰다.

데이터베이스 미러링

SQL 서버 2005의 관리적 측면에서 가장 강조하는 개선점은 데이터베이스 미러링 기능이다. 데이터베이스 미러링 기능은 이전 버전에서의 로그 전달 기능을 더욱 개선시킨 것이다. 데이터베이스 미러링을 이용하여 원본 서버의 트랜잭션 로그를 스트리밍으로 대상 서버에 전달하고, 이를 대상 서버에서 적용시켜 원본 서버 장애시 신속한 대체를 할 수 있게 한다. 데이터베이스 미러링을 구현하려면 3대의 SQL 서버가 필요한데, 각 서버는 ‘Principal’, ‘’Mirror’, ‘witness’라는 각각의 역할을 구성하게 된다.



우선 원본(Principal) 서버는 트랜잭션이 수행되는 서버를 말하며, 미러링 서버는 트랜잭션 로그가 동기화되어 적용될 대상 서버를 말한다. 미러링 서버에서 사용자는 바로 데이터를 읽을 수는 없다. 트랜잭션 로그는 원본 서버에서 생성되어 미러링 서버로 지속적으로 전달되어 적용된다. 이렇게 하면 결과적으로 거의 동시에 서버는 복제되는 데이터를 갖게 되는 것이다. 목격자(Witness) 서버는 원본 서버와 미러링 서버 사이에서 감시 역할을 수행하고 있는 서버라고 보면 된다. 만약에 원본 서버에 장애가 발행한다면 목격자 서버는 자동으로 미러링 서버로 하여금 장애복구를 수행하게 한다. 무엇보다 데이터베이스 미러링의 장점은 지연시간이 거의 없이 서버의 장애복구를 수행하게 하는 점이라고 할 수 있다.

스냅샷 격리

스냅샷 격리(Snapshot Isolation)는 SQL 서버 2005에서 새롭게 추가된 트랜잭션 격리 레벨이다. 스냅샷 격리를 이용하면 데이터에 쓰기 작업을 하고 있더라도 데이터를 읽을 수 있게 된다. SQL 서버 2000에서는 쓰기 작업 중인 데이터에는 자동적으로 Exclusive Lock이 걸리게 되어 해당 데이터를 다른 사용자가 읽을 수 없게 되어 있었다. SQL 서버 2005에서는 스냅샷 격리가 시행되고 있는 데이터의 쓰기 작업이 된 데이터를 tempd에 위치시키고 이를 읽게 하는 방식으로 스냅샷 격리를 수행한다.
물론 기본으로 스냅샷 격리가 해당 서버에서 실행되는 것은 아니다. 이것은 데이터베이스 옵션을 바꾸어서 실행하게 할 수 있다. 예를 들어 Adventure Works라는 데이터베이스에서 스냅샷 격리를 사용하게 하려면 다음과 같이 데이터베이스 옵션을 바꾸면 된다.

Alter Database AdventureWorks Set Allow_Snapshot_Isolation = ON


앞의 문장처럼 하면 데이터베이스 레벨에 적용되는 것이지만 이것을 해당 연결 세션에만 적용하고 싶으면 다음과 같이 해서 적용한다.

Set Transaction Isolaiton Level Snapshot

SQL 서버를 조금만 공부해본 사용자라면 앞의 두 문장의 형태가 눈에 익을 것이다.

SQL 서버 2005 보안

SQL 서버 2005에서 가장 큰 보안상의 변화는 사용자 스키마 분리 기능이다. 데이터베이스에서 하나의 스키마에 두 개의 테이블 이름이 같다면 이것은 절대 같이 있을 수 없다. 하나의 스키마에서 객체는 반드시 이름이 구분되어야만 한다. 예를 들어 SQL 서버에서 테이블을 만들게 되면 자연히 만들 때의 사용자가 소유주가 된다. 대부분 우리는 DBO 사용자 권한으로 테이블을 만들게 되고, 이렇게 되면 dbo가 소유주인 테이블, 즉 dbo.tablename으로 이루어지고 이를 좀 더 구체적으로 서술하면 servername.dbname.dbo.tablename이라는 형태로 구성된다. 이렇게 하면 데이터베이스를 액세스하는 사용자들은 모두 쿼리를 할 때 dbo 스키마를 기본으로 사용한다.



예를 들어 <그림 4>를 보자. 일반적인 사용자들은 dbo 스키마를 기본 스키마로 하기 때문에 Select * From orders라고 질의했을 경우에 자연적으로 Select * From servername.dbname.dbo.table라는 문맥으로 실행된다. 항상 dbo를 기본 스키마로 사용하는 것이다.


Bill(no default schema) SELECT * FROM Orders SELECT * FROM Products Ted(Default schema = sales) SELECT * FROM Orders SELECT * FROM Products dbo products sales Orders

그렇지만 Ted라는 사용자를 생성하여 그 사용자에게 디폴트 스키마로 sales를 정해주면 Ted가 조회하는 스키마는 기본적으로 Select * From orders라고 하면 Select * From servername.dbname. dbo.orders가 된다. 우선은 sales라는 스키마를 만들어서 이 스키마에 테이블을 만들어 주게 된다.

CREATE SCHEMA sales CREATE TABLE orders (OrderID INT, SalesPersonID INT, OrderDate DATETIME) GRANT SELECT ON orders TO Ted

다음은 Ted의 기본 스키마로 sales를 지정하는 구문이다.

ALTER USER Ted WITH DEFAULT_SCHEMA = sales

이렇게 하면 Ted의 기본 스키마는 sales가 되고 Ted는 특별하게 dbo.orders라고 명시하지 않는 이상은 sales.orders를 조회하게 된다.
그리고 SQL 서버 2005에서는 로그인 계정을 생성할 때 윈도우의 계정 정책을 적용할 수 있게 되었다. 이는 이전버전까지의 SQL 로그인이 특별한 계정 암호에 대한 제한이 없어 비교적 간단한 암호를 설정할 수도 있었기 때문에 추가된 기능이다. 예를 들어 Tom이라는 새로운 로그인을 만들어 보자.

CREATE LOGIN [서버name\Tom] FROM WINDOWS WITH DEFAULT_DATABASE = AdventureWorks

앞의 구문을 실행하면 Tom이라는 로그인이 만들어지고, 기본 데이터베이스로 AdventureWorks가 지정된다. 그런 후 만들어진 계정에 암호를 주고, 윈도우에서 사용하는 암호정책을 적용하는 구문을 실행할 수 있다.

ALTER LOGIN Tom WITH PASSWORD = ’P@ssw0rd’, DEFAULT_DATABASE = AdventureWorks, CHECK_EXPIRATION = ON, CHECK_POLICY = ON

이와 같이 실행하면 Tom에게는 윈도우의 암호 만료 기간과 암호정책이 적용되어 사용된다.

비즈니스 인텔리전스 측면

갈수록 요구사항이 증가되는 기업의 BI 솔루션에 대하여 SQL 서버 2000에서는 Analysis 서비스와 리포팅 서비스를 통하여 이에 대한 솔루션을 제공하고 있다. 다음은 SQL 서버 2005에서 제공하는 주요 BI 솔루션 기능이다.

◆ SQL 서버 2005에서 제공하는 주요 BI 솔루션 기능

1. Analysis Services : SQL 서버 2005에서 Anaysis 서비스는 BI 솔루션 개발을 위한 확장된 기능을 제공하고 있다.

2. Business Intelligence development Studio : 새로이 추가된 도구이다. 이 도구를 사용하여 개발자는 큐브, 차원, 마이닝 구조 등의 Analysis 구성요소를 개발할 수 있다.

3. DTS(Data Transformation Services) : 네이티브 코드와 관리되는 코드에 대한 지원이 추가되어 더욱 용이하게 DTS를 디자인할 수 있다.

4. 데이터 마이닝 : 4개의 새로운 마이닝 알고리즘이 추가되어 대용량의 데이터에서 더욱 쉽게 데이터를 마이닝할 수 있게 되었다.

5. 리포팅 서비스 : 리포팅 서비스를 이용하여 OLAP 등의 데이터를 사용자에게 용이하게 리포팅할 수 있는 기능이 추가되었다. 리포팅 서비스는 원래 SQL 서버 2005에 처음 탑재될 예정이었으나 앞당겨서 SQL 서버 2000에서도 사용할 수 있게 출시된 상태다.

6. Clustering Support : Analysis 서비스에서도 장애 복구 클러스터링을 이용하여 고가용성을 보장하게 되었다.



BI 측면에서 SQL 서버 2005의 눈에 띄는 사항을 보면 ‘Business Intelligence Development Studio’라는 새로운 통합 개발 환경을 제공한다는 점이다. 이 도구를 이용하여 BI 개발자는 큐브를 생성하거나 데이터 원본에 대한 뷰, 리포트, 변환 패키지 등을 생성할 수 있게 되었다. 이전 버전에서는 ‘Analysis Services manager’라는 도구에서 단순히 큐브나 차원 등을 생성하고 이를 DTS 등과 통합해서 사용해야만 했었다.
BI Development Studio는 4개의 윈도우를 사용하는데, 먼저 솔루션 탐색기에서 객체에 대한 뷰나 디자인을 볼 수 있게 한다. Tool Box Window는 사용 가능한 BI 프로젝트의 각종 컨트롤 목록을 보게 한다.
그리고 Business Intelligence Development Studio에서는 Analysis 서비스, Data Transformation 서비스, 그리고 리포팅 서비스 등의 3개의 프로젝트를 생성할 수 있게 한다.

“개발과 관리도 컨버전스로 간다”

SQL 서버 2005를 보면 갈수록 개발자와 DBA가 통합되어 가고 있다는 점을 느낄 수 있다. 예를 들면 Business Intellience Studio라든지 SQL 서버 매니지먼트 스튜디오라는 통합도구만 봐도 그렇다. 마치 2002 월드컵 당시에 히딩크가 중시했던 멀티플레이어의 존재가 SQL 서버에서도 중시된다는 생각이 든다. 개발자와 관리자가 따로 담당하던 영역이 갈수록 융합되고 개발과 관리를 다 전문적인 지식으로 무장해야 SQL 서버를 제대로 다룰 수 있기 때문이다.
이는 필자가 생각하기에 요즘의 IT 흐름과 일맥상통한다. 가전제품에서만 디지털 컨버전스가 이루어지고 있는 것이 아니라, IT 엔지니어들도 개발과 관리를 아우르는 컨버전스를 해야 하는 시대이기 때문이다. IT 컨버전스 시대에 우리도 해야 할 게 갈수록 늘어나고 있다는 것은 기뻐해야 할 일인지 슬퍼해야 할 일인지 모르지만, 이번 기사를 본 독자들은 하나는 느낄 수 있을 것이다. “닷넷을 해야 되겠군”
필수는 아니지만 SQL 서버 2005를 파워풀하게 사용하기 위해서는 어쩔 수 없는 일이 되었다. 누구의 말처럼 SQL 서버 2005를 스포츠카에 비유한다면 닷넷을 사용하지 않으면 스포츠카를 타고 시속 60km로 달리는 것과 마찬가지이기 때문이다.


제공 : DB포탈사이트 DBguide.net

출처 : 마이크로소프트웨어 [2004년 12월호]