[SQL Server] CDC

CDC : Change Data Capture 는 데이터가 변경 된 경우 이를 저장하는 기능이다.

해당 기능을 이전에 사용했을 때는 깊은 고민없이 사용하였다. 최근 데이터 동기화 방안과 RO 구성에 대한 다양한 방식을 찾다가 CDC 기능을 다시 확인하였고 트리거와 복제 등의 구성과 어떤게 다른점이 있어서 사용을 하는지에 대해 알아보고자 했다.

 

CDC 특징

1. CDC는 데이터 변경의 값을 트랜잭션 로그를 기반으로 하여 추적한다.

https://learn.microsoft.com/ko-kr/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver16

 

2. 원본 테이블에 접근하지 않고 타겟 테이블에 데이터 변경에 대한 기록을 남기기 때문에 서비스에 영향이 적다.

3. 다양한 튜닝을 통하여 CDC의 성능 향상이 가능하다.

4. 원본 테이블에 스키마 변경이 생기면 CDC에 수동으로 반영이 필요하다. (DDL이 미포함이므로)

 

CDC의 경우 복구모델이 FULL이 아니어도 가능하다. 

(SQL SERVER 문서 내용)

데이터베이스에서 변경 데이터 캡처를 사용하도록 설정하면 복구 모드가 단순 복구로 설정되어 있어도 캡처 대상으로 표시된 모든 변경 사항이 캡처 프로세스에 의해 수집될 때까지 로그 자르기 지점이 진행되지 않습니다. 캡처 프로세스가 실행 중이 아니고 수집할 변경 사항이 있는 경우 CHECKPOINT를 실행하면 로그가 잘리지 않습니다.

즉 SIMPLE 모드로 설정되어 있을 때 로그 파일이 생성되지 않는게 아니라 CHECKPOINT 등의 주기 이후에 로그 파일이 짤리게 된다. 이 과정에서 CDC가 일어나고 만약 CDC가 완료되지 않았다면 로그파일을 자르는 결과가 발생하는CHECKPOINT 행위가 실패하게된다.

 

그렇다면 트리거, 복제와는 어떤 차이가 있을까?

 

DML 트리거

트리거에 정의된 테이블 또는 뷰에 영향을 주는 DML(데이터 조작 언어) 이벤트가 발생할 때 자동으로 적용되는 특수한 유형의 저장 프로시저로 LOCKING, SHARING 등에서 성능 이슈가 발생할 수 있다.

 

트랜잭션 로그 복제

 

트랜잭션 로그의 내용을 모니터링하여 변경된 내용만을 구독자 서버로 전송하는 방식이며 첫 동기화인 스냅샷 단계는 스냅샷 에이전트가 게시될 테이블의 스키마와 데이터 전부를 스냅샷 파일로 스냅샷 폴더에 저장하고 배포DB에도 초기 스냅샷 내용을 기록한다. 배포 에이전트가 스냅샷 작업을 각 구독자에게 배포하여 초기 동기화를 수행한다.(이때 초기 스냅샷 파일은 동기화가 완료되면 자동 삭제된다.)

이후 변경내용은 로그판독기(logreader)가 트랜잭션 로그를 읽어 ‘복제’ 표시된 트랜잭션을 배포DB로 전송한다.(게시대상에 포함된 테이블은 테이블 속성에 ‘복제’가 표시되므로 해당 테이블에 대한 모든 변경은 트랜잭션 로그에 ‘복제’가 표시되어 기록된다.)

배포 DB에서 복제 에이전트는 배포DB에 기록된 트랜잭션 중 커밋된 트랜잭션만 구독DB로 배포한다. 특히 밀어넣기(push) 구독방식을 사용할 경우 거의 실시간(1초미만)으로 구독DB에 적용할 수 있다.

트랜잭션 로그의 내용을 모니터링하여 변경된 내용만을 구독자 서버로 전송하는 방식이다.

즉 데이터 변경 내용은 트랜잭션 로그가 아닌 복제 에이전트를 통해 전파되며 이는 DDL도 포함이 된다.

 

※ 행 필터는 주의해서 사용한다. 로그판독기가 트랜잭션 로그를 검색할 때 테이블 업데이트에 의해 영향받는 각 행에 필터를 적용해야 하므로 로그판독기 작업에 부하가 발생하게 된다.

 

 

트리거는 변경 작업이 발생한 테이블에서 직접 실행되고, 변경된 데이터를 처리하기 위해 저장 프로시저를 호출한다.

반면에 복제는 변경 작업을 원본 데이터베이스에서 캡처하고, 변경 내용을 대상 데이터베이스로 전파하여 복제하는 방식으로 작동하며 CDC는 유저 데이터 변경을 CDC 테이블에 저장한다.

 

---

2024-03-25

복제와 cdc가 동시에 사용되면 복제 에이전트의 데이터 캡처를 통해서 cdc, distribution DB에 적용된다는 내용 확인

복제 먼저 기록되며 CDC가 이어서 작업이 진행되고 복제 커밋에서 지연이 생길 경우 CDC 는 대기 지연 발생

트랜잭션 복제

변경 데이터 캡처 및 트랜잭션 복제는 동일한 데이터베이스에 함께 존재할 수 있지만 두 기능이 모두 설정된 경우 변경 테이블 채우기가 다르게 처리됩니다. 변경 데이터 캡처 및 트랜잭션 복제는 항상 동일한 sp_replcmds프로시저를 사용하여 트랜잭션 로그에서 변경 내용을 읽습니다. 변경 데이터 캡처를 자체적으로 사용하도록 설정하면 SQL Server 에이전트 작업이 호출됩니다sp_replcmds. 동일한 데이터베이스에서 두 기능을 모두 사용하도록 설정하면 로그 판독기 에이전트가 호출합니다 sp_replcmds. 이 에이전트는 변경 테이블과 distribution 데이터베이스 테이블을 모두 채웁니다. 자세한 내용은 복제 로그 판독기 에이전트를 참조하세요.

 

 

 

 

 

참조

https://learn.microsoft.com/ko-kr/sql/relational-databases/track-changes/track-data-changes-sql-server?view=sql-server-ver16

https://blog.naver.com/hgchon/70170946187

https://www.sqlservercentral.com/forums/topic/difference-in-cdc-vs-trigger

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e2c3b53b-b68a-43bf-99bb-18b0110b0bee/replication-vs-cdc?forum=sqlreplication

https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-ver16

https://bryteflow.com/sql-server-change-data-capture-cdc-for-real-time-sql-server-replication/

https://learn.microsoft.com/ko-kr/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver16