데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

FILE GROUP 간 데이터 이동

jevida(강성욱) 2016.09.13 06:48 Views : 1601

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'

 

 

 

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



강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1693 SQL Server Net Libraries jevida(강성욱) 2016.09.13 1109
1692 SQL Server 별칭 설정 및 사용 jevida(강성욱) 2016.09.13 2656
1691 SQL Server에 연결된 어플리케이션 드라이버 버전 확인 jevida(강성욱) 2016.09.13 884
1690 SQL Server 성능 대시보드 jevida(강성욱) 2016.09.13 2219
1689 SSMS를 이용한 데이터베이스 복사 하기 jevida(강성욱) 2016.09.13 11622
1688 SQL Server에서 DB2 링크드 서버 연결 방법 jevida(강성욱) 2016.09.13 3293
1687 SQL Server 인덱스 활성 / 비활성 하기 jevida(강성욱) 2016.09.13 2363
1686 파티션 오프셋과 SQL Server에 대한 디스크 할당 단위 크기 jevida(강성욱) 2016.09.13 1688
1685 Read the End of a Large Error Log (에러로그 끝 부분 읽기) jevida(강성욱) 2016.09.13 1299
1684 데이터베이스 여유공간 확인 jevida(강성욱) 2016.09.13 1445
» FILE GROUP 간 데이터 이동 jevida(강성욱) 2016.09.13 1601
1682 문서화 되지 않은 sys.fn_PhysLocFormatter 함수 jevida(강성욱) 2016.09.13 949
1681 SQL Server 관리자 전용 연결(DAC) jevida(강성욱) 2016.09.13 3397
1680 파일 접근 권한으로 인한 DB복원 실패 작업 해결하기 jevida(강성욱) 2016.09.13 1940
1679 DBCC CHECKDB 실행과 히스토리 관리 jevida(강성욱) 2016.09.13 1847
1678 MDF를 이용한 데이터베이스 복원 jevida(강성욱) 2016.09.13 1175
1677 EventID 3041 Error jevida(강성욱) 2016.09.13 1421
1676 데이터베이스 미러 백업 jevida(강성욱) 2016.09.13 1313
1675 SNAPSHOT을 이용한 SQL Server 복원 jevida(강성욱) 2016.09.13 2322
1674 복사 전용 백업(COPY ONLY BACKUP) jevida(강성욱) 2016.09.13 3394





XE Login