다음을 통해 공유

변경 데이터 캡처(CDC)란?

  • 아티클

적용 대상: SQL Server Azure SQL Managed Instance

이 문서에서는 테이블 및 행이 수정되었을 때 데이터베이스에 작업을 기록하는 CDC(변경 데이터 캡처)에 대해 알아봅니다.

이 문서에서는 CDC가 SQL Server 및 Azure SQL Managed Instance에서 작동하는 방법을 설명합니다. Azure SQL 데이터베이스의 경우 Azure SQL 데이터베이스를 사용한 CDC를 참조하세요.

개요

변경 데이터 캡처는 SQL Server 에이전트를 사용하여 테이블에서 발생하는 삽입, 업데이트 및 삭제를 기록합니다. 따라서 관계형 형식을 사용하여 이러한 데이터 변경 사항에 쉽게 액세스할 수 있습니다. 이러한 변경 데이터를 대상 환경에 적용하는 데 필요한 열 데이터와 필수 메타데이터는 수정된 행에 대해 캡처되어 추적된 소스 테이블의 열 구조를 미러링하는 변경 테이블에 저장됩니다. 또한, 소비자가 이 변경 데이터에 체계적으로 액세스할 수 있도록 테이블 값 함수를 사용할 수 있습니다.

이 기술을 사용하는 데이터 소비자의 좋은 예로는 추출, 변환 및 로드(ETL) 애플리케이션이 있습니다. ETL 애플리케이션은 SQL Server 원본 테이블에서 데이터 웨어하우스 또는 데이터 마트로 변경 데이터를 증분 방식으로 로드합니다. 데이터 웨어하우스 내의 원본 테이블 표현은 원본 테이블의 변경 내용을 반영해야 하지만 원본의 복제본(replica)을 새로 고치는 E2E 기술은 여기에 적절하지 않습니다. 대신 소비자가 다른 종류의 데이터 대상 표현에 적용할 수 있도록 구조화된 안정적인 변경 데이터 스트림이 필요합니다. SQL Server 변경 데이터 캡처는 이러한 기술을 제공합니다.

데이터 흐름

다음 그림에서는 변경 데이터 캡처의 주요 데이터 흐름을 보여 줍니다.

변경 데이터 캡처 데이터 흐름 다이어그램

변경 데이터 캡처에 대한 변경 데이터 원본은 SQL Server 트랜잭션 로그입니다. 추적된 원본 테이블에 삽입, 업데이트 및 삭제가 적용되면 이러한 변경을 설명하는 항목이 로그에 추가됩니다. 로그는 캡처 프로세스의 입력 역할을 합니다. 그런 다음 로그를 읽고 추적된 테이블의 연관된 변경 테이블에 변경에 대한 정보를 추가합니다. 지정된 범위에서 변경 테이블에 나타나는 변경을 열거하여 해당 정보를 필터링된 결과 집합의 형태로 반환하는 함수가 제공됩니다. 필터링된 결과 집합은 일반적으로 일부 외부 환경의 원본 표현을 업데이트하는 애플리케이션 프로세스에서 사용됩니다.

캡처 인스턴스

데이터베이스 내의 개별 테이블에 대한 변경 사항을 추적하려면 먼저 데이터베이스에 대해 변경 데이터 캡처를 명시적으로 사용하도록 설정해야 합니다. 이 작업은 sys.sp_cdc_enable_db 저장 프로시저를 사용하여 수행합니다. 데이터베이스를 사용하도록 설정한 경우 sys.sp_cdc_enable_table 저장 프로시저를 사용하여 소스 테이블을 추적된 테이블로 식별할 수 있습니다. 테이블에 변경 데이터 캡처를 사용하도록 설정하면 관련 캡처 인스턴스가 만들어져 원본 테이블에서의 변경 데이터 배포가 지원됩니다. 캡처 인스턴스는 변경 테이블과 최대 두 개의 쿼리 함수로 구성됩니다. 캡처 인스턴스의 구성 세부 정보를 설명하는 메타데이터는 변경 데이터 캡처 메타데이터 테이블인 cdc.change_tables, cdc.index_columns및 cdc.captured_columns에 보존됩니다. 이 정보는 저장 프로시저 sys.sp_cdc_help_change_data_capture를 사용하여 검색할 수 있습니다.

캡처 인스턴스와 관련된 모든 개체는 변경 데이터 캡처를 사용하도록 설정된 데이터베이스의 변경 데이터 캡처 스키마에 만들어집니다. 캡처 인스턴스 이름은 유효한 개체 이름이어야 하며 데이터베이스 캡처 인스턴스에서 고유해야 합니다. 기본적으로 이름은 소스 테이블의 <스키마 이름_테이블 이름>입니다. 연결된 변경 테이블은 캡처 인스턴스 이름에 _CT를 추가하여 이름이 지정됩니다. 모든 변경 내용을 쿼리하는 데 사용되는 함수의 이름은 캡처 인스턴스 이름 앞에 fn_cdc_get_all_changes_ 를 추가하여 지정됩니다. 캡처 인스턴스가 net changes를 지원하도록 구성된 경우 net_changes 쿼리 함수도 생성되고 캡처 인스턴스 이름 앞에 fn_cdc_get_net_changes_를 추가하여 이름이 지정됩니다.

Important

단일 소스 테이블에 동시에 연결할 수 있는 캡처 인스턴스의 최대 개수는 2개입니다.

변경 테이블

변경 데이터 캡처 변경 테이블의 처음 5개의 열은 메타데이터 열입니다. 이 열은 기록된 변경 내용과 관련된 추가 정보를 제공합니다. 나머지 열은 이름과 일반적으로 유형이 소스 테이블에서 식별된 캡처된 열을 미러링합니다. 이러한 열에는 소스 테이블에서 수집된 캡처된 열 데이터가 저장됩니다.

소스 테이블에 적용되는 각 삽입 또는 삭제 작업은 변경 테이블 내에서 단일 행으로 나타납니다. 삽입 작업의 결과로 생성되는 행의 데이터 열에는 삽입 이후의 열 값이 포함되며 삭제 작업의 결과로 생성되는 행의 데이터 열에는 삭제 이전의 열 값이 포함됩니다. 업데이트 작업의 경우 하나의 행 항목에서 업데이트 이전의 열 값을 식별하고 다른 행 항목에서 업데이트 이후의 열 값을 식별해야 합니다.

변경 테이블의 각 행에는 변경 활동을 해석할 수 있는 다른 메타데이터도 포함되어 있습니다. __$start_lsn 열은 변경에 할당된 커밋 로그 시퀀스 번호(LSN)를 식별합니다. 커밋 LSN은 동일한 트랜잭션 내에서 커밋된 변경 내용을 식별할 뿐만 아니라 해당 트랜잭션을 정렬합니다. __$seqval 열은 동일한 트랜잭션에서 발생하는 더 많은 변경의 순서를 지정하는 데 사용할 수 있습니다. 열 __$operation은 변경 내용과 관련된 1 = 삭제, 2 = 삽입, 3 = 업데이트(이전 이미지) 및 4 = 업데이트(이후 이미지) 작업을 기록합니다. __$update_mask 열은 캡처된 각 열에 대해 정의된 비트가 하나씩 있는 가변 비트 마스크입니다. 삽입 및 삭제 항목의 경우 업데이트 마스크에는 모든 비트가 설정됩니다. 그러나 행 업데이트에는 변경된 열에 해당하는 비트가 설정됩니다.

유효 간격

데이터베이스에 대한 변경 데이터 캡처 유효성 간격은 캡처 인스턴스에 변경 데이터를 사용할 수 있는 시간입니다. 유효 간격은 데이터베이스 테이블에 대한 첫 번째 캡처 인스턴스가 생성될 때 시작되어 현재 시간까지 계속됩니다.

데이터베이스

변경 테이블에 저장된 데이터는 주기적이고 체계적으로 데이터를 정리하지 않으면 관리하기 힘들 정도로 커집니다. 변경 데이터 캡처 정리 프로세스는 보존 기반 정리 정책을 시행하는 역할을 합니다. 먼저 시간 제한을 충족하기 위해 유효 간격의 로우 엔드포인트로 이동합니다. 그런 다음 만료된 변경 테이블 항목을 제거합니다. 기본적으로 3일 분량의 데이터가 보존됩니다.

하이엔드에서는 캡처 프로세스가 각각의 새로운 변경 데이터 일괄 처리를 커밋할 때 변경 테이블 항목이 있는 각 트랜잭션에 대해 cdc.lsn_time_mapping에 새 항목이 추가됩니다. 매핑 테이블 내에서 커밋 LSN(로그 시퀀스 번호)과 트랜잭션 커밋 시간(각각 start_lsn 열 및 tran_end_time 열)은 모두 보존됩니다. cdc.lsn_time_mapping 에 있는 최대 LSN 값은 데이터베이스 유효성 기간의 상위 워터마크를 나타냅니다. 이 값의 해당 커밋 시간은 보존 기반 정리에서 새 하위 워터마크를 계산하는 기반으로 사용됩니다.

캡처 프로세스는 트랜잭션 로그에서 변경 데이터를 추출하기 때문에 변경이 소스 테이블에 커밋되는 시간과 연결된 변경 테이블에 변경이 표시되는 시간 사이에 기본 제공 대기 시간이 있습니다. 이 대기 시간은 일반적으로 짧지만, 캡처 프로세스가 관련 로그 항목을 처리할 때까지 변경 데이터를 사용할 수 없다는 점을 기억하는 것이 중요합니다.

캡처 인스턴스

데이터베이스 유효 간격과 개별 캡처 인스턴스의 유효 간격이 일치하는 것이 일반적이지만, 항상 일치하는 것은 아닙니다. 캡처 인스턴스의 유효 간격은 캡처 프로세스가 캡처 인스턴스를 인식하고 관련 변경 사항을 변경 테이블에 기록하기 시작할 때 시작됩니다. 따라서 캡처 인스턴스가 서로 다른 시간에 생성된 경우 각 인스턴스는 서로 다른 로우 엔드포인트를 갖게 됩니다. sys.sp_cdc_help_change_data_capture가 반환하는 결과 집합의 start_lsn 열에는 정의된 각 캡처 인스턴스의 현재 로우 엔드포인트가 표시됩니다. 정리 프로세스가 변경 테이블 항목을 정리하면 사용 가능한 변경 데이터의 새로운 최저점을 반영하도록 모든 캡처 인스턴스에 대한 start_lsn 값을 조정합니다. 현재 새 하위 워터마크보다 작은 start_lsn 값을 포함하는 캡처 인스턴스만 조정됩니다. 시간이 지남에 따라 새로운 캡처 인스턴스가 생성되지 않으면 모든 개별 인스턴스의 유효 간격이 데이터베이스 유효 간격과 일치하는 경향이 있습니다.

요청에 대한 추출 간격이 캡처 인스턴스에 대한 현재 변경 데이터 캡처 유효 간격에 완전히 포함되어야 하므로 유효 간격은 변경 데이터 소비자에게 중요합니다. 추출 간격의 로우 엔드포인트가 유효 간격의 로우 엔드포인트의 왼쪽에 있는 경우 즉시 정리로 인해 변경 데이터가 누락될 수 있습니다. 추출 간격의 하이 엔드포인트가 유효 간격의 하이 엔드포인트의 오른쪽에 있으면 캡처 프로세스가 추출 간격으로 표시된 시간 동안 아직 처리되지 않았음을 나타내며, 누락된 변경 데이터도 있을 수 있습니다.

sys.fn_cdc_get_min_lsn 함수는 캡처 인스턴스에 대한 현재 최소 LSN 값을 검색하는 데 사용되고 sys.fn_cdc_get_max_lsn 함수는 현재 최대 LSN 값을 검색하는 데 사용됩니다. 변경 데이터를 쿼리할 때 지정된 LSN 범위가 이 두 LSN 값에 속하지 않으면 변경 데이터 캡처 쿼리 기능이 실패합니다.

원본 테이블에 대한 변경 내용 처리

추적 중인 소스 테이블의 열 변경 사항을 수용하는 것은 다운스트림 소비자에게는 어려운 문제입니다. 소스 테이블에서 변경 데이터 캡처를 사용하도록 설정해도 이러한 DDL 변경이 발생하는 것을 방지할 수는 없지만, 변경 데이터 캡처는 기본 소스 테이블의 열 구조가 변경되더라도 API를 통해 반환된 결과 집합을 보존함으로써 소비자에게 미치는 영향을 완화합니다. 이 고정된 열 구조는 정의된 쿼리 함수가 액세스하는 기본 변경 테이블에도 반영됩니다.

변경 테이블을 채우는 캡처 프로세스는 소스 테이블이 변경 데이터 캡처를 사용하도록 설정되었을 때 캡처를 위해 식별되지 않은 새 열을 무시하여 고정 열 구조 변경 테이블을 수용합니다. 추적되는 열이 삭제되면 후속 변경 항목에서 해당 열에 대해 null 값이 제공됩니다. 그러나 기존 열의 데이터 형식이 변경되는 경우 캡처 메커니즘으로 인해 추적되는 열에 데이터 손실이 발생하지 않도록 변경 사항이 변경 테이블에 전파됩니다. 또한 캡처 프로세스는 추적된 테이블의 열 구조에 대해 감지된 모든 변경 사항을 cdc.ddl_history 테이블에 게시합니다. 다운스트림 애플리케이션에서 조정해야 할 수 있는 변경 사항에 대해 경고를 수신하려면 저장 프로시저 sys.sp_cdc_get_ddl_history를 사용하면 됩니다.

일반적으로 현재 캡처 인스턴스는 연결된 소스 테이블에 DDL 변경 사항이 적용될 때 그 모양을 계속 유지합니다. 그러나 새 열 구조를 반영하는 테이블에 대한 두 번째 캡처 인스턴스를 만들 수 있습니다. 이 옵션을 사용하면 캡처 프로세스에서 동일한 소스 테이블을 두 개의 서로 다른 열 구조를 가진 두 개의 별개의 변경 테이블로 변경할 수 있습니다. 따라서 한 변경 테이블에서는 현재 작업 프로그램에 계속 공급을 수행하고 두 번째 변경 테이블에서는 새 열 데이터를 통합하려고 하는 개발 환경을 운영할 수 있습니다. 캡처 메커니즘이 두 변경 테이블을 함께 채우도록 허용하면 변경 데이터의 손실 없이 한 테이블에서 다른 테이블로 전환할 수 있습니다. 이는 두 변경 데이터 캡처 타임라인이 겹치는 경우 언제든지 발생할 수 있습니다. 전환이 영향을 받으면 더 이상 사용되지 않는 캡처 인스턴스를 제거할 수 있습니다.

Important

단일 소스 테이블에 동시에 연결할 수 있는 캡처 인스턴스의 최대 개수는 2개입니다.

로그 판독기 에이전트에 대한 관계

변경 데이터 캡처 프로세스의 논리는 sqlservr.exe의 일부로 구축된 내부 서버 함수이며 트랜잭션 복제에서도 트랜잭션 로그에서 변경 사항을 수집하는 데 사용되는 sp_replcmds 저장 프로시저에 내장되어 있습니다. SQL Server 및 Azure SQL Managed Instance에서 데이터베이스에 변경 데이터 캡처만 사용하도록 설정하는 경우 변경 데이터 캡처 SQL Server 에이전트 캡처 작업을 sp_replcmds를 호출하는 수단으로 만듭니다. 복제가 있는 경우, 트랜잭션 로그 리더만 사용하여 이 두 소비자의 변경 데이터 요구 사항을 모두 충족합니다. 이 전략은 동일한 데이터베이스에 대해 복제와 변경 데이터 캡처를 모두 사용하도록 설정할 때 로그 경합을 크게 줄여줍니다.

변경 데이터 캡처를 위한 이 두 가지 운영 모드 간의 전환은 변경 데이터 캡처를 사용하도록 설정된 데이터베이스의 복제 상태가 변경될 때마다 자동으로 발생합니다.

참고 사항

SQL Server 및 Azure SQL Managed Instance에서 캡처 논리의 두 인스턴스 모두 프로세스를 실행하려면 SQL Server 에이전트가 실행 중이어야 합니다.

캡처 프로세스의 주요 작업은 로그를 스캔하고 변경 데이터 캡처 변경 테이블에 열 데이터 및 트랜잭션 관련 정보를 기록하는 것입니다. 캡처 프로세스는 채우는 모든 변경 데이터 캡처 변경 테이블에서 트랜잭션적으로 일관된 경계를 보장하기 위해 각 스캔 주기마다 자체 트랜잭션을 열고 커밋합니다. 테이블이 변경 데이터 캡처에 새로 사용하도록 설정되면 이를 감지하여 로그에서 변경 항목에 대해 적극적으로 모니터링되는 테이블 집합에 자동으로 포함합니다. 마찬가지로 변경 데이터 캡처를 사용하지 않도록 설정하는 것도 감지되어 소스 테이블이 변경 데이터에 대해 적극적으로 모니터링되는 테이블 세트에서 제거됩니다. 로그 섹션에 대한 처리가 완료되면 캡처 프로세스는 서버 로그 잘라내기 논리에 신호를 보내고, 서버는 이 정보를 사용해 잘라내기 대상 로그 항목을 식별합니다.

Important

데이터베이스가 변경 데이터 캡처를 사용하도록 설정된 경우, 복구 모드가 단순 복구로 설정되어 있더라도 캡처 프로세스에서 캡처 대상으로 표시된 모든 변경 사항을 수집할 때까지 로그 잘림 지점이 진행되지 않습니다. 캡처 프로세스가 실행되고 있지 않고 수집해야 할 변경 사항이 있는 경우 검사점을 실행해도 로그가 잘리지 않습니다.

캡처 프로세스는 추적된 테이블의 DDL 변경 내용에 대한 기록을 유지 관리하는 데에도 사용됩니다. 변경 데이터 캡처와 관련된 DDL 문은 변경 데이터 캡처가 가능한 데이터베이스 또는 테이블이 삭제되거나 변경 데이터 캡처가 가능한 테이블의 열이 추가, 수정 또는 삭제될 때마다 데이터베이스 트랜잭션 로그에 항목을 생성합니다. 이러한 로그 항목은 캡처 프로세스에 의해 처리되며, 캡처 프로세스는 관련 DDL 이벤트를 cdc.ddl_history 테이블에 게시합니다. sys.sp_cdc_get_ddl_history 저장 프로시저를 사용하여 추적된 테이블에 영향을 미치는 DDL 이벤트에 대한 정보를 얻을 수 있습니다.

Warning

  • MaxCmdsInTran은 항상 켜지도록 설계되지 않았습니다. 누군가가 실수로 단일 트랜잭션에서 많은 수의 DML 작업을 수행한 경우를 해결하기 위해 존재합니다(전체 트랜잭션이 배포 데이터베이스에 있을 때까지 명령 배포가 지연되고 잠금이 유지되는 등). 이 상황이 자주 발생하는 경우 애플리케이션 논리를 검토하여 트랜잭션 크기를 줄일 수 있는 방법을 찾아야 합니다.
  • 지정된 게시물 데이터베이스에 CDC와 복제를 모두 사용하도록 설정되어 있는 경우 MaxCmdsInTran이 지원되지 않습니다. 이 구성에서 MaxCmdsInTran을 사용하면 CDC 변경 테이블의 데이터가 손실될 수 있습니다. 또한 큰 트랜잭션을 복제하는 동안 MaxCmdsInTran 매개 변수가 추가되고 제거된 경우에도 PK 오류가 발생할 수 있습니다.

에이전트 작업

일반적으로 변경 데이터 캡처를 사용하도록 설정된 데이터베이스에는 데이터베이스 변경 테이블을 채우는 데 사용되는 작업과 변경 테이블 정리를 담당하는 작업 등 두 가지 SQL Server 에이전트 작업이 연관되어 있습니다. 두 작업 모두 Transact-SQL 명령을 실행하는 단일 단계로 구성됩니다. 호출되는 Transact-SQL 명령은 작업의 논리를 구현하는 변경 데이터 캡처 정의 저장 프로시저입니다. 데이터베이스의 첫 번째 테이블이 변경 데이터 캡처에 사용하도록 설정되면 작업이 생성됩니다. 정리 작업은 항상 만들어집니다. 캡처 작업은 데이터베이스에 대해 정의된 트랜잭션 게시물이 없는 경우에만 생성됩니다. 데이터베이스에 대해 변경 데이터 캡처와 트랜잭션 복제를 모두 사용하도록 설정되어 있고 데이터베이스에 더 이상 정의된 게시물이 없기 때문에 트랜잭션 로그 리더 작업이 제거된 경우에도 캡처 작업이 생성됩니다.

캡처 작업과 정리 작업 모두 기본 매개 변수를 사용하여 만들어집니다. 캡처 작업은 즉시 시작됩니다. 이 작업은 지속적으로 실행되며, 스캔 주기당 최대 1000개의 트랜잭션을 처리하고 주기 사이에 5초의 대기 시간을 갖습니다. 정리 작업은 매일 오전 2시에 실행되며, 4320분 또는 3일 동안 변경 테이블 항목을 유지하여 단일 삭제 문으로 최대 5,000개의 항목을 제거합니다.

데이터베이스에 변경 데이터 캡처를 사용하지 않도록 설정하는 경우 변경 데이터 캡처 에이전트 작업이 제거됩니다. 캡처 작업은 데이터베이스에 첫 번째 게시물이 추가되고 변경 데이터 캡처와 트랜잭션 복제가 모두 사용하도록 설정된 경우에도 제거될 수 있습니다.

내부적으로 변경 데이터 캡처 에이전트 작업은 저장 프로시저 sys.sp_cdc_add_job 및 sys.sp_cdc_drop_job을 사용하여 각각 생성 및 삭제됩니다. 이러한 저장 프로시저는 관리자가 이러한 작업의 생성 및 제거를 제어할 수 있도록 노출되어 있습니다.

관리자는 변경 데이터 캡처 에이전트 작업의 기본 구성을 명시적으로 제어할 수 없습니다. 기본 구성 매개 변수를 수정할 수 있도록 저장 프로시저 sys.sp_cdc_change_job이 제공됩니다. 또한 sys.sp_cdc_help_jobs 저장 프로시저를 사용하면 현재 구성 매개 변수를 볼 수 있습니다. 캡처 작업과 정리 작업 모두 시작 시 msdb.dbo.cdc_jobs 테이블에서 구성 매개 변수를 추출합니다. sys.sp_cdc_change_job을 사용하여 이러한 값을 변경하면 작업이 중지되었다가 다시 시작될 때까지 적용되지 않습니다.

변경 데이터 캡처 에이전트 작업을 시작 및 중지할 수 있는 다른 두 개의 저장 프로시저 sys.sp_cdc_start_job 및 sys.sp_cdc_stop_job이 제공됩니다.

참고 사항

캡처 작업을 시작 및 중지해도 변경 데이터는 손실되지 않습니다. 캡처 프로세스가 변경 테이블에 저장할 변경 항목에 대해 로그를 적극적으로 스캔하지 못하도록 할 뿐입니다. 로그 스캔이 최대 수요 기간 동안 부하를 추가하지 않도록 하는 합리적인 전략은 캡처 작업을 중지했다가 수요가 줄어들면 다시 시작하는 것입니다.

두 SQL Server 에이전트 작업 모두 변경 데이터 캡처 환경의 기본 요구 사항을 충족할 수 있을 만큼 유연하고 충분히 구성할 수 있도록 설계되었습니다. 그러나 두 경우 모두 핵심 기능을 제공하는 기본 저장 프로시저가 노출되어 있으므로 추가 사용자 지정이 가능합니다.

데이터베이스 엔진 서비스 또는 SQL Server 에이전트 서비스가 네트워크 서비스 계정으로 실행 중인 경우 변경 데이터 캡처가 올바르게 작동하지 않을 수 있습니다. 이로 인해 22832 오류가 발생할 수 있습니다.

다른 기능과 상호 운용성

변경 데이터 캡처는 다른 SQL Server 기능으로 작업할 때 몇 가지 제한 사항이 있습니다. 자세한 내용은 상호 운용성에서 확인할 수 있습니다.

알려진 문제

변경 데이터 캡처와 관련된 알려진 문제 및 오류는 CDC 관련 알려진 문제에서 확인할 수 있습니다.

참고 항목