MSSQL, CDC 원본 테이블이 alter 될 경우

CDC가 활성화된 테이블에서 ALTER 명령어로 테이블 구조가 변경되는 경우에는 몇 가지 중요한 상황이 발생한다. tableA와 같이 이미 CDC가 활성화된 테이블에서 컬럼 추가, 컬럼 삭제, 데이터 타입 변경 등의 구조 변경을 진행할 때, CDC에 미치는 영향에 대해 설명하겠다.

1. 테이블 구조 변경 시의 영향

• 새로운 컬럼 추가: tableA에 새로운 컬럼을 추가하면, CDC에 의해 해당 컬럼의 변경 내역이 자동으로 추적되지 않는다. 즉, CDC가 활성화된 이후에 추가된 컬럼은 기본적으로 CDC에서 무시된다. 이러한 경우, CDC 기능이 변경된 스키마를 반영하도록 다시 설정해야 한다.
• 컬럼 삭제: CDC 활성화 이후에 특정 컬럼을 삭제하면, 해당 컬럼에 대한 변경 사항은 더 이상 추적되지 않으며, 변경 로그 테이블에도 더 이상 존재하지 않게 된다. 그러나 이미 기록된 변경 데이터는 로그 테이블에 남아 있게 된다.
• 컬럼 타입 변경: 컬럼의 데이터 타입을 변경하면 CDC에서 해당 컬럼에 대한 변경 사항을 제대로 추적하지 못할 수 있다. 데이터 타입 변경 시 기존의 변경 내역과 호환성 문제가 발생할 수 있으며, 이러한 경우에는 CDC를 재설정하거나 다시 구성하는 것이 필요할 수 있다.

2. CDC에 미치는 영향 및 해결 방안

• CDC 재설정 필요: 테이블 구조가 변경되면 CDC는 변경된 스키마 정보를 자동으로 인식하지 않는다. 따라서 다음과 같은 절차를 통해 CDC를 재설정하거나 업데이트해야 한다.
  1. CDC 비활성화: 먼저 테이블에 대해 CDC를 비활성화해야 한다.
  2. 테이블 변경: 이후 필요한 테이블 구조 변경(ALTER TABLE)을 수행한다.
  3. CDC 재활성화: 테이블 변경이 완료되면 다시 CDC를 활성화하여 변경된 스키마를 반영하도록 한다.

EXEC sys.sp_cdc_disable_table 
    @source_schema = 'dbo', 
    @source_name = 'tableA', 
    @capture_instance = 'dbo_tableA';
    
    
EXEC sys.sp_cdc_enable_table 
    @source_schema = 'dbo', 
    @source_name = 'tableA', 
    @role_name = NULL;

 

 

• 데이터 손실 방지: CDC를 비활성화하고 재설정하는 과정에서 기존 변경 내역이 손실될 수 있다. 따라서 중요한 변경 이력을 유지해야 한다면, 변경 내역을 다른 테이블이나 외부 저장소로 백업하는 것이 좋다.

3. sys.sp_cdc_enable_table 재설정 시 주의사항

• 기존 변경 데이터 보존: CDC를 비활성화하면 기존의 변경 테이블(cdc.dbo_tableA_CT)이 삭제될 수 있기 때문에 변경 내역을 보존하고 싶다면 백업이 필요하다.
• 데이터베이스 잠금: 테이블을 변경하거나 CDC를 재활성화하는 동안 데이터베이스 잠금이 발생할 수 있다. 대용량 데이터베이스나 운영 환경에서는 이러한 변경 작업을 수행할 때 주의해야 하며, 적절한 유지보수 시간을 확보하는 것이 좋다.

4. 테이블 변경을 반영하는 대안

• 새로운 Capture Instance: 테이블 구조 변경 이후 기존 변경 사항을 유지하면서 새로운 변경 사항을 반영하고자 한다면, 새로운 CDC 인스턴스를 만들 수도 있다. 즉, sp_cdc_enable_table을 호출할 때 @capture_instance를 새로 정의하여 기존 인스턴스와 병행하여 사용할 수 있다. 이를 통해 데이터 변경 사항을 더 잘 관리할 수 있다.

결론

tableA가 ALTER를 통해 변경되면, CDC가 자동으로 새로운 구조를 반영하지 않기 때문에, CDC를 재설정하거나 적절하게 관리하는 작업이 필요하다. 구조 변경이 발생했을 때의 정확한 처리와 데이터 손실 방지 조치를 취하는 것이 중요하다.