[MSSQL] 데이터베이스 파일그룹 정리
SQL Server 데이터베이스에는 2가지의 데이터 파일과 로그 파일 총 3가지의 파일로 데이터베이스를 운영합니다.
데이터 파일 : 테이블, 인덱스, 저장 프로시저 및 뷰 등의 개체와 데이터를 저장합니다.
로그 파일 : 데이터베이스의 모든 트랜잭션을 복구하는 데 필요한 정보를 저장합니다. 로그 파일은 트랜잭션 로그 파일이라고 흔히 부릅니다.
데이터 파일은 할당 및 관리를 간편하게 수행하기 위해 파일 그룹으로 그룹화하여 관리 할 수 있습니다.
● 데이터베이스 파일
| 파일 | 설명 |
| Primary File (.mdf) | 데이터베이스의 시작 정보를 포함하며 데이터베이스의 나머지 파일을 관리합니다. 모든 데이터베이스에는 Primary File이 있습니다. 데이터 파일 확장명은 .mdf입니다. |
| Secondary File (.ndf) | 선택적 사용자 정의 데이터 파일입니다. 각 파일을 서로 다른 디스크 드라이브에 배치하여 데이터를 여러 디스크에 분산 시킬 수 있습니다. 데이터 파일 확장명은 .ndf입니다. |
| Transaction Log File (.ldf) | 이 로그에는 데이터베이스 복구에 사용되는 정보가 저장됩니다. 데이터베이스마다 최소한 하나의 트랙잭션 로그 파일이 필요합니다. 로그 파일 확장명은 .ldf입니다. |
기본적으로 데이터와 트랜잭션 로그는 단일 디스크 시스템을 처리하기 위해 동일한 드라이브와 경로에 생성이 됩니다.
이 선택은 실제 서비스 환경에서는 최적이 아닐 수도 있다고 합니다. MS에서 권장하는 방법은 데이터와 로그 파일은 서로 다른 디스크에 배치하는 것을 권장합니다. ( 다중 I/O )
● 파일 그룹
아래의 SQL문은 "MyDB"데이터베이스를 생성하는 SQL이다.
데이터베이스에는 Primary, Secondary, Log 파일이 존재한다고 위에서 언급했다. 아래 SQL문은 "MyDB"데이터베이스에
하나의 주 데이터 파일, MyDB_FG1 파일그룹에는 보조 데이터 파일이 두개, 하나의 트랜잭션 로그 파일로 이루어지도록 데이터베이스를 만드는 SQL문이다.
USE master;
GO
-- Create the database with the default data
-- filegroup, filestream filegroup and a log file. Specify the
-- growth increment and the max size for the
-- primary data file.
CREATE DATABASE MyDB
ON PRIMARY
( NAME='MyDB_Primary',
FILENAME=
'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_Prm.mdf',
SIZE=4MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
FILEGROUP MyDB_FG1
( NAME = 'MyDB_FG1_Dat1',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_1.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
( NAME = 'MyDB_FG1_Dat2',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB_FG1_2.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM
( NAME = 'MyDB_FG_FS',
FILENAME = 'c:\Data\filestream1')
LOG ON
( NAME='MyDB_log',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\MyDB.ldf',
SIZE=1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB);
GO
ALTER DATABASE MyDB
MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO
-- Create a table in the user-defined filegroup.
USE MyDB;
CREATE TABLE MyTable
( cola int PRIMARY KEY,
colb char(8) )
ON MyDB_FG1;
GO
-- Create a table in the filestream filegroup
CREATE TABLE MyFSTable
(
cola int PRIMARY KEY,
colb VARBINARY(MAX) FILESTREAM NULL
)
GO아래 그림에서는 위 예제의 결과를 요약하여 사진으로 나타낸 것이다.
● 파일 및 파일 그룹 채우기 전략
파일 그룹은 각 파일 그룹에 있는 모든 파일에 대해 "균형 잡힌 채우기 전략"을 사용합니다.
파일 그룹에 데이터를 쓸 때 SQL Server 데이터베이스 엔진은 첫 번째 파일이 꽉 찰 때까지 모든 데이터를 쓰지 않고, 파일 그룹 내의 각 파일에 해당 파일의 사용 가능한 공간에 비례하는 양을 씁니다.
예를 들어 F1 파일에는 100MB의 빈 공간이 있고, F2 파일에는 200MB의 빈 공간이 있다면, F1파일은 F2파일보다 2배의 익스텐트(저장단위)가 제공되어 F2파일에 F1파일보다 약 2배의 데이터를 사용합니다. 이렇게 하면 거의 동시에 두 파일이 꽉 차며 간단한 스트라이프가 수행됩니다.
자동 증가 전략으로는 파일 그룹이 3개의 파일로 구성되고 모두 자동 증가하도록 설정되었다고 가정하면, 파일 그룹에 속한 모든 파일의 공간이 꽉 차면 첫 번째 파일만 확장됩니다. 첫 번째 파일이 꽉 차고 파이 ㄹ그룹에 더 이상 데이터를 쓸 수 없게 되면 두 번째 파일이 확장됩니다. 두 번째 파일이 꽉 차고 파일 그룹에 더 이상 데이터를 쓸 수 없게 되면 세번째 파일이 확장됩니다. 세 번째 파일이 꽉 차고 파일 그룹에 더 이상 데이터를 쓸 수 없게 되면 첫 번째 파일이 다시 확장되는 됩니다.
● 파일 및 파일 그룹 디자인 규칙
- 파일이나 파일 그룹은 둘 이상의 데이터베이스에서 사용할 수 없습니다. ( MyDB 데이터베이스의 데이터와 개체가 있는 MyDB.mdf or MyDB.ndf 파일은 다른 데이터베이스에서 사용할 수 없습니다. )
- 파일은 한 파일 그룹의 멤버만 될 수 있습니다.
- 트랜잭션 로그 파일은 파일 그룹의 일부가 될 수 없습니다.
● SQL Server 권장 사항
- 데이터베이스는 기본적으로 하나의 주 데이터 파일(mdf)과 하나의 트랜잭션 로그 파일(ldf)만 있으면 됩니다.
- 여러 데이터 파일을 사용하는 경우 추가 파일에 대한 두 번째 파일 그룹을 만들고 해당 파일 그룹을 기본 파일 그룹으로 만들어야 합니다. 이렇게 하면 주 파일에는 시스템 테이블과 개체만 있게 됩니다.
- 성능을 극대화하려면 가능한 여러 개의 사용 가능한 디스크에 파일이나 파일 그룹을 만듭니다. 디스크 공간이 많이 필요한 개체는 여러 파일 그룹에 배치합니다.
- 특정 물리적 디스크에 개체를 배치할 수 있도록 파일 그룹을 사용합니다.
- 동일한 조인 쿼리에서 사용되는 여러 테이블은 여러 파일 그룹에 배치합니다. 조인된 데이터는 병렬 디스크 I/O를 사용하기 때문에 성능이 향상됩니다.
- 자주 액세스되는 테이블과 해당 테이블에 속한 비클러스터형 인덱스는 여러 파일 그룹에 배치합니다. 여러 파일 그룹을 사용하면 파일이 여러 물리적 디스크에 있을 경우 병렬 I/O가 수행되기 때문에 성능이 향상됩니다.
- 트랜잭션 로그 파일은 다른 파일 및 파일 그룹과 동일한 물리적 디스크에 배치하지 마세요.
● 참고
데이터베이스 파일 및 파일 그룹 - SQL Server
데이터베이스 파일에 대해 알아보고 할당 및 관리를 위해 SQL Server에서 파일 그룹을 만드는 방법을 알아봅니다. 예제, 규칙, 권장 사항을 살펴봅니다.
docs.microsoft.com