데이터베이스 관리 – DB축소

 

데이터베이스를 생성하여 운용하다보면 데이터 사이즈는 증가한다.

하지만 데이터 파일의 모든 크기를 사용하지 않을 수도 있다.

즉, 데이터를 삭제하면 데이터 파일 내에 빈 공간이 존재하게 된다. 만약 하드디스크 공간이 없거나 액세스 범위(물리적인 스캔 위치)를 줄이기 위하여 DB축소 작업을 진행하는데 어떻게 하는 것인지 또한 어떤 종류가 있는지 알아 본다.

 

DB 축소하기.

 

DB의 크기는 데이터베이스를 생성할 때 미리 지정할 수 있다고 배웠다.

(08_데이터베이스 생성 편 참고)

또한 데이터베이스의 자동증가 옵션을 통하여 사용에 따라 지속적으로 데이터베이스의 크기를 증가 할 수 있다고 하였다.

 

그렇다면 DB의 사이즈가 늘기만 할까? 만약 불필요한 데이터를 삭제하여 디스크 공간을 절약하고(이는 디스크 사이즈가 작을 때 사용. 현업에서는 잘 사용하지 않는다.) 또는 빈공간을 제거하여 디스크의 물리적인 파일 위치의 집적도를 높임으로써 스캔의 범위를 줄이고자 할 때에는 어떻게 할까?

 

그래서 DB축소에 대해서 알아 보기로 한다.

 

1. 데이터 베이스를 생성한다. (본 시리즈를 따라 실습하는 독자라면 TestDB가 생성되어 있을 것이다.)

 

TestDB를 생성한다. 필자는 TestDB라는 이름으로 SQL디폴트 값으로 생성하였다.

MDF : 사이즈 2M, 자동증가 1M

LDF : 사이즈 1M, 자동증가 10%

 

 

실제 파일 크기를 확인한다.

 

SSMS에서 확인한 결과와 약간의 오차는 있지만 비슷한 것을 확인 할 수 있다.

 

2. 테스트 테이블을 생성하여 데이터를 입력하여 DB사이즈를 증가 시킨다.

실제 데이터 파일이 증가 된 것을 확인 할 수 있다.

 

 

이런식으로 tbl_Test에 'B'데이터, 'C' 데이터를 입력하자.

 

데이터 사이즈가 늘어 난 것을 확인 할 수 있다.

 

 

현재 tbl_Test의 데이터는 아래의 상태로 들어 있을 것이다.

 

 

3. 데이터를 삭제 한다.

물리적인 파일을 확인 한다.

사이즈가 줄어들지 않았다. 데이터파일이 삭제 되었는데 왜 사이즈가 줄어 들지 않았을까?

실제 DB에서 사용하는 공간을 체크해 보았다.

24.6메가의 여유 공간이 확인 되었다.

 

그런데 왜 사이즈가 줄지 않는 것인가?

현재 tbl_Test의 데이터는 아래의 상태로 들어 있을 것이다.

즉 사용하지 않는 공간 또한 물리적인 데이터 크기로 자리 잡고 있다.

 

 

4. 데이터베이스 축소.

(Msdn 참고

Shrink Database : http://msdn.microsoft.com/ko-kr/library/ms190488(SQL.100).aspx

Shrink File : http://msdn.microsoft.com/ko-kr/library/ms189493(v=SQL.100).aspx

)

 

1)SSMS 사용하기.

해당 DB에서 마우스 오른 쪽 클릭 – [태스크] – [축소] – [데이터베이스 | 파일]

[데이터베이스] : 해당 데이터베이스의 데이터 파일과 로그 파일을 모두 축소 한다.

[파일] : 해당 데이터베이스에 대해 지정한 데이터 또는 로그 파일의 크기를 축소하거나 같은 파일 그룹의 다른 파일로 이동하여 파일을 비우고 데이터베이스에서 제거 한다.

 

 

 

DB사이즈의 축소 테스트 이므로 여유공간 0%를 선택한다. (여유공간을 남기지 않겠다는 뜻이다.)

현업에서는 어느정도 여유공간을 남겨 둠으로써 DB사이즈 증가시 IO의 병목을 줄일 수 있다.

 

데이터 파일의 사이즈가 축소 된 것을 확인 할 수 있다. 물론 실제 파일의 크기 또한 줄어 든 것을 확인 할 수 있다.

 

2) 쿼리 이용하기.

구문

DBCC SHRINKDATABASE

( database_name | database_id | 0

[ , target_percent ]

[ , { NOTRUNCATE | TRUNCATEONLY } ]

)

[ WITH NO_INFOMSGS ]

 

 

NOTRUNCATE 옵션 : 데이터를 정리만 할 뿐 파일의 크기를 줄이지 않는다.

 

TRUNCATEONLY 옵션 : 데이터는 정리하지 않고 뒤의 남는 부분만 줄인다.

 

 

옵션 설정없이 디폴트로 사용하게 되면 위의 NOTRUNCATE 와 TRUNCATEONLY옵션이 차례대로 적용되어 실행 된다.

 

 

이렇게 하여 DB의 축소하는 것을 살펴 보았다.

하지만 과연 현업에서 DB를 축소할 일이 얼마나 있을까?

또한 DB를 축소하고 나면 다른 문제는 발생하지 않을까?

 

필자가 해외 출장에 겪은 일화 이다.

 

아무런 작업을 하지 않는 DB에 퍼포먼스가 나오지 않는다.

실제 DB를 살펴봐도 아무런 작업이나 부하가 없다. 인덱스도 잘 타고 있다.

담당 DBA와 이야기 중에 DB축소 작업을 진행 하였다는 정보를 알아 내었다.

어떤 문제가 있었을까?

 

예를 들어 순차적으로 데이터가 들어 있고 인덱스가 있다고 가정하자.

이때 오래된 데이터를 삭제하면서 공간을 절약하고자 축소작업을 하였다.

데이터가 이동한다. 인덱스의 위치는 어떻게 될까? 데이터의 이동으로 인하여 데이터 순서가 엇갈려 있을 수 있다. 실행계획 상으로는 인덱스를 타고 있지만 물리적으로는 Random I/O 하고 있을지 모른다.

필자는 담당DBA에게 통계 업데이트 및 인덱스 재정렬 작업을 권장하였다.

 

그리고 왠만하면 DB축소 하지 말라고 권고 하였다. (디스크가 500GB넘게 여유 공간이 있는데 왜자꾸 하는지 모르겠다. 주로 아시아 권역이 DB축소를 좋아하는 듯 하다.)

 

DB를 관리하는데 있어서 하나의 기능만을 배워서 적용할 경우 그 파장이 매우 큰 경우가 많다.

항상 여러가지 상황을 염두에 두고 철저한 테스트 및 기술을 습득하여 사용하길 바란다.

 

스토리지(DAS, NAS, SAN) 와 RAID 레벨

 

과거 데이터가 적을 때에는 서버내의 하드디스크 만으로도 충분히 활용이 가능 했으나 요즘의 데이터는 과거와 비교하여 데이터폭발이라고 할 만큼 많은 양의 디스크를 요구 한다.

 

특히 인터넷이 대중화 되고 온라인 컨텐츠가 늘어 날수록 데이터 증가 속도는 더욱 빨라지고 더 많은 용량의 저장공간을 필요로 한다.

 

그렇다면 이 많은 데이터를 어디에 저장할까? 그래서 나온 것이 스토리지 이다.

 

스토리지는 구성 방식에 따라 여러가지로 나누어 지는데 여기서는 일반 적으로 많이 쓰고 있는 3가지 방식에 대해서 살펴 보고자 한다.

 

특정 벤더 업체나 모델명은 언급 하지 않고 개념만 살펴 보도록 한다.

 

DAS(Direct Attached Storage)?

 

서버와 전용 케이블로 연결한 외부 저장장치 이다. 쉽게 생각하면 일반 사용자들이 많이 쓰는 외장형 하드디스크라고 생각하면 된다. 일반 데스크탑 컴퓨터 에서는 USB 또는 e-SATA 등으로 연결하지만 서버용은 좀더 빠른 성능과 대용량 그리고 안정성을 지원하기 위하여 전용 컨트롤러와 케이블을 사용한다.

 

장점 : 가격이 상대적으로 싸다. 설치 및 운용이 쉽다. 하드디스크를 추가한 만큼 용량이 증가.

단점 : 서버가 다운된 경우 사용 불능. 다른 서버와 연결이 어렵다. 스토리지가 분리 될 경우 데이터 손실 발생 가능성이 있다.(이를 보통 스토리지가 떨어졌다 라고 표현한다.)

 

 

NAS(Network Attached Storage)?

 

FILE Server는 네트워크가 발달하지 못햇던 시절 파일 공유 서비스를 위하여 범용OS에서 제공되는 일부분의 기능을 사용하여 구현하였고, 저장장치는 주로 PC에 내장된 디스크를 이용하였다. 그리고 서버/클라이언트 구주로 파일서버가 서버로서의 역할을 하고 각 End-User 의 단말기(PC)가 클라이언트 역할을 하도록 구현되었다. 시간이 지나면서 데이터의 용량이 폭증하고 파일 공유 및 서비스를 위한 I/O가 보다 높은 대역폭을 필요하게 되었다. 이런 한계를 극복한 것이 NAS이다.

NAS는 글자 그대로 네트워크(LAN)에 연결된 스토리지 이다.

 

장점 : 파일 공유가 쉽다. 네트워크에 연결 하면 됨으로 특정 장비에 종속적이지 않다.

단점 : Latency Time 증가.(느리다) 대규모 처리에서 성능 문제 발생. DB의 경우 로컬에 캐싱하는데 스토리지 장애 발생 경우 데이터 Consistency 문제.

 

 

SAN(Storage Area Network)?

 

'광저장장치영역 네트워크'라고 불린다. 특수 목적용 고속 네트워크로서 대규모 네트워크 사용자들을 위하여 이기종 간의 데이터 저장장치를 관련 데이터 서버와 함께 연결해 별도의 네트워크를 구성해 관리한다.

1990년대 말부터 개발 되기 시작하여 현재 대부분의 고용량 및 고성능을 요구하는데서는 보편적으로 쓰고 있다. 시간이 지날수록 고성장세를 나타내고 있다.

장점 : 서로 다른 종류의 저장장치 연결 가능. 백업, 보관, 검색 가능. 저장장치간 데이터 이동 가능.

단점 : 가격이 비싸다.

 

 

 

스토리지의 3종류에 대해서 알아 보았다. 사용자의 환경에 맞추어 적절한 스토리지를 선택하여 비용대비 최대의 효과를 구현할 수 있기를 바란다.

 

Raid Level

 

데이터 베이스의 성능은 쿼리를 얼만큼 효율적으로 잘 만드느냐!

옵티마이져를 얼마나 이해해서 최적의 수행경로를 찾느냐로 성능을 향상 시킬 수 있다.

 

하지만 소프트웨어는 하드웨어에 종속적이므로 하드웨어 성능 자체가 느리다면 아무리 튜닝을 해도 한계가 있기 마련이다.

 

컴퓨터에서 가장 느린 장치가 무엇일까?

하드 디스크 이다. 이번 시간은 하드디스크를 여러 개 묶어서 성능을 향상시키는 방법에 대해 알아 보자.

 

RAID는 Redunadant Array of Inexpensive Disks 의 약자로 값싼 디스크를 여러장 묶어 대용량의 저장공간을 만들고자 하는 요구로 1980년대 처음 등장하였다. 최근에는 디스크 가격이 고용량 저가격이 형성되면서 의미는 많이 퇴색해져 중복성과 성능향상으로 바뀌고 있다.

 

RAID는 구성에 따라 총 6가지 (0, 1, 2, 3, 4, 5, 6)레벨로 나눌 수 있으며 레벨에 따라 신뢰성과 성능 향상을 나타낸다.

 

RAID 0

2개 이상의 디스크를 사용하여 구성. 스트라이핑 모드.

장점 : 같은 섹터에 병렬기록, 읽기/쓰기 향상.

단점 : 디스크 장애시 복구 불능.

사용처 : 중요하지 않는 데이터에 빠른 처리가 요구될 때 사용.

 

RAID 1

동일한 Raid 볼륨으 추가적으로 구성 한 것. 미러링 모드

장점 : 디스크 장애시 복구 가능. 읽기 성능 향상.(다중 스레드 사용시)

단점 : 동일 데이터를 중복해서 써야하므로 쓰기 속도 느림.

사용처 : 속도보다는 안정성을 추구하는 데이터에 적합. 전체용량의 1/2 만 사용가능.

 

RAID 2

RAID 0의 장점을 사용하면서 신뢰성을 높이기 위한 방법. 4개를 스트라이핑 모드 구성 + 3개의 패리티 체크 구성.

장점 : 여러 개의 디스크 장애시 복구 가능.

단점 : 볼륨 구성 단위가 크다.

 

 

RAID 3

RAID2의 단점을 개선 한 모델. 하나의 볼륨에만 패리티 저장.

장점 : RAID2보다 적은 볼륨 사용.(디스크 절약)

단점 : 동일 위치 동시 장해시 복구 불능.

 

RAID 4

구성은RAID3와 동일. 단 저장 단위가 RAID3는 바이트 단위. RAID4는 블록 단위.

 

 

 

 

 

 

 

RAID 5

RAID4의 단점을 개선 시킨 모델. 성능샹 효율을 위해 패리티 디스크들을 각 볼륨에 분할.

최소 3개의 디스크 필요.

장점 : 어느 정도 속도 보장.

단점 : 다중 디스크 장애시 복구 불능. (1장 까지 장애 복구 가능)

 

RAID 6

RAID5에 비해 신뢰성에 기반을 둔 레벨. 패리티 디스크를 추가함으로써 동시 오류에 복구가 가능하도록 설계.

추가적인 패리티 기록을 위하여 속도가 느리다.

 

 

최근에는 하이브리드 방식으로 RAID레벨의 장점을 섞어 성능과 신뢰성 비용 효과를 보고 있다.

  • RAID 0+1
  • RAID 1 + 0
  • RAID 5 + 0
  • RAID 5+ 1
  • RAID 6 + 0

 

RAID Level Comparision (Adaptec 자료)

 

 

 

레이드의 종류에 대해서 알아 보았다. 그렇다면 우리의 환경에서는 어떤 레이드를 고려해야 할까?

무조건 빠른 것? 안정적인 것?

 

지난 시간에 데이터베이스 생성을 다루면서 LDF 파일은 빠른 디스크에 저장할 경우 성능 향상을 기대할 수 있다고 하였다. 그렇다면 어떤 레이드 레벨이 좋을까?

 

위의 도표를 참고로 가장 빠른 레이드 레벨은 0 이지만 안정성에 취약한 부분이 존재한다.

LDF 파일은 중요함으로 중요성을 강요한 1 의 모드가 필요 할 수도 있다.

이럴땐 1 + 0 로 레벨을 설정하여 성능과 안정성 을 추구 할 수 있다.

 

이처럼 현업의 업무 성향이나 데이터의 중요도 등을 고려하여 최적의 레이드 레벨을 구현하여 비용대비 최대의 효익을 추구하길 바란다.

 

 

 

데이터베이스 생성.

 

데이터베이스란 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비용을 낮추는게 좋다고 생각한다.

System DB 살펴보기.

(master, model, msdb, tempdb)

 

MS SQL Server를 설치하면 기본적은 시스템DB가 설치된다.

우선 결론부터 말하자면 SQL Server 정보를 포함하고 있으므로 반드시 백업해야 한다.

 

시스템 DB에는 무엇이 있는지 살펴 보도록 하자.

[SSMS] - [데이터베이스] – [시스템 데이터베이스]

 

 

만약 [시스템 데이터베이스] 항목이 보이지 않는 다면 [도구] - [옵션] – [환경] – [일반]

에서 개체 탐색기에서 [시스템 개체 숨기기] 체크 표시를 해제하고 SSMS를 다시 시작한다.

 

 

 

Master, Model, Msdb, Tempdb - 4개의 시스템 데이터 베이스가 생성된 것을 볼 수 있다.

 

각 DB의 기능과 역할에 대해 알아 보도록 하자.

 

 

1. Master 데이터베이스

 

SQL Server에서 가장 중요한 데이터베이스로 이 데이터 베이스에 문제가 생긴다면 SQL Server자체가 정상적인 작동을 하지 않는다.

Master 데이터베이스에는 SQL Server의 디스크 정보, 계정정보, 사용자가 만든 데이터베이스에 대한 정보, 구성 정보 등등 저장된다.

따라서 master 데이터베이스는 주기적으로 백업을 해서 비상시를 대비해야 한다.

백업 주기는 계정 생성이나 DB 생성 및 삭제 등 변경 작업 후에 백업하는 것이 가장 좋다.

백업 사이즈가 크지 않으니 백업하는데 부담은 없을 것이다.

 

우선 master 데이터 베이스를 선택하여 확장 하여 테이블 및 뷰, 저장 프로시저 등 어떤 것들이 있는지 알아 보도록 하자.

 

많은 시스템 테이블 및 뷰 그리고 시스템 저장 프로시져, 기타 정보를 확인 할 수 있다,

 

모든 테이블 및 뷰, 저장 프로시져의 기능을 여기서는 다 살펴볼 수 없으므로

자세한 내용이 궁금하신 독자라면 MSDN사이트 또는 BOL(Books Online)을 참고하길 바란다.

 

2. model 데이터베이스

 

Model 데이터베이스는 단순히 템플릿 데이터베이스 이다. 사용자 데이터베이스를 생성할 때 기본 모델이 된다. CREATE DATABASE를 실행하면 데이터베이스가 생성되는데 이때 model 데이터베이스를 기본으로 DB가 생성된다.

앞으로 생성할 DB에 특정한 환경을 구성하고 싶다면 model 데이터베이스를 수정하면 된다.

 

3. msdb 데이터베이스

 

SQL Server의 예약된 작업, 즉 에이전트에 등록된 작업을 관리 한다.

사용자가 임의로 삭제하거나 변경을 가해서는 안 된다.

사이즈가 크지 않음으로 주기적으로 백업하여 관리하는 것이 좋다.

 

여기서는 간단하게 잡 스케쥴에 대한 몇 가지 정보를 살펴 보겠다.

자세한 내용이 궁금하신 독자라면 MSDN사이트 또는 BOL(Books Online)을 참고하길 바란다.

 

 

1) sysjobs : 에이전트에서 실행될 각 예약 작업의 정보를 저장한다.

참조 : http://msdn.microsoft.com/ko-kr/library/ms189817.aspx

 

열 이름

데이터 형식

설명

job_id

uniqueidentifier

작업의 고유한 ID입니다.

originating_server_id

int

작업을 가져온 서버의 ID입니다.

name

sysname

작업의 이름입니다.

enabled

tinyint

작업을 실행할 수 있는지를 표시합니다.

description

nvarchar(512)

작업 설명입니다.

start_step_id

int

실행을 시작해야 하는 작업 단계의 ID입니다.

category_id

int

작업 범주의 ID입니다.

owner_sid

varbinary(85)

작업 소유자의 보안 ID(SID)입니다.

notify_level_ eventlog

int

어떤 상황에서 Microsoft Windows 응용 프로그램 로그에 알림 이벤트를 기록해야 할��를 지정하는 비트 마스크입니다.

0 = 안 함

1 = 작업이 성공할 경우

2 = 작업이 실패할 경우

3 = 작업이 완료될 때마다(작업 결과와 관계없음)

notify_level_email

int

작업을 완료했을 때, 어떤 상황에서 알림 전자 메일을 전달해야 할지를 지정하는 비트 마스크입니다.

0 = 안 함

1 = 작업이 성공할 경우

2 = 작업이 실패할 경우

3 = 작업이 완료될 때마다(작업 결과와 관계없음)

notify_level_netsend

int

작업을 완료했을 때, 어떤 상황에서 네트워크 메시지를 전달해야 할지를 지정하는 비트 마스크입니다.

0 = 안 함

1 = 작업이 성공할 경우

2 = 작업이 실패할 경우

3 = 작업이 완료될 때마다(작업 결과와 관계없음)

notify_level_page

int

작업을 완료했을 때, 어떤 상황에서 호출을 해야 할지를 지정하는 비트 마스크입니다.

0 = 안 함

1 = 작업이 성공할 경우

2 = 작업이 실패할 경우

3 = 작업이 완료될 때마다(작업 결과와 관계없음)

notify_email_ operator_id

int

정보를 알릴 운영자의 전자 메일 이름입니다.

notify_netsend_ operator_id

int

네트워크 메시지를 전달할 때 사용하는 컴퓨터 또는 사용자의 ID입니다.

notify_page_ operator_id

int

호출을 전달할 때 사용하는 컴퓨터 또는 사용자의 ID입니다.

delete_level

int

작업을 완료했을 때 어떤 상황에서 작업을 삭제해야 할지를 지정하는 비트 마스크입니다.

0 = 안 함

1 = 작업이 성공할 경우

2 = 작업이 실패할 경우

3 = 작업이 완료될 때마다(작업 결과와 관계없음)

date_created

datetime

작업을 만든 날짜입니다.

date_modified

datetime

작업을 마지막으로 수정한 날짜입니다.

version_number

int

작업 버전입니다.

 

 

2) sysjobschedules : 에이전트가 실행할 작업에 관한 일정 정보를 포함한다.

참조 : http://msdn.microsoft.com/ko-kr/library/ms188924(SQL.90).aspx

 

열 이름

데이터 형식

설명

schedule_id

int

일정의 ID입니다.

job_id

uniqueidentifier

작업 ID입니다.

next_run_date

int

작업을 실행하도록 예약된 다음 날짜입니다. 날짜 형식은 YYYYMMDD입니다.

next_run_time

int

작업을 실행하도록 예약된 시간입니다. 시간 형식은 HHMMSS이며 24시간제를 사용합니다.

 

3) sysjobservers : 특정 작업과 하나 이상의 대상 서버와의 연관 또는 관계를 정의한다.

참조 : http://msdn.microsoft.com/ko-kr/library/ms187761(SQL.90).aspx

 

열 이름

데이터 형식

설명

job_id

uniqueidentifier

작업 ID 번호입니다.

server_id

int

서버 ID입니다.

last_run_outcome

tinyint

작업이 마지막으로 실행되었을 때의 결과입니다.

0 = 실패

1 = 성공

3 = 취소

last_outcome_?message

nvarchar(1024)

last_run_outcome 열과 연관된 메시지(있는 경우)입니다.

last_run_date

int

작업을 마지막으로 실행한 날짜입니다.

last_run_time

int

작업을 마지막으로 실행한 시간입니다.

last_run_duration

int

작업의 실행 시간(초)입니다.

 

4) sysjobsteps : 에이전트에서 실행될 작업의 각 단계에 대한 정보를 포함.

참조 : http://msdn.microsoft.com/ko-kr/library/ms187387(SQL.90).aspx

 

 

열 이름

데이터 형식

설명

job_id

uniqueidentifier

작업의 ID입니다.

step_id

int

작업 단계의 ID입니다.

step_name

sysname

작업 단계의 이름입니다.

subsystem

nvarchar(40)

SQL Server 에이전트에서 작업 단계를 실행하는 데 사용하는 하위 시스템의 이름입니다.

command

nvarchar(max)

subsystem이 실행할 명령입니다.

flags

int

예약되어 있습니다.

additional_ parameters

ntext

예약되어 있습니다.

cmdexec_success_ code

int

성공을 표시하기 위해 CmdExec 하위 시스템 단계가 반환하는 오류 수준 값입니다.

on_success_action

tinyint

단계가 성공적으로 실행되었을 때 수행되는 작업입니다.

on_success_step_id

int

단계가 성공적으로 실행되었을 때 다음으로 실행되는 단계의 ID입니다.

on_fail_action

tinyint

단계가 성공적으로 실행되지 않았을 때 수행되는 작업입니다.

on_fail_step_id

int

단계가 성공적으로 실행되지 않았을 때 다음으로 실행되는 단계의 ID입니다.

server

sysname

예약되어 있습니다.

database_name

sysname

subsystem이 TSQL인 경우 command가 실행되는 데이터베이스의 이름입니다.

database_user_name

sysname

단계를 실행할 때 그 계정을 사용할 데이터베이스 사용자의 이름입니다.

retry_attempts

int

단계가 실패했을 때 재시도하는 횟수입니다.

retry_interval

int

재시도 간에 대기하는 시간입니다.

os_run_priority

int

예약되어 있습니다.

output_file_name

nvarchar(200)

subsystem이 TSQL 또는 CmdExec일 경우 단계의 출력이 저장되는 파일의 이름입니다.

last_run_outcome

int

작업 단계의 이전 실행 결과입니다.

0 = 실패

1 = 성공

2 = 다시 시도

3 = 취소됨

5 = 알 수 없음

last_run_duration

int

단계가 마지막으로 실행된 기간(hhmmss)입니다.

last_run_retries

int

작업 단계의 마지막 실행에서 재시도한 횟수입니다.

last_run_date

int

단계가 마지막으로 실행을 시작했을 때의 날짜(yyyymmdd)입니다.

last_run_time

int

단계가 마지막으로 실행을 시작했을 때의 시간(hhmmss)입니다.

proxy_id

int

작업 단계에 대한 프록시입니다.

step_uid

uniqueidentifier

작업 단계에 대한 식별자입니다.

 

4. tempdb 데이터베이스

 

SQL Server 가 임시로 사용하는 데이터베이스 이다. 가장 큰 특징은 SQL Server가 시작 될 때 마다 새로 생성된다. 즉 어떠한 작업을 tempDB에 한다면 SQL Server가 시작될 때 모두 지워진다.

Tempdb의 주요 용도는 쿼리의 중간결과 저장 또는 사용자의 임시테이블(#temp, ##temp) 등으로 사용되며 서비스 시작 시 초기화 됨으로 별도의 백업을 할 필요가 없다.

 

* tempdb는 임시로 사용되는 것이나 그 역할이 매우중요하므로 별도의 물리적 디스크를 분리하여 최대한 빠르게 구성하는 것이 운영상에 이점이 있다.

자세한 내용은 추후 운영 부분에 다루어 보도록 하겠다.

+ Recent posts