08_SQL 2008 강좌 - 데이터베이스 생성

데이터베이스 생성.

 

데이터베이스란 SQL Server의 개체(테이블, 뷰, 인덱스 등)을 저장하기 위한 공간이다.

SSMS로 데이터베이스를 생성하는 방법과 쿼리 구문을 이용하는 방법을 알아 보도록 한다.

 

데이터베이스 생성은 Sysadmin 역할 또는 CREATE DATABASE 권한을 가진 사용자만이 가능하다.

앞에서 배운 윈도우 인증 또는 SA권한 또는 Sysadmin 권한을 부여한 계정으로 접속한 독자는 데이터베이스를 생성하는데 문제가 없을 것이다.

 

SSMS를 이용한 데이터베이스 생성.

 

1. SSMS를 실행 – 개체 탐색기에서 [데이터베이스] – 마우스 오른쪽 클릭 – [새 데이터 베이스]

 

2. 데이터베이스 이름을 입력한다. (여기에서는 'TestDB'라는 DB명을 가진 DB를 생성한다.)

 

데이터베이스 이름을 입력하면 기본값으로 정보가 채워진다. (박스 참조)

(SQL Server2008은 인스턴스당 32767개의 데이터베이스를 생성할 수 있다.)

 

- 논리적 이름 : 데이터베이스에서 사용하는 논리적 이름이다. 사용자 변경 가능.

- 처음 크기 : 데이터베이스를 생성했을 때 파일의 크기이다.

- 자동 증가 : 데이터가 가득 찾을 때 자동으로 증가하는 크기. 사용자 변경 가능.

- 경로 : 실제 물리적인 데이터파일이 기록되는 경로. 사용자 변경 가능

 

[확인] 을 클릭하여 데이터베이스를 생성한다.

 

데이터 파일을 확인 한다. (데이터베이스를 생성할 때 지정한 경로에 MDF, LDF 파일이 생성된다.)

(파일 확장자가 보이지 않는 독자는 [폴더 및 검색] 옵션에서 알려진 파일 형식의 파일 확장명 숨기기 항목의 체크를 해제 한다.

 

 

TestDB.mdf 라는 이름으로 2048KB의 파일이 생성된 것을 확인 할 수 있다. 이 파일은 실제 물리적으로 데이터가 저장되는 파일이다.

TestDB_log.ldf 또한 1024KB의 파일이 생성되며 트랜잭션 로그가 쌓이는 공간이다.

데이터 사이즈가 증가하여 파일의 용량이 가득 차게 되면 지정된 옵션(자동증가)에 따라 파일 사이즈가 확장 된다.

 

쿼리 구문을 이용한 데이터베이스 생성.

 

쿼리 구문을 이용하여 데이터베이스를 생성할 수 있다. SSMS에서 사용하는 옵션 또한 쿼리로 가능하다.

 

1. 쿼리문을 입력하고 F5를 눌러 실행하여 데이터베이스를 생성한다.

[데이터베이스] – [새로고침] 하여 데이터베이스가 정상적으로 생성되었는지 확인 한다.

 

USE master GO   CREATE DATABASE TestDB2 --데이터베이스명 --데이터 파일 설정 부분 ON PRIMARY ( NAME = N'TestDB2' --논리적 이름 ,FILENAME = 'C:\SQL_Test\TestDB2.MDF' --물리적 파일 생성 경로. ,SIZE = 2MB --초기 파일 크기 ,MAXSIZE = UNLIMITED --자동증가(무제한) ,FILEGROWTH = 1MB) --자동증가 크기.(1MB씩 파일 증가)   --트랜잭션 로그 파일 설정 부분 LOG ON ( NAME = N'TestDB2_log' --논리적 이름 ,FILENAME = 'C:\SQL_Test\TestDB2_log.LDF' --물리적 파일 생성 경로. ,SIZE = 1MB --초기 파일 크기 ,MAXSIZE = 1024GB --자동증가(1024GB까지 증가) ,FILEGROWTH = 10%) --자동증가 크기 (현재 크기의 10%씩 증가)

 

 

데이터 파일을 확인 한다.

 

 

 

 

 

Tip

 

1. 파일 그룹

1) 파일 그룹은 기본적으로 Primary가 생성되어 있다. 그 안에는 데이터파일(TestDB.MDF, TestDB_log.LDF)이 존재한다. 즉 2개의 파일과 1개의 파일그룹으로 기본 구조가 이루어져 있다.

기본적으로 Primary 그룹은 삭제나 수정이 불가능 하다.

 

 

2) 파일 그룹 추가하기.

데이터베이스 생성에서 [파일 그룹]을 선택하여 추가 버튼을 클릭하여 새로운 그룹을 등록한다.

 

3) 데이터베이스 이름을 입력하고 추가 버튼을 클릭하여 데이터파일을 추가한다. 이때 파일 그룹을 SECOND를 선택하여 그룹을 분리 할 수 가 있다.

 

기본 개념은 이렇다. 파일 그룹을 분리하여 데이터를 분산하여 기록 하는 것이다.

물리적이 드라이브를 분리하여 데이터파일 그룹을 나누어 저장한다면 스트라이핑과 비슷한 효과를 발휘 할 수 있어 성능향상에 도움이 된다.

 

 

 

 

물리적인 관점에서 본다면 IO(Input Output '입출력') 분산을 위하여 드라이브(물리적인 디스크)를 분리하는 것이 좋다.(논리적인 파티션 드라이브는 의미가 없다.)

 

만약, 스토리지(대용량 데이터 저장장치)등을 사용하여 레이드 구성 및 파티션 구성이 가능 하다면 디스크 드라이브를 여러 개로 분리하여 파일 그룹을 생성하여 데이터 파일을 분산하는 것이 성능 향상에 도움이 된다.

 

우선 데이터베이스 생성시 추가 버튼을 클릭하여 여러 개의 데이터파일을 생성하자.

이때 확장자는 .NDF 로만들어 진다.

필자는 드라이브가 <C:> 드라이브 밖에 없어서 물리적으로 나누지 못했지만 환경이 된다면 NDF를 물리적으로 분리(<D:>, <E:>, <F:>) 하여 IO를 분산하자

 

 

데이터 파일을 확인하자. 확장자 NDF가 생성된 것을 확인 할 수 있다.

 

 

 

LDF는 트랜잭션로그가 기록되는 곳으로 많은 IO를 발생시킨다. 빠른 디스크에 위치하면 성능향상을 기대 할 수 있다..

 

 

2. 처음크기와 자동 증가.

 

데이터베이스를 생성할 때 초기 크기를 어떻게 산정하는가? 디스크의 최대크기? 또한 파일이 가가 찾을 때 자동증가 사이즈는 어떻게 산정하는가?

많은 고민을 해보아야 하는 부분이다.

자동증가를 작게 자주 할 것인가? 아니면 자동증가 빈도를 줄이되 용량을 크게 증가 할 것인가?

 

(A) 상황 (자동 증가 용량을 작게 설정한 경우)

초기 설정한 데이터베이스 파일의 사용량이 가득 찾을 때 이후부터 설정된 용량 (예시 : 10MB) 만큼 자동 증가가 일어난다.

만약 트랜잭션이 빈번하여 많은 데이터가 입력되었을 때 수시로 자동증가를 하게 될 것이다. 이렇게 되면 INSERT 되는 IO외에도 파일의 증가를 위한 하드디스크 할당 비용이 들어간다. 이 때문에 짧은 시간동안의 성능 저하가 발생하기도 한다.

 

(B) 상황 (자동 증가 용량을 크게 설정한 경우)

자동 증가를 크게 설정한 경우에는 빈번한 디스크할당의 비용이 줄어들기는 하지만 다른 문제가 있다. 즉 한번 파일이 증가할 때 큰 사이즈로 증가 하기 때문에 디스크 할당 시간이 오래 걸리면 그 동안 성능 저하가 발생한다.

예를들어 10MB씩 증가하는데 1초 정도의 성능저하가 발생한다면 500MB 할당시에는 50초 정도의 성능 저하가 발생한다.

 

따라서 자동증가의 설정은 스토리지(디스크)의 성능과 운영이슈에 맞추어서 적절히 설정하여야 한다.

스토리지 속도가 빠르다면 작은 사이즈의 자동증가를 하면 순식간에 디스크가 할당 되므로 성능 저하 없이 증가가 가능 할 것이며 느린 디스크라면 주기적인 용량 체크로 점검 시 미리 늘려 주는 방법이 좋다고 생각한다. (필자의 개인 적인 생각)

 

3. 자동 증가의 설정.

 

자동증가시 MB로 설정하는 방법이 있고 % 로 설정하는 방법이 있다. 이때 %의 함정에 주의해야 한다.

 

(A) 초기 500MB를 할당하고 증가 사이즈를 100MB로 했다고 가정하자.

자동증가가 될때마다 100MB의 용량으로 증가 한다.

 

(B) %의 설정일 때는 이야기가 달라진다.

처음 500MB 할당 -> 1차 증가 : 50MB할당(500MB의 10%) ->2차 증가 :55MB 할당 (처음500MB + 1차증가 50MB 의 10%) ~~~~ 이렇게 증가 하다 보면 자동증가의 사이즈가 커져서 예기치 못한 성능 저하가 발생 할 수 있다.

 

필자가 생각하는 최선의 방법은 평소의 트랜잭션양을 산정하여 최대한 디스크를 할당 하여 추가 할당에 따른 IO비용을 낮추는게 좋다고 생각한다.