하나포스닷컴에서 SQL Server 의 역할 서명옥

Presentation on theme: "하나포스닷컴에서 SQL Server 의 역할 서명옥"— Presentation transcript:

1 하나포스닷컴에서 SQL Server 의 역할 서명옥 Mosuh@hanafos.com Mosuh@hotmail.com

2 SQL Server 의 역할  주요 서비스에 활용 높은 가격대 성능 비 높은 가격대 성능 비 향상된 기능 향상된 기능 편리한 관리 툴 편리한 관리 툴  패밀리, CP 들의 주 DB Server 로 활용 관리 및 유지보수의 편리함 관리 및 유지보수의 편리함 리소스 공유 리소스 공유

3 SQL Server 의 적용 사례  인증 및 회원정보 (DreamPass) 서비스 Membership, Session 관리용 Membership, Session 관리용  Billing 서비스 (DreamCash)  데이터웨어하우스 (HdDW)  Service Management System(DMS)  Content Management Service(XTree)  일반 서비스 자료실, 예매, 검색, 이벤트 등 자료실, 예매, 검색, 이벤트 등  패밀리 서비스 Mnet, CGV, 7Poker, Yasisi 등 Mnet, CGV, 7Poker, Yasisi 등

4 SQL Server 의 적용 사례 - DreamPass  구성도

5 SQL Server 의 적용 사례 - DreamPass  특징 인증과 회원 정보 제공 서비스의 분리 인증과 회원 정보 제공 서비스의 분리 HA 구성을 통한 System Fault 대비 HA 구성을 통한 System Fault 대비 단방향 Transactional Replication 구축을 통한 Disk Fail 대비 및 OLTP 와 DSS 업무 분리 단방향 Transactional Replication 구축을 통한 Disk Fail 대비 및 OLTP 와 DSS 업무 분리 Login 정보 기록용 별도 DB Server 사용 Login 정보 기록용 별도 DB Server 사용 url : http://www.hanafos.com url : http://www.hanafos.comhttp://www.hanafos.com

6 SQL Server 의 적용 사례 - Single Sign On(DreamPass)  데이터베이스 설계 및 구성 시 고려 사항 용도별 DB 구분 용도별 DB 구분 최적화된 Column 선정 및 Data Length 설정 최적화된 Column 선정 및 Data Length 설정 Stored Procedure 적극 활용 Stored Procedure 적극 활용 User Defined Function 이용한 Code 간소화 User Defined Function 이용한 Code 간소화 이력 데이터 정리 방안 수립 이력 데이터 정리 방안 수립 고객 서비스 중심의 Index 생성 고객 서비스 중심의 Index 생성 다수의 파일 그룹 생성 다수의 파일 그룹 생성

7 SQL Server 의 적용 사례 - Billing( 드림캐쉬 )  구성도  향후 구성 계획

8 SQL Server 의 적용 사례 - Billing( 드림캐쉬 )  특징 Backup & Restore 를 이용한 Standby Server 구축 Backup & Restore 를 이용한 Standby Server 구축 이용자 서비스와 통계용 DB Server 분리 이용자 서비스와 통계용 DB Server 분리 url : http://cash.hanafos.com url : http://cash.hanafos.comhttp://cash.hanafos.com  문제점 서비스 DB 에 OLTP 및 DSS 용 Index 서비스 DB 에 OLTP 및 DSS 용 Index Data Modification 부하 발생 Data Modification 부하 발생 데이터 파일 크기 증가에 따른 관리 부담 가증 데이터 파일 크기 증가에 따른 관리 부담 가증

9 SQL Server 의 적용 사례 - 데이터웨어하우스 (HdDW)  구성도

10 SQL Server 의 적용 사례 - 데이터웨어하우스 (HdDW)  특징 SQL Server 7.0 및 MS OLAP Server 이용한 초기 구축 SQL Server 7.0 및 MS OLAP Server 이용한 초기 구축 SQL Server 2000 및 MS Analysis Service 로 업그레이드 SQL Server 2000 및 MS Analysis Service 로 업그레이드 50GB/ 일 Raw 데이터 처리 후 Summary 데이터만 유지 50GB/ 일 Raw 데이터 처리 후 Summary 데이터만 유지 통계 및 다차원 분석 기능 제공 통계 및 다차원 분석 기능 제공 SessionDB 및 MemberShip DB 분석 통한 개인화 적용 SessionDB 및 MemberShip DB 분석 통한 개인화 적용

11 SQL Server 의 적용 사례 - SMS(DMS) : Architecture Management Server Regacy inventory Server inventory Service inventory Web Browser Managed Server WMI, SNMP, TCP Remote Manager Alert Manager Log Manager SMS, E-Mail, Messenger Management Web Server System Monitor Management Console OLEDB Inventory Manager Agents Management Report Manager Alerts Manager Audit Manager Network Device Performance Monitoring Log Manager Trends Manager System Monitoring

12 SQL Server 의 적용 사례 - SMS(DMS)  특징 Server 리소스 관리 자동화 Server 리소스 관리 자동화 Network 현황 관리 Network 현황 관리 Error 발생에 대한 Alerting Error 발생에 대한 Alerting 리소스 과다 사용 서버에 대한 Alerting 리소스 과다 사용 서버에 대한 Alerting Server 및 Service Monitoring Server 및 Service Monitoring Service 관리 Service 관리 패밀리 권한 및 정보 관리 패밀리 권한 및 정보 관리 장애 관리 장애 관리

13 SQL Server 의 적용 사례 - 패밀리 서비스  패밀리 모델의 특징 Infra 제공 (HW,SW, Membership, Billing, Mail, VOD, FTP 등 ) Infra 제공 (HW,SW, Membership, Billing, Mail, VOD, FTP 등 ) 마케팅 툴 제공 마케팅 툴 제공 멤버쉽 공유 멤버쉽 공유 패밀리 각자의 도메인 사용 (www.mnet27.co.kr, www.cgv.co.kr) 패밀리 각자의 도메인 사용 (www.mnet27.co.kr, www.cgv.co.kr)www.mnet27.co.krwww.cgv.co.krwww.mnet27.co.krwww.cgv.co.kr 서비스 모니터링 서비스 모니터링 서비스 Tuning 및 Troubleshooting 서비스 Tuning 및 Troubleshooting 신규 사이트에 대한 품질 테스트 신규 사이트에 대한 품질 테스트

14 SQL Server 의 적용 사례 - 패밀리 서비스  패밀리 사이트에 대한 DBA 의 역할 부분적인 DB 설계 지원 부분적인 DB 설계 지원 부분적인 데이터 마이그레이션 지원 부분적인 데이터 마이그레이션 지원 SQL Server 환경 구성 및 DB 생성 SQL Server 환경 구성 및 DB 생성 DB Stress Test DB Stress Test Query 및 Index Tuning Query 및 Index Tuning Backup 및 Restore Backup 및 Restore Data Consistency Check Data Consistency Check Blocking Monitor Blocking Monitor File Size 관리 File Size 관리

15 하나포스닷컴 내의 SQL Server 관리 노하우 서명옥 Mosuh@hanafos.com Mosuh@hotmail.com

16 SQL Server 운영 정책  Goal  설치 및 환경 구성  데이터베이스 생성 및 운영  계정 및 권한  Backup 및 Restore  Replication  유지보수 및 모니터링  개발자 Tips  Tuning

17 SQL Server 운영 정책 - Goal  안정적인 고품질 서비스 유지 장애 방지 장애 방지 24 시간 모니터링 24 시간 모니터링 동일한 Response Time 동일한 Response Time 신속한 복구 신속한 복구 최소의 인원으로 최대의 관리 효과를 … 최소의 인원으로 최대의 관리 효과를 …

18 SQL Server 운영 정책 - 설치 및 환경 구성  무인 설치  SQL Server 메모리 고정 ( 주요 서버 대상 )  Client 와 Server 의 MDAC Version 일치  신규 Service Pack 적용 시 사전 테스트  SQLServer 와 SQLAgent Service 계정으로 Domain Account 등록 ( 설치시 )  Alert 용 SQLAgent Mail Session 및 SQLMail 설정  불필요한 Service 정지 IIS 관련 서비스, Search, DTC 서비스등 IIS 관련 서비스, Search, DTC 서비스등

19 SQL Server 운영 정책 - 설치 및 환경 구성 ( 계속 …)  2~4GB Memory 보유 시스템에 대한 작업 Boot.ini 에 /3GB Option 설정 Boot.ini 에 /3GB Option 설정 Q283037 Q283037 Q283037  4GB 이상의 Memory 보유 시스템에 대한 작업 Boot.ini 에 /PAE Option 설정 Boot.ini 에 /PAE Option 설정 Configuration Parameter 조정 Configuration Parameter 조정 Awe enabled 설정 Awe enabled 설정 SQL Server Memory 고정 SQL Server Memory 고정 SQL Server 용으로 실제 메모리 예약 SQL Server 용으로 실제 메모리 예약

20 SQL Server 운영 정책 - 데이터베이스 생성 및 운영  Data File 크기 제한  Data 및 Log File 의 초기 크기 여유 있는 설정  FileGroup 및 File 을 이용한 대용량 테이블의 분리  “ 메가 단위로 ” 파일 속성 설정  파일 크기 자동 증가 Job 설정  여유 공간 임계치 이상의 File 에 대한 축소 Job 설정  주기적인 Log Backup 수행으로 Log File 증가 방지

21 SQL Server 운영 정책 - 계정 및 권한  SQL Server 인증 방식 이용  DBA Group 에 System Administrators 서버 역할 할당  Sa 보안 및 개발자의 원활한 작업을 위해 데이터베이스 별 DBO 할당  db_datareader 데이터베이스 역할 계정 활용  db_owner role 이용 제한

22 SQL Server 운영 정책 - Backup 및 Restore  1 회 / 일 Database Full Backup  1 회 / 시간 Log Backup(Point-in-Time Recovery 를 위한 필수 사항 )  신속한 백업을 위한 Disk Backup 수행 후 백업 미디어로 재백업 수행  신속한 복구를 위해 Disk 상에 이틀 이상의 백업 파일 보관  Backup Job 이 DB 의 생성, 삭제 인식 후 자동 Backup 진행  Master, MSDB, Distribution 백업 수행

23 SQL Server 운영 정책 - Replication  도입 목적 Standby Server Standby Server  활용 범위 관리자, Call Center Application, DW 관리자, Call Center Application, DW  구현 방법 단방향 트랜잭션 복제 단방향 트랜잭션 복제  사전 준비 Publication 대상 테이블에 Primary Key 생성 Publication 대상 테이블에 Primary Key 생성 Subscriber 를 Linked Server 로 사전 등록 Subscriber 를 Linked Server 로 사전 등록

24 SQL Server 운영 정책 - Replication( 계속.)  설정 테이블 단위의 Publication 설정 테이블 단위의 Publication 설정 Naming Rule : dbname_Tblname Naming Rule : dbname_Tblname Push Subscription Push Subscription 거의 실시간 복제 가능 거의 실시간 복제 가능  구독 초기화 NoSync 방식 : 초기 데이터가 많은 경우 이용 NoSync 방식 : 초기 데이터가 많은 경우 이용 Sync 방식 : Schema 및 데이터 초기화 Sync 방식 : Schema 및 데이터 초기화

25 SQL Server 운영 정책 - Replication( 계속.)  추가 작업 Identity 보유 테이블에 대한 처리 Identity 보유 테이블에 대한 처리 구독 테이블에 Identity 설정 구독 테이블에 Identity 설정 Custom Stored Procedure 내 Source 수정 필요 Custom Stored Procedure 내 Source 수정 필요Ex) create procedure "sp_MSins_test" @c1 int,@c2 char(10) ASBEGIN set identity_insert test on insert into "test"( "c1", "c2" ) values ( @c1, @c2) set identity_insert test off END

26 SQL Server 운영 정책 - Replication( 계속.)  복제 설정 후 주의 사항 Stored Procedure, UDF 등의 자동 복제가 안되므로 발행, 구독 데이터베이스에 동시 수정 ( 현 설정 구조상 ) Stored Procedure, UDF 등의 자동 복제가 안되므로 발행, 구독 데이터베이스에 동시 수정 ( 현 설정 구조상 ) 게시 테이블 일부 속성 변경 시 복제 안됨 게시 테이블 일부 속성 변경 시 복제 안됨 게시 테이블 일부 속성 변경 제한 게시 테이블 일부 속성 변경 제한 컬럼, 추가 삭제 방식 변경 ( 게시 메뉴에서 수행 ) 컬럼, 추가 삭제 방식 변경 ( 게시 메뉴에서 수행 )

27 SQL Server 운영 정책 - 유지보수 및 모니터링  Index Rebuild(DBCC DBReindex, Create Index)  DBCC IndexDefrag  DBCC CheckDB  Update Statistics  Idle Connection 정리  Long Running Transaction 확인  Blocking Monitoring  EvenLog 확인

28 SQL Server 운영 정책 - 유지보수 및 모니터링 ( 계속.)  CPU 사용량 확인  Network Traffic 확인  Disk 여유 공간 확인  IIS 의 Request Queue Length 확인  SQL Server Port Alive 확인  SQL Server Connection 가능 확인  SQLAgent Job, SMS, SQLMail, SQLAgent Mail Session 을 이용한 자동 모니터링

29 SQL Server 운영 정책 - 개발자 Tips Code 저장용 Column 에 대한 Character 사용 제한 Code 저장용 Column 에 대한 Character 사용 제한 Client Side Cursor 이용 Client Side Cursor 이용 Result Set 제한 Result Set 제한 Select … from tblname with(nolock) 이용 Select … from tblname with(nolock) 이용 Stored Procedure 이름으로 sp_ prefix 사용 금지. Q263889 Stored Procedure 이름으로 sp_ prefix 사용 금지. Q263889Q263889 Fully Qualified Object Name 사용. Fully Qualified Object Name 사용. DBO 만 Object 생성 허용 DBO 만 Object 생성 허용

30 SQL Server 운영 정책 - 개발자 Tips( 계속 ) OLE DB for SQL Server 이용 OLE DB for SQL Server 이용 TCP/IP Socket 이용 TCP/IP Socket 이용 Stored Procedure 내에서 Input Parameter 변경 금지 혹은 Sub Procedure 이용 Stored Procedure 내에서 Input Parameter 변경 금지 혹은 Sub Procedure 이용 Create Proc P_s @inParam int as Select * from X where Col1=@inParam Retrun Create Proc P_a @inParam int as Declare @iMul int Set @iMul=@inParam*15 Exec P_s @iMul Return Ex) create proc P_a @inParam int as Declare @iMul int Set @iMul=@inParam*15 Select * from X where Col1=@iMulReturn

31 SQL Server 운영 정책 - Tuning Index Tuning 이 가장 효과적인 성능 개선 방법임 Index Tuning 이 가장 효과적인 성능 개선 방법임 Query Tuning 으로 부가적인 성능 개선 가능 Query Tuning 으로 부가적인 성능 개선 가능 Ex) nolock, top 이용, where 문 최적화 Tuning Tool Tuning Tool Profiler, Query Analyzer Profiler, Query Analyzer

32 용도 및 규모별 관리 방안  용도에 따른 관리 Mission Critical vs. DSS Mission Critical vs. DSS  규모에 따른 관리 대용량 Database vs. 대용량 Database vs. 다수의 소규모 Database

33 용도 및 규모별 관리 방안 - Mission Critical vs. DSS Mission Critical Database Mission Critical Database 빈번한 Log Backup 수행 빈번한 Log Backup 수행 Index Defragmentation 수행 Index Defragmentation 수행 최소의 Index 유지 최소의 Index 유지 Transaction Monitoring Transaction Monitoring DSS DSS Log Truncate 후 Database Backup Log Truncate 후 Database Backup Update Statistics 수행 Update Statistics 수행 Table 혹은 Database 단위의 Index Rebuild Table 혹은 Database 단위의 Index Rebuild

34 용도 및 규모별 관리 방안 - 대용량 DB vs. 다수의 소규모 DB 대용량 DB 대용량 DB FileGroup 을 이용한 데이터 분산 FileGroup 을 이용한 데이터 분산 Index 또는 테이블 단위의 Index Rebuild Index 또는 테이블 단위의 Index Rebuild 한 서버에 많은 소규모 DB 가 존재하는 경우 한 서버에 많은 소규모 DB 가 존재하는 경우 DB 단위의 Index Rebuild DB 단위의 Index Rebuild Idle Connection Monitoring Idle Connection Monitoring CPU Usage Monitoring CPU Usage Monitoring

35 유지보수 작업 적용 예  Backup Job  Blocking Monitoring Job  File Extension Job