서버 간 데이터 동기화 구현

서버 간 데이터 동기화 구현

백도훈

에이디컨설팅 선임 컨설턴트

Question

다른 서버에 있는 테이블을 동기화하려고 합니다. 보안과 안정성 그리고 편의성을 다 만족하는 테이블 동기화 방법이 뭐 없을까요?

 

사내 정책상 제약 사항이 많은 경우 각기 다른 서버의 테이블 동기화를 유지해야 한다면 어떤 방법이 있을까요?

여러 방법이 있겠지만, 말 그대로 통합(integration) 서비스인 SSIS를 이용해보는 것도 좋을 것 같습니다.

 

코드 테이블에 동기화 상태 값을 저장할 컬럼과 트리거를 추가하여 재미있는 패키지를 구성할 수 있을 것 같네요. ^^;;

컬럼 추가가 불가능하다면 코드 테이블의 PK를 키 값으로 하는 별도의 테이블을 생성해서 관리하는 방법도 있을 것입니다.

 

이렇게만 설명하면 막연하니 간단한 예를 들어보겠습니다.

---------------------------- -- 1. 가상 기본 환경 ---------------------------- USE TEMPDB   CREATE TABLE CODETABLE (                   SEQ INT IDENTITY                   , COL1 VARCHAR(10) DEFAULT('AAA')                   , COL2 VARCHAR(10) DEFAULT('BBB')                   , COL3 VARCHAR(10) DEFAULT('CCC') ) GO   INSERT CODETABLE DEFAULT VALUES GO 10   SELECT * FROM CODETABLE

 

 

현재 CODETABLE 테이블의 상태가 이렇다고 가정하겠습니다.

 

이 테이블에 컬럼과 트리거를 추가해보겠습니다.

-------------------------------------------- -- 2. 컬럼 추가 및 트리거 생성 -------------------------------------------- ALTER TABLE CODETABLE ADD STATUS CHAR(1) DEFAULT 'N' NOT NULL GO   CREATE TRIGGER TR_CODETABLE ON CODETABLE FOR UPDATE AS                   IF (NOT UPDATE(STATUS))                                    UPDATE CODETABLE                                    SET STATUS = 'N'                                    FROM CODETABLE AS A                                    INNER JOIN                                    INSERTED AS B                                    ON A.SEQ = B.SEQ GO   SELECT * FROM CODETABLE   ------------------------------------------------------------ -- 3. 테스트를 위해 상태값을 Y로 업데이트 ------------------------------------------------------------ UPDATE CODETABLE SET STATUS = 'Y'   SELECT * FROM CODETABLE

 

 

STATUS외의 컬럼 값이 UPDATE되거나 INSERT되면 다음과 같이 됩니다.

--------------------------------------------- -- 4. UPDATE 와 INSERT 테스트 --------------------------------------------- UPDATE CODETABLE SET COL1 = 'ZZZ' WHERE SEQ = 5 INSERT CODETABLE DEFAULT VALUES   SELECT * FROM CODETABLE

상태 값이 Y가 아닌 값이 동기화 대상이 되겠습니다.

 

 

SSIS 패키지 구현

 

SSIS로 테이블 동기화를 구현해보겠습니다.

환경은 원본 테이블이 있는 데이터베이스 A,

동기화 할 테이블이 있는 데이터베이스 B, C가 있다고 가정하겠습니다.

 

동기화는 A à B à C의 순서도 진행한다고 가정할 때 STATUS 값의 변화는 다음과 같습니다.

	STATUS 값
A DB에 코드 입력 또는 변경	N
B DB 동기화 시작	B
C DB 동기화 시작	C
C DB 동기화 완료	Y

 

상태 값을 계속 변경해주는 것은 동기화 하는 도중 값이 또 변경되더라도 누락되는 행이 없도록 하기 위함이고

서버 별로 상태 값을 바꿔주는 것은 혹시나 동기화 대상 서버가 다운되더라도 어디까지 동기화를 진행했는지 알기 위해서 입니다.

 

일단 앞에서 테스트한 값을 초기화 시키기 위해서 원본 데이터베이스에서 아래의 쿼리를 실행합니다.

TRUNCATE TABLE CODETABLE GO   INSERT CODETABLE DEFAULT VALUES GO 10   SELECT * FROM CODETABLE

 

그리고 테이블을 동기화 할 데이터베이스 B, C에 아래의 쿼리를 실행해서 테이블과 프로시저를 생성합니다.

USE TEMPDB   CREATE TABLE CODETABLE (                   SEQ INT IDENTITY                   , COL1 VARCHAR(10) DEFAULT('AAA')                   , COL2 VARCHAR(10) DEFAULT('BBB')                   , COL3 VARCHAR(10) DEFAULT('CCC') ) GO   CREATE PROC USP_CODEUPDATE @SEQ INT, @COL1 VARCHAR(10), @COL2 VARCHAR(10), @COL3 VARCHAR(10) AS IF EXISTS (SELECT 1 FROM CODETABLE WHERE SEQ = @SEQ)                   UPDATE CODETABLE SET COL1 = @COL1, COL2 = @COL2, COL3 = @COL3 WHERE SEQ = @SEQ ELSE                   INSERT CODETABLE (COL1, COL2, COL3) VALUES (@COL1, @COL2, @COL3)

 

패키지를 만들어보겠습니다.

 

1.       연결 관리자에서 새 OLE DB 연결을 추가하겠습니다.
새로 만들기 버튼을 클릭해서 연결 관리자 창을 여시고 원본 DB 접속 정보를 입력합니다.
생성을 마치면 연결 이름을 SourceDB라고 변경하겠습니다.

2.       위의 1번과 마찬가지 방법으로 동기화 대상 DB를 위한 연결을 두 개 더 추가합니다.
추가 후 이름을 각각 TargetDB1, TargetDB2라고 변경해주겠습니다.

3.       첫 번째 대상 테이블을 동기화 해주기 위해 원본 테이블의 상태 값을 바꿔주겠습니다.
SourceDB에는 위에서 만든 CODETABLE이 있어야 합니다.
SQL 실행 작업을 추가하고 이름을 상태 값 변경 (N->B)으로 변경하겠습니다.
Connection 속성은 SourceDB로 지정하고 SQLStatement 속성에 다음의 쿼리를 입력하겠습니다.

UPDATE CODETABLE SET STATUS = 'B' WHERE STATUS = 'N'

4.       데이터 흐름 작업을 추가하고 이름을 테이블 동기화 1으로 변경하겠습니다.
더블 클릭해서 데이터 흐름 디자인 창을 엽니다.
OLE DB 원본을 추가합니다.
OLE DB 원본을 더블 클릭해서 편집 창을 엽니다.
OLE DB 연결 관리자로 SourceDB를 지정하고 데이터 액세스 모드는 SQL 명령으로 선택합니다.
SQL 명령 텍스트로 아래의 쿼리를 입력합니다.

SELECT SEQ, COL1, COL2, COL3, STATUS FROM CODETABLE WHERE STATUS = 'B'

 

5.       OLE DB 명령을 추가합니다.
편집기를 열고 연결 관리자를 TargetDB1로 설정합니다.
구성 요소 속성 탭의 SqlCommand 속성에 아래의 쿼리를 입력합니다.

USP_CODEUPDATE ?, ?, ?, ?

 

열 매핑 탭에서 입력 열과 대상 열이 같은 이름으로 매핑이 되어 있는지 확인합니다.



사실 이 작업은 OLE DB 명령을 사용하기 보다 임시 테이블을 만들어 처리하는 방식이 좋을 수도 있습니다.
하지만 변경되는 건수가 적다면 굳이 매번 임시 테이블을 생성하지 않고 OLE DB 명령을 이용하는 것도 방법입니다.

이것으로 첫 번째 테이블의 동기화 부분이 구현되었습니다.
두 번째 테이블의 동기화는 첫 번째 테이블의 동기화와 구현은 거의 동일한데 쿼리에서 상태 값 변경과 대상 DB만 다릅니다.

6.       제어 흐름 디자인 창으로 돌아옵니다.
정석은 아니지만 작업이 비슷하니깐 잠시 사도를 걸어보겠습니다.
앞에서 만든 상태 값 변경 (N->B) 작업을 복사하고 같은 디자인 창에 붙여 넣기를 하겠습니다.
상태 값 변경 (N->B) 1이라는 작업이 복사되어 생성될 것 입니다.
이름을 상태 값 변경 (B->C)로 변경하고 테이블 동기화 1과 연결하겠습니다.
편집 창을 열어서 SQLStatement 속성의 쿼리를 살짝 바꿔주겠습니다.

UPDATE CODETABLE SET STATUS = 'C' WHERE STATUS = 'B'

속성 값만 조금 바뀌었을 뿐입니다.

 

7.       또 Copy & Paste를 하겠습니다. ^^;;;
테이블 동기화 1도 복사 & 붙여 넣기를 하면 테이블 동기화 1 1 이라는 작업이 생성될 것 입니다.
이름을 테이블 동기화 2로 변경하고 상태 값 변경 (B->C)와 연결해줍니다.



이런 모양이 되겠지요?

8.       더블 클릭해서 데이터 흐름 디자인 창을 열겠습니다.
OLE DB 원본과 OLE DB 명령이 이미 있지요?

OLE DB 원본의 편집 창을 열어서 SQL 명령 텍스트를 변경해주겠습니다.

SELECT SEQ, COL1, COL2, COL3, STATUS FROM CODETABLE WHERE STATUS = 'C'

 

9.       다음은 OLE DB 명령의 편집 창을 열겠습니다.
여기서는 연결 관리자만 TargetDB2로 설정해주면 됩니다.

10.    제어 흐름 디자인 창으로 돌아오겠습니다.
마지막으로 복사 & 붙여 넣기를 하겠습니다.;;;

상태 값 변경 (B->C) 작업을 복사해서 붙여 넣고 이름을 상태 값 변경 (C->Y)라고 변경하겠습니다.
테이블 동기화 2와 연결하고 편집 창을 열어 SQLStatement 속성에 아래의 쿼리를 입력합니다.

UPDATE CODETABLE SET STATUS = 'Y' WHERE STATUS = 'C'

 

11.    패키지가 완성 되었습니다.
현재 원본 DB의 CODETABLE에는 다음과 같은 데이터가 입력되어 있습니다.



또한 동기화 대상 테이블에는 아무 데이터가 없습니다.
F5를 눌러서 디버그 해보겠습니다.



정상적으로 동기화가 되었습니다.
그럼, 원본 DB 값을 UPDATE, INSERT 해보겠습니다.

UPDATE CODETABLE SET COL1 = 'QWERTY', COL2 = 'ASDFGH', COL3 = '12345' WHERE SEQ = 7 INSERT CODETABLE DEFAULT VALUES INSERT CODETABLE (COL1, COL2, COL3) VALUES ('가나다', '123', 'ABC')   SELECT * FROM CODETABLE

패키지 실행 후 결과를 확인해보겠습니다.

12.    마지막으로 한가지 테스트를 더 해보겠습니다.
동기화 중 동기화 되려는 정보가 다시 변경이 된다면 어떻게 될까요?

이걸 테스트 하기 위해서 패키지 중간에 중단점을 추가하겠습니다.
첫 번째 테이블 동기화가 진행된 시점에 재 수정 이벤트가 발생했다고 가정하기 위해
테이블 동기화 1에서 오른쪽 마우스 클릭을 하고 중단점 편집을 선택하겠습니다.

OnPostExecute 이벤트를 받는 경우 중단에 체크하겠습니다.



자, 중단점이 생성됐기 때문에 테이블 동기화 1 옆에 빨간 점이 생겼을 것입니다.

그럼 테스트를 해볼까요?
먼저 원본 테이블을 UPDATE하겠습니다.

UPDATE CODETABLE SET COL1 = 'ZZZ' WHERE SEQ = 2 SELECT * FROM CODETABLE

패키지를 실행합니다.
그러면 중단점이 설정되었기 때문에 테이블 동기화 1만 실행하고 멈출 것 입니다.
패키지는 중지하지 말고 그대로 두겠습니다. 쿼리를 실행 후 계속 진행해야하니까요.
번거롭지만 여기까지 결과를 보겠습니다.
모두가 예상하신 대로,



상태 값을 통해 B DB서버의 동기화가 진행 중이라는 것과 실제로 TargetDB 1에만 동기화가 되었다는 것을 알 수 있습니다.
여기서 또 원본 DB에서 쿼리를 실행해 UPDATE를 해보겠습니다.

UPDATE CODETABLE SET COL1 = 'GOOD', COL2 = 'DAY' WHERE SEQ = 2 UPDATE CODETABLE SET COL1 = '12345', COL2 = '67', COL3 = '8' WHERE SEQ = 8 INSERT CODETABLE (COL1, COL2, COL3) VALUES ('LAST', 'TEST', 'DATA') SELECT * FROM CODETABLE

자, 이제 멈춰있는 패키지를 계속 실행해보겠습니다.
F5 버튼을 다시 눌러서 진행하면 패키지는 무사히 완료되고 테이블은?



상태 값이 N으로 바뀌었기 때문에 동기화가 중단되었습니다.
대신 다음 동기화 주기 때 모두 동기화가 되겠지요.

데이터 흐름의 OLE DB 원본의 쿼리 조건을 조금 바꿔서 Y가 아닌 값은 모두 업데이트하는 모양으로 바꿔줘도 좋을 것 같네요.
그렇게 짰다면 TargetDB 2의 값은 동기화가 되어 있겠지요.

그 작업은 글을 읽으시는 분들께 맡겨보도록 하겠습니다. ^^;;;

남은 것은 Agent에 등록해서 정기적으로 실행되도록 하는 것인데요.
이 방법에 대해서는 기본 강좌에 올라와 있고 하니 게시물을 참고해주세요~