에러 처리를 이용한 데이터 동기화 작업

에러 처리를 이용한 데이터 동기화 작업

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 

 

서버간 데이터를 동기화 하는 작업에서 SSIS를 이용해서 효과적으로 처리할 수 있습니다.

이미 이전 몇 개의 기술자료에서 이를 언급하였습니다.

 

Ÿ          원격 서버간 데이터 동기화 구현

Ÿ          엑셀 파일과 테이블 데이터 동기화하기

Ÿ          조회 변환을 이용한 데이터 처리

Ÿ          조회 변환을 이용한 데이터 동기화 작업

 

원격 서버 간에 데이터를 동기화 하는 방법은 SSIS에서 뿐만 아니라 ETL 작업에서 자주 언급되는 주제입니다.

지금까지는 주로 조회(Lookup) 변환을 이용해서 이를 구현하는 방안을 소개하였습니다. 조회 변환을 이용하는 형태는 대상 테이블의 건수가 적을 때에는 매우 효과적이지만, 만약 동기화 해야 할 데이터가 많은 경우, 조회를 위한 메모리 캐싱 작업에서 많은 시간과 부하가 걸리는 단점이 있습니다.

본 글에서는 조회 변환 대신 대상 테이블의 에러 처리를 이용해서 구현하는 방법을 다뤄보겠습니다.

 

다음과 같은 시나리오를 이용해서 설명합니다.

 

원본 서버의 테이블과 대상 서버의 테이블은 각각 Primary Key(또는 Unique 제약 조건)이 있습니다. 원본의 데이터 중 새로 발생된 데이터만 입력하고 기존의 데이터는 별도의 테이블에 저장하고자 합니다.

 

Source Instance: localhost

USE TEMPDB GO   IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='SOURCE' AND XTYPE = 'U') DROP TABLE SOURCE GO   --원본 테이블 CREATE TABLE SOURCE (         SEQ INT IDENTITY,         VAL VARCHAR(10) DEFAULT('AAA'),         CONSTRAINT PK_SOURCE PRIMARY KEY(SEQ) ) GO    --임의 데이터 발생 SET NOCOUNT ON DECLARE @I AS INT SET @I = 1   WHILE @I<=100 BEGIN INSERT SOURCE DEFAULT VALUES SET @I = @I + 1 END SET NOCOUNT OFF GO

 

Target Instance: localhost\SS2005

USE TEMPDB GO   IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='TARGET' AND XTYPE = 'U') DROP TABLE TARGET GO   --대상 테이블 CREATE TABLE TARGET (         SEQ INT,         VAL VARCHAR(10),         CONSTRAINT PK_TARGET PRIMARY KEY(SEQ) ) GO

 

일단 여기까지 수행해서 원본에는 100개의 데이터를 넣고선 다음과 같이 패키지를 구성합니다.

일단 한 번 패키지를 수행하면 정상적으로 100개의 데이터가 이관될 것입니다.

 

자, 그럼 원본에서 10개 정도의 데이터를 추가로 입력한 후, 다시 패키지를 돌려봅시다.

Target 테이블에 Seq열에 Primary Key가 설정되어 있기 때문에 당연히 중복 에러로 인해서 실패가 납니다.

 

이걸 막기 위해, 이전에는 원본과 대상 중간에 조회 변환을 두고선, 대상 테이블의 데이터를 읽어와서 조회 작업을 수행한 후, 조회가 되지 않는 데이터(오류 데이터)만 대상으로 입력하는 방식으로 구현했습니다.

 

이번에는, 이와는 달리 OLE DB 대상에서 처리하도록 합니다.

OLE DB 대상을 더블 클릭해서 편집기를 연 다음, 아래 그림과 같이 데이터 액세스 모드를 기본값인 테이블 또는 뷰 – 빠른 로드에서 테이블 또는 뷰로 변경합니다. (빠른 로드만 아니면 됩니다.)

 

그런 다음, 오류 출력 탭을 선택한 후, 다음과 같이 오류 부분의 속성을 행 리디렉션으로 변경합니다.

 

참고로, 데이터 액세스 모드가 “빠른 로드” 인 경우에는 오류 출력을 설정할 수 없습니다.

 

일단 여기까지 설정하면 아래와 같이 OLE DB 대상에 기분 나쁜(^^) 경고 표시가 나타납니다. 오류를 다른 경로로 출력하도록 설정해 놓고선 다른 경로를 지정하지 않았기 때문입니다.

자, 그럼 대상 서버에 다음과 같이 오류 데이터를 저장할 테이블을 만듭니다.

Target Instance: localhost\SS2005

USE TEMPDB GO   IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='EXISTDATA' AND XTYPE = 'U') DROP TABLE EXISTDATA GO   --기존 데이터 테이블 CREATE TABLE EXISTDATA (         SEQ INT,         VAL VARCHAR(10),         CONSTRAINT PK_EXISTDATA PRIMARY KEY(SEQ) ) GO

 

 그런 다음, OLE DB 대상을 하나 더 추가하고선 OLE DB 대상의 빨간 선(오류 출력)을 연결합니다.

연결하면 자동으로 오류 설정 창이 나타나지만, 이미 설정했기 때문에 그냥 확인을 누릅니다.

 

새로 추가한 OLE DB 대상(그림에선 “기존 데이터”)을 더블 클릭해서 편집기를 연 다음, 새로 생성한 테이블을 지정하고 매핑 탭에서 열을 설정합니다.

 

 

자, 여기까지 설정한 후에 패키지를 실행해 봅시다.

 

 

110개가 입력되었고, 이 중에서 기존에 있었던(=PK 중복 오류가 발생한) 데이터는 별도로 빠지고(100개), 나머지 데이터가 입력됩니다.

 

이렇게 별도로 분리된 기존 데이터는 SQL 실행 작업을 이용해서 DELETE-INSERT를 하거나 UPDATE를 하도록 처리하면 될 것입니다.

만약, 중복된 데이터인 경우에는 무시하고 싶다면, 에러 처리 부분에서 행 리디렉션 대신에 오류 무시로 설정하면 됩니다.

 

 

조회를 이용한 작업과 비교해 볼 때, 데이터 캐싱 단계를 없앨 수 있기 때문에 대량 데이터인 경우에는 우수한 성능을 낼 수 있습니다.

하지만 다음과 같은 단점도 있습니다. 우선 데이터 처리 방식으로 인해 처리되는 속도가 빠른 처리 모드에 비해 약간 저하될 수도 있습니다. 또한, PK 외에 열에 대해 중복 체크를 하기 위해서는 대상 테이블에 Unique 제약 조건과 같은 부가적인 설정을 해 줘야 합니다.

 

반드시 이 방법이 데이터 동기화 처리에서 최상은 아닙니다. 테이블의 형태가 각기 다른 만큼 최적 방법도 모두 다르기 때문에 상황에 맞게 적절한 방식을 적용하는 것이 최상입니다.