[MSSQL] 5.Database 구성요소

SQL Server에서 Database는 table, index, stored procedure, trigger, rule, permission, view, default 등의 데이터를 조작할 수 있도록 지원하는 개체들의 집합으로 구성되어 있습니다.

 

1. 데이터 파일과 로그 파일

Database는 어떻게 저장이 될까

SQL Server도 파일에 Data를 저장한다는 것입니다.

SQL Server가 설치된 폴더로 가시면 Data라는 폴더가 있습니다. 그림에서 보시는 바와 같이 여기에 파일들이 있습니다.



SQL Server의 데이터 파일들

이 파일들은 로컬 컴퓨터에만 저장이 가능합니다.

네트워크를 통한 저장은 허용되지 않습니다.

mdf는 Data를 저장하는 파일이며, ldf는 로그를 저장하는 파일입니다.

로그는 Database에서 일어나는 모든 일을 저장하고, 때에 따라서는 복구작업을 위해서 저장한 기록을 사용하도록 합니다.

ndf도Data를 저장하는 파일입니다. 다만 어떤 것이 처음으로 시작된 파일인지를 알려주기 위해서 구분하여 놓은 것입니다.

Mdf에는 주로 Database의 시작과 관련된 정보를 가지고 있으면서 Data를 저장하는 기능도 가지고 있습니다. 일반적으로 Data 파일은 하나로도 충분하지만, 두 개 이상의 파일을 생성하여야 한다면, ndf의 확장자를 가지게 됩니다. 즉 mdf 파일에 mdf 이외에 추가적으로 Data를 저장하기 위한 보조 Data 파일인 셈입니다.



파일 구조

하나의 Extent는 8개의 Page로 구성이 되어 있는 셈입니다. (8KB * 8 = 64kb)

또한 1MB는 128개의 Page가 들어갈 수 있습니다. (8KB * 128 = 1024KB)

또는 16개의 Extent가 들어가는 셈입니다.(64KB * 16 = 1024KB)

 

Page는 6개의 Type으로 나누어 볼 수 있습니다.

Page Type	내용
Data	Text, ntext, image를 제외한 Data Row
Index	Index 순서
Text/Image	Text, ntext, image Data
Global Allocation Map	할당된 Extent에 대한 정보
Page Free Space	Page내에 사용가능한 빈 공간에 대한 정보
Index Allocation Map	Table이나 Index에 의해서 사용되는 Extent에 대한 정보

     Page의 Type

유형에 대한 정보는 각 Page에서 96byte의 공간을 차지하는 Page Header라는 부분에 기록합니다.

Row Offset Table은 Page의 맨 마지막으로부터 기록을 시작합니다. 여기에는 Data Page에 기록된 각 Data Row에 대한 정보를 기록합니다.

 



SQL Server의 페이지 구조

 

Extent는 두 가지 유형으로 나누어 볼 수 있습니다. 혼합 Extent와 단일 Extent로 분류할 수 있습니다.

혼합 Extent는 하나의 Extent에 여러 개체들을 저장하는 것을 말합니다. 즉 하나의 Extent에 table, index 등의 여러 개체들이 동시에 저장되는 것을 말합니다. 단일 Extent는 하나의 Extent에 하나의 개체만이 저장되는 Extent를 말합니다.



Extent의 유형

Table이나 Index의 크기가 커져서 8 Page의 크기로 커지면, 단일 extent로 변환됩니다.

지금까지 Data 파일의 구조에 대해서 알아보았습니다.

 

Log 파일은 Data 파일의 25% 정도의 크기로 설정되어 있습니다. 최초에는 이 설정대로 사용하는 것이 좋습니다. 차후 Database 사용에 따라서 이 설정을 바꾸어 사용하는 것이 권장됩니다.

 

2. 시스템 데이터베이스

생성하지도 않은 Database들이 몇 개 이는 SQL Server에서 제공하는 System Database입니다.



시스템 데이터베이스

Northwind와 Pubs는 MSSQL에서 기본적으로 제공하는 샘플 Database입니다. 따라서 System Database를 설명하는 부분에서는 제외하도록 하겠습니다.

그럼 나머지 4개의 Database가 있는데요, 이들을 System Database라고 부릅니다

▣ Master Database

• SQL Server에 등록된 모든 Database의 정보
• SQL Server의 초기정보
• 사용자 Login 계정정보
• System 설정과 관련된 정보들
• 사용자 Database의 초기화와 관련된 주 Data File의 위치정보
• Master Database에 대한 Backup 정보

다음과 같은 정보를 저장하고 있습니다. SQL Server의 중요한 정보들을 담고 있기 때문에 수시로 Backup을 하여, Database가 깨어졌을 때를 대비하는 것이 좋습니다.

▣ MSDB

SQL Server Agent Service에서 제어하는 자동화와 관련된 정보를 저장하는 System Database입니다.

SQL Server Agent에서 제어하는 Scheduling 작업과 경고, 작업, 관리자 등에 대한 정보를 저장하게 됩니다.

• 스케줄된 작업
• DTS(data Transformation Service) Import/Export 마법사에 의해 생성된 Package
• Online Backup/Restore의 History
• Replication 작업

▣ Model

SQL Server에서 Database의 Template Database입니다.

만약 사용자가 새로운 Database를 생성하게 되면 SQL Server는 Model Database에 만들어져 있는 기본적인 전체모양을 새로 생성하는 Database로 복사를 하게 됩니다. 이때 Model Database에 들어있는 Data는 복사하지 않고, 전체적인 개체들을 복사하게 됩니다. 즉 Model Database는 SQL Server의 Database의 기본적인 틀을 제공하는 것입니다.

▣ Tempdb

Tempdb라는 이름 그대로 임시로 작업을 하는 공간입니다. 이 Database는 자동으로 크기가 증가하고 감소하는 기능을 가지고 있습니다. 또한 SQL Server가 시작될 때마다 자동으로 지워지고 새롭게 생성됩니다. 가령 임시 테이블이나 임시 Stored Procedure의 경우에는 사용이 끝나고 사용자의 접속이 끊어지면, 이들은 삭제되게 됩니다. 따라서 Tempdb에 Data를 저장한다던지, Table을 생성하는 등의 개체를 생성하는 작업은 무의미하며, 사용자 입장에서 상당히 위험한 결과를 초래할 수 있습니다.

 

1. Create Databases

공부하는 입장이기 때문에 T-SQL을 이용하여 작성을 하시는 것을 습관화하시라는 점입니다.

Database를 생성하기 위해서는 Database 생성에 대한 권한이 있어야만 합니다.

(Admin부분의 보안)

하나의 Server에 32,767개 이상의 Database는 생성을 할 수 없습니다.

 

식별자에 대해서는 T-SQL Overview 시간에 살펴보았습니다.

물리적인 명칭은 앞서서 Database Component에서 설명 드린 mdf와 ldf의 명칭입니다. 즉, 탐색기를 이용하여 MSSQL 폴더 밑에 Data 폴더에 있는 파일들의 명칭입니다. Master.mdf 이것이 바로 물리적인 명칭입니다. master, msdb, tempdb 등이 논리적인 명칭입니다.

 

CREATE DATABASE database_name

[ ON [PRIMARY]

[ [,...n] ]

[, [,...n] ]

]

[ LOG ON { [,...n]} ]

[ FOR LOAD | FOR ATTACH ]

CREATE DATABASE 는 Database를 생성하는 기본적인 SQL문장 입니다.

database_name 은 앞서 말씀 드린 논리적인 명칭을 말하는 것입니다. (유일해야함)

ON은 다음에 오게 될 filespec에 대한 지정을 뜻합니다.

PRIMARY는 파일그룹에 대한 부분입니다.

filespect에 대한 부분은 Data 파일에 대한 부분을 설명하여 놓은 부분

LOG 는 Log에 대한 정보를 표기한다는 것을 명시하는 것입니다.

 

Filespec은 다음과 같이 구성되어 있습니다.

( [ NAME = logical_file_name, ]

FILENAME = 'os_file_name'

[, SIZE = size]

[, MAXSIZE = { max_size | UNLIMITED } ]

[, FILEGROWTH = growth_increment] ) [,...n]

NAME은 파일의 논리적인 명칭을, 다음 줄의 FILENAME은 물리적인 명칭을 뜻합니다.

SIZE 는 최초 Database의 크기를 뜻합니다.

MAXSIZE 는 파일이 증가할 수 있는 최대의 크기를 말합니다.

FILEGROWTH 는 증가치를 말하는 것입니다.

MB단위로도 지정이 가능하며, 파일크기의 %로 지정할 수도 있습니다.

FOR LOAD는 SQL Server의 이전 버전과의 호환성을 위해서 제공하는 키워드 입니다. 또한 FOR ATTACH 는 기존에 생성되어 있던 물리적 파일을 가지고 Database를 생성하기 위해서 제공하는 키워드입니다.

sp_attach_db를 사용하는 것이 권장되며, filespec이 16개 이상인 경우에 사용하는 것을 권장하고 있습니다.

 

2. 데이터베이스 옵션

Enterprise Manager를 통해서도 가능하며

sp_dboption 이라는 system stored procedure를 통해서도 가능합니다.

우선 sp_dboption 의 SQL문법은 다음과 같습니다.

sp_dboption [[@dbname =] 'database']

[, [@optname =] 'option_name']

[, [@optvalue = ] 'value']

• @dbname : Database의 논리적인 명칭
• @optname : option에 대한 설정
• @optvalue : option에 대한 값

Option에 대해서 개략적으로 살펴보도록 하겠습니다.

 

Database Option	설                명
Dbo use only	Dbo의 권한을 가진 사람만이 Database를 사용할 수 있는 Option입니다. 기존에 연결되어 있던 사용자는 계속 해서 사용을 할 수 있지만, 추가로 접근요청을 하는 사용자는 dbo가 아니면 Database에 접근을 할 수 없습니다
Read only	Database를 읽기 전용으로 하는 Option입니다
Select into/bulkcopy	Bulkcopy나 SELECT INTO와 같이 로그에 기록되지 않는 Database 작업을 가능하게 합니다.
Single user	Database에 하나의 사용자만이 사용을 할 수 있도록 하는 Option입니다.
Trunc.log on chkpt	Check Point가 발생하면 Transaction이 완료된 로그는 지워버리는 Option입니다. 이는 개발과정에서 유용하게 사용할 수 있습니다.
Autoshrink	이 Option은 Database를 주기적으로 검사하여 Database에 여유공산이 있을 때 파일크기를 줄여주는 Option입니다.
ANSI null default	다음에 설명될 Table 작성시 NULL인 컬럼인지 아닌지를 지정하지 않으면, 기본값으로 NULL 컬럼을 지정하는 Option입니다
ANSI null	NULL값과 비교되는 것은 모두 False로 반환하게 되는 Option입니다. 앞선 강좌에서 설명 드린 NULL값의 비교에 대한 설명입니다. ANSI 기준에 의해서 값을 비교할 것인지 아닌지를 결정하는 Option입니다
Quoted Identifier	식별자에 대한 부분입니다. Table이나 Index와 같은 개체에 대해서는 쌍따옴표를 사용하고 일반 문자열은 작은 따옴표를 사용하는 것을 지정하는 Option입니다.

 

3. 데이터 파일과 로그 파일 - 실습

http://www.mssql.org/LectureSQL/

 

 

1. Managing Data and Log File Growth (데이터베이스 변경)

Data의 지속적인 입력과 수정, 삭제 작업등에 의해서 Data 파일의 크기는 물론, Log 파일의 크기도 커지게 됩니다. 이러한 Database에 대한 변경작업은 다음의 3가지 방법이 있습니다.

• 파일 확장의 자동화 : Data 파일과 Log 파일에 대해서 자동적으로 크기를 크게 할 수 있는 옵션설정
• 파일의 확장 : Data 파일과 Log 파일의 현재 크기나 최대 크기를 수동으로 늘려주는 방법
• 파일의 추가 : Data 파일이나 Log 파일의 수를 하나 더 늘려주는 방법

 

SQL문장인 MODIFY FILE을 예제

USE master 

GO

ALTER DATABASE Video_Sample1 

MODIFY FILE 

(NAME = video_sample1_data, 

SIZE = 20MB) 

GO

 

CREATE 구문이 ALTER로 바뀌었고, ON에 해당되는 부분이 MODIFY FILE이라는 부분으로 변경되었습니다. Filespec에 해당되는 부분은 CREATE DATABASE에서의 filespec과 같습니다. Log 파일의 크기를 확장하는 SQL문장 역시 이와 같습니다. NAME을 확장하려는 파일명을 명시하고 SIZE나 MAXSIZE를 명시하면 파일에 대한 크기를 확장할 수 있습니다.

 

파일을 추가하는 ADD FILE 문장

ALTER DATABASE Video_Sample1 

ADD FILE 

( NAME = video_sample1_data2, 

FILENAME = 'c:\mssql7\data\ video_sample1_data2.ndf', 

SIZE = 5MB, 

MAXSIZE = 100MB, 

FILEGROWTH = 5MB) 

GO

 

2. Expanding a Transaction Log

Database의 사용자가 많아지거나, Data의 입력 및 변경 작업이 늘어나게 됨에 따라서 Transaction Log를 확장할 필요성도 커지게 됩니다. 만약 Transaction Log를 저장하는 저장공간이 적다면, SQL Server는 Data변경에 따른 기록을 할 수 없기 때문에, 아무런 Data 변경작업을 할 수 없게 됩니다.

Transaction Log를 Monitor해야만 합니다. Windows NT나 Windows 2000 Server 군의 Monitor기능을 이용하여 가능합니다.

 

3. Shrinking a Database or File 

저장공간의 효율적인 운용을 위해서 파일의 크기를 줄여야 하는 경우

DBCC SHRINKDATABASE라는 SQL문장(Database Consistency Checker의 준말)

DBCC SHRINKDATABASE 

( database_name [, target_percent] 

[, {NOTRUNCATE | TRUNCATEONLY}] 

)

Database_name은 논리적인 명칭,Database를 줄이고 나서의 여유공간의 Percent를 말합니다.

NOTRUCATE는 빈공간을 정리만 하고, 여유공간에 대해서는 운영체제가 계속해서 가지고 있는 Option입니다.

 

여러 개의 파일 중 하나만을 지정하여 할당된 크기를 줄일 수 있도록 지원하는 SQL문장이 DBCC SHRINKFILE입니다.

DBCC SHRINKFILE 

( {file_name | file_id } 

{ [, target_size] 

| [, {EMPTYFILE | NOTRUNCATE | TRUNCATEONLY}] 

} 

)

 

4. Dropping a Database

다음과 같은 경우에는 Database를 삭제할 수 없습니다.

• 해당 Database를 Restore하고 있는 경우
• 사용자가 해당 Database를 열어서 Data를 읽거나 변경하고 있는 경우
• 해당 Database가 복제 작업을 진행 중에 있는 경우

 

5. 파일과 파일 그룹

Filegroup은 말 그대로 파일들의 집합을 말합니다. 즉 파일이 하나 이상일 경우, 하나의 그룹으로 묶어 놓는 다는 것입니다.

 



파일그룹의 예

입력이 많이 일어나는 테이블과 조회가 많이 일어나는 테이블들을 분산하여 저장하게 한다면 효율적인 디스크관리를 할 수 있을 것입니다. 

 

파일그룹에는 Primary File Group와 User Defined File Group , 두 가지 유형이 있습니다.

 

Primary File Group은 Database 생성 시, 기본적으로 생성이 되는 파일 그룹입니다. Default File Group입니다.  모든 System Table과 생성시 파일그룹을 명시하지 않은 모든 Table과 Index는 primary File Group에 저장되어 집니다.

Default File Group은 Database 내에 Table이나 Index 같은 개체를 만들면서 해당 개체가 저장될 특정 파일그룹을 지정하지 않은 경우 Default로 지정되는 파일그룹을 말합니다.

 

실습: http://www.mssql.org/LectureSQL/

Log 파일에 대한 Filegroup는 모두 NULL로 지정이 되어 있습니다. 여기서 보시듯이 Log 파일은 Filegroup으로 묶을 수 없습니다.

 Log 파일은 여러 개가 생성되더라도 하나의 파일처럼 순차적으로 돌아가면서 사용되어지기 때문에 파일그룹으로 묶어서 사용하는 것이 의미가 없습니다.