Transcript Core_Skill_for_DBA

DBA를 위한 SQL 핵심 기술
Microsoft Technet Advisor Group
MCSE/MCDBA/MCT
유 형 욱 ([email protected])
순서

Session 1 : DB 유지 관리를 위한 핵심 기술


14:00 ~ 15:30 (90 min.)
Session 2 : DB의 고가용성을 위한 핵심 기술

15:50 ~ 17:20 (90 min.)
Session 1

DBA’s Role?

SQL 백업과 복원

로그 삭제와 성능

저장 프로시저의 Recompile & Performance
Session 1

DBA’s Role?

SQL 백업과 복원

로그 삭제와 성능

저장 프로시저의 Recompile & Performance
DBA’s Role?

SQL Server Installation



운영체제의 필수요소만 사용
SQL Server without IIS, DHCP……
SQL Server Security



SQL 인증에 대한 고려
로그인, 사용자를 사용한 보안 향상
디폴트 포트를 사용하지 말 것




TCP 1433 (디폴트)
UDP 1434 (변경 불가)
Firewall 구성을 통한 보안 향상
복잡한 패스워드 사용
DBA’s Role?

SQL Server Management



Backup & Restore
최신 SP 및 Patch 적용
DB Size 모니터링


데이터베이스 및 로그의 Size 감시
색인 재생성 (Index Rebuild) – fill factor
Session 1

DBA’s Role?

SQL 백업과 복원

로그 삭제와 성능

저장 프로시저의 Recompile & Performance
SQL 백업과 복원

SQL Server 2000 백업의 특징


암호설정
트랜잭션을 존중하지 않는 백업


But, 진행중인 트랜잭션의 일부 로그를 함께 백업
복구모델 (Recovery Model)



전체 복구 모델 (Full)
대량 로그 복구 모델 (Bulk Logged)
단순 모델 (Simple)
SQL 백업과 복원

백업의 유형

전체 백업



한 번은 반드시 수행
전체 데이터페이지와 일부 로그가 백업
트랜잭션 로그 백업



로그 정보를 백업
백업 수행시 로그는 삭제됨
백업없이 삭제만 하고 싶다면
with no_log | truncate_only
SQL 백업과 복원

백업의 유형

차등 백업


마지막 전체 백업 이후 변경된 모든 데이터페이지 백업
파일/파일 그룹 백업

백업과 복원시 유리
SQL 백업과 복원

복원 옵션





RESTORE FILELISTONLY
RESTORE HEADERONLY
RESTORE LABELONLY
RESTORE VERIFY ONLY
STOPAT을 사용한 특정 시점으로의 복원

Stopat | Stopatmark | Stopbeforemark
SQL 백업과 복원

대기 중인 서버 (Standby Server)



저렴한 MSCS
로그 적재 마법사 구현 (SQL Enterprise Edt.)
데이터 파일로부터 복원



sp_attach_db
sp_attach_single_file_db
sp_detach_db
Session 1

DBA’s Role?

SQL 백업과 복원

로그 삭제와 성능

저장 프로시저의 Recompile & Performance
로그 삭제와 성능

관리자는 주기적으로 로그를 비워주어야 한다.

Log Size 확인



로그 삭제



DBCC SQLPERF (LOGSPACE)
EM에서의 로그 정보 확인
BACKUP LOG database to disk = ‘location’ with
option, option, …
BACKUP LOG database with no_log | truncate_only
로그 삭제시는 해당 로그의 백업 유무를 반드시 확인

복구 모델에 따른 점검
Session 1

DBA’s Role?

SQL 백업과 복원

로그 삭제와 성능

저장 프로시저의 Recompile & Performance
저장 프로시저의
Recompile & Performance

Stored Procedure 는 SQL 성능 향상에
긍적적 영향

Create Proc | Alter Proc | Drop Proc

저장 프로시저는 반복적으로 수행될 때 캐시에
저장된 실행 계획을 그대로 사용 (Recompile
하지 않음)
저장 프로시저의
Recompile & Performance

저장 프로시저를 이용한 보안 관리

매개변수 사용 가능

저장 프로시저의 종류



시스템 저장 프로시저 (sp_help,
sp_addlinkedserver…)
사용자 저장 프로시저 (Technet_1….)
확장 저장 프로시저 (xp_cmdshell….)
저장 프로시저의
Recompile & Performance

With Recompile 과 Performance




저장 프로시저는 반복적으로 수행시 캐시 정보를
사용
테이블에 NC Index가 구성되어 있고 범위안에
속해 있는 데이터가 유동적으로 변할 때
ID < 2, ID < 100, ID < 1000 보다 작은 데이터를
가져와라?
NC Index는 범위를 주고 찾는 작업에 불리
저장 프로시저의
Recompile & Performance

With Recompile 사용 방법

CREATE PROC p_test
WITH RECOMPILE
AS…

EXEC p_test WITH RECOMPILE
Session 1 Summary

DBA’s Role?




SQL 백업과 복원





백업 및 복원 스케쥴에 따라 중요한 데이터에 대해 주기적으로 백업
장애 발생시에 신속하게 상황을 판단
복구 모델에 따른 복원 전략 설정
한번쯤은 장애 Simulation 수행
로그 삭제와 성능



주기적인 작업에 대해 매뉴얼을 만든다
정보에 민감!!
최신 SP 및 Patch 적용
로그의 범람을 주의!
경고(Alerts)를 사용하여 로그 범람을 모니터링
저장 프로시저의 Recompile & Performance


저장 프로시저의 이점
예외적인 경우에서 재 컴파일과 성능 사항을 고려
Session 2

자동화 작업

경고 (Alerts)

SQL 유지관리 마법사

DTS Designer를 활용한 데이터 전송

복제 (Replication)
Session 2

자동화 작업

경고 (Alerts)

SQL 유지관리 마법사

DTS Designer를 활용한 데이터 전송

복제 (Replication)
자동화 작업

자동화는 msdb를 사용한다.

자동화의 필수 : SQLAgent 서비스


해당 서비스를 시작하는 계정은 반드시
관리권한을 갖고 있어야 함
Exchange Server 를 통한 상호 연동


경고 메일
성능 메일
자동화 작업

자동화를 수행하기 위한 작업(Tasks)



자동화 작업에 따른 Notification



단계 설정 (Step)
스케쥴링 (Schedule)
운영자 등록 필요
메일, Net Send, 호출기 등
다중 서버 작업

ex) 10대의 SQL 서버에서 Master DB 백업을 수행하려고
한다.
자동화 작업

백업 자동화 수행


로그 삭제 자동화 수행


백업 작업(Tasks)를 만들어서 스케쥴링
특정 값을 초과했을 시 삭제
Web 데이터의 Publishing

데이터가 변경될 때 업데이트 가능

ex) 은행의 환율 데이터
Session 2

자동화 작업

경고 (Alerts)

SQL 유지관리 마법사

DTS Designer를 활용한 데이터 전송

복제 (Replication)
경고 (Alerts)

Error message 경고


특정 데이터가 삭제 되었을 때
Performance 경고

‘로그가 가득찼습니다!’
Session 2

자동화 작업

경고 (Alerts)

SQL 유지관리 마법사

DTS Designer를 활용한 데이터 전송

복제 (Replication)
SQL 유지관리 마법사

데이터베이스 관리를 효율적으로!

주기적, 반복적인 작업들을 스케쥴링!

관리자가 주기적으로 수행하는 일반적 작업





백업
인덱스 재생성
데이터베이스 공간 증가 및 축소
로그파일 삭제
오래된 파일 삭제
Session 2

자동화 작업

경고 (Alerts)

SQL 유지관리 마법사

DTS Designer를 활용한 데이터 전송

복제 (Replication)
DTS Designer를 활용한 데이터 전송

DTS (Data Transformation Service)


데이터 전송을 위해 사용할 수 있는 편리한 도구
DTS 구현 방식





가져오기 / 내보내기 마법사 사용
DTS Transfer
DTS Designer
DTS Bulk Insert
Bulk Copy (CUI)
DTS Designer를 활용한 데이터 전송

DTS는 내부적으로 패키지를 만든다.





SQL Server (msdb)
Microsoft Repository (메타데이터)
COM
Visual Basic
DTS Designer


패키지를 임의대로 구성하여 활용
ex) SQL 데이터를 정렬해서 다른 DB로 넘기고 그
후에 ……
Session 2

자동화 작업

경고 (Alerts)

SQL 유지관리 마법사

DTS Designer를 활용한 데이터 전송

복제 (Replication)
복제 (Replication)

다른 서버에게 데이터를 복제

복제의 종류


2 Phase Commit (Distributed Transaction)
트랜잭션 복제


스냅샷 복제



변경된 데이터만 상대방에게 전송
전체 데이터 페이지를 복제
모든 복제의 초기 동기화 작업에는 스냅샷 복제 사용
병합 복제

양쪽 서버의 데이터를 서로 합쳐서 복제

But, 데이터 충돌의 위험 (rowguid, uniqueidentifier)
복제 (Replication)

복제의 구성 요소

게시자


배포자


실제 복제 데이터를 배포하는 개체
구독자


Article을 사용해 복제의 데이터를 만드는 개체
배포자로부터 복제를 받아오는 개체
Updating Subscriber



데이터를 받는 쪽에서도 수정 가능
이때는 분산 트랜잭션이 사용
DTC 서비스
복제 (Replication)

복제의 에이전트

스냅샷 에이전트


로그 판독기 에이전트


로그 판독기 에이전트가 읽어온 로그를 실제로 배포
병합 에이전트


트랜잭션 복제에서 변경된 로그를 판독
배포 에이전트


데이터를 동기화!
병합 복제에서 게시자, 구독자의 데이터 변경을
모니터링
기타 에이전트
Session 2 Summary

자동화 작업



경고 (Alerts)




여러 작업을 동시에 만들어줄 수 있는 기능
복잡한 과정을 하나의 인터페이스에서 구성할 수 있으나 때로는 관리자가 수동으로
작업을 해줘야 함
DTS Designer를 활용한 데이터 전송


시스템 감시를 보다 효율적으로!
메일 시스템과의 연동
SQL 유지관리 마법사


관리자가 해야하는 작업들을 스케쥴링
스케쥴된 작업들의 내용을 주기적으로 점검
Designer를 이용한 패키지 구성
복제 (Replication)


각 복제 유형의 차이점
사내 DB의 특성을 고려한 복제 구성
Reference Sites

Technet Web site at
http://www.microsoft.com/korea/technet

Microsoft SQL Home at
http://www.microsoft.com/korea/sql

Microsoft SQL Newsgroups at
http://support.microsoft.com/newsgroups

Many scripts at
http://www.swynk.com/
감사합니다.