파일 및 파일 그룹 아키텍처

파일 및 파일 그룹 아키텍처

 

• Version : All Version

 

SQL Server에서 데이터베이스는 운영체제의 파일 시스템에 종속 적이다. 데이터와 로그는 같은 파일에 저장되지 않으며 각 파일은 한 데이터베이스에서만 사용 된다. 파일 그룹은 명명된 파일 모음이며 백업 및 복원 작업 등의 데이터 배치 및 관리 태스크에 도움이 된다.

 

[데이터베이스 파일]

데이터베이스 파일의 종류에는 3가지의 유형이 있다.

• 주 데이터 파일 : 데이터베이스의 기본 파일 이며 모든 데이터베이스에는 하나의 주 데이터 파일이 있다. 주로 사용되는 확장자는 .mdf 이다.
• 보조 데이터 파일 : 주 데이터 파일이 아닌 모든 데이터 파일을 구성. 데이터베이스에 따라 보조 데이터파일을 사용하지 않거나 여러 개 있을 수도 있다. 주로 사용되는 확장자는 .ndf 이다.
• 로그 파일 : 데이터베이스를 복구하는데 사용되는 모든 로그 정보가 들어 있다. 최소 1개 이상의 로그 파일을 가지며 주로 사용되는 확장자는 .ldf 이다.

 

SQL Server에서 데이터베이스의 모든 파일 위치는 데이터베이스의 주 파일과 master 데이터베이스에 기록 된다. SQL Server는 master 데이터베이스의 파일 위치 정보를 사용한다. (따라서 master 데이터베이스를 주기적으로 백업하여 보관하여야 한다.) 그러나 아래의 경우에는 주 파일의 위치 정보를 사용하여 master 데이터베이스의 파일 위치 항목을 초기화 환다.

• CREATE DATABASE시 FOR ATTACH 또는 FOR ATTACH_REBUILLD_LOG 옵션으로 연결하는 경우
• SQL Server 2000 이나 7.0에서 업그레이드 하는 경우
• Master 데이터베이스를 복원 하는 경우

 

[logical_file_name / os_file_name]

• Logical_file_name : 모든 T-SQL문에서 물리적 파일을 참조하는데 사용되는 이름. 논리적 파일이름은 SQL Server 식별자 규칙을 따라야 하며 데이터베이스의 논리적 파일 이름 사이에서 고유 해야한다.
• Os_file_name : 디렉터리 경로를 포함하는 물리적인 파일 이름. 운영체제의 파일 규칙에 따라야 한다. 읽기/쓰기데이터 파일 그룹과 로그 파일은 NTFS 압축 파일 시스템에 배치 할 수 없다.

 

 

[데이터 파일 페이지]

SQL Server의 데이터 파일은 0부터 시작하여 순차적으로 번호가 할당 된다. 데이터베이스 파일마다 고유한 파일 ID 번호가 있다. 데이터베이스에서 페이지를 고유하게 식별하려면 해당 파일 ID와 페이지 번호 모두 필요하다.

 

다음 그림은 4MB의 주 데이터 파일과 1MB의 보조 데이터 파일이 있는 데이터베이스의 페이지 번호를 보여 준다. 각 파일의 첫 페이지는 특성에 대한 정보를 포함하는 파일 헤더 페이지이다. 또한 각 페이지마다 할당 맵 정보 등이 포함 된다. 주 데이터파일과 첫 번째 로그 파일에 모두 저장되는 시스템 페이지중 하나는 데이터베이스의 특성에 대한 정보를 포함하는 데이터 부팅 페이지 이다. 자세한 내용은 아래 링크에서 확인 하기 바란다.

• 페이지 및 익스텐트 이해 : http://sqlmvp.kr/140186753568

 

 

[파일 크기]

SQL Server에서 파일은 지정 된 크기부터 자동으로 증가 할 수 있다. 한 파일 그룹에 여러 개의 파일이 있을 경우 모든 파일이 가득 찰 때 까지는 자동으로 증가 하지 않는다. 이 경우 라운드로빈 방식으로 증가 한다. 각 파일의 최대 크기는 OS에서 지원하는 모든 공간을 사용한다.

 

[데이터베이스 스냅숏 파일]

• 사용자 스냅숏 : 사용자가 만든 데이터베이스 스냅숏은 하나 이상의 스파스 파일에 데이터를 저장한다. 스파스 파일 기술은 NTFS 파일 시스템의 기능이다. 처음에는 스파스 공간이 할당되어 있지 않다. 사용자가 스냅숏을 생성하면 스파스 파일을 사용한다. 자세한 내용은 스냅숏에서 다루기로 한다.
• 데이터베이스 스냅숏 : DBCC 명령에 의해 내부적으로 사용된다. DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, DBCC CHECKFILEGROUP 등이 있다. 내부 데이터베이스 스냅숏은 원래 데이터베이스 파일의 스파스 대체 데이터 스트림을 사용한다. 스파스 파일과 마찬가지로 대체 데이터 스트림은 NTFS 파일 시스템의 기능이다. 스파스 대체 데이터 스트림을 사용하면 파일 크기나 볼륨 통계에 영향을 주지 않고 여러 데이터 할당을 하나의 파일 또는 폴더와 연결 할 수 있다.

 

[데이터베이스 파일 그룹]

할당 및 관리를 위해 파일 그룹에서 데이터베이스 개체와 파일을 함께 그룹화 할 수 있다.

주 파일 그룹 : 데이터 파일과 다른 파일 그룹에 특별히 할당되지 않은 기타 파일을 포함한다. 시스템 테이블의 모든 페이지는 주 파일 그룹에 할당 된다.

사용자 정의 그룹 : 사용자가 생성한 파일 그룹이다. FILEGORUP 키워드를 사용하여 추가 한다. 로그 파일은 파일 그룹에 포함되지 않는다. 로그 공간은 데이터 공간과 별도로 관리 된다. 한 파일이 두 개 이상의 파일 그룹에 포함 될 수 없다. 테이블 및 인덱스를 특정 파일 그룹에 연결 할 수 있다. 이 경우 관련 된 페이지가 해당 그룹에 할당되거나 테이블과 인덱스를 분할 할 수 있다. 분할 된 테이블과 인덱스의 데이터는 데이터베이스의 개별 파일 그룹에 각각 배치할 수 있는 단위로 나누어 진다. 각 데이터베이스에서 한 파일 그룹이 기본 파일 그룹으로 지정된다. 파일 그룹을 지정하지 않고 테이블이나 인덱스를 만들면 기본 파일 그룹에 모든 페이지를 할당하는 것으로 간주 된다. 기본 파일 그룹을 지정하지 않으면 주 파일 그룹이 기본 파일 그룹이 된다.

 

아래 그림은 파일 그룹이 할당 된 예시 이다.

USE master; GO   CREATE DATABASE MyDB ON PRIMARY ( NAME='MyDB_Primary', FILENAME= 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\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\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_FG1_1.ndf', SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB), ( NAME = 'MyDB_FG1_Dat2', FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_FG1_2.ndf', SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB) LOG ON ( NAME='MyDB_log', FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\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

 

 

 

[SSMS에서 파일 추가]

[데이터베이스 속성] - [파일] 탭에서 추가 버튼을 이용하여 파일을 추가한다.

 

 

[SSMS에서 파일 그룹 추가 및 기본값 수정]

[데이터베이스 속성] – [파일 그룹] 탭에서 추가 버튼을 클릭하여 파일 그룹을 추가 한다. 파일 그룹의 기본 값은 우측의 [기본값] 속성에서 변경 할 수 있다.

 

 

[참고 자료]

http://msdn.microsoft.com/ko-kr/library/ms179316(v=sql.105).aspx

 

2013-04-15 / 강성욱 / http://sqlmvp.kr