수업목적 저작물 이용 가이드라인 · 2020-03-31 · 가이드라인 수업목적 저작물 이용 가이드라인 2015. 06. 수업목적 저작물 이용 가이드라인
Integration Services Best...
Transcript of Integration Services Best...
SQL Server Integration Services Best Practices
한국마이크로소프트(유)
Premier Field Engineer
한대성
발표자 소개
• [경력] – (현)Microsoft Premier Field Engineer
– 옥션 DBA – DW System 구축/관리
– AD Consulting 책임 컨설턴트
– (2006~2008) SQL MVP
– SSIS Pocket Book (2007)
– 실젂 SQL Server MVP 53 번역 (2010)
• [활동 커뮤니티] – SQLLeader.com
다룰 내용
• SSIS Pain Points
• 데이터 변환에서 매핑 처리
• 패키지 관리 최적화
• 데이터 흐름 최적화
• 패키지 보안 관리
• 패키지 모니터링
• 이기종 데이터 처리 최적화
• 패키지 마이그레이션
본 세미나는 Exchange Migration 및
구축 활용 방안에 대하여 소개해 드리고자 합니다.
SSIS Pain Points
• 데이터 처리가 까다롭다 – Data Type, Size 등 암시적 허용을 허락하지 않는다.
• 패키지 개발이 어렵다
– 생소한 태스크나 변환이 많다.
• 개발 후 실행 시 처리가 안 된다
– 개발 환경에서는 잘 되는데, 서버에 등록만 하면 에러가 발생한다.
• 에러 메시지 해독하기가 어렵다
– 어디서, 무엇을, 어떻게 봐야 하는가?
• 마이그레이션이 어렵다
– DTS 패키지에서 SSIS 패키지로 어떻게 이관?
– 기졲 DTS 패키지를 그냥 쓸 수는 없나?
데이터 변환에서 매핑 처리 • 유니코드(nchar, nvarchar), 비 유니코드(char, varchar)간 자동 변환
허용하지 않음
• 대상 열의 데이터 크기가 적은 경우 유효성 경고
• 호환되지 않는 데이터 타입인 경우 매핑 실패
• 해결 방법 1. 데이터 변환이나 파생 열 변환을 이용해서 원본의 데이터 타입을 변환한다.
2. 원본 쿼리에서 형 변환을 수행해서 가져옦다.
3. 변환해야 할 열이 많은 경우, 데이터 가져오기/내보내기 마법사 이용해서 자동 변환 수행 패키지로 저장한 후, 이를 복사해서 사용
패키지 관리 최적화 • 패키지를 파일로 저장
– 기본 저장 방식
– TFS등을 이용해서 버젂 관리 가능
– SQL Server 없이도 패키지 실행 가능
– SQL Server 저장 방식에 비해 실행 속도가 빠름
– 파일 관리 필요 - 백업 / 파일 보안
– 사용자 키 기반 또는 암호 기반 패키지 암호화 설정 필요
• 패키지를 DB에 저장 – 기졲 DTS와 동일한 방식
– 패키지 암호화 관리를 DB에서 수행 가능
– DB Backup 만으로 패키지 백업 및 관리
– 패키지 배포, 수정 시 어려움 – 파일로 내려 받은 후, 수정하고 다시 DB에 저장
자주 추가되거나 변경될 수 있는 경우, 파일로 저장하는 것을 권장. 거의 변경이 없는 경우, DB에 저장하는 것을 권장
데이터 흐름 최적화 ★ 패키지에서 발생하는 대부분의 성능 이슈는 데이터 흐름에서 발생
– Memory/Threads 등 리소스 부족
– 외부 DB 연결 및 변환 오류
– 조회(Lookup) 변환으로 인한 처리 성능 저하
• 최적화 방법 – SSIS 데이터 흐름 내에서는 가능한 한 동기식 변환만을 사용
• 동기식 변환 - 파생 열 변환, 열 복사, 멀티캐스트, 조건부 분할 등
• 반동기식 변환 – 행 샘플링
• 비동기식 변환 – 정렬, 집계
- 정렬, 집계 등의 작업은 가급적 DB 서버에서 처리하도록 구성
- 적젃한 데이터 원본 및 변환 유형 사용
• ODBC, ADO.NET 대싞 OLE DB 사용
• Local SQL Server인 경우, SQL Server 대상 사용 (Cluster 시 불가)
• Oracle인 경우, Attunity Provider(2008 Enterprise Only)
- 하나의 데이터 흐름 작업 내에는 가급적 하나의 처리 흐름만 가지도록 구성
- 데이터 연결의 Packet Size를 조젃 (ex:32767)
데이터 흐름 최적화 • 데이터 이관 작업 최적화 단계
1) 데이터 원본에서 읽어오는 성능을 분석 • 데이터 원본에 다른 변환이나 대상을 제거하고, 멀티 캐스트나 조건부 분할과 같은 Dummy 변환작업
만을 연결 - 이 속도가 최대 처리 성능임
• SQL Server인 경우, 읽어오는 쿼리 튜닝 or WITH (NOLOCK) 옵션 등을 추가해서 성능 비교
• Provider 유형을 변경하면서 성능 비교 – ADO.NET, OLE DB, 기타 Provider
2) 데이터 원본에 필요한 변환을 추가해서 처리 성능 분석 • 데이터 원본에서만 읽어오는 속도와 비교 – 변환에서 처리 시간이 오래 걸리는지 판단
3) 데이터 대상을 추가해서 처리 성능 분석 • 대부분의 성능 저하는 이 단계에서 발생
• 원본에서 읽어오는 속도 및 변환을 추가했을 때의 속도와 비교
• 테이블에 저장할 때 Bulk Insert 방식으로 처리되는지, 행 단위로 처리되는지를 분석
패키지 보안 관리 • 패키지를 만드는 계정과 실행하는 계정 간의 차이로 인해 암호 데이
터(Sensitive Data) 해독 오류 발생 – SQL Server 로그인 암호, FTP 사용자 암호 등
• 해결 방법 – 암호를 사용하도록 설정 – ProtectionLevel = EncryptSensitiveDataWithPassword
– SQL Agent Proxy 기능 이용
– 패키지를 DB에 저장 - ProtectionLevel=ServerStorage
– http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=1238
패키지 모니터링 • 실행 모니터링
– SSIS의 기본 로깅 방법 이용
• 주요 이벤트 : OnPreExecute, OnPostExecute, OnTaskFailed, OnError, OnPipelineRowsSent
– Custom Monitoring Report 구성 가능
• 에러 메시지 분석
– SQL Agent Log 분석 or SSIS 패키지에서 로그 설정
– 누가 실행했으며, 어떤 단계에서 어떤 이유로 에러가 발생했는가?
– 자식 태스크가 실패하면 부모 태스크도 오류로 처리됨 • 에러를 발생시킨 원인 태스크와 희생자 태스크 구분 필요
– Event Handler의 Error Description 시스템 변수 이용해서 에러 메시지만 캡쳐
패키지 모니터링
• 에러 메시지 분석
– 어떤 계정으로 실행?
– 실행 환경은? 32bit/64bit
– 최초 에러가 발생한 부분은?
– 패키지 젂체 실행 결과는?
이기종 데이터베이스 연결 • Oracle
– MSDAORA • 32bit만 가능, 대상으로 사용 시 Bulk Insert 불가, 모든 문자열 데이터를 비 유니코드(DT_STR)로 처리, Oracle
하위 버젂(8.x 이하)에서만 사용 권장
– ORAOLEDB • 32bit, 64bit 버젂 별도 졲재 (모두 설치 필요), 대상으로 사용 시 Bulk Insert 불가, 모든 문자열 데이터를 유니코
드(DT_WSTR)로 처리, MSDAORA보다 성능이 좋음
– Attunity Provider • SQL 2008 Enterprise 에서 설치/사용 가능. 문자열 데이터를 원본 형태대로 처리. Bulk Insert 가능. Oracle 데이
터 처리 시 가장 성능이 좋음
• DB2 – MS OLE DB Provider for DB2 및 IBM OLE DB Provider for DB2
• 처리 성능이 비슷함
• 32bit/64bit 동시 사용 가능
– 데이터 처리 시, 젂자(Full-width) 처리 작업이 필요한 경우도 있음 SQL CLR 등을 이용
• MYSQL – ODBC를 이용한 처리
– 32bit용으로 처리를 해야 할 경우, 패키지 실행 부분에서 “Use 32 bit runtime” 옵션 체크
패키지 마이그레이션 • DTS 패키지
– 32bit Application
– SSIS 패키지로 재 작성하는 것을 권장
• 패키지 마이그레이션 – 대부분의 경우, SSIS에서 자동 변환 가능
– 동적 속성 작업, 데이터 흐름 작업(Pump 작업)에서 스크립트를 사용한 경우 등 일부 형태에 대해서는 마이그레이션 불가
• 기졲 DTS 패키지 사용 방안 – SSIS 패키지로 재 작성
– SSIS 패키지 내에서 DTS 패키지 개체로 포함
– Database에 레거시(Legacy) DTS에 등록해서 사용
: dtsrun.exe를 이용해서 예약 작업 설정 가능
Q & A
SQL Server Integration Services
Best Practices