[MSSQL] CDC 주요 명령어

 

사실 생각해보면 "이럴 떄 무슨 명령어 써야해?"가 주된 목적이다.

 

 

주요 명령어

CDC 켜고 끄기 - DB

-- 사용중인 DB CDC ON
USE <DB-Name>
EXECUTE sys.sp_cdc_enable_db

-- 사용중인 DB CDC OFF
USE <DB-Name>
EXECUTE sys.sp_cdc_disable_db

DB에 CDC 활성화해도
테이블 단위 enable 처리하지 않으면 CDC가 적용되지 않는다.

 

CDC 켜고 끄기 - Table

 

-- Enable CDC on table
EXECUTE sys.sp_cdc_enable_table
        @source_schema = N'<schema-name>',
        @source_name = N'<table-name>',
    --     @capture_instance = ,
        @supports_net_changes = 1, -- 모든 레코드를 Log Sequence Number(LSN) 으로 고유식별, 이 값을 하나의 순 변경행 반환.
        @role_name = NULL

-- Disable CDC on table
EXECUTE sys.sp_cdc_disable_table
        @source_schema = N'<schema-name>',
        @source_name = N'<table-name>',
        @capture_instance = N'<capture-instance-name'

 

capture_instance 란?

CDC 로인해 데이터가 담기는 테이블이다.

위 구문에서 

@capture_instance = <capture-instance-name> 으로 명시적으로 이름을 붙여줄 수 있지만

생략시 암시적으로 다음과 같이 네이밍된다.

@capture_instance = ${<schema-name>_<table-name>}

 

---

CDC Config 확인 - Database

사용중인 DB의 모든 table 구성정보 확인

SELECT * FROM cdc.change_tables;

CDC config 확인 - Table

특정 테이블의 CDC 구성정보를 확인할 수 있다.

EXECUTE sys.sp_cdc_help_change_data_capture
        @source_schema = N'<schema-name>',
        @source_name = N'<table-name>'

 

 

쿼리 결과값

column	datat type	description
source_schema	sysname	원본 테이블 스키마의 이름입니다.
source_table	sysname	원본 테이블의 이름입니다.
capture_instance	sysname	캡처 인스턴스의 이름입니다.
object_id	int	원본 테이블과 관련된 변경 테이블의 ID입니다.
source_object_id	int	원본 테이블의 ID입니다.
start_lsn	binary(10)	변경 테이블 쿼리의 하위 엔드포인트를 나타내는 LSN(로그 시퀀스 번호)입니다. NULL = 하위 엔드포인트가 설정되지 않았습니다.
end_lsn	binary(10)	변경 테이블 쿼리의 상위 엔드포인트를 나타내는 LSN입니다. SQL Server 2012(11.x)에서 이 열은 항상 NULL입니다.
supports_net_changes	bit	순 변경 지원이 활성화됩니다.
has_drop_pending	bit	SQL Server 2012(11.x)에서 사용되지 않습니다.
role_name	sysname	변경 데이터에 대한 액세스를 제어하는 데 사용되는 데이터베이스 역할의 이름입니다. NULL = 역할이 사용되지 않습니다.
index_name	sysname	원본 테이블의 행을 고유하게 식별하는 데 사용되는 인덱스 이름입니다.
filegroup_name	sysname	변경 테이블이 있는 파일 그룹의 이름입니다. NULL = 변경 테이블이 데이터베이스의 기본 파일 그룹에 있습니다.
create_date	datetime	캡처 인스턴스가 활성화된 날짜입니다.
index_column_list	nvarchar(max)	원본 테이블의 행을 고유하게 식별하는 데 사용되는 인덱스 열의 목록입니다.
captured_column_list	nvarchar(max)	캡처된 원본 열 목록입니다.

 

별도로 capture_instance 이름을 지정하지 않았다면 위 명령어를 실행하여 `capture_instance` 이름을 얻어낼 수 있을 것이다.

 

`capture_instance`는 실제 CDC 로인해 변경된 데이터 기록이 담길 테이블이고, 시스템 함수로 자주 이름이 쓰이기 때문에 꼭 알아둬야한다.

 

 

변경된 모든 데이터 조회 - 테이블 단위

-- capture-instance 이름을 인자로 넘겨 변경 기록을 모두 조회한다.
SELECT * FROM cdc.fn_cdc_get_all_changes_<capture-instance-name> ( sys.fn_cdc_get_min_lsn(<capture-instance-name>), sys.fn_cdc_get_max_lsn(), N'all')

 

 

마지막으로 변경된 데이터만 조회 - 테이블 단위

-- 실제 변경된 데이터 (net) 레코드만 가져온다.
SELECT * FROM cdc.fn_cdc_get_net_changes_<capture-instance-name> ( sys.fn_cdc_get_min_lsn(<capture-instance-name>), sys.fn_cdc_get_max_lsn(), N'all')

-- 일반 DML 로도 가능하다.
SELECT *
FROM cdc.<capture-instance-name>_CT
WHERE __$operation <> 3 -- UPDATE BEFORE 제외
ORDER BY __$start_lsn, updated_at DESC; -- 최신 데이터 순 정렬

 

결과값으로 $start_lsn 이라는 값을 얻게되는데 이 값이 Log Sequence Number 라고 변경된 데이터 row 를 그대로 담고있다. 이 값이 높은 값일 수록 최근에 변경된 데이터다.

위 쿼리를 잘 보면 lsn 의 최소값부터 최대값까지 한번에 구하고 있는 것을 알 수 있다.

 

 

 

-- 변경 이전 데이터 제외하고 조회
SELECT *
FROM cdc.dbo_ClassID_CT
-- WHERE __$operation <> 3
ORDER BY __$start_lsn, updated_at DESC; --

 

 

__$operation

• 1: DELETE
• 2: INSERT
• 3: UPDATE_BEFORE
• 4: UPDATE_AFTER

 

---

Reference

sys.sp_cdc_help_change_data_capture(Transact-SQL) - SQL Server