원격에서 패키지 호출하기 – (4) xp_cmdshell 명령을 이용한 호출

원격에서 패키지 호출하기 - (4) xp_cmdshell 명령을 이용한 호출 한대성 MS SQL Server MVP 에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자 <강좌 구성> 1. 구현 환경 설명 2. 테스트 용 패키지 만들기 3. SQL Agent Job을 이용한 호출 4. xp_cmdshell 명령을 이용한 호출 5. 웹 서비스를 이용한 호출 4. xp_cmdshell 명령을 이용한 호출 본 강좌를 진행하기에 앞서 xp_cmdshell 명령에 대해서 살펴보겠습니다. xp_cmdshell 명령은 SQL 쿼리문을 이용하여 쉘(Shell) 명령을 수행할 수 있는 기능입니다. 자세한 사항은 온라인 도움말을 참고하시고, 여기서는 강좌에 필요한 수준만 설명 드리겠습니다. 쉘 명령(또는 명령 쉘)이 뭔지 모르실 분들을 위해 다음 그림으로 설명하겠습니다.       위와 같은 커맨드 창에서의 명령뿐만 아니라, notepad.exe, calc.exe 등과 같이 실행 메뉴에서 명령을 입력해서 프로그램 또는 기능을 수행하는 것을 쉘 명령이라 합니다. 쿼리를 사용하다 보면, 종종 데이터베이스 쿼리 외에도 파일을 복사하거나 삭제, 프로그램 실행과 같은 명령 작업을 수행해야 할 경우가 있습니다. 이 때 사용할 수 있는 기능이 바로 xp_cmdshell 명령입니다. 예)    유용한 기능처럼 보이지만 이 기능은 SQL Server 2005에서는 기본적으로 비활성화되어 있습니다. 보안상 문제가 될 수 있기 때문입니다. SQL Server의 sysadmin 또는 이 명령을 수행시킬 수 있는 권한을 가진 SQL 로그인 계정만 알고 있다면 서버에 직접 접속하지 않고도 외부에서 쿼리 분석기만으로 서버의 모든 작업을 수행할 수 있기 때문입니다. 그렇다고, 이 기능을 쓰지 말아야 할까요? 필요하다면 활성화해서 쓰면 됩니다. 대신 보안 관리를 좀 더 철저히 신경 써서 해 주면 되겠지요. SQL Server 2005에서는 이전 버전에 비해 훨씬 더 정교하고 철저하게 권한 설정을 할 수 있기 때문에 필요한 경우라면 이 기능을 활성화해서 사용하도록 합니다. 이 기능은 다음과 같이 SQL Server 노출 영역 구성에서 설정하거나 sp_configure 명령을 이용해서 활성화할 수 있습니다.       자, 그럼 이 기능과 패키지 원격 실행과 무슨 상관이 있을까요? 이전 강좌 글에서 설명한 것과 같이 SSIS 패키지를 실행하기 위해서는 dtexec.exe 또는 dtexecui.exe라는 프로그램이 있어야 한다고 설명했습니다. 참고로, SQL Agent 작업 또한 Agent에서 dtexec.exe 프로세스를 호출해서 실행되는 방식입니다.    master.dbo.xp_cmdshell ‘패키지를 호출하는 명령’ 형태로 외부에서 서버에 있는 패키지를 실행시킵니다. 패키지를 호출하는 명령은 다음 강좌에 자세히 설명되어 있습니다. http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=307 패키지가 있는 디렉터리에서 패키지 파일을 더블 클릭합니다.    다음과 같은 패키지 실행 유틸리티(dtexecui.exe)가 실행이 되고, 패키지를 실행시킬 여러 옵션들을 설정할 수 있습니다. 본 예에서는 3강에서 값 설정했던 방식대로 값 설정 탭에서 입력 값을 지정해보겠습니다.    그런 다음, 명령줄 탭으로 이동하면 앞에서 구성한 여러 옵션들에 대한 명령어가 자동으로 생성됩니다. 이 부분을 복사한 후 패키지 실행 유틸리티를 닫습니다.    이렇게 복사한 내용 앞에 다음과 같이 dtexec 만 붙이면 본 패키지에 대한 실행 명령이 됩니다. dtexec /FILE "D:\Data\My Documents\Visual Studio 2005\Projects\원격 호출 예제\원격 호출 예제\ProcessPackage.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Variables[사용자::InputVal].Properties[Value]";10 쿼리 분석기에서 다음과 같이 사용하면 됩니다.    xp_cmdshell 명령은 동기 처리 방식입니다. 즉, xp_cmdshell에서 실행하는 명령이 끝나기 전까지는 완료되지 못하고 실행 중인 상태로 유지됩니다. 이에 비해 SQL Agent 작업을 호출하는 sp_start_job 명령은 비동기 처리 방식입니다. 즉, 호출되는 작업이 끝나지 않더라도 sp_start_job 명령은 종료됩니다. 예를 들어 이 차이점을 설명하겠습니다. 10분이 걸리는 SSIS 패키지 작업이 있다고 할 때 SQL Agent Job을 이용한 방식으로 실행시킬 경우, 쿼리 분석기에서 sp_start_job 명령을 수행하면 바로 종료됩니다. 즉, 실행 하도록 명령만 내리는 것입니다. 이 작업이 1분이 걸리든, 10분이 걸리든 상관없이 sp_start_job 명령은 바로 종료됩니다. 이에 비해 xp_cmdshell 명령은 패키지가 처리 완료될 때까지, 즉 10분 동안 실행 중으로 나타납니다. 다음 표는 SQL Server Agent 작업을 이용하는 방법과 xp_cmdshell을 이용하는 방법을 비교한 것입니다. 구분 SQL Agent 작업 이용 xp_cmdshell 이용 처리 방식 비동기 동기 실행 주체 SQL Agent 작업(Job) xp_cmdshell 명령 호출 명령 msdb.dbo.sp_start_job ‘작업 이름’ master.dbo.xp_cmdshell 동시 실행 동일한 패키지를 동시에 실행 불가 동일한 패키지를 동시에 여러 명이 실행 가능 제한 사항 패키지에 대해 SQL Agent 작업을 만들어 줘야 하며, SQL Agent 서비스가 실행 중이어야 함 xp_cmdshell 기능을 활성화 해 줘야 함. 입력 변수 불가능하지 않으나 구현하기 까다로움 용이함 출력 값 직접 전달받지 못함 직접 전달받지 못함 SQL Server SQL 2000 또는 2005 필요함 SQL 2000 또는 2005 필요함 이와 같은 차이점들을 잘 파악한 후 상황에 맞게 적절하게 두 가지 방법 중 적절히 선택해서 사용하면 됩니다. 예를 들어 작업 수행 모니터링 화면에서 패키지를 실행할 수 있는 기능을 추가하고자 한다면 SQL Agent 작업을 이용하는 방식으로 사용하는 게 바람직할 것입니다. 만약 여기에 xp_cmdshell 명령을 이용하도록 설정한다면 사용자가 화면에서 작업 시작 버튼을 클릭한 후 패키지가 종료될 때까지 기다린 후에야 완료 응답을 받게 될 것이고 이 시간이 짧지 않다면 대부분의 경우 웹 페이지에서 TIMEOUT이 발생할 것입니다. 여기까지 대략 SQL Server 를 이용해서 패키지를 호출하는 방식을 살펴봤습니다. 5강에서는 SQL Server 없이 패키지를 실행시키는 웹 서비스를 이용한 패키지 실행 방식에 대해 살펴보겠습니다.