FILE GROUP 간 데이터 이동

FILE GROUP 간 데이터 이동

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

비즈니스 환경에 따라 별도의 파일 그룹에 대형 테이블 또는 일부 테이블을 보관하고 계획에 따라 파일 그룹을 읽기 전용으로 사용하는 경우가 있다. 이 때 별도의 파일그룹에 데이터를 이동하는 방법은 무엇이 있는지 알아 보자.

 

일반적으로 파일 그룹간에 데이터파일은 이동할 수 없다. 클러스터 인덱스를 통하여 파일그룹간의 데이터를 이동 할 수 있다.

클러스터형 인덱스의 리프 수준과 데이터 페이지는 정의가 동일하므로, 클러스터형 인덱스를 만들고 ON partition_scheme_name 또는 ON filegroup_name 절을 사용하여 효과적으로 테이블을 만든 파일 그룹의 테이블을 새 파티션 구성표 또는 파일 그룹으로 이동합니다. http://msdn.microsoft.com/ko-kr/library/ms188783.aspx

 

실습을 위하여 데이터베이스를 생성 한다. 이때 파일 그룹을 추가하여 생성 한다.

CREATE DATABASE SW_TEST ON PRIMARY (NAME = 'SW_TEST', FILENAME = 'D:\SQL_DATA\SW_TEST.MDF'), FILEGROUP SECOND (NAME ='SW_TEST2', FILENAME = 'D:\SQL_DATA\SW_TEST2.NDF') LOG ON (NAME ='SW_TEST_LOG', FILENAME = 'D:\SQL_DATA\SW_TEST_LDF.NDF') GO   SP_HELPDB SW_TEST

 

 

 

테이블을 만들고 클러스터 인덱스를 생성한다. 기본값인 Primary 그룹에 테이블 및 인덱스가 생성된 것을 확인 할 수 있다.

CREATE TABLE TBL_A ( COL1 INT IDENTITY, COL2 INT ) GO   CREATE CLUSTERED INDEX CI_TBL_A_COL1 ON TBL_A(COL1) GO   SP_HELPINDEX TBL_A

 

 

 

SSMS를 이용하여 인덱스 그룹을 변경함으로써 데이터 파일의 그룹을 이동하자. [테이블] – [인덱스] 에서 속성을 선택 한다.

 

 

[인덱스 속성]에서 [저장소]를 선택하고 파일그룹에서 이동할 파일 그룹을 선택 한다. (실습에서는 SECOND 파일그룹 선택)

 

 

파일 그룹 이동 후 인덱스 정보를 살펴 보면 다음과 같이 파일 그룹이 SECOND로 변경된 것을 확인 할 수 있다.

 

 

스크립트를 이용한 방법을 알아 보자. 아래 스크립트를 이용하여 클러스터 인덱스 생성시 [SECOND]그룹에 생성하도록 옵션을 지정하였다. 이때 동일한 이름의 인덱스가 있는 경우 인덱스를 삭제하고 새로 생성하도록 옵션을 지정하였다. 또한 시스템의 부하를 최소화 하기 위하여 [ONLINE]옵션을 함께 주었다.

CREATE CLUSTERED INDEX CI_TBL_A_COL1 ON TBL_A(COL1) WITH (DROP_EXISTING=ON, ONLINE = ON) ON [SECOND] GO   SP_HELPINDEX TBL_A

 

 

 

클러스터 인덱스를 이용한 데이터 파일그룹 이동시 왜 시스템 부하가 일어 날까? 프로파일러를 확인해 보면 스크립트를 이용했을 때와 같이 인덱스를 삭제하고 다른 파일 그룹에 인덱스를 새로 생성하는 것을 확인 할 수 있다.

 

 

테이블에서 고유키 또는 기본키 생성시 파일그룹을 지정하여 생성 할 수 있다.

CREATE TABLE TBL_B( COL1 INT CONSTRAINT PK_TBL_B_COL1 PRIMARY KEY, COL2 INT) ON SECOND GO   SP_HELPINDEX TBL_B

 

 

 

인덱스가 어떤 파일 그룹에 있는지 확인 하는 스크립트를 만들어 보았다. 이를 통하여 한눈에 인덱스에 대한 파일 그룹을 확인하여 운영 할 수 있다.

SELECT     OBJECT_NAME(B.OBJECT_ID) AS TBL_NAME,     B.NAME AS INDEX_NAME,     B.TYPE_DESC AS INDEX_TYPE,     A.NAME AS FG_NAME,     C.TYPE AS OBJ_TYPE,     A.TYPE,     A.TYPE_DESC FROM SYS.FILEGROUPS AS A     INNER JOIN SYS.INDEXES AS B         ON A.DATA_SPACE_ID = B.DATA_SPACE_ID     INNER JOIN SYS.ALL_OBJECTS AS C         ON B.OBJECT_ID = C.OBJECT_ID WHERE C.TYPE = 'U' --AND OBJECT_NAME(B.OBJECT_ID) = 'TBL_A'

 

 

 

효율적인 파일그룹 관리를 통하여 효율적인 서비스가 되도록 전략을 구성하자.

 

2012-11-26 / 강성욱 / http://sqlmvp.kr