[SQL 서버 특강] ③ 합리적인 DTS 실행 비법

DTS 사용 시나리오는 아래와 같이 데이터베이스 관리, 개발의 거의 전 영역에 걸쳐 관계를 맺고 있다.

데이터웨어하우징 - 트랜잭션 프로세싱 시스템에서 데이터를 복사, 보고서용 롤업(roll-up) 테이블 메꿈
OLAP 큐브 구축
플랫(flat) 파일과 비 데이터베이스에서 데이터베이스로 데이터를 대량 복사
MS 오피스 문서 보고서 생성
분산 트랜잭션 코디네이터(DTC)를 이용해 멀티데이터베이스 작동을 수행

DTS는 데스크톱 PC에 설치된 프로그램이나 필요할 경우 DTS 패키지를 실행할 수 있도록 한 웹사이트를 통해 활성화된다. DTS 패키지 설치에 있어 가장 복잡한 결정 중 하나는 DTS 패키지를 사용할지, 사용한다면 어떤 방법을 택할지 선택하는 것이다.

패키지 설치 방법

DTS 패키지를 적재적소에 사용할 수 있도록 설정하려면 몇가지 사항을 고려해야 한다. 하나씩 살펴보자.

▲SQL 서버 잡(job)

SQL 서버에서 잡을 생성하고 sp_start_job 저장 프로시저를 호출할 수 있다.

sp_start_job은 비동기 프로세스라는 것이 단점이다. 성공하든 실패하든 그 결과를 볼 수 없기 때문에 sp_help_job 시스템 저장 프로시저를 통해 결과를 정기적으로 조사할 수밖에 없다.

성공이나 실패 여부를 우려하지 않고 호출하려 하는 경우 비동기 방식의 잡은 데스크톱이나 웹 애플리케이션 개발을 복잡하게 만들 수 있다. 잡은 관리자 권한이 없어도 실행될 수 있다. 그러나 이렇게 설정하려면 몇가지 절차가 더 필요하다.

▲클라이언트 데스크톱에 DTS DLL 설치

두번째 방식은 엔터프라이즈 매니저 프로그램이나 DTS DLL을 컴퓨터에 설치하고 DTS 패키지를 호출하는 것이다. DTS 패키지를 사용자 컴퓨터에서 수행하는 것은 가능한 대안이긴 하지만 각 컴퓨터마다 설치를 해야 하며 DTS 패키지가 업데이트됐을 때 배포 방안을 만들어놔야 한다는 것이 단점이다.

▲Sp_OA 확장 저장 프로시저 사용

이 방식이 바로 이번 강좌에서 집중적으로 살펴볼 것으로 sp_OA 시스템 저장 프로시저를 활용해 DTS 패키지를 프로그램적으로 호출하는 것이다. Sp_OA 시스템 저장 프로시저를 사용하면 sp_start_job 프로시저를 이용하거나 DTS DLL을 데스크톱에 설치함으로써 생기는 제약점에 영향을 받지 않게 된다.

VBS를 이용해 DTS 패키지를 호출하자

DTS 패키지를 수행하기 위한 저장 프로시저를 구축하는 것은 VBS(VBScript) 스크립트 작성에서 시작한다. 사실 sp_OA 저장 프로시저를 직접 사용하는 것은 좀 어설플 수 있기 때문에 sp_OA 저장 프로시저로 코드를 구현하기 전에 VBS로 원하는 스크립트 코드를 구현하는게 제일 좋다. 게다가 코드 작성에 비주얼 베이직을 사용하기 때문에 스크립트 개발도 더 단순화된다.

그림 1에 있는 라이브러리를 프로젝트 레퍼런스에 추가함으로써 DTS 패키지 오브젝트 라이브러리를 참조시킬 수 있다. DTS 패키지를 실행하기 위한 VBS 스크립트는 이곳에서 볼 수 있다.

코드에서 보다시피 LoadFromStorageFile 함수를 사용했다. 개발은 대부분 테스트 환경에서 진행된다. DTS의 구조적 파일 포맷은 테스트 환경에서 실제 업무 환경으로 이전할 때 가장 빛을 발한다.

Sp_OA를 구현하자

VBS 스크립트를 작성했다면 sp_OA 확장 저장 프로시저를 이용해 코드를 구현할 준비가 된 것이다. VBS 스크립트와 마찬가지로 sp_OA 시스템 저장 프로시저도 오브젝트 라이브러리의 COM+ API와 상호 작용할 수 있도록 한다.

Sp_OACreate는 비주얼 베이직이나 VBS에서 호출하는 CreateObject 함수와 유사하다. Sp_OAGetProperty, sp_OASetProperty, 그리고 sp_OAMethod를 사용하면 오브젝트 라이브러리의 속성과 함수를 사용할 수 있다.

비주얼 베이직이나 VBS와 달리 sp_OA 저장 프로시저는 COM+ 에러코드가 발생해도 SQL 명령어가 실패하거나 하진 않는다. 따라서 sp_OA 함수는 성공했는지 일일이 검사해야 한다.

또한 sp_OA 저장 프로시저의 많은 부분은 참조자 변수를 사용한다. 즉 Sp_OA 저장 프로시저의 적절한 변수 옆에는 항상 OUTPUT 명령어를 참조시켜야 한다. OUTPUT 명령어가 생략되면 집행된 Transact SQL은 경고문을 발생시키지 않는다. 런타임시 저장 프로시저는 제대로 실행될 것이다. 그러나 유효한 값을 반환하진 못한다. 상세한 구현 코드는 이곳에서 볼 수 있다.

이 구현 코드에는 DTS 패키지의 이름을 바꾸고 효율적으로 구현할 수 있도록 하는 테이블이 포함돼 있다. sp_AdRunDTSPackageOnServer 저장 프로시저는 ID 값을 변수로 받아들인다. 실행을 계속하기 이전에 프로시저는 SQL 서버의 DTS 패키지 경로를 T_AdDTSPackageSetup 테이블에서 추출해낸다.

필수적인 보안 사항들

보안에 대한 본격적인 논의는 강좌의 범위를 벗어나지만 일부 고려해야 할 점만 지적하고 넘어가자.

sp_AdRunDTSPackageOnServer 저장 프로시저를 구현하려면 마스터 데이터베이스에서 sp_OA 확장 시스템 저장 프로시저의 EXECUTE 실행 권한이 필요하다. 악의적인 SQL 서버 전문 사용자가 원래 목적 이외의 용도로 sp_OA를 사용하는 것을 방지하려면 보다 엄격한 보안 수준을 구현해 애플리케이션용 SQL 서버의 역할을 명확하게 설정하라.

CURRENT_USER Transact SQL 함수는 안전이나 가벼운 수준의 보안에 도움이 된다. CURRENT_USER와 T_AdDTSPackageSetup 테이블의 모든 필드를 사용해 질의를 하면 특정 DTS 패키지를 수행할 수 있도록 권한이 설정돼 있는지 알 수 있다.

DTS 패키지는 SQL 서버 에이전트 서비스용 계정 설정이란 보안 맥락에서 볼 때 SQL 서버에서 수행돼야 할 것이다. 따라서 DTS 패키지가 ASCII 파일을 파일 시스템에서 읽는 경우를 상정해보면 SQL 서버 에이전트의 계정이 파일 사용 권한을 갖도록 설정됐는지 확인해야 한다.

알아두면 좋은 점들

sp_OA 시스템 저장 프로시저를 사용해 다른 COM+ 라이브러리와도 상호 작용할 수 있다. 또한 이 프로시저들은 ODBC, 그리고 SQL 서버 시스템이 아니더라도 액티브X 데이터 오브젝트(ADO)를 사용한다면 유용하게 쓰일 수 있다.

아마 눈치 챘을지 모르지만 10줄 정도 길이의 VBS 스크립트로도 가능한 작업을 구태여 구현한다는 것은 비실용적이다. 게다가 결과로 얻어지는 Transact SQL 코드는 바빠지게 되며 자주 수행하기에 약간은 성가시다.

SQL 서버 2000 북스 온라인에는 어떤 COM 오브젝트 라이브러리가 지원되는지, 그리고 sp_OA 시스템 저장 프로시저와 관련해 상세하게 기술돼 있다. 다음에 DTS 패키지를 수행해야 할 일이 생긴다면 sp_OA 시스템 저장 프로시저의 사용을 고려해 보라. @