[MSSQL] 데이터베이스 파일그룹 정리

728x90

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가 수행되기 때문에 성능이 향상됩니다.
트랜잭션 로그 파일은 다른 파일 및 파일 그룹과 동일한 물리적 디스크에 배치하지 마세요.

● 참고

https://docs.microsoft.com/ko-kr/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver15

데이터베이스 파일 및 파일 그룹 - SQL Server

데이터베이스 파일에 대해 알아보고 할당 및 관리를 위해 SQL Server에서 파일 그룹을 만드는 방법을 알아봅니다. 예제, 규칙, 권장 사항을 살펴봅니다.

docs.microsoft.com