CDC(변경 데이터 캡처) 정의
적용 대상:
SQL Server Azure SQL 데이터베이스Azure SQL Managed Instance
이 문서에서는 테이블 및 행이 수정되었을 때 데이터베이스에 작업을 기록하는 CDC(변경 데이터 캡처)에 대해 알아봅니다. 변경 데이터 캡처는 일반적으로 Azure SQL Database, SQL Server 및 Azure SQL Managed Instance 사용할 수 있습니다.
개요
CDC(변경 데이터 캡처)는 SQL Server 에이전트를 사용하여 테이블에 적용되는 삽입, 업데이트 및 삭제 작업을 기록합니다. 이로 인해 변경 세부 정보가 쉽게 사용할 수 있는 관계형 형식으로 제공됩니다. 대상 환경에 변경 내용을 적용하는 데 필요한 열 정보 및 메타데이터가 수정된 행에 대해 캡처되고 추적된 원본 테이블의 열 구조를 반영하는 변경 테이블에 저장됩니다. 소비자가 변경 데이터에 체계적으로 액세스할 수 있도록 테이블 반환 함수가 제공됩니다.
이 기술의 대상이 될 수 있는 데이터 소비자는 ETL(추출, 변환 및 로드) 애플리케이션이 좋은 예입니다. ETL 애플리케이션은 증분식으로 SQL Server 원본 테이블의 변경 데이터를 데이터 웨어하우스 또는 데이터 마트로 로드합니다. 데이터 웨어하우스 내의 원본 테이블 표현은 원본 테이블의 변경 내용을 반영해야 하지만 원본의 복제본을 새로 고치는 엔드 투 엔드 기술은 적절하지 않습니다. 대신 소비자가 다른 종류의 데이터 대상 표현에 적용할 수 있도록 구조화된 안정적인 변경 데이터 스트림이 필요합니다. SQL Server 변경 데이터 캡처는 이러한 기술을 제공합니다.
CDC & Azure SQL Database
Azure SQL Database에서 변경 데이터 캡처 스케줄러는 저장 프로시저를 호출하여 변경 데이터 캡처 테이블의 주기적 캡처 및 정리를 시작하는 SQL Server 에이전트를 대체합니다. 스케줄러는 안정성 또는 성능을 위해 외부 종속성 없이 SQL Database 내에서 캡처 및 정리를 자동으로 실행합니다. 사용자는 필요에 따라 캡처 및 정리를 수동으로 실행할 수 있습니다.
변경 데이터 캡처에 대해 알아보려면 다음 데이터 노출 에피소드를 참조하세요.
성능 고려 사항
Azure SQL Database에서 변경 데이터 캡처를 사용하도록 설정하는 기능의 성능 영향은 SQL Server 또는 Azure SQL Managed Instance에 대해 CDC를 사용하도록 설정하는 기능의 성능 영향과 비슷합니다. CDC를 사용하도록 설정했을 때 성능에 영향을 주는 몇 가지 측면은 아래와 같습니다.
- 추적된 CDC 사용 테이블 수
- 추적된 테이블의 변경 빈도
- CDC 아티팩트(예: CT 테이블, cdc_jobs 등)가 동일한 데이터베이스에 저장되기 때문에 원본 데이터베이스에서 사용할 수 있는 공간
- 데이터베이스가 단일 또는 풀링된 데이터베이스인지 여부입니다. 탄력적 풀에 있는 데이터베이스의 경우 CDC가 사용되는 테이블 수를 고려하는 것 외에도 해당 테이블이 속한 데이터베이스 수에 주의해야 합니다. 풀에 있는 데이터베이스는 리소스(예: 디스크 공간)를 공유하므로 여러 데이터베이스에서 CDC를 사용하면 탄력적 풀 디스크 크기의 최대 크기에 도달할 위험이 있습니다. CPU, 메모리 및 로그 처리량과 같은 리소스를 모니터링합니다.
고객에게 보다 구체적인 성능 최적화 지침을 제공하려면 각 고객의 워크로드에 대한 자세한 정보가 필요합니다. 그러나 TPCC 워크로드에서 실행된 성능 테스트를 기반으로 하는 몇 가지 일반적인 지침은 다음과 같습니다.
Azure SQL Database에서 CDC를 사용하도록 설정하기 전과 동일한 성능 수준을 보장하려면 vCore 수를 늘리거나 더 높은 데이터베이스 계층(예: 하이퍼스케일)으로 전환하는 것이 좋습니다.
프로덕션의 데이터베이스에서 CDC를 사용하도록 설정하기 전에 공간 사용률을 면밀히 모니터링하고 워크로드를 철저히 테스트합니다.
로그 생성 속도를 모니터링합니다. 자세한 내용은 여기를 참조하세요.
검사/정리는 사용자 워크로드의 일부입니다(사용자의 리소스가 사용됨). 전체 행이 변경 테이블에 추가되고 업데이트 작업의 경우 사전 이미지도 포함되므로 성능에 상당한 영향을 줄 수 있습니다.
탄력적 풀 - 대기 시간 증가를 방지하기 위해 CDC 사용 데이터베이스 수가 풀의 vCore 수를 초과하면 안 됩니다. 여기에서 조밀한 탄력적 풀의 리소스 관리에 대해 자세히 알아보세요.
정리 – 고객의 워크로드에 따라 보존 기간을 기본값인 3일보다 작게 유지하여 정리가 변경 테이블의 모든 변경 내용을 따라잡도록 하는 것이 좋습니다. 일반적으로 보존 기간을 낮게 유지하고 데이터베이스 크기를 추적하는 것이 좋습니다.
변경 내용이 변경 테이블에 채워질 경우에 대비해 제공된 SLA(서비스 수준 약정)가 없습니다. 하위 초 대기 시간도 지원되지 않습니다.
데이터 흐름
다음 그림에서는 변경 데이터 캡처의 주요 데이터 흐름을 보여 줍니다.
변경 데이터 캡처에 대한 변경 데이터 원본은 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를 사용하여 검색할 수 있습니다.
캡처 인스턴스와 관련된 모든 개체는 변경 데이터 캡처를 사용하도록 설정된 데이터베이스의 변경 데이터 캡처 스키마에 만들어집니다. 캡처 인스턴스 이름에 대한 요구 사항은 유효한 개체 이름이며 데이터베이스 캡처 인스턴스에서 고유하다는 것입니다. 기본적으로 이름은 원본 테이블의 스키마 name_table 이름>입니다<. 관련 변경 테이블의 이름은 캡처 인스턴스 이름에 _CT 를 추가하여 지정됩니다. 모든 변경 내용을 쿼리하는 데 사용되는 함수의 이름은 캡처 인스턴스 이름 앞에 fn_cdc_get_all_changes_ 를 추가하여 지정됩니다. 캡처 인스턴스가 순 변경을 지원하도록 구성된 경우 캡처 인스턴스 이름에 fn_cdc_get_net_changes_ 추가하여 net_changes 쿼리 함수도 만들어지고 이름이 지정됩니다.
변경 테이블
변경 데이터 캡처 변경 테이블의 처음 5개 열은 메타데이터 열입니다. 이러한 열은 기록된 변경 내용과 관련된 추가 정보를 제공합니다. 나머지 열은 원본 테이블에서 이름 및 유형(일반적으로 사용됨)으로 식별된 캡처된 열을 반영합니다. 이러한 열은 원본 테이블에서 수집된 캡처된 열을 보유합니다.
원본 테이블에 적용되는 각 삽입 또는 삭제 작업은 변경 테이블 내에 단일 행으로 나타납니다. 삽입 작업의 결과로 생성되는 행의 데이터 열에는 삽입 이후의 열 값이 포함되며 삭제 작업의 결과로 생성되는 행의 데이터 열에는 삭제 이전의 열 값이 포함됩니다. 업데이트 작업의 경우 하나의 행 항목에서 업데이트 이전의 열 값을 식별하고 다른 행 항목에서 업데이트 이후의 열 값을 식별해야 합니다.
변경 테이블의 각 행에는 변경 작업을 해석할 수 있도록 추가 메타데이터도 포함됩니다. 열 __$start_lsn은 변경 내용에 할당된 커밋 LSN(로그 시퀀스 번호)을 식별합니다. 커밋 LSN은 동일한 트랜잭션 내에서 커밋된 변경 내용을 식별할 뿐만 아니라 해당 트랜잭션을 정렬합니다. 열 __$seqval을 사용하여 동일한 트랜잭션 내에서 발생하는 추가 변경 내용을 정렬할 수 있습니다. 열 __$operation은 변경 내용과 관련된 1 = 삭제, 2 = 삽입, 3 = 업데이트(이전 이미지) 및 4 = 업데이트(이후 이미지) 작업을 기록합니다. 열 __$update_mask는 캡처된 각 열에 대해 1비트가 정의된 가변 비트 마스크입니다. 삽입 및 삭제 항목의 경우 업데이트 마스크에는 항상 모든 비트가 설정됩니다. 그러나 업데이트 행의 경우에는 변경된 열에 해당하는 비트만 설정됩니다.
유효성 간격
데이터베이스에 대한 변경 데이터 캡처 유효성 간격은 캡처 인스턴스에 변경 데이터를 사용할 수 있는 시간입니다. 유효성 간격은 데이터베이스 테이블에 대한 첫 번째 캡처 인스턴스가 만들어질 때 시작되어 현재 시간까지 지속됩니다.
데이터베이스
데이터를 주기적으로 체계적으로 정리하지 않으면 변경 테이블에 보관된 데이터는 관리되지 않고 증가합니다. 변경 데이터 캡처 정리 프로세스는 보존을 기반으로 하는 정리 정책을 적용합니다. 먼저 이 프로세스는 시간 제한에 따라 유효성 간격의 하위 엔드포인트로 이동한 다음 만료된 변경 테이블 항목을 제거합니다. 기본적으로 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 변경 내용이 관련 원본 테이블에 적용될 때 현재 캡처 인스턴스는 해당 셰이프를 계속 유지합니다. 그러나 새 열 구조를 반영하는 테이블에 대한 두 번째 캡처 인스턴스를 만들 수 있습니다. 이렇게 하면 캡처 프로세스에서 동일한 원본 테이블에 대한 변경 내용을 두 개의 개별 변경 테이블에 적용하여 서로 다른 두 열 구조를 만들 수 있습니다. 따라서 한 변경 테이블에서는 현재 작업 프로그램에 계속 공급을 수행하고 두 번째 변경 테이블에서는 새 열 데이터를 통합하려고 하는 개발 환경을 운영할 수 있습니다. 캡처 메커니즘에서 두 변경 테이블을 동시에 채우도록 하면 변경 데이터 손실 없이 한 테이블에서 다른 테이블로 전환할 수 있습니다. 이는 두 변경 데이터 캡처의 시간대가 겹칠 때 언제든지 발생할 수 있습니다. 전환이 발생하면 더 이상 사용되지 않는 캡처 인스턴스를 제거할 수 있습니다.
참고
단일 원본 테이블에 동시에 연결할 수 있는 최대 캡처 인스턴스 수는 두 개입니다.
로그 판독기 에이전트에 대한 관계
변경 데이터 캡처 프로세스에 대한 논리는 저장 프로시저 sp_replcmds에 포함되어 있습니다. 이는 sqlservr.exe의 일부로 작성되었으며 트랜잭션 로그에서 변경 내용을 수집하기 위해 트랜잭션 복제에 사용되는 내부 서버 함수입니다. SQL Server 및 Azure SQL Managed Instance에서 데이터베이스에 변경 데이터 캡처만 사용하도록 설정하는 경우 변경 데이터 캡처 SQL Server 에이전트 캡처 작업을 sp_replcmds를 호출하는 수단으로 만듭니다. 복제도 있는 경우에는 이러한 소비자 모두에 대한 변경 데이터 요구를 충족시키기 위해 트랜잭션 로그 판독기만 사용합니다. 이러한 전략을 사용하면 동일한 데이터베이스에 복제와 변경 데이터 캡처를 모두 사용하도록 설정하는 경우 로그 경합이 상당히 줄어듭니다.
변경 데이터 캡처를 사용하도록 설정된 데이터베이스의 복제 상태가 변경될 때마다 변경 데이터를 캡처하기 위한 이러한 두 운영 모드 간의 전환이 자동으로 발생합니다.
참고
SQL Server 및 Azure SQL Managed Instance에서 캡처 논리의 두 인스턴스에서는 모두 프로세스를 실행하기 위해 SQL Server 에이전트가 실행 중이어야 합니다.
캡처 프로세스의 주요 기능은 로그를 검색하고 변경 데이터 캡처 변경 테이블에 열 데이터 및 트랜잭션 관련 정보를 작성하는 것입니다. 이 프로세스가 채우는 모든 변경 데이터 캡처 변경 테이블에서 트랜잭션상 일관된 경계를 유지하기 위해 캡처 프로세스는 검색 주기마다 자체 트랜잭션을 열고 커밋합니다. 또한 이 프로세스는 변경 데이터 캡처를 사용하도록 테이블이 새로 설정되는 경우를 검색하고 로그의 변경 항목에 대해 현재 모니터링되는 테이블 집합에 해당 테이블을 자동으로 포함합니다. 마찬가지로 변경 데이터 캡처를 사용하지 않도록 설정하는 경우도 검색되어 변경 데이터에 대해 현재 모니터링되는 테이블 집합에서 해당 원본 테이블이 제거됩니다. 로그 섹션 처리가 완료되면 캡처 프로세스에서 서버 로그 잘림 논리에 신호를 보냅니다. 이 논리에서는 이 정보를 사용하여 자를 로그 항목을 식별합니다.
참고
데이터베이스에 변경 데이터 캡처가 설정된 경우 복구 모드가 단순 복구로 설정되어 있더라도 캡처 프로세스에서 캡처로 표시된 모든 변경 내용을 수집하기 전까지는 로그를 잘라낼 지점이 처리되지 않습니다. 캡처 프로세스가 실행되고 있지 않고 수집할 변경 내용이 있는 경우에는 CHECKPOINT를 실행해도 로그가 잘리지 않습니다.
캡처 프로세스는 추적된 테이블의 DDL 변경 내용에 대한 기록을 유지 관리하는 데에도 사용됩니다. 변경 데이터 캡처와 관련된 DDL 문은 변경 데이터 캡처를 사용하도록 설정된 데이터베이스 또는 테이블이 삭제되거나 변경 데이터 캡처를 사용하도록 설정된 테이블의 열이 추가, 수정 또는 삭제될 때마다 데이터베이스 트랜잭션 로그에 항목을 만듭니다. 이러한 로그 항목은 캡처 프로세스에 의해 처리됩니다. 그런 다음 캡처 프로세스는 cdc.ddl_history 테이블에 관련 DDL 이벤트를 게시합니다. 저장 프로시저 sys.sp_cdc_get_ddl_history를 사용하여 추적된 테이블에 영향을 주는 DDL 이벤트 관련 정보를 가져올 수 있습니다.
에이전트 작업
일반적으로는 두 개의 SQL Server 에이전트 작업이 변경 데이터 캡처를 사용하도록 설정된 데이터베이스와 연결됩니다. 그 중 하나는 데이터베이스 변경 테이블을 채우는 데 사용되는 작업이고, 다른 하나는 변경 테이블 정리 작업을 수행하는 작업입니다. 두 작업 모두 Transact-SQL 명령을 실행하는 단일 단계로 구성됩니다. 호출되는 Transact-SQL 명령은 작업의 논리를 구현하는 변경 데이터 캡처 정의된 저장 프로시저입니다. 데이터베이스의 첫 번째 테이블에 변경 데이터 캡처를 사용하도록 설정하는 경우 이러한 작업이 만들어집니다. 정리 작업은 항상 만들어집니다. 캡처 작업은 데이터베이스에 대해 정의된 트랜잭션 게시가 없는 경우에만 만들어집니다. 캡처 작업은 데이터베이스에 변경 데이터 캡처와 트랜잭션 복제를 둘 다 사용하도록 설정하고 데이터베이스에 더 이상 정의된 게시가 없어 트랜잭션 로그 판독기 작업이 제거되는 경우에도 만들어집니다.
캡처 작업과 정리 작업 모두 기본 매개 변수를 사용하여 만들어집니다. 캡처 작업은 즉시 시작됩니다. 이 작업은 계속 실행되며 검색 주기당(주기 사이에 5초의 대기 시간 있음) 최대 1000개의 트랜잭션을 처리합니다. 정리 작업은 매일 오전 2시에 실행됩니다. 4320분 또는 3일 동안 변경 테이블 항목을 유지하여 단일 delete 문으로 최대 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 에이전트 서비스가 NETWORK SERVICE 계정으로 실행 중인 경우 변경 데이터 캡처가 제대로 작동하지 않습니다. 이로 인해 오류 22832가 발생할 수 있습니다.
참고
Azure SQL Database에서 에이전트 작업은 캡처 및 정리를 자동으로 실행하는 스케줄러로 대체됩니다.
Azure SQL 데이터베이스에서 CDC 캡처 및 정리
Azure SQL Database에서 변경 데이터 캡처 스케줄러는 저장 프로시저를 호출하여 변경 데이터 캡처 테이블의 주기적 캡처 및 정리를 시작하는 SQL Server 에이전트를 대체합니다.
스케줄러는 안정성 또는 성능을 위해 외부 종속성 없이 SQL Database 내에서 캡처 및 정리를 자동으로 실행합니다. 사용자는 여전히 sp_cdc_scan 및 sp_cdc_cleanup_change_tables 프로시저를 사용하여 필요에 따라 캡처 및 정리를 수동으로 실행할 수 있습니다. CDC 캡처 작업은 20초마다 실행되고 정리 작업은 1시간마다 실행됩니다.
Azure SQL Database에는 변경 데이터 캡처를 모니터링하는 데 도움이 되는 두 가지 동적 관리 뷰인 sys.dm_cdc_log_scan_sessions 및 sys.dm_cdc_errors가 포함되어 있습니다.
데이터 정렬 차이점
변경 데이터 캡처를 위해 구성된 테이블의 열과 데이터베이스 간에 데이터 정렬이 다른 상황을 알고 있어야 합니다. CDC에서는 중간 스토리지를 사용하여 측면 테이블을 채웁니다. 테이블에 데이터베이스 데이터 정렬과는 다른 데이터 정렬이 있는 CHAR 또는 VARCHAR가 있고 이러한 열이 비 ASCII 문자(예: 더블바이트 DBCS 문자)를 저장할 경우, CDC는 변경된 데이터를 기본 테이블의 데이터에 일관되게 유지하지 못할 수 있습니다. 중간 스토리지 변수에 연결된 데이터 정렬이 있을 수 없기 때문입니다.
기본 테이블과 변경 캡처 데이터 간의 일관성을 유지하기 위해 다음 방법 중 하나를 고려합니다.
비 ASCII 데이터를 포함하는 열에 NCHAR 또는 NVARCHAR 데이터 형식을 사용합니다.
또는 열 및 데이터베이스에 동일한 데이터 정렬을 사용합니다.
예를 들어 SQL_Latin1_General_CP1_CI_AS 데이터 정렬을 사용하는 데이터베이스가 하나 있는 경우 다음 표를 고려합니다.
CREATE TABLE T1(
C1 INT PRIMARY KEY,
C2 VARCHAR(10) collate Chinese_PRC_CI_AI)
데이터 정렬이 다르기 때문에(Chinese_PRC_CI_AI) CDC는 열 C2에 대한 이진 데이터를 캡처하지 못할 수 있습니다. 이 문제를 방지하기 위해 NVARCHAR를 사용합니다.
CREATE TABLE T1(
C1 INT PRIMARY KEY,
C2 NVARCHAR(10) collate Chinese_PRC_CI_AI --Unicode data type, CDC works well with this data type
)
필요한 사용 권한
SQL Server 또는 Azure SQL Managed Instance에 대해 변경 데이터 캡처를 사용하려면 Sysadmin 권한이 필요합니다. Azure SQL Database에 대해 변경 데이터 캡처를 사용하려면 db_owner 역할이 필요합니다.
일반 지침
CDC(변경 데이터 캡처)가 제대로 작동하려면 CDC 스키마, 변경 테이블, CDC 시스템 저장 프로시저, 기본 cdc 사용자 권한(sys.database_principals) 또는 사용자 이름 바꾸기 cdc 와 같은 CDC 메타데이터를 수동으로 수정해서는 안 됩니다.
속성이 로 설정된 1sys.objects의 모든 개체 is_ms_shipped 는 수정하면 안 됩니다.
SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,is_ms_shipped
FROM sys.objects
WHERE is_ms_shipped= 1 AND SCHEMA_NAME(schema_id) = 'cdc'
알려진 제한 사항 및 문제
CDC(변경 데이터 캡처)의 알려진 제한 사항 및 문제 목록입니다.
Linux
CDC는 이제 Linux의 SQL Server 2017(CU18부터) 및 Linux의 SQL Server 2019에 대해 지원됩니다.
columnstore 인덱스
클러스터형 columnstore 인덱스가 있는 테이블에서는 변경 데이터 캡처를 사용하도록 설정할 수 없습니다. SQL Server 2016부터는 비클러스터형 columnstore 인덱스가 있는 테이블에서 이 기능을 사용하도록 설정할 수 있습니다.
변수를 사용하여 파티션 전환
데이터베이스 또는 CDC(변경 데이터 캡처)가 있는 테이블에서 파티션이 전환되는 변수를 사용하는 것은 문에서 ALTER TABLE ... SWITCH TO ... PARTITION ... 지원되지 않습니다. 자세히 알아보려면 파티션 전환 제한 사항을 참조하세요.
Azure SQL 데이터베이스의 CDC 가용성
CDC는 데이터베이스 계층 S3 이상에서만 사용하도록 설정할 수 있습니다. 하위 점수(기본, S0, S1, S2) Azure SQL 데이터베이스는 CDC에서 지원되지 않습니다.
서브코어 SLO에 CDC를 사용하도록 설정된 S3 이상의 데이터베이스 계층에서 Dbcopy는 현재 CDC 아티팩트가 유지되지만 나중에 CDC 아티팩트가 제거될 수 있습니다.
Azure SQL 데이터베이스에서 사용자 지정 캡처 및 정리
Azure SQL 데이터베이스에서 CDC에 대한 캡처 빈도 및 정리 프로세스를 구성할 수 없습니다. 캡처 및 정리는 스케줄러에 의해 자동으로 실행됩니다.
계산 열
CDC는 계산 열이 지속형으로 정의되어 있더라도 계산 열에 대한 값을 지원하지 않습니다. 캡처 인스턴스에 포함된 계산 열은 항상 NULL 값을 갖습니다. 이 동작은 의도된 것이며 버그가 아닙니다.
PITR(특정 시점 복원)
데이터베이스에서 CDC를 Microsoft Azure Active Directory(Azure AD) 사용자로 사용하도록 설정하면 PITR(지정 시간 복원)을 하위 코어 SLO로 복원할 수 없습니다. 데이터베이스를 원본 이상의 SLO와 동일하게 복원한 다음 필요한 경우 CDC를 사용하지 않도록 설정하는 것이 좋습니다.
Microsoft Azure AD(Azure Active Directory)
Azure SQL Database에서 Microsoft Azure Active Directory(Azure AD) 사용자로 데이터베이스를 만들고 CDC(변경 데이터 캡처)를 사용하도록 설정하는 경우 SQL 사용자(예: sysadmin 역할)는 CDC 아티팩트 사용 안 함/변경을 수행할 수 없습니다. 그러나 다른 Azure AD 사용자는 같은 데이터베이스에서 CDC를 사용하거나 사용하지 않을 수 있습니다.
마찬가지로 Azure SQL Database를 SQL 사용자로 만드는 경우 변경 데이터 캡처를 Azure AD 사용자로 사용/사용하지 않도록 설정하면 작동하지 않습니다.
적극적인 로그 잘림
Azure SQL Database 또는 SQL Server CDC(변경 데이터 캡처)를 사용하도록 설정하는 동안 ADR(가속 데이터베이스 복구)의 적극적인 로그 잘림 기능이 비활성화되어 있습니다. CDC 검사가 데이터베이스 트랜잭션 로그에 액세스하기 때문입니다. 활성 트랜잭션은 트랜잭션 커밋 및 CDC 검사가 catch되거나 트랜잭션이 중단될 때까지 트랜잭션 로그 잘림을 계속 유지합니다. 이로 인해 트랜잭션 로그가 평소보다 더 많이 채워질 수 있으며 트랜잭션 로그가 채워지지 않도록 모니터링해야 합니다.
VARCHAR 및 VARBINARY에 대한 ALTER COLUMN 이후 CDC 실패
CDC 사용 테이블에 있는 열의 데이터 형식이 에서 TEXTVARCHARIMAGE 또는 로 VARBINARY 변경되고 기존 행이 오프행 값으로 업데이트되는 경우 업데이트 후 CDC 검사로 인해 오류가 발생합니다.
Microsoft Azure Active Directory(Azure AD)을 사용하여 만든 복원된 Azure SQL DB에서 CDC 사용이 실패함
Azure SQL Database에서 Microsoft Azure Active Directory(Azure AD) 사용자로 데이터베이스를 만들고 CDC를 사용하도록 설정하지 않은 경우 CDC를 사용하도록 설정한 다음, 데이터베이스를 복원하고 복원된 데이터베이스에서 CDC를 사용하도록 설정하면 CDC를 사용하도록 설정하지 못합니다.
이 문제를 해결하려면 다음 단계를 따릅니다.
- 서버의 Azure AD 관리자로 로그인
- 데이터베이스에서 ALTER AUTHORIZATION 명령을 실행합니다.
ALTER AUTHORIZATION ON DATABASE::[<restored_db_name>] TO [<azuread_admin_login_name>];
EXEC sys.sp_cdc_enable_db
사용자 지정 스키마 또는 이름이 지정된 cdc 사용자가 데이터베이스에 존재하는 경우 CDC를 사용하도록 설정하려고 하면 실패합니다.
데이터베이스에서 CDC를 사용하도록 설정하면 라는 cdc새 스키마와 사용자가 만들어집니다. 따라서 시스템 사용을 위해 예약되어 있으므로 사용자 지정 스키마 또는 라는 cdc사용자를 수동으로 만드는 것은 권장되지 않습니다.
CDC와 관련이 없는 데이터베이스에서 사용자 지정 스키마 또는 라는 cdc 사용자를 수동으로 정의한 경우 시스템 저장 프로시저 sys.sp_cdc_enable_db 가 아래 오류 메시지와 함께 데이터베이스에서 CDC를 사용하도록 설정하지 못합니다.
'cdc'라는 데이터베이스
<database_name>사용자 또는 'cdc'라는 스키마가 현재 데이터베이스에 이미 있으므로 변경 데이터 캡처에 데이터베이스를 사용하도록 설정할 수 없습니다. 이러한 개체는 변경 데이터 캡처에만 필요합니다. 사용자 또는 스키마를 삭제하거나 이름을 바꾼 후 작업을 다시 시도하십시오.
이 문제를 해결하려면 다음을 수행합니다.
- 빈
cdc스키마와cdc사용자를 수동으로 삭제합니다. 그런 다음, 데이터베이스에서 CDC를 성공적으로 사용하도록 설정할 수 있습니다.
데이터 계층 가져오기/내보내기 및 추출/게시 작업을 사용하여 데이터베이스 가져오기
CDC 사용 SQL 데이터베이스의 경우 SqlPackage, SSDT 또는 기타 SQL 도구를 사용하여 가져오기/내보내기 또는 추출/게시 cdc 하는 경우 스키마와 사용자가 새 데이터베이스에서 제외됩니다. 가져오기/내보내기 및 추출/배포 작업에 포함되지 않은 추가 CDC 개체에는 sys.objects에서 로 is_ms_shipped=1 표시된 테이블이 포함됩니다.
CDC를 사용하도록 설정하지 않고 새 데이터베이스를 가져오거나 설정하기 위해 가져오기/내보내기 및 추출/배포 작업에서도 제외되는 사용자 지정 스키마 또는 데이터베이스에 명명 cdc 된 사용자를 정의한 경우에도 입니다.
참고 항목
데이터 변경 내용 추적(SQL Server)
변경 데이터 캡처 사용 및 사용 안 함(SQL Server)
변경 데이터 작업(SQL Server)
변경 데이터 캡처 관리 및 모니터링(SQL Server)
임시 테이블