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

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

파일 및 파일 그룹 아키텍처

 

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

 

 

[파일 크기]

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



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1770 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (3/4) – 뷰(View)의 인덱스 확인 jevida(강성욱) 2016.09.27 972
1769 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (2/4) – 뷰(View) 확인 jevida(강성욱) 2016.09.27 1447
1768 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (1/4) – SQL 문 최적화 및 Worktables jevida(강성욱) 2016.09.27 931
1767 SQL Server DMV를 이용한 통계 정보 확인 jevida(강성욱) 2016.09.27 1452
1766 DMV를 이용한 플랜 캐시 사용 정보 확인 jevida(강성욱) 2016.09.27 1172
1765 SQL Server 테이블 및 인덱스 구조 아키텍처(4/4) – 비클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1064
1764 SQL Server 테이블 및 인덱스 구조 아키텍처(3/4) – 클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1362
1763 SQL Server 테이블 및 인덱스 구조 아키텍처(2/4) – 힙 구조 jevida(강성욱) 2016.09.27 1067
1762 SQL Server 테이블 및 인덱스 구조 아키텍처(1/4) – 테이블 및 인덱스 구성 jevida(강성욱) 2016.09.27 1123
1761 SQL Server 트랜잭션 로그 아키텍처(4/4) – 미리 쓰기 트랜잭션 로그 jevida(강성욱) 2016.09.27 1537
1760 SQL Server 트랜잭션 로그 아키텍처(3/4) – 검사점 및 로그의 활성 부분 jevida(강성욱) 2016.09.27 1048
1759 SQL Server 트랜잭션 로그 아키텍처(2/4) – 트랜잭션 로그 물리 아키텍처 jevida(강성욱) 2016.09.27 1094
1758 SQL Server 트랜잭션 로그 아키텍처(1/4) – 트랜잭션 로그 논리 아키텍처 jevida(강성욱) 2016.09.27 1246
» 파일 및 파일 그룹 아키텍처 jevida(강성욱) 2016.09.27 797
1756 SQL Server 페이지 및 익스텐트 아키텍처(4/4) – 수정된 익스텐트 추적 jevida(강성욱) 2016.09.27 1128
1755 SQL Server 페이지 및 익스텐트 아키텍처(3/4) – 개체에서 사용하는 공간 관리 jevida(강성욱) 2016.09.27 970
1754 SQL Server 페이지 및 익스텐트 아키텍처(2/4) – 익스텐트 할당 및 빈공간 관리 jevida(강성욱) 2016.09.27 1222
1753 SQL Server 페이지 및 익스텐트 아키텍처(1/4) – 페이지 및 익스텐트 이해 jevida(강성욱) 2016.09.27 2720
1752 SQL Server Error Log 보관 주기 설정 jevida(강성욱) 2016.09.15 2159
1751 SQL Server 네트워크 백업 트러블슈팅(UNC 설정) jevida(강성욱) 2016.09.15 5024





XE Login