07_SQL 2008 강좌 - System 데이터베이스 살펴보기

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

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