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