SQL Server™ 2000: DBA의 역할과 책임 하 성희

DBA의 임무와 책임 설치 및 환경 설정 보안 운영 업무 적절한 서비스 레벨 제공 시스템 가동 시간 극대화 문서화 작업 설계 및 개발 참여 또는 지원 기타

설치 및 환경 설정 DBA가 충분히 참여 문서화 작업 및 활용 목표 : 시스템을 SQL Server에 최적으로 구성 재작업 시 시간과 노력 절감 실수 최소화 일관된 환경 구성 (다중 서버 환경)

설치 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 속성(구성)]  [보안] 탭 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 - 권한 부여 금지 REVOKE [DENY] REVOKE - + DENY GRANT DENY

역할 관리 역할 로그인을 고정 서버 역할의 구성원으로 등록 : sp_addsrvrolemember 고정 서버 역할 / 고정 데이터베이스 역할 사용자 정의 데이터베이스 역할 로그인을 고정 서버 역할의 구성원으로 등록 : sp_addsrvrolemember 데이터베이스 사용자를 고정 데이터베이스 역할의 구성원으로 등록 : sp_addrolemember 역할을 만들고, 역할에 권한을 설정하고, 역할에 사용자를 추가

운영 서비스 관리 백업과 복원 사용자 및 권한 관리 시스템 데이터베이스 관리 사용자 데이터베이스 생성 및 관리 데이터베이스 여유 공간 점검 데이터베이스 일관성 (Consistency) 점검 인덱스 관리 통계 관리 점검 및 모니터링 장애 대처

서비스 관리 서비스 시작 시작 옵션 지정 서비스 중지

서비스 시작 필요한 서비스 시작 서비스 로그온 계정 적절하게 설정 시작 유형 설정 (필요한 경우) SQL Server SQL Server Agent Distributed Transaction Coordinator Microsoft Search Microsoft Message Queuing 서비스 로그온 계정 적절하게 설정 시작 유형 설정 (필요한 경우)

시작 옵션 지정 서비스 시작 매개 변수 설정 -m : 단일 사용자 모드 -Ttrace# : 추적 플래그 지정 엔터프라이즈 관리자 : [SQL Server 속성(구성)]  [일반] 탭  [시작 매개 변수] -m : 단일 사용자 모드 참고 : ALTER DATABASE <DB명> SET SINGLE_USER WITH ROLLBACK -Ttrace# : 추적 플래그 지정 예 : -T1204, -T3605

서비스 중지 중지 방법 일시 중지  중지 SHUTDOWN SQL Server 엔터프라이즈 관리자 쿼리 분석기 또는 osql 유틸리티 사용 자동 복구 작업량 (Automatic recovery) 최소화 SQL Server 엔터프라이즈 관리자 SQL Server 서비스 관리자 관리 도구의 [서비스] net stop mssqlserver 일시 중지  중지 SHUTDOWN 제외

백업과 복원 백업 복원 * 백업과 복원은 두 번째 세션에서 다루어지므로 세부 내용 생략 * 적절한 백업 전략 수립 주기적인 백업 체계 수립 주기적인 백업본 원복 테스트 복원 장애 발생 시 복원 시나리오 수립 및 테스트 * 백업과 복원은 두 번째 세션에서 다루어지므로 세부 내용 생략 *

넓은 의미의 백업 데이터베이스 백업 로그 백업 데이터 백업 (텍스트 파일) 스크립트 백업 데이터베이스 스크립트 백업 작업(Job) 스크립트 백업 복제 스크립트 백업

데이터베이스 스크립트 백업 주기적으로 데이터베이스 스크립트 저장 작업 방법 엔터프라이즈 관리자에서 [데이터베이스]  데이터베이스 선택  [도구]  [SQL 스크립트 생성]

작업(Job) 스크립트 백업 작업 스크립트 파일로 받아 내기 작업 목록 파일로 받아 내기 msdb 백업 [관리]  [SQL Server 에이전트]  [작업]  작업 선택  [모든 작업]  [SQL 스크립트 생성] 작업 목록 파일로 받아 내기 [관리]  [SQL Server 에이전트]  [작업]  [목록 내보내기] 파일 형식 지정 가능 msdb 백업

시스템 데이터베이스 관리 시스템 데이터베이스 주기적으로 백업 tempdb 크기 확장 / tempdb 이동 master, tempdb, model, msdb 주기적으로 백업 master msdb model (수정한 경우) disribution (서버를 복제 배포자로 구성한 경우) tempdb 크기 확장 / tempdb 이동

사용자 데이터베이스 생성 및 관리 CREATE DATABASE ALTER DATABASE DROP 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 지정한 데이터베이스 내 모든 오브젝트들의 할당과 구조적 무결성 검사 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) 순서 재정렬 Fillfactor 재설정 페이지들은 작업전과 동일한 파일에 남아 있음 1 2 3 5 4 6 7 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

오류 로그 구성 관리 오류 로그 파일 수 변경 오류 로그 파일 Cycle 엔터프라이즈 관리자 : [관리]  [SQL Server 로그]  [구성] 레지스트리 키 : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NumErrorLogs 오류 로그 파일 Cycle sp_cycle_errorlog / DBCC ERRORLOG AV 발생 등으로 인해 파일 크기가 비정상적으로 커진 경우 수행

프로세스 sp_who sp_lock DBCC INPUTBUFFER DBCC OPENTRAN SQL Server 프로세스에 대한 정보 제공 sp_lock 잠금 관련 정보 제공 DBCC INPUTBUFFER DBCC INPUTBUFFER (<spid>) 클라이언트에서 마지막으로 보낸 명령문 확인 DBCC OPENTRAN DBCC OPENTRAN (‘<DB명>’)

프로세스 강제 종료 - KILL 강제 종료 대상 KILL 권한 Processadmin 역할에 등록 예 운영에 지장을 주는 연결(Connection) 예: 잠금으로 다른 중요 프로세스 수행을 차단하는 프로세스 Orphaned session KILL 권한 sysadmin, processadmin 역할 Processadmin 역할에 등록 예 EXEC sp_addsrvrolemember ‘operator', 'processadmin' EXEC sp_helpsrvrolemember

성능 모니터링 시스템 모니터 활용 엔터프라이즈 관리자 활용 sp_who

성능 문제 해결 점검/모니터링  문제점 감지  성능 병목 원인 확인  튜닝  효과 분석  문서화 튜닝 시스템 튜닝 인덱스 튜닝 응용 프로그램 튜닝 지원

장애 대처 요령 당황하지 않는다 DBA가 직접 확인한다. 성급하게 결론을 내리지 않는다 신중하게 대처한다 문제를 더 악화시키지 않아야 한다 직접 처리하기 어려운 경우에는 지원을 요청한다 Sqldiag 유틸리티

기타 관리 요소 클러스터링 (Clustering) 복제 (Replication) 전체 텍스트 검색 엔진 (Full-Text Search) * 각각 별도의 지식을 필요로 함 *

Syhshh@hotmail.com