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

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

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

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

4. 설치 • DBA가 하드웨어, SQL Server 이외의 소프트웨어 설치에도 참여 • 설치 전 계획 수립 • 테스트용 시스템에 사전 설치 테스트 • 설치 후 설치 과정 문서화

5. 환경 설정 • 하드웨어 • 예: RAID • 소프트웨어 • 네트워크 • 구성 (Configuration) 관리

6. 구성(Configuration) 관리 • 설정 방법 • sp_configure / RECONFIGURE • 엔터프라이즈 관리자 • 옵션 계층 • 서버 옵션 < 데이터베이스 옵션 < SET 옵션 < 참고(hint) • 설정 내용 및 변경 이력 문서화 요망 • 하드웨어를 충분히 활용하도록 설정

7. 보안 • 사용자 관리 • 시스템 감사 (Audit) • 네트워크

8. 사용자 관리 • 사용자 로그인 관리 • 데이터베이스 사용자 관리 • 권한(Permission) 관리 • 역할(Role) 관리

9. SQL Server 보안 모델 SQL Server 컴퓨터에 연결 SQL Server에게 로그인 인증 요청 SQL Server에 연결 원하는 데이터베이스로 연결 및 액세스 인가 데이터베이스 컨텍스트 설정 원하는 작업 수행 작업 권한 검증 (데이터베이스 내의 모든 작업에 대해 권한 검증)

10. 사용자 로그인 관리 • 인증 모드 • Windows 통합 인증 / 혼합 모드 인증 • 엔터프라이즈 관리자 : [SQL Server 속성(구성)]  [보안] 탭 • SQL Server 로그인 등록 • sp_addlogin / sp_grantlogin • 엔터프라이즈 관리자 : [보안]  [로그인] • BUILTIN\Administrators 그룹의 구성원인 Windows NT 사용자는 자동으로 sysadmin 서버 역할의 구성원이 됨

11. 사용자 로그인 관리 • 로그인 삭제 • sp_droplogin • sp_revokelogin : Windows NT 사용자/그룹 로그인 제거 • 데이터베이스 사용자 삭제 후 작업 • 만들 때와 역순

12. 데이터베이스 사용자 관리 • SQL Server 로그인 등록 후 작업 • 등록 방법 • sp_adduser / sp_grantdbaccess • 엔터프라이즈 관리자 : 데이터베이스  [사용자]

13. GRANT 권한관리 • GRANT - 권한 부여 • REVOKE - GRANT 또는 DENY 취소 • DENY - 권한 부여 금지 GRANT REVOKE [DENY] REVOKE - + DENY DENY

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

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

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

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

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

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

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

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

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

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

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

25. 사용자 데이터베이스 생성 및 관리 • CREATE DATABASE • 충분한 공간 할당 • 최대 크기 지정 • ALTER DATABASE • 복구 모델 변경 • 공간 추가 및 제거 • 크기, 최대 크기, 증가량 변경 등등등 • DROP DATABASE

26. 데이터베이스 축소 • 축소 방법 • DBCC SHRINKDATABASE • DBCC SHRINKFILE • 데이터베이스 옵션 ‘autoshrink’ 을 true로 설정 • 트랜잭션 로그 축소 • 로그 파일 크기가 커진 경우 • 로그 백업 또는 삭제  DBCC SHRINKFILE

27. 데이터베이스 이동 • 이동 방법 • sp_attach_db & sp_detach_db • BACKUP & RECOVERY • 로그 전달 (Log-shipping) • 테스트 환경 구축 • 서버 이전

28. 데이터베이스 여유 공간 점검 • 주기적으로 DB별로 free space를 점검하여 충분한 space 미리 확보 • 성능 측면 & 장애 예방 측면 • sp_spaceused • sysindexes 테이블의 부정확성 정정 • @updateusage = 'TRUE' • DBCC UPDATEUSAGE

29. 데이터베이스 일관성 점검 • DBCC CHECKDB • 지정한 데이터베이스 내 모든 오브젝트들의 할당과 구조적 무결성 검사 • DBCC CHECKTABLE • 테이블이나 인덱스된 뷰에 대하여 데이터, 인덱스, text, ntext, image 페이지의 무결성 검사

30. DBCC CHECKDB - 점검 • 데이터베이스 무결성 검사 • 유의 사항 • 테이블 검사 중 DDL 문 블로킹 발생 • 사용 시 권장 사항 • 시스템 사용이 적을 때 실행 • 디스크 백업 등의 다른 디스크 I/O를 수행하지 않아야 함 • 수행에 필요한 tempdb 공간 확인 • ESTIMATEONLY 옵션

31. DBCC CHECKDB – 오류 복구 • 오류 복구 • REPAIR_ALLOW_DATA_LOSS • REPAIR_FAST • REPAIR_REBUILD • 작업 방법 EXEC sp_dboption ‘<DB명>', 'single', true DBCC CHECKDB (‘<DB명>', ‘<복구 옵션>')

32. 인덱스 관리 • 인덱스 만들기 • 인덱스 삭제 • 인덱스 조각화 제거

33. 인덱스 만들기 • 효율적인 인덱스 디자인 • 성능에 필수 • 쿼리를 충분히 분석 • 인덱스 튜닝 마법사 활용 • 시스템을 사용하는 동안 지속적인 인덱스 관리 • 인덱스 별로 적절한 fillfactor 설정 • CREATE INDEX • CREATE TABLE / ALTER TABLE (primary key, unique 제약 조건)

34. 인덱스 만들기 • 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)

35. CREATE INDEX가 쿼리처럼 실행됨예 • 테이블 T에 인덱스 i1 on (a,b,c) 이 존재 • CREATE INDEX i2 on T (b,c)  전체 테이블 대신 인덱스 i1 을 스캔 • CREATE INDEX i3 on T (a,b)  인덱스 i1 을 스캔 (정렬 작업은 수행되지 않음)  인덱스 생성 순서 결정 시 고려

36. 인덱스 삭제 • 불필요한 인덱스 삭제 • DROP INDEX • ALTER TABLE (primary key, unique 제약 조건)

37. 인덱스 조각화 제거 • 목적 : 성능 향상 • 조각화 상태 확인 • 분석 후 재구성 여부 결정 • 인덱스 조각 모으기 • DBCC INDEXDEFRAG • 인덱스 재구성 • DBCC DBREINDEX • 클러스터된 인덱스 삭제 후 다시 생성 • CREATE INDEX … DROP_EXISTING

38. 조각화 상태 확인DBCC SHOWCONTIG • 테이블의 데이터와 인덱스 조각화 정보 제공 • INSERT, UPDATE, DELETE 시 조각화 발생

39. 인덱스 조각 모으기DBCC INDEXDEFRAG • 온라인 인덱스 재구성  명령 실행 중에도 인덱스 사용 가능 • 성능 영향 적음 • 최소의 데이터 공간 사용 • 중지 가능 및 재시작 가능 • 재구성보다 느림 • 로그 백업이 커짐 • 조각화가 완전하게 제거되지 않음

40. 60% 4 1 2 7 6 3 5 1 2 3 4 5 6 7 온라인 인덱스 재구성DBCC INDEXDEFRAG • 인덱스의 잎 레벨(leaf level) 순서 재정렬 • Fillfactor 재설정 • 페이지들은 작업전과 동일한 파일에 남아 있음

41. 인덱스 재구성DBCC DBREINDEX • 블로킹 유발  가능한 한 서비스 중단 후 수행 • 제약 조건 삭제하고 다시 만들 필요 없음 • DBCC DBREINDEX (‘<테이블명>’,’’,<fillfactor>) • 테이블의 모든 인덱스를 다시 작성 • DBCC DBREINDEX (‘<테이블명>’,’<인덱스명>’,<fillfactor>) • 테이블의 해당 인덱스를 다시 작성

42. 통계 (Statistics) 관리 • 통계 만들기 • 통계 조회 • 통계 갱신

43. 통계 만들기 • 인덱스가 있는 컬럼에는 통계가 자동으로 만들어짐 • 데이터베이스 옵션 ‘auto create statistics’가 true  인덱스 없는 컬럼이 조건절이나 조인에 사용되면 자동으로 통계 만들어짐 • 수동으로 통계 만들기 • CREATE STATISTICS

44. 통계 조회 • sp_helpstats • DBCC SHOW_STATISTICS • STATS_DATE

45. 통계 갱신 • UPDATE STATISTICS • sp_updatestats • 데이터베이스 옵션 ‘auto update statistics’ • 데이터가 많이 추가되거나 변경되거나 제거되는 경우  통계 갱신 • 쿼리 계획이 부적절한 경우 통계에 문제가 있을 가능성 있음  통계 갱신

46. 점검 및 모니터링 • SQL Server 오류 로그 • 프로세스 • 성능 모니터링

47. SQL Server 오류 로그 • 주기적으로 errorlog 파일 점검 요망 • 확인 방법 • xp_readerrorlog 사용 • EXEC master..xp_readerrorlog • EXEC master..xp_readerrorlog 3 • 엔터프라이즈 관리자 • [관리]  [SQL Server 로그] • 텍스트 에디터 • Program Files\Microsoft SQL Server \Mssql\Log\Errorlog

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

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

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