본문 바로가기
IT/DB

MSSQL 2005 - 파일그룹 생성 SQL 예제

by 불멸남생 2024. 7. 31.

파일그룹이란?

파일그룹(Filegroup)은 여러 데이터 파일을 하나의 논리적 그룹으로 묶는 기능을 제공합니다. 파일그룹을 사용하면 데이터베이스 파일의 물리적 배치를 제어하고, 데이터베이스의 성능을 향상시키며, 백업 및 복구 전략을 효율적으로 관리할 수 있습니다. MSSQL 2005에서는 기본적으로 PRIMARY 파일그룹이 존재하며, 추가로 SECONDARY 파일그룹을 생성할 수 있습니다.

MSSQL 2005에서 파일그룹 생성하기

데이터베이스 생성 및 파일그룹 설정

아래의 SQL 예제는 MSSQL 2005에서 새로운 데이터베이스를 생성하고, PRIMARY 및 SECONDARY 파일그룹을 설정하는 방법을 보여줍니다.

반응형
CREATE DATABASE [brainDB]
ON PRIMARY    -- 지금부터 PRIMARY 그룹에 데이터 파일을 생성
(NAME = 'BrainDB',   -- 첫번째 데이터 파일
 FILENAME = N'D:\DBData\brainDB.mdf', -- 파일경로와 파일명
 SIZE = 10240KB, -- 처음 크기
 MAXSIZE = 102400KB, --  최대파일 크기
 FILEGROWTH = 1024KB), -- 파일 증가 : 1MB 단위로
(NAME = 'BrainDB2',   -- 두번째 데이터 파일
 FILENAME = N'D:\DBData\brainDB2.ndf', -- 파일경로와 파일명
 SIZE = 10240KB, -- 처음 크기
 MAXSIZE = 102400KB, --  최대파일 크기
 FILEGROWTH = 1024KB) -- 파일 증가 : 1MB 단위로

FILEGROUP [SECONDARY] -- 지금부터 SECONDARY 그룹에 데이터 파일을 생성
(NAME = 'BrainDB3',   -- 세번째 데이터 파일
 FILENAME = N'E:\DBData\brainDB3.ndf', -- 파일경로와 파일명
 SIZE = 5120KB, -- 처음 크기
 MAXSIZE = 51200KB, --  최대파일 크기
 FILEGROWTH = 1024KB), -- 파일 증가 : 1MB 단위로
(NAME = 'BrainDB4',   -- 네번째 데이터 파일
 FILENAME = N'D:\DBData\brainDB4.ndf', -- 파일경로와 파일명
 SIZE = 5120KB, -- 처음 크기
 MAXSIZE = 51200KB, --  최대파일 크기
 FILEGROWTH = 1024KB) -- 파일 증가 : 1MB 단위로

LOG ON -- 지금부터 로그파일을 생성
(NAME = 'BrainDB_Log',   -- 로그파일
 FILENAME = N'F:\DBLog\brainDB_log.ldf', -- 파일경로와 파일명
 SIZE = 3072KB, -- 처음 크기
 MAXSIZE = 102400KB, --  최대파일 크기
 FILEGROWTH = 10%) -- 파일 증가 : 10% 단위로

위 예제에서는 두 개의 파일그룹(PRIMARY, SECONDARY)과 각각의 파일그룹에 속하는 여러 데이터 파일을 생성하고 설정하는 방법을 보여줍니다. PRIMARY 파일그룹에는 brainDB.mdf와 brainDB2.ndf 파일이 포함되고, SECONDARY 파일그룹에는 brainDB3.ndf와 brainDB4.ndf 파일이 포함됩니다. 로그 파일은 brainDB_log.ldf로 생성됩니다.

반응형

파일그룹 활용의 장점

성능 향상

파일그룹을 사용하면 데이터 파일을 여러 디스크에 분산시켜 I/O 성능을 향상시킬 수 있습니다. 예를 들어, 자주 접근하는 테이블을 별도의 파일그룹에 저장하고, 해당 파일그룹을 빠른 디스크에 배치함으로써 성능을 극대화할 수 있습니다.

데이터 관리의 유연성

파일그룹을 사용하면 데이터베이스의 논리적 구조를 더욱 유연하게 관리할 수 있습니다. 데이터 파일을 필요에 따라 추가하거나 제거할 수 있으며, 데이터베이스의 크기와 성능 요구 사항에 따라 파일그룹을 동적으로 조정할 수 있습니다.

백업 및 복구 전략의 효율성

파일그룹 단위로 백업 및 복구를 수행하면 데이터베이스 관리가 더욱 효율적입니다. 예를 들어, 변경되지 않은 파일그룹을 제외하고, 변경된 파일그룹만 백업함으로써 백업 시간을 단축할 수 있습니다. 또한, 파일그룹별로 복구를 수행하여 데이터베이스 복구 시간을 최소화할 수 있습니다.

반응형

파일그룹 관리 SQL 예제

파일그룹에 테이블 생성

파일그룹을 생성한 후, 특정 파일그룹에 테이블을 생성할 수 있습니다. 아래 예제는 SECONDARY 파일그룹에 테이블을 생성하는 방법을 보여줍니다.

CREATE TABLE [dbo].[ExampleTable]
(
    [ID] INT NOT NULL,
    [Name] NVARCHAR(100) NOT NULL,
    CONSTRAINT [PK_ExampleTable] PRIMARY KEY CLUSTERED ([ID])
) ON [SECONDARY]

위 예제에서는 ExampleTable 테이블이 SECONDARY 파일그룹에 생성됩니다. 이로써 테이블의 데이터가 SECONDARY 파일그룹의 데이터 파일에 저장됩니다.

반응형

파일그룹에 인덱스 생성

특정 파일그룹에 인덱스를 생성하여 데이터베이스 성능을 최적화할 수 있습니다. 아래 예제는 PRIMARY 파일그룹에 인덱스를 생성하는 방법을 보여줍니다.

CREATE NONCLUSTERED INDEX [IX_ExampleTable_Name]
ON [dbo].[ExampleTable]([Name])
WITH (SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF, ONLINE = ON)
ON [PRIMARY]

위 예제에서는 ExampleTable 테이블의 Name 열에 비클러스터 인덱스가 PRIMARY 파일그룹에 생성됩니다.

반응형

파일그룹 모니터링 및 관리

파일그룹 상태 확인

현재 데이터베이스의 파일그룹 상태를 확인하려면 아래 SQL 쿼리를 사용할 수 있습니다.

SELECT 
    df.name AS [File Name],
    df.type_desc AS [File Type],
    fg.name AS [Filegroup Name],
    df.size * 8 / 1024 AS [Size (MB)],
    df.max_size * 8 / 1024 AS [Max Size (MB)],
    df.growth * 8 / 1024 AS [Growth (MB)]
FROM 
    sys.database_files df
JOIN 
    sys.filegroups fg ON df.data_space_id = fg.data_space_id
ORDER BY 
    fg.name, df.name;

위 쿼리는 데이터베이스의 각 파일에 대한 파일 이름, 파일 유형, 파일그룹 이름, 크기, 최대 크기, 성장 크기 등의 정보를 반환합니다.

반응형

파일그룹 크기 조정

데이터베이스 파일의 크기를 조정하려면 ALTER DATABASE 문을 사용합니다. 아래 예제는 brainDB4.ndf 파일의 크기를 조정하는 방법을 보여줍니다.

ALTER DATABASE [brainDB]
MODIFY FILE
(
    NAME = 'brainDB4',
    SIZE = 10240KB, -- 새로운 크기
    MAXSIZE = 204800KB, -- 최대 크기
    FILEGROWTH = 2048KB -- 파일 증가 단위
);

위 예제에서는 brainDB4.ndf 파일의 크기를 10MB로 설정하고, 최대 크기를 200MB로 늘리며, 파일 증가 단위를 2MB로 조정합니다.

반응형

결론

MSSQL 2005에서 파일그룹을 효과적으로 활용하면 데이터베이스 성능을 최적화하고 관리 효율성을 높일 수 있습니다. 파일그룹을 생성하고 관리하는 다양한 방법을 이해하고 적용함으로써 데이터베이스 관리에 큰 도움이 될 것입니다.

반응형