DTS를 손쉽게 해주는 툴이 있습니다.

정확히는 DTS 위져드로 마법사 기능이지요.

간단히 원본 / 타겟을 설정해서 처리하면 되며 처음 접하시는 분들도 손쉽게

처리가 가능하도록 되어 있습니다.

DTS작업은 크게 3가지로 나뉘어 집니다.

1. 원본 데이터베이스에서 테이블 및 뷰 복사 - 보통 Copy Data 라고 합니다.

말그대로 데이터베이스의 여러 개체들을 포함하는 정보를 SQL서버에 가져가는

것이 아닌.. 테이블의 틀 / 테이블의 데이터만을 복사한다고 보시면 됩니다.

기본키 , 참조키 , 저장 프로시져 등은 넘길 수 없습니다.

2. 쿼리를 사용하여 전송할 데이터 지정

DTS작업에 대한 약간의 유동성을 부여하는 것으로 이 작업은..

OpenRowSet이라고 하는 쿼리로 작동됩니다.

OpenRowSet은 이런 식으로 사용됩니다.

이런 식으로 작업이 되며 보시는 바와 같이.. From 절의 테이블이 들어갈 자리를

인라인 뷰처럼 대신 합니다. 모 좋습니다.

From 절에는 반드시 테이블이 들어갈 필요 없이 저렇게 2차원 배열형 데이터만 있으면

되지요. 이렇게 연결후..만약 from 절 앞에 into 서버명.DB명.dbo.새로운테이블명

하시면? 네. 바로 SELECT INTO에 의한 테이블 복사가 가능하고..

이 컬럼에 적절한 변환 함수를 적용할 수도 있지요.

이것이 쿼리를 이용한 전송의 기본이며.. 사실 그다지 사용할 일은 많지 않을 것입니다.

사용해 보신 분이라면? 대부분 데이터 복사(첫번째 옵션)에 역시나 "변환" 옵션이

있기 때문에 이부분을 이용 하시거나.. DTS패키지로 생성후 적절한 프로그래밍

로직을 추가 하셨을 겁니다. 다음 트랜스퍼를 이야기 드리지요.

3. SQL서버 데이터베이스간 개체 및 데이터 복사

보시는 바와 같이 SQL서버 끼리만 가능합니다. 옵션 사항은 복잡한듯 하지만..

사실 "전송"이 목적이라면 거의 건드리실 필요는 없으실 거구요.

호스팅을 받거나 IDC의 서버로 전송을 주로 하시는 분들은 종종 이녀석으로 데이터를

전송하거나 전송 받으실 겁니다.

중요한 것은 DB의 모든 개체를 복사한다는 것이구요.

테이블과 관련된 모든 개체인 데이터, 기본키, 참조키, 인덱스, 그리고 

저장 프로시져, 뷰, 사용자와 역할, 규칙, 기본값, 사용자 정의 데이터형과

사용자 정의 함수등 을 전송이 가능하며 이 두가지인 데이터베이스 다이어그램 / 

풀텍스트인덱스 데이터는 이 데이터베이스 개체만 트랜스퍼의 방법으로는 불가 합니다. 

그렇다고 해서 복사 안한다면 재미 없지요. -_-;

방법은 이장이 끝날때 쯤 말씀을 드릴 거구요..

이 전송을 아무래도 주로 사용하게 될테니.. 설명 드릴 겁니다.

그럼 찬찬히 진행해 보도록 하지요.

1. 원본 데이터베이스에서 테이블 및 뷰 복사 

간단합니다. 여러 제약등을 복사하는 것이 아니기 때문에 큰 무리없이 잘 수행되구요.

저는 간단히 엑세스에서 데이터를 가져와 보겠습니다. 여기서 중요한건..

엑세스를 가져온다는게 아닙니다. -_-;;  엑셀이건.. SQL서버의 데이터이건

다 처리할 수 있다는 것이지요. - OLEDB나 ODBC가 존재 한다면 말입니다.!!!!

그리고 가끔 올라오는 질문으로.. 엑세스 아이콘의 데이터 소스가 없다..

라는 질문이 올라 오는데요.. 간단합니다. 엑세스를 설치 하셔야만 물론

해당하는 제공자가 시스템에 등록되는 것입니다. 엑세스를 설치 하시면 됩니다.

엑셀이 없다면? -_- 물론 엑셀을 설치 하시면 되지요. DTS엔진이 있는 곳에만

설치 하시면 되며 당연히 소스 서버나 목적 서버에는 없어도 됩니다.

 - DTS엔진이 있는 곳이라는게 이해가 안되시면... 바로 앞에서 보신..

   꼬옥 1. DBMS간 데이터 전송에 대한 이해에 대한 글을 다시 읽어 보세요.

그럼 DTS를 시작해 보지요.

데이터 확인을 해 보겠습니다. 원본 화일을 열어보니?

FPNWIND.mdb 화일이라는 mdb 화일을 열어보니..

대충 이런식의 테이블로 구성되어 있으며 각 테이블에 여러 데이터와

기본키같은 개체들이 포함되어 있습니다. 전송을 시작해 보도록 하지요.

이러한 FPNWIND라는 데이터베이스를 생성 했으며 모든작업 -> 데이터 가져오기

를 선택 했습니다. - 여기서.. 가져오기를 하면? 데이터 타겟은 선택한 DB가 

자동으로 선택되는 것이며 내보내기를 하면? 소스 데이터가 자동으로 선택한 DB가

되는 것일 뿐입니다. DTS 위져드 진행중 바꾸셔도 무관하니 참고 하세요.

추가적으로 DTS위져드를 수행하는 방법은 가지가지 입니다. 저는 필요시마다..

DB에서 데이터 전송을 선택하는 것을 선호하며 하시던 방식이 있다면 편하게 사용하셔도

무관합니다.

계획에 MDB화일을 저의 SQL서버로 가져오기로 했습니다.

이렇게 데이터 원본을 엑세스로 잡았습니다.

- 누차 말씀 드리지만.. 데이터 원본을 적절하게 잡기만 하면 되는 겁니다.

타겟은? 당연히 저의 SQL서버 겠지요.

이렇게 대상 선택 부분은 당연히 SQL서버 대상에 적절한 서버의 이름과..

인증 방식 - 현재 저는 Windows인증이 된 상태(로컬 서버기 때문) 그리고 데이터베이스

를 적절히 생성 했습니다. 당연히 제 시스템이 아닌 다른 시스템에 하실 수 있으며..

인증이 적절히 수행되었는가 안되었는가는 데이터베이스 리스트박스를 눌렀을때

데이터베이스 이름들이 안나오면 인증이 적절하지 않은 것입니다.

또한 여기서 데이터베이스 이름들이 나왔다고 해서 DTS를 완전히 수행할 권한을

받은 것은 아니며 정확한 권한은 소스 / 타겟서버에 어떤 권한으로 접근 하는지를

정확히 파악하고 있어야 합니다. - 특히 호스팅일 경우 더더욱 주의해야 합니다.!!!

다음을 눌러서 테이블 복사 / 쿼리 지정을 보겠습니다.

보시면 이런 화면으로.. 엑세스 -> SQL서버로 작업이 진행되는 것을 보실 수 

있으며 복사 옵션과 쿼리 옵션이 활성화 되어 있습니다.

세번째 옵션인 SQL서버간 개체 / 데이터 복사는 당연히 SQL서버 끼리만 되니 활성화

안된 것이지요. 첫번째 복사 옵션으로 진행 하겠습니다.

이렇게 선택하는 부분이 나옵니다. 모두 선택 버튼을 눌러 모든 테이블 / 뷰를

전송할 예정입니다. 여기서 변환 이라는 부분이 있습니다.

고객 테이블에 대한 변환을 눌러 보면?

대강 이런 화면이 나오며 여기서 적절하게 컬럼의 데이터 형을 변환하거나..

NULL허용 여부, 대상(SQL서버)의 테이블 이름변경, 컬럼의 크기 등을 적절히

변환이 가능하며.. Identity Insert 옵션을 지정할 수 도 있습니다.

SQL편집 버튼을 누르면 

이런 식의 테이블 생성 스크립트를 볼 수 있으며 변환 탭을 선택하시면?

이렇게 열의 매칭에 대한 변환을 수행할 수 있습니다.

적절한 함수를 아래 보이는 언어 탭에서 VB스크립 / J스크립중 선택해 변환 함수를

사용이 물론 가능합니다. 

개인적으로 선호하는 변환 방식을 말씀 드리면..

저는 무조건 SQL서버로 보낸후 변환 시키기를 좋아 합니다. -_-;;

물론 일회성 작업일 경우는 대부분 SQL서버로 보내고 처리하며..

SQL서버에서.. 테이블 디자인을 바꾸거나.. 컬럼을 바꾸거나..

데이터 변환 역시 VB스크립에서 보다는 T-SQL의 함수로 쓰는게 저는 편해서 이렇게

씁니다. VB스크립 / J스크립중 편한게 있다면? 그것을 사용하셔도 물론 좋지요.

지금 저의 목적은 오로지 데이터만을 전송하는 것이니 다음을 눌러 진행하겠습니다.

그리고 이런 화면에서.. 즉시 실행을 할 것인지..

나중에 작업을 걸어 수행하게 할 것인지.. DTS패키지로 저장할 것인지의 옵션이

있지만 여기서는 속편하게 즉시 실행만 해 보겠습니다.

마침을 이렇게 누르면 전송이 완료가 되는 것을 보실 수 있을 것입니다.

작업이 완료 되었으면.. SQL서버에 데이터가 잘 넘어 왔는지 확인해 보도록 하지요.

보시면 이렇게 테이블과 데이터는 잘 넘어 왔으나..

데이터형 / 길이 / NULL여부 등등의 여러 조건들은 수작업으로 매칭해 주시면

됩니다.

간단히 엑세스로 수행해 보았습니다.

만약 소스가 SQL서버이고 타겟이 엑세스 화일이라면?

역시나 소스에서 적절한 SQL서버 연결과 DB를 잡고.. 타겟에서 빈 엑세스 화일을

생성후 해당하는 화일에 DTS하시면 되겠지요.

이번에는 SQL서버 -> SQL서버 작업을 해 보겠습니다.

northwind2라는 빈 데이터베이스를 생성하고 이곳에 nothwind 데이터베이스의

테이블들을 복사해 보겠습니다.

이렇게 Northwind2 데이터베이스를 생생후 northwind2데이터베이스에서 가져오기를

수행하겠습니다.

데이터 원본은 이럴 것입니다.

이렇게 데이터베이스가 Northwind 데이터베이스일 것이고

뒤에서 타겟은? Northwind2 데이터베이스겠지요.

방식을 지정하는 부분에서 세번째 SQL서버간 개체/ 데이터 복사가 활성화

됩니다만.. 저희는 한번더 테스트를 위해 첫번째 테이블 복사를 해 보지요.

다음화면에서 전송할 테이블은? 모두 선택을 눌러 모든 테이블 / 뷰를 선택합니다.

주의 하셔야 합니다. - 아래쪽으로 스크롤해 보시면?

Northwind의 뷰가 어떻게 전송이 되나요?

네. 뷰 -> 테이블로 변환 됩니다. 어거지로 복사한다는 느낌이 강하지요.

여하간 다음을 누르고 즉시 실행을 하시면 데이터는 잘 넘어 갈 것입니다.

그리고 northwind2 데이터베이스의 테이블등을 조사해 보시면?

이렇게 기본키제약이나.. 참조키 등등의 제약들이 전송 안된것을 보실 수 있습니다.

이제는 모든 데이터베이스 개체를 전송하기 위한 SQL서버간 개체 / 데이터 전송을

해 보겠습니다. 두번째 방식인 쿼리를 이용한 복사는 많이 안쓰시게 되기 때문에..

다루지 않을 것입니다.

자 Northwind3라는 빈 데이터베이스를 생성 했습니다.

데이터 원본은 Northwind 데이터베이스이며 타겟은? Northwind3 데이터베이스

입니다.

그리고 작업을 수행 하는데요..

이렇게 세번째 옵션인 SQL서버간 개체 / 데이터 복사를 택합니다.

이렇게 복사할 개체 선택 부분이 나오며 

대상개체를 먼저 삭제할 것인지, 종속개체를 포함할 것인지 그리고 약간 의아하실

확장 속성의 의미는.. 컬럼의 주석 같은 것으로..

이렇게 컬럼의 설명 부분의 주석 같은 것 역시 복사하는 것을 의미 합니다.

자세한 정보를 원하시면?

이곳의 팁게시판 정보

를 보시면 확장 속성의 저장소에 대해 좀더 도움 되실 겁니다.

다음으로 데이터를 바꿀 것인지.. 추가할 것인지를 선택 하시면 되며..

이렇게 모든 개체 선택의 체크를 제거후 개체선택 버튼을 보면 모든 개체에 대한

처리가 가능하다는 것을 알 수 있습니다.

취소 하시고 모든개체 복사의 체크박스를 다시 체크 되게 하신후..

기본옵션 사용의 체크를 제거한후 옵션 - 버튼을 누릅니다.

이러한 화면을 보실 수 있으며..

가장 문제가 되는 보안 옵션 부분입니다.

양쪽 서버 모두에 SA계정과 같은 System Admin 그룹에 속한 사용자로 접근을 하더라도

이 두개의 체크되어 있는 옵션인 데이터베이스 사용자 및 역할 복사와

개체 수준 사용 권한 복사 두개의 체크를 제거하심이 완전한 복사에 좋습니다.

이 두 옵션을 제거하라고 말씀 드리는 이유는.. 대부분의 개발 작업은?

자신의 시스템에서 SA계정과 같은 사용자로 작업 합니다.

그리고나서 IDC나 호스팅 하는 곳에 전송을 하는데..

타겟측에서 DB 권한이나 개체 수준 사용권한을 적절하게 처리 못하는 경우가 많습니다.

물론 로컬 시스템에서 테스트 시는 당연히 권한 / 계정에 대한 처리가 필요 없지요.

같으니까요. 하지만 원격지.. 특히나.. 호스팅일 경우는?

예를들어 호스팅 서버는 sa계정을 받지 못하며 대부분 dbo권한 - DB소유자 권한

정도만을 받게 됩니다. 뭐 나쁘다는 것은 아닙니다. 이럴 때 서버측의 데이터를 자신의

시스템으로 전송 받고 싶습니다.

이때.. 서버측에 받은 계정이 자신의 로컬 시스템에 있습니까?

없다면? 계정이 없다는 오류를 만나실 겁니다.

계정이 있더라도 권한등의 충돌이 날 오류가 많습니다.

그럼 어떻게 하는가? 호스팅 받는 원격 서버의 데이터를 가져오기 위해서는

자신의 시스템에 계정을 생성하고.. - 호스팅 업체로부터 받은 계정과 같은 계정

그리고나서 전송을 수행하시면 됩니다. 상세한 계정 생성은 마지막 강좌인

로그인 및 권한 부분에서 생성하고 처리하실 것이니 조금만 기다려 주시고..

이야기가 길어졌습니다만!!!!

그냥 속편하게.. 계정과 관련된 부분을 제거하고 넘기면 된다..

라는 정도만 생각하시고.. 

나머지는 기본 옵션으로 두시고.. 전송을 하시면 됩니다.

이때.. 이 작업을 저장해 보도록 하겠습니다. 어떻게?

이렇게 즉시 실행에 체크하고 이어서 DTS 패키지 저장을 해 보겠습니다.

이 DTS패키지라는 것은?

지금 선택하신 여러 옵션들에 대해서 - 데이터 원본, 데이터베이스,

전송 방식, 옵션등의 선택한 사항들을 저장하는 것입니다. SQL서버에 저장하지요.

코난이는 이렇게 이름 정도만 주고 작업을 수행했습니다.

작업을 수행하시면? 우선 잘 되는 것을 보실 것이며 

작업이 끝난후... 

확인해 보시면... 모든 기본키 / 참조키 등등의 데이터베이스 개체가

잘 전송 된것을 아실 겁니다.

또한 패키지 저장을 수행 했으니..  데이터변환 서비스 - 로컬 패키지 부분을 보시면?

이렇게 지금 수행한 작업의 흐름이 패키지화 되어서 저장되어 있는 것을

보실 수 있습니다.

이 작업을 매일 새벽 6시에 수행하려면?

이렇게 예약 부분에서 자동 수행이 될 시각및 반복 수행을 지정할 수 있습니다.

즉시 패키지를 수행하는 옵션도 있지요.

그럼 정리해 볼까요?

데이터 복사 옵션을 이용하면? 말그대로 테이블 / 뷰의 복사가 이루어 지지만..

기본키, 참조키, 저장 프로시져 등의 개체는 복사가 불가합니다.

세번째 옵션인 SQL서버간 전송은?

SQL서버 끼리만 가능하며 SQL7 <-> SQL2000역시 가능합니다만..

제약조건으로 개체의 사용 수준 권한 등의 보안 관련 옵션들 전송에 문제가 있을

수 있으며 또한 개체의 소유권 및 그 권한까지 그대로 전송하기 때문에

전송 받는 서버에 적절한 계정 / 권한이 설치 되어 있어야만 합니다.

IDC에 서버를 넣고 쓰신다면? SQL서버간 전송시 

1. 기본옵션 사용의 체크 제거

2. 데이터베이스 사용자 및 역할 복사 체크 제거

3. 개체 수준 사용 권한 복사 체크 제거를 하고 트랜스퍼

하시면 아주 잘 됩니다.

호스팅을 받는 분이라면?

1. 호스팅해주는 업체에서 주는 계정과 같은 내부 UID를 가지는 계정이 전송 받으려는

시스템에 존재 해야만 합니다.

uid의 확인은. 해당 테이블의 sysusers 테이블을 여시면 uid와 name을 보실 수 

있으며 해당 uid를 가지고 sysobjects 테이블을 열면 uid에 대한 DB개체를 볼 수 있습니다.

이정도만 하도록 하구요...

트랜스퍼를 한번에 잘 되길 바란다면 욕심이 크신 겁니다.

저역시 여러번의 시행 착오를 거치고 수행하고.. 다시 만들고.. 합니다.

천천히 안된다고 포기 마시고.. DTS시 에러가 생기면 에러 메시지를 보여주니..

잘 읽어 보시고 문제를 해결후 다시 DTS를 수행하면 되겠지요.

호스팅을 받을 경우 트랜스퍼가 영 맘에 안드신다면?

이럴때는 좀더 속편하게 테이블 복사 DTS를 하시고.. 개체 스크립팅을 받으신후..

이 스크립팅 받은 SQL구문을 서버측에서 실행해 해당하는 개체를 생성하는

것도 나쁘지 않은 방법 입니다.

자.. 여기까지..

이제 언급해 드리기로한.. 데이터베이스 다이어그램 전송과

풀텍스트 데이터 전송에 대해서 말씀 드리지요.

다이어그램 전송은? -_-;;  그냥 트랜스퍼 하고 옴겨진 곳에서 다시 생성하시면

되겠지만.. 명색이 SQLER인데. -_-;;;

말씀 드려야 겠지요. ^_^;;;

약간 까다로울 수 있습니다.

1. 시스템 테이블을 수정할 수 있게 한다.

2. 'dtproperties' 라는 다이어그램 정보를 가지고 있는 시스템 테이블을 사용자 테이블

속성으로 변경한다.

3. dtproperties 테이블에 Identity Insert 속성을 ON 시킨다.

4. INSERT SELECT로 dtproperties 테이블의 데이터를 복사한다.

5. dtproperties 테이블을 다시 시스템 테이블로 변경한다.

6. 시스템 테이블 수정을 불가하게 한다. 입니다.

당연히 모르셔도 되는거니 넘어가셔도 상관 없습니다. -_-;;

자 진행해 보도록 하지요.

느끼시는 것처럼.. 쓰잘데기 없는데에 목숨건다고 생각하실지도.. -_-;;

이 내용은..

작성자 : Brian Knight

출처 : http://www.sqlservercentral.com

부분의 내용이기도 하답니다. 참고 하시길 바랍니다.

다음으로 풀텍스트 데이터 처리르 하려면?

멀리 가실 필요 없습니다. 거북엄마님이 이미 정리해 주신 내용이 있지요.

http://sqler.pe.kr/FriendLec/TurtleMa/LecM/mq16.asp

을 참고 하시면 도움 되실 겁니다. ^_^

위에서 만든 DTS패키지가 있는데요.. 이녀석을 전송하고 싶을때는?

이녀석은 또한 특이합니다.

출처 : swynk.com

작성자 : Darren Green

SQLER의 팁게시판 참고

역시나 설명을 자세히 드리고 싶지만.. 필요하시다면?

이곳을 이용하시면 되실 겁니다.

자 여기까지~~~~ ^_^  이 내용들은 그다지~~ 신경 안쓰셔도 물론 되겠지요? ^_^

그렇다면 여기서 DTS작업들의 보완하고 싶은 점을 생각해 보지요.

1. 매일매일 원격 서버측의 데이터를 우리 회사의 데이터 복사 용도의 DB로 옮겨

오고 싶습니다. DB이름이 SQLER라고 원격 서버에 되어 있으며..

매일매일 가져와서.. SQLER20011230 식으로.. 년월일을 포함해 가져오고 싶습니다.

트랜스퍼를 매일 매일 하기도 힘든데.. 자동으로 할 순 없는 것인지?

또한 전송이 완료되면 완료 메시지를 메일로 받거나..

실패하면 실패라는 메시지를 바로 메일로 받을 수 는 없을지요?

2. 하루에 SQL서버에 로드해야할 데이터가 1000만건입니다.

저희는 사용자의 클릭 데이터까지 저장 하므로.. 1000만건의 데이터가 넘는

정보를 옮겨야 하는데.. 앞으로 서버가 늘어날 예정이어서 수억건이 될지도 모릅니다.

DTS로 해보니 속도가 많이 느려 새벽에 작업을 수행해 데이터를 삽입시 

오전이 될때까지 계속 수행이 됩니다. 조금더 빠르게 수행할 수는 없는 것인지요?

1번의 답변으로는 DTS 패키지를 생성하시면 됩니다. 바로 다음에 설명드릴

DTS패키지 생성 부분에서 전역변수를 이용한 DTS패키지 샘플을 생성해 보실 거구요.

2번의 답변으로는 BCP와 Bulk Insert 입니다.

벌크 작업으로 특정 옵션을 적절하게 줄 경우 최적의 속도로 데이터를 전송할

수 있지요.

찬찬히.. 위 두가지 내용을 각각 알아 보도록 하겠습니다.

먼저 DTS패키지 입니다.

▶ 12. DTS(Data Transfer Service) - 

                    2. DTS 위져드 사용