Transcript DBAdmin.ppt
™
SQL Server 2000:
DBA의 역할과 책임
하 성희
DBA의 임무와 책임
설치 및 환경 설정
보안
운영 업무
적절한 서비스 레벨 제공
시스템 가동 시간 극대화
문서화 작업
설계 및 개발 참여 또는 지원
기타
설치 및 환경 설정
목표 : 시스템을 SQL Server에
최적으로 구성
DBA가 충분히 참여
문서화 작업 및 활용
재작업 시 시간과 노력 절감
실수 최소화
일관된 환경 구성 (다중 서버 환경)
설치
DBA가 하드웨어, SQL Server 이외의
소프트웨어 설치에도 참여
설치 전 계획 수립
테스트용 시스템에 사전 설치 테스트
설치 후 설치 과정 문서화
환경 설정
하드웨어
예: RAID
소프트웨어
네트워크
구성 (Configuration) 관리
구성(Configuration) 관리
설정 방법
옵션 계층
sp_configure / RECONFIGURE
엔터프라이즈 관리자
서버 옵션 < 데이터베이스 옵션 < SET 옵션
< 참고(hint)
설정 내용 및 변경 이력 문서화 요망
하드웨어를 충분히 활용하도록 설정
보안
사용자 관리
시스템 감사 (Audit)
네트워크
사용자 관리
사용자 로그인 관리
데이터베이스 사용자 관리
권한(Permission) 관리
역할(Role) 관리
SQL Server 보안 모델
SQL Server 컴퓨터에 연결
SQL Server에게 로그인 인증 요청
SQL Server에 연결
원하는 데이터베이스로 연결 및 액세스 인가
데이터베이스 컨텍스트 설정
원하는 작업 수행
작업 권한 검증
(데이터베이스 내의 모든 작업에 대해 권한 검증)
사용자 로그인 관리
인증 모드
SQL Server 로그인 등록
Windows 통합 인증 / 혼합 모드 인증
엔터프라이즈 관리자 : [SQL Server
속성(구성)] [보안] 탭
sp_addlogin / sp_grantlogin
엔터프라이즈 관리자 : [보안] [로그인]
BUILTIN\Administrators 그룹의
구성원인 Windows NT 사용자는
자동으로 sysadmin 서버 역할의
구성원이 됨
사용자 로그인 관리
로그인 삭제
sp_droplogin
sp_revokelogin : Windows NT
사용자/그룹 로그인 제거
데이터베이스 사용자 삭제 후 작업
만들 때와 역순
데이터베이스 사용자 관리
SQL Server 로그인 등록 후 작업
등록 방법
sp_adduser / sp_grantdbaccess
엔터프라이즈 관리자 : 데이터베이스
[사용자]
권한 관리
GRANT - 권한 부여
REVOKE - GRANT 또는 DENY 취소
DENY
- 권한 부여 금지
GRANT
REVOKE [DENY]
-
REVOKE
+
DENY
GRANT
DENY
역할 관리
역할
고정 서버 역할 / 고정 데이터베이스 역할
사용자 정의 데이터베이스 역할
로그인을 고정 서버 역할의 구성원으로
등록 : sp_addsrvrolemember
데이터베이스 사용자를 고정 데이터베이스
역할의 구성원으로 등록 :
sp_addrolemember
역할을 만들고, 역할에 권한을 설정하고,
역할에 사용자를 추가
운영
서비스 관리
백업과 복원
사용자 및 권한 관리
시스템 데이터베이스 관리
사용자 데이터베이스 생성 및 관리
데이터베이스 여유 공간 점검
데이터베이스 일관성 (Consistency) 점검
인덱스 관리
통계 관리
점검 및 모니터링
장애 대처
서비스 관리
서비스 시작
시작 옵션 지정
서비스 중지
서비스 시작
필요한 서비스 시작
SQL Server
SQL Server Agent
Distributed Transaction Coordinator
Microsoft Search
Microsoft Message Queuing
서비스 로그온 계정 적절하게 설정
시작 유형 설정 (필요한 경우)
시작 옵션 지정
서비스 시작 매개 변수 설정
-m : 단일 사용자 모드
엔터프라이즈 관리자 :
[SQL Server 속성(구성)] [일반] 탭 [시작
매개 변수]
참고 : ALTER DATABASE <DB명>
SINGLE_USER WITH ROLLBACK
-Ttrace# : 추적 플래그 지정
예 : -T1204, -T3605
SET
서비스 중지
중지 방법
SHUTDOWN
쿼리 분석기 또는 osql 유틸리티 사용
자동 복구 작업량 (Automatic recovery) 최소화
SQL Server 엔터프라이즈 관리자
SQL Server 서비스 관리자
관리 도구의 [서비스]
net stop mssqlserver
일시 중지 중지
SHUTDOWN 제외
백업과 복원
백업
적절한 백업 전략 수립
주기적인 백업 체계 수립
주기적인 백업본 원복 테스트
복원
장애 발생 시 복원 시나리오 수립 및 테스트
* 백업과 복원은 두 번째 세션에서 다루어지므로 세부 내용 생략 *
넓은 의미의 백업
데이터베이스 백업
로그 백업
데이터 백업 (텍스트 파일)
스크립트 백업
데이터베이스 스크립트 백업
작업(Job) 스크립트 백업
복제 스크립트 백업
데이터베이스 스크립트 백업
주기적으로 데이터베이스 스크립트
저장
작업 방법
엔터프라이즈 관리자에서
[데이터베이스] 데이터베이스 선택 [도구]
[SQL 스크립트 생성]
작업(Job) 스크립트 백업
작업 스크립트 파일로 받아 내기
작업 목록 파일로 받아 내기
[관리] [SQL Server 에이전트] [작업]
작업 선택 [모든 작업] [SQL
스크립트 생성]
[관리] [SQL Server 에이전트] [작업]
[목록 내보내기]
파일 형식 지정 가능
msdb 백업
시스템 데이터베이스 관리
시스템 데이터베이스
주기적으로 백업
master, tempdb, model, msdb
master
msdb
model (수정한 경우)
disribution (서버를 복제 배포자로 구성한
경우)
tempdb 크기 확장 / tempdb 이동
사용자 데이터베이스 생성 및
관리
CREATE DATABASE
ALTER DATABASE
충분한 공간 할당
최대 크기 지정
복구 모델 변경
공간 추가 및 제거
크기, 최대 크기, 증가량 변경 등등등
DROP DATABASE
데이터베이스 축소
축소 방법
DBCC SHRINKDATABASE
DBCC SHRINKFILE
데이터베이스 옵션 ‘autoshrink’ 을
true로 설정
트랜잭션 로그 축소
로그 파일 크기가 커진 경우
로그 백업 또는 삭제 DBCC
SHRINKFILE
데이터베이스 이동
이동 방법
sp_attach_db & sp_detach_db
BACKUP & RECOVERY
로그 전달 (Log-shipping)
테스트 환경 구축
서버 이전
데이터베이스 여유 공간 점검
주기적으로 DB별로 free space를
점검하여 충분한 space 미리 확보
성능 측면 & 장애 예방 측면
sp_spaceused
sysindexes 테이블의 부정확성 정정
@updateusage = 'TRUE'
DBCC UPDATEUSAGE
데이터베이스 일관성 점검
DBCC CHECKDB
지정한 데이터베이스 내 모든
오브젝트들의 할당과 구조적 무결성 검사
DBCC CHECKTABLE
테이블이나 인덱스된 뷰에 대하여 데이터,
인덱스, text, ntext, image 페이지의
무결성 검사
DBCC CHECKDB - 점검
데이터베이스 무결성 검사
유의 사항
테이블 검사 중 DDL 문 블로킹 발생
사용 시 권장 사항
시스템 사용이 적을 때 실행
디스크 백업 등의 다른 디스크 I/O를
수행하지 않아야 함
수행에 필요한 tempdb 공간 확인
ESTIMATEONLY 옵션
DBCC CHECKDB – 오류 복구
오류 복구
REPAIR_ALLOW_DATA_LOSS
REPAIR_FAST
REPAIR_REBUILD
작업 방법
EXEC sp_dboption ‘<DB명>', 'single', true
DBCC CHECKDB (‘<DB명>', ‘<복구 옵션>')
인덱스 관리
인덱스 만들기
인덱스 삭제
인덱스 조각화 제거
인덱스 만들기
효율적인 인덱스 디자인
성능에 필수
쿼리를 충분히 분석
인덱스 튜닝 마법사 활용
시스템을 사용하는 동안 지속적인
인덱스 관리
인덱스 별로 적절한 fillfactor 설정
CREATE INDEX
CREATE TABLE / ALTER TABLE
(primary key, unique 제약 조건)
인덱스 만들기
tempdb 선택적 사용
복구 모델
SORT_IN_TEMPDB
대량 로그 복구 (Bulk_Logged) 사용 고려
내림차순 지원
SELECT col1, col2 FROM t …
ORDER BY col1 ASC, col2 DESC
CREATE INDEX Idx1 on t
(col1 ASC, col2 DESC)
CREATE INDEX가 쿼리처럼
실행됨
예
테이블 T에 인덱스 i1 on (a,b,c) 이
존재
CREATE INDEX i2 on T (b,c) 전체
테이블 대신 인덱스 i1 을 스캔
CREATE INDEX i3 on T (a,b)
인덱스 i1 을 스캔 (정렬 작업은
수행되지 않음)
인덱스 생성 순서 결정 시 고려
인덱스 삭제
불필요한 인덱스 삭제
DROP INDEX
ALTER TABLE (primary key, unique
제약 조건)
인덱스 조각화 제거
목적 : 성능 향상
조각화 상태 확인
인덱스 조각 모으기
DBCC INDEXDEFRAG
인덱스 재구성
분석 후 재구성 여부 결정
DBCC DBREINDEX
클러스터된 인덱스 삭제 후 다시 생성
CREATE INDEX … DROP_EXISTING
조각화 상태 확인
DBCC SHOWCONTIG
테이블의 데이터와 인덱스 조각화 정보
제공
INSERT, UPDATE, DELETE 시 조각화
발생
인덱스 조각 모으기
DBCC INDEXDEFRAG
온라인 인덱스 재구성 명령 실행
중에도 인덱스 사용 가능
성능 영향 적음
최소의 데이터 공간 사용
중지 가능 및 재시작 가능
재구성보다 느림
로그 백업이 커짐
조각화가 완전하게 제거되지 않음
온라인 인덱스 재구성
DBCC INDEXDEFRAG
인덱스의 잎 레벨(leaf level) 순서 재정렬
4 1
2
7 6 3
5
1 2
3
4 5 6
7
Fillfactor 재설정
60%
페이지들은 작업전과 동일한 파일에 남아
있음
인덱스 재구성
DBCC DBREINDEX
블로킹 유발 가능한 한 서비스 중단 후
수행
제약 조건 삭제하고 다시 만들 필요 없음
DBCC DBREINDEX
(‘<테이블명>’,’’,<fillfactor>)
테이블의 모든 인덱스를 다시 작성
DBCC DBREINDEX
(‘<테이블명>’,’<인덱스명>’,<fillfactor>)
테이블의 해당 인덱스를 다시 작성
통계 (Statistics) 관리
통계 만들기
통계 조회
통계 갱신
통계 만들기
인덱스가 있는 컬럼에는 통계가
자동으로 만들어짐
데이터베이스 옵션 ‘auto create
statistics’가 true 인덱스 없는
컬럼이 조건절이나 조인에 사용되면
자동으로 통계 만들어짐
수동으로 통계 만들기
CREATE STATISTICS
통계 조회
sp_helpstats
DBCC SHOW_STATISTICS
STATS_DATE
통계 갱신
UPDATE STATISTICS
sp_updatestats
데이터베이스 옵션 ‘auto update
statistics’
데이터가 많이 추가되거나 변경되거나
제거되는 경우 통계 갱신
쿼리 계획이 부적절한 경우 통계에
문제가 있을 가능성 있음 통계 갱신
점검 및 모니터링
SQL Server 오류 로그
프로세스
성능 모니터링
SQL Server 오류 로그
주기적으로 errorlog 파일 점검 요망
확인 방법
xp_readerrorlog 사용
엔터프라이즈 관리자
EXEC master..xp_readerrorlog
EXEC master..xp_readerrorlog 3
[관리] [SQL Server 로그]
텍스트 에디터
Program Files\Microsoft SQL Server
\Mssql\Log\Errorlog
오류 로그 구성 관리
오류 로그 파일 수 변경
엔터프라이즈 관리자 : [관리] [SQL
Server 로그] [구성]
레지스트리 키 :
HKEY_LOCAL_MACHINE\SOFTWARE\
Microsoft\MSSQLServer\MSSQLServer
\NumErrorLogs
오류 로그 파일 Cycle
sp_cycle_errorlog / DBCC ERRORLOG
AV 발생 등으로 인해 파일 크기가
비정상적으로 커진 경우 수행
프로세스
sp_who
sp_lock
잠금 관련 정보 제공
DBCC INPUTBUFFER
SQL Server 프로세스에 대한 정보 제공
DBCC INPUTBUFFER (<spid>)
클라이언트에서 마지막으로 보낸 명령문 확인
DBCC OPENTRAN
DBCC OPENTRAN (‘<DB명>’)
프로세스 강제 종료 - KILL
강제 종료 대상
운영에 지장을 주는 연결(Connection)
Orphaned session
KILL 권한
예: 잠금으로 다른 중요 프로세스 수행을
차단하는 프로세스
sysadmin, processadmin 역할
Processadmin 역할에 등록 예
EXEC sp_addsrvrolemember
‘operator', 'processadmin'
EXEC sp_helpsrvrolemember
성능 모니터링
시스템 모니터 활용
엔터프라이즈 관리자 활용
sp_who
성능 문제 해결
점검/모니터링 문제점 감지 성능
병목 원인 확인 튜닝 효과 분석
문서화
튜닝
시스템 튜닝
인덱스 튜닝
응용 프로그램 튜닝 지원
장애 대처 요령
당황하지 않는다
DBA가 직접 확인한다.
성급하게 결론을 내리지 않는다
신중하게 대처한다
문제를 더 악화시키지 않아야 한다
직접 처리하기 어려운 경우에는 지원을
요청한다
Sqldiag 유틸리티
기타 관리 요소
클러스터링 (Clustering)
복제 (Replication)
전체 텍스트 검색 엔진 (Full-Text
Search)
* 각각 별도의 지식을 필요로 함 *
[email protected]