MSSQL, CDC (Change Data Capture) 기능 사용하기

Change Data Capture(CDC)는 SQL Server에서 데이터베이스 내 테이블의 변경 사항을 추적하는 기능이다. CDC는 테이블에 대한 INSERT, UPDATE, DELETE 작업을 자동으로 캡처하여 변경 로그 테이블에 저장한다. 이를 통해 데이터 변경 내역을 쉽게 조회하거나 복제할 수 있게 해준다. CDC는 주로 데이터 웨어하우스 구축이나 데이터 변경 추적을 필요로 하는 애플리케이션에서 사용된다.

CDC를 사용하면 원본 테이블의 데이터 변경 시 어떤 데이터가 변경되었는지 확인할 수 있으며, 이를 통해 데이터의 변경 이력을 관리하고 다른 시스템과 동기화할 수 있다.

예를 들어, tableA에 대해 CDC가 활성화되어 있다고 가정하자. tableA의 구조는 다음과 같다:

CREATE TABLE tableA (
    ID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Quantity INT
);

CDC를 사용하여 tableA의 변경 사항을 추적하려면 다음과 같은 과정을 거친다:

1. CDC 활성화: 먼저 데이터베이스와 테이블에 대해 CDC를 활성화한다.

-- 데이터베이스에 대해 CDC 활성화
EXEC sys.sp_cdc_enable_db;

-- tableA에 대해 CDC 활성화
EXEC sys.sp_cdc_enable_table 
    @source_schema = 'dbo', 
    @source_name = 'tableA', 
    @role_name = NULL;

CDC가 활성화되면 cdc.dbo_tableA_CT라는 변경 테이블이 자동으로 생성된다. 이 테이블은 tableA의 데이터 변경 내역을 캡처하게 된다.

1. 데이터 변경 시 예시: tableA에서 데이터가 변경되는 경우를 예로 들어보자.

• 데이터 INSERT: tableA에 새로운 데이터를 추가했다고 가정한다.

INSERT INTO tableA (ID, Name, Quantity)
VALUES (1, 'ProductA', 100);

이 경우 CDC는 cdc.dbo_tableA_CT에 해당 데이터의 변경 내역을 기록한다. cdc.dbo_tableA_CT 테이블은 변경된 데이터의 메타데이터(예: 변경 유형, 변경 시각 등)를 포함하고 있으며, 'Net changes'와 같은 옵션을 통해 해당 변경사항을 조회할 수 있다.

• 데이터 UPDATE: tableA의 ID가 1인 행의 Quantity를 150으로 업데이트한다고 가정하자.

UPDATE tableA
SET Quantity = 150
WHERE ID = 1;

이 경우 cdc.dbo_tableA_CT 테이블에 기존 값과 새로운 값이 모두 기록된다. CDC는 이 변경 내역을 통해 어떤 데이터가 수정되었는지, 이전 값이 무엇이었는지 추적할 수 있게 해준다.

• 데이터 DELETE: tableA의 ID가 1인 데이터를 삭제한다고 가정하자.

DELETE FROM tableA
WHERE ID = 1;

이 경우에도 cdc.dbo_tableA_CT에 해당 행이 삭제되었다는 내용이 기록된다. 이를 통해 사용자는 데이터 삭제 내역을 추적할 수 있다.

1. CDC 변경 데이터 확인: 변경 사항을 확인하기 위해 변경 테이블(cdc.dbo_tableA_CT)을 조회할 수 있다.

SELECT *
FROM cdc.dbo_tableA_CT
ORDER BY __$start_lsn;

이 쿼리는 tableA에서 발생한 INSERT, UPDATE, DELETE 작업의 변경 내역을 시간 순서대로 보여준다. CDC 변경 테이블에는 _$_operation이라는 열이 있어 작업 유형을 나타낸다. 예를 들어:

• 1: INSERT
• 2: DELETE
• 3: 이전 값 (UPDATE의 경우)
• 4: 새로운 값 (UPDATE의 경우)

이를 통해 tableA의 변경 사항을 손쉽게 추적하고 데이터 변경에 대한 이력을 확인할 수 있다.

위와 같은 과정을 통해 CDC를 사용하면 데이터 변경 내역을 실시간으로 추적하고 필요한 시점에 활용할 수 있다.