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)를 이용하여 보다 빠른 쿼리를 수행할 수도 있다. 쿼리를 컴파일하는데 있어서도 분할된 뷰에서는 테이블이 많을수록 느렸지만, 테이블 파티셔닝에서는 파티션 개수에 상관없이 빠른 속도를 보장한다.
<그림 1> 테이블 파티셔닝
테이블 파티셔닝은 파티셔닝 함수와 스키마를 이용하여 구현을 한다. 파티셔닝 함수로는 경계 영역을 구분하고 스키마로는 실제 물리적인 파일 그룹에 각 파티션을 맵핑한다.
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>와 같다.
<그림 2> 초기 상태
TransactionHistory에는 12개의 파티션이 있고 TransactionHistoryArchive에는 2개의 파티션이 있다. TransactionHistoryArchive는 자주 사용하지 않는 데이터를 모아 두는 곳이므로 두 개의 파티션만을 만들었다. 여기에서 TransactionHistory 테이블에서 2004년도 9월 데이터를 위한 새로운 데이터를 위한 공간을 확보하자.
ALTER PARTITION FUNCTION TransactionRangePF1() SPLIT RANGE ('9/01/2004');
<그림 3> TransactionHistory에 2004년도 9월 1일로 분할
새로운 공간을 확보하기 위하여 기존 공간을 분할하여 총 13개의 파티션을 만들었다. 이제는 TransactionHistoryArchive에도 역시 새로운 공간을 확보하자.
ALTER PARTITION FUNCTION TransactionArchivePF2() SPLIT RANGE ('10/01/2003');
<그림 4> TransactionHistoryArchive에 2003년도 10월 1일로 분할
각각의 테이블에 새로운 공간을 할당하였으니 이제 파티션을 옮겨 보자.
ALTER TABLE [Production].[TransactionHistory] SWITCH PARTITION 1 to [Production].[TransactionHistoryArchive] PARTITION 2;
<그림 5> TransactionHistory의 파티션 1을 TransactionHistoryArchive의 파티션 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-05-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 |
| 65536 |
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');
<그림 6> TransactionHistory에서 2003년 10월 1일 병합
병합을 한 후 다시 테이블 파티션 정보를 보자.
| 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개의 행이 생겼다. 이제 TransactionHistoryArchive도 병합을 하자.
ALTER PARTITION FUNCTION TransactionArchivePF2() MERGE RANGE ('9/01/2003');
<그림 7> TransactionHistoryArchive에서 2003년 9월 1일 병합
이렇게 함으로써 슬라이딩 윈도우 작업을 완료할 수 있다. 작업이 간단하지는 않지만 이런 일련의 작업들이 실제로는 메타 데이터를 가지고 작업을 하기 때문에 상당히 빠르게 수행된다.
<그림 8> 슬라이딩 윈도우 구현이 완료된 상태
간편한 이력 관리를 위한 데이터베이스 스냅샷
SQL 서버 2005에서는 간단하게 데이터베이스에 대한 백업본을 만들 수 있다. 보통 개발자가 어떤 간단한 작업을 할 때 실수를 할까봐 트랜잭션을 걸고 작업을 종종 한다. 그러다가 실수를 하면 롤백하면 되기 때문이다. 그런데 이렇게 작업을 하면 잠금이 걸리기 때문에 다른 사용자들은 대기하고 기다려야 하는 불편이 있다. 하지만 이제는 데이터베이스 스냅샷을 사용하면 현재 데이터베이스의 내용을 간단하게 백업을 할 수 있기 때문에 트랜잭션을 걸지 않아도 된다. 만약 실수를 하게 되면 간단하게 복구를 할 수 있다.
스냅샷은 읽기만 할 수 있는 데이터베이스이다. 만들 때에는 실제 데이터의 복사본을 만드는 것이 아니고 메타 데이터만으로 만들기 때문에 상당히 빠르고 적은 용량으로 만들 수 있다. 실제 구현을 보면 먼저 스냅샷은 현재 데이터베이스와 동일한 저장 공간을 예약하고 원본 데이터베이스에서 변경이 일어나면 먼저 스냅샷 데이터베이스에 복사를 한 후 원본 데이터베이스를 변경한다. 이를 복사-쓰기(copy-on-write) 기술이라고 부른다.
<그림 9> 복사-쓰기 기술
<그림 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,34라는 값을 넣고 Test_01이라는 Test 데이터베이스의 스냅샷을 만들었다. 앞에서 만든 Test_01.ss 라는 파일의 실제 크기를 보면 다음과 같다.
<화면 3> Test_01.ss의 파일 크기
크기는 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>은 미러링의 동작 방법이다.
<그림 10> 미러링 동작 방법
미러링은 데이터 자체를 서로 전송하는 것이 아니라 로그만을 서로 전달한다. 애플리케이션으로부터 데이터 수정 작업이 들어오면 이를 먼저 로그에 기록한 다음 미러 서버에게도 그 로그 정보를 전달하여 미러 데이터베이스에도 동일 정보를 유지하도록 해준다. 이러한 동작은 감시 서버(witness server)가 계속 감시하고 있다가 만약 주 서버가 다운이 되면 바로 미러 서버를 주 서버로 바꾸어 동작하게 한다. 그동안 애플리케이션은 별도의 프로그램 수정 없이도 자동으로 미러 서버를 주 서버로 간주하여 접속을 유지한다. 그럼 직접 실습을 해보자.
원래는 제대로 된 실습을 위해서는 주 서버(Principal Server), 미러 서버(Mirror Server), 감시 서버(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를 기존으로 살펴보았고 앞으로는 베타3도 나와야 하고 정식버전도 나와야 한다. 앞으로 새로운 버전이 나오면서 또 어떻게 바뀔지는 모르겠다. 하지만 베타2 정도면 어느 정도 SQL 서버 2005에서 구현하려는 핵심 기능은 대부분 들어 있다고 봐도 될 것이다.
그 핵심 기능을 요약해 보면 개발자의 관점에서는 닷넷과의 통함이 될 수 있을 것이고, 관리자의 관점에서는 향상된 가용성(availability)이라고 할 수 있을 것이다. 이제 올 하반기에 나올 정식 SQL 서버 2005를 기다리면서 또 다른 항해를 준비하려고 한다.
출처 : 마이크로소프트웨어 2005년 5월호
댓글을 달아 주세요