변경 데이터 작업

  • 아티클

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

TVF(테이블 반환 함수)를 통해 데이터 캡처 소비자를 변경하는 데 변경 데이터를 사용할 수 있습니다. 이러한 함수의 모든 쿼리에는 반환된 결과 집합을 개발할 때 고려할 LSN(로그 시퀀스 번호)의 범위를 정의하는 두 매개 변수가 필요합니다. 간격을 바인딩한 LSN 상한 값과 하위 LSN 값은 모두 간격 내에 포함된 것으로 간주됩니다.

TVF 쿼리에 사용할 적절한 LSN 값을 결정하는 데 도움이 되는 몇 가지 함수가 제공됩니다. 함수 sys.fn_cdc_get_min_lsn 캡처 인스턴스 유효성 간격과 연결된 가장 작은 LSN을 반환합니다. 유효 간격은 현재 캡처 인스턴스에 변경 데이터를 사용할 수 있는 시간 간격입니다. 함수 sys.fn_cdc_get_max_lsn 유효성 간격에서 가장 큰 LSN을 반환합니다. sys.fn_cdc_map_time_to_lsn 및 sys.fn_cdc_map_lsn_to_time 함수는 기존 타임라인 LSN 값을 배치하는 데 도움이 됩니다.

변경 데이터 캡처는 닫힌 쿼리 간격을 사용하므로 연속 쿼리 창에서 변경 내용이 중복되지 않도록 순서대로 다음 LSN 값을 생성해야 하는 경우가 있습니다. sys.fn_cdc_increment_lsn 및 sys.fn_cdc_decrement_lsn 함수는 LSN 값에 대한 증분 조정이 필요한 경우에 유용합니다.

LSN 경계 유효성 검사

사용하기 전에 TVF 쿼리에 사용할 LSN 경계의 유효성을 검사하는 것이 좋습니다. 캡처 인스턴스의 유효성 간격을 벗어나는 Null 엔드포인트 또는 엔드포인트는 변경 데이터 캡처 TVF에서 오류를 강제로 반환합니다.

예를 들어 쿼리 간격을 정의하는 데 사용되는 매개 변수가 유효하지 않거나 범위를 벗어났거나 행 필터 옵션이 잘못된 경우 모든 변경 내용에 대한 쿼리에 대해 다음 오류가 반환됩니다.

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ...

순 변경 쿼리에 대해 반환되는 해당 오류는 다음과 같습니다.

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ...

참고 항목

메시지 313의 메시지는 오해의 소지가 있으며 오류에 대한 실제 원인을 나타내지 않습니다. 이 어색한 사용은 TVF 내에서 명시적 오류를 발생하지 못하기 때문에 발생합니다. 그럼에도 불구하고 정확하지 않은 경우 인식할 수 있는 오류를 반환하는 값은 단순히 빈 결과를 반환하는 것이 바람직하다고 간주되었습니다. 빈 결과 집합은 변경 내용을 반환하지 않는 유효한 쿼리와 구별할 수 없습니다.

권한 부여 실패는 다음과 같이 모든 변경 내용을 쿼리할 때 오류를 반환합니다.

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'fn_cdc_get_all_changes_...', database 'MyDB', schema 'cdc'.

순 변경 내용을 쿼리할 때도 마찬가지입니다.

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object fn_cdc_get_net_changes_...', database 'MyDB', schema 'cdc'.

알려진 이러한 TVF 오류를 차단하고 오류에 대한 보다 의미 있는 정보를 반환하는 방법을 보려면 Enumerate Net Changes Using TRY CATCH 템플릿을 참조하십시오.

참고 항목

SQL Server Management Studio에서 변경 데이터 캡처 템플릿을 찾으려면 보기 메뉴에서 템플릿 탐색기를 클릭하고 SQL Server 템플릿을 확장한 다음 변경 데이터 캡처 폴더를 확장합니다.

쿼리 함수

추적 중인 원본 테이블의 특성과 캡처 인스턴스가 구성된 방식에 따라 변경 데이터를 쿼리하기 위한 하나 또는 두 개의 TVF가 생성됩니다.

  • 함수 cdc.fn_cdc_get_all_changes_<capture_instance> 지정된 간격에 대해 발생한 모든 변경 내용을 반환합니다. 이 함수는 항상 생성됩니다. 항목은 항상 먼저 변경의 트랜잭션 커밋 LSN을 기준으로 정렬된 다음 해당 트랜잭션 내에서 변경 내용을 시퀀스하는 값으로 반환됩니다. 선택한 행 필터 옵션에 따라 업데이트 시 최종 행이 반환(행 필터 옵션 "all")되거나 업데이트 시 새 값과 이전 값이 모두 반환(행 필터 옵션 "all update old")됩니다.

  • cdc.fn_cdc_get_net_changes_capture_instance> 함수<는 원본 테이블을 사용할 때 매개 변수 @supports_net_changes 가 1로 설정되면 생성됩니다.

    참고 항목

    이 옵션은 원본 테이블에 정의된 기본 키가 있거나 @index_name 매개 변수를 사용하여 고유 인덱스를 식별한 경우에만 지원됩니다.

    netchanges 함수는 수정된 원본 테이블 행당 하나의 변경 사항을 반환합니다 . 지정된 간격 동안 해당 행에 대해 둘 이상의 변경 내용이 기록되는 경우 열 값에는 행의 마지막 내용이 반영됩니다. 대상 환경을 업데이트하는 데 필요한 작업을 올바르게 식별하려면 TVF는 간격 동안 행에 대한 초기 작업과 행의 최종 작업을 모두 고려해야 합니다. 행 필터 옵션 'all'을 지정한 경우 net changes 쿼리에서 반환하는 작업은 삽입, 삭제 또는 업데이트(새 값) 중 하나입니다. 집계 마스크 계산과 관련된 비용이 있으므로 이 옵션은 항상 업데이트 마스크를 null로 반환합니다. 행의 모든 변경 내용을 반영하는 집계 마스크가 필요한 경우 '모두 마스크' 옵션을 사용합니다. 다운스트림 처리에서 삽입 및 업데이트를 구분할 필요가 없는 경우 'all with merge' 옵션을 사용합니다. 이 경우 작업 값은 삭제의 경우 1, 삽입 또는 업데이트일 수 있는 작업의 경우 5의 두 값만 사용합니다. 이 옵션은 파생 작업이 삽입 또는 업데이트여야 하는지 여부를 결정하는 데 필요한 추가 처리를 제거하고 이 구분이 필요하지 않은 경우 쿼리의 성능을 향상시킬 수 있습니다.

쿼리 함수에서 반환되는 업데이트 마스크는 변경 데이터 행에서 변경된 모든 열을 식별하는 압축 표현입니다. 일반적으로 이 정보는 캡처된 열의 작은 하위 집합에만 필요합니다. 함수는 애플리케이션에서 직접 사용할 수 있는 형식으로 마스크에서 정보를 추출하는 데 도움을 줄 수 있습니다. sys.fn_cdc_get_column_ordinal 함수는 지정한 캡처 인스턴스에 대해 명명된 열의 서수 위치를 반환하는 반면 sys.fn_cdc_is_bit_set 함수는 제공된 마스크에 있는 비트의 패리티를 함수 호출에서 전달된 서수를 기반으로 반환합니다. 이러한 두 함수를 함께 사용하면 업데이트 마스크의 정보를 효율적으로 추출하고 변경 데이터 요청과 함께 반환할 수 있습니다. 이러한 함수를 사용하는 방법에 대한 데모는 마스크와 함께 모두 사용하여 순 변경 내용 열거 템플릿을 참조하세요.

쿼리 함수 시나리오

다음 섹션에서는 cdc.fn_cdc_get_all_changes_capture_instance 및 cdc.fn_cdc_get_net_changes_<capture_instance> 쿼리 함수를 사용하여 변경 데이터 캡처 데이터를 쿼리하는 일반적인 시나리오를<> 설명합니다.

캡처 인스턴스 유효성 간격 내의 모든 변경 내용 쿼리

변경 데이터에 대한 가장 간단한 요청은 캡처 인스턴스의 유효성 간격 내에 있는 모든 현재 변경 데이터를 반환하는 요청입니다. 이 요청을 수행하려면 먼저 유효성 간격의 LSN 하한 및 상한 경계를 결정합니다. 그런 다음 이러한 값을 사용하여 매개 변수 @from_lsn@to_lsn 를 식별하고 쿼리 함수 cdc.fn_cdc_get_all_changes_<capture_instance> 또는 cdc.fn_cdc_get_net_changes_<capture_instance> 전달합니다. 하한을 가져오려면 sys.fn_cdc_get_min_lsn 함수를 사용하고 상한을 가져오려면 sys.fn_cdc_get_max_lsn 함수를 사용합니다. 쿼리 함수 cdc.fn_cdc_get_all_changes_<capture_instance> 사용하여 현재 유효한 모든 변경 내용을 쿼리하려면 샘플 코드의 유효한 범위에 대한 모든 변경 내용을 열거하는 템플릿을 참조하세요. 함수 cdc.fn_cdc_get_net_changes_ 사용하는 유사한 예제는 유효한 범위의 순 변경 내용 열거형을 참조하세요<capture_instance>.

마지막 변경 내용 집합 이후의 모든 새 변경 내용 쿼리

일반적인 애플리케이션의 경우 변경 데이터 쿼리는 지속적인 프로세스로, 마지막 요청 이후 발생한 모든 변경 내용에 대해 주기적으로 요청합니다. 이러한 쿼리의 경우 함수 sys.fn_cdc_increment_lsn 사용하여 이전 쿼리의 상한에서 현재 쿼리의 하한을 파생시킬 수 있습니다. 이 메서드는 쿼리 간격이 항상 두 끝점이 간격에 포함된 닫힌 간격으로 처리되기 때문에 행이 반복되지 않도록 합니다. 그런 다음, 함수 sys.fn_cdc_get_max_lsn 사용하여 새 요청 간격에 대한 하이 엔드포인트를 가져옵니다. 마지막 요청 이후 모든 변경 내용을 가져오기 위해 쿼리 창을 체계적으로 이동하려면 샘플 코드에 대한 이전 요청 이후의 모든 변경 내용 열거 템플릿을 참조하세요.

지금까지 모든 새 변경 내용 쿼리

쿼리 함수에서 반환된 변경 내용에 적용되는 일반적인 제약 조건은 현재 날짜와 시간까지 이전 요청 간에 발생한 변경 내용만 포함하는 것입니다. 이러한 쿼리의 경우 이전 요청에서 하한을 확인하기 위해 사용된 @from_lsn 값에 sys.fn_cdc_increment_lsn 함수를 적용합니다. 시간 간격의 상한은 특정 시점으로 표현되므로 쿼리 함수에서 사용하려면 먼저 LSN 값으로 변환해야 합니다. datetime 값을 해당 LSN 값으로 변환할 수 있기 때문에 캡처 프로세스가 지정된 상한까지 커밋된 모든 변경 내용을 처리했는지 확인해야 합니다. 이는 모든 적격 변경 내용이 변경 테이블에 전파되었는지 확인하는 데 필요합니다. 이 작업을 수행하는 한 가지 방법은 데이터베이스 변경 테이블에 대해 기록된 현재 최대 커밋 lsn이 요청 간격의 원하는 종료 시간을 초과하는지 확인하기 위해 주기적으로 검사 대기 루프를 구성하는 것입니다.

지연 루프가 캡처 프로세스가 모든 관련 로그 항목을 이미 처리했는지 확인한 후 함수 sys.fn_cdc_map_time_to_lsn 사용하여 LSN 값으로 표현된 새 하이 엔드포인트를 확인합니다. 지정된 시간 동안 커밋된 모든 항목을 검색하려면 함수 sys.fn_cdc_map_time_to_lsn 호출하고 '보다 작거나 같음' 옵션을 사용합니다.

참고 항목

작업을 하지 않은 기간에는 캡처 프로세스에서 지정된 커밋 시간까지 변경 내용을 처리했다는 사실을 표시하기 위해 cdc.lsn_time_mapping 테이블에 더미 항목이 추가됩니다. 이렇게 하면 프로세스에 대한 최근 변경 내용이 없을 때 캡처 프로세스가 뒤쳐진 것처럼 보이지 않습니다.

지금까지 모든 변경 내용을 열거하는 템플릿은 이전 전략을 사용하여 변경 데이터를 쿼리하는 방법을 보여 줍니다.

모든 변경 결과 집합에 커밋 시간 추가

데이터베이스 변경 테이블에 연결된 항목이 있는 각 트랜잭션의 커밋 시간은 테이블 cdc.lsn_time_mapping 사용할 수 있습니다. 모든 변경 내용에 대한 요청에서 반환한 __$start_lsn 값을 cdc.lsn_time_mapping 테이블 항목의 start_lsn 값과 조인하면 변경 데이터와 함께 tran_end_time을 반환하여 변경 데이터에 원본의 트랜잭션 커밋 시간을 표시할 수 있습니다. 모든 변경 내용에 커밋 시간 추가 결과 집합 템플릿은 이 조인을 수행하는 방법을 보여 줍니다.

동일한 트랜잭션의 다른 데이터와 변경 데이터 조인

경우에 따라 변경 데이터를 원본에서 커밋할 때 트랜잭션에 대해 수집된 다른 정보와 조인하는 것이 유용합니다. 테이블 cdc.lsn_time_mapping tran_begin_lsn 열은 이러한 조인을 수행하는 데 필요한 정보를 제공합니다. 원본의 업데이트가 발생하면 시스템 동적 뷰 sys.dm_tran_database_transactions database_transaction_begin_lsn 값을 변경 데이터와 조인할 다른 정보와 함께 저장해야 합니다. 함수 fn_convertnumericlsntobinary 사용하여 database_transaction_begin_lsn 및 tran_begin_lsn 값을 비교합니다. 이 함수를 만드는 코드는 Create Function 템플릿 fn_convertnumericlsntobinary에서 사용할 수 있습니다. 지정된 tran_begin_lsn 있는 모든 변경 내용을 반환하는 템플릿은 조인에 영향을 주는 방법을 보여 줍니다.

Datetime 래퍼 함수를 사용하여 쿼리

변경 데이터를 쿼리하는 일반적인 애플리케이션 시나리오는 datetime 값을 통해 경계가 지정된 슬라이딩 윈도우를 사용하여 변경 데이터를 정기적으로 요청하는 것입니다. 이러한 클래스의 소비자에 대해 변경 데이터 캡처는 변경 데이터 캡처 쿼리 함수에 대한 사용자 지정 래퍼 함수를 만드는 스크립트를 생성하는 sys.sp_cdc_generate_wrapper_function 저장 프로시저를 제공합니다. 이러한 사용자 지정 래퍼를 사용하면 쿼리 간격을 날짜/시간 쌍으로 표현할 수 있습니다.

저장 프로시저에 대한 호출 옵션을 사용하면 호출자가 액세스할 수 있는 모든 캡처 인스턴스 또는 지정된 캡처 인스턴스에 대해서만 래퍼를 생성할 수 있습니다. 지원되는 옵션에는 캡처 간격의 하이 엔드포인트를 열어야 하는지 닫을지 여부, 결과 집합에 포함해야 하는 사용 가능한 캡처된 열 중 어떤 열에 연결된 업데이트 플래그가 있어야 하는지를 지정하는 기능도 포함됩니다. 프로시저는 캡처 인스턴스 이름에서 파생 가능한 생성된 함수 이름과 래퍼 저장 프로시저에 대한 create 문이라는 두 개의 열이 있는 결과 집합을 반환합니다. 모든 변경 내용 쿼리를 래핑하는 함수는 항상 생성됩니다. 캡처 인스턴스를 만들 때 @supports_net_changes 매개 변수를 설정한 경우 순 변경 내용 함수를 래핑하는 함수도 생성됩니다.

애플리케이션 디자이너는 스크립트 생성 저장 프로시저를 호출하여 래퍼 저장 프로시저에 대한 create 문을 생성하고 결과 만들기 스크립트를 실행하여 함수를 만듭니다. 캡처 인스턴스를 만들 때 자동으로 발생하지 않습니다.

Datetime 래퍼는 사용자가 소유하며 호출자의 기본 스키마에 만들어지지 않습니다. 생성된 함수는 대부분의 사용자에게 수정 없이 적합합니다. 그러나 함수를 만들기 전에 생성된 스크립트에 추가 사용자 지정을 항상 적용할 수 있습니다.

모든 변경 내용 쿼리를 래핑하는 함수의 이름 뒤에 캡처 인스턴스 이름이 fn_all_changes_. 순 변경 래퍼에 사용되는 접두사는 fn_net_changes_. 두 함수는 모두 연결된 변경 데이터 캡처 TVF와 마찬가지로 3개의 인수를 사용합니다. 그러나 래퍼에 대한 쿼리 간격은 두 개의 LSN 값이 아닌 두 개의 datetime 값으로 바인딩됩니다. @row_filter_option 두 함수 집합에 대한 매개 변수는 동일합니다.

생성된 래퍼 함수는 변경 데이터 캡처 타임라인 체계적으로 걷기 위한 다음 규칙을 지원합니다. 이전 간격의 매개 변수가 후속 간격의 매개 변수로 @start_time 사용될 것으로 예상됩니다@end_time. 래퍼 함수는 날짜/시간 값을 LSN 값에 매핑하고 이 규칙을 따르는 경우 데이터가 손실되거나 반복되지 않도록 합니다.

지정된 쿼리 창에서 닫힌 상한이나 열린 상한을 지원하는 래퍼를 생성할 수 있습니다. 즉, 호출자는 커밋 시간이 추출 간격의 상한과 같은 항목을 간격 내에 포함할지 여부를 지정할 수 있습니다. 기본적으로 상한이 포함됩니다.

값 또는 @to_lsn 값에 대해 @from_lsn null 값을 제공하면 생성된 쿼리 TVF가 실패하지만 datetime 래퍼 함수는 null을 사용하여 datetime 래퍼가 모든 현재 변경 내용을 반환할 수 있도록 합니다. 즉, null이 쿼리 창의 하위 엔드포인트로 datetime 래퍼로 전달되는 경우 캡처 인스턴스 유효성 간격의 하위 엔드포인트는 쿼리 TVF에 적용되는 기본 SELECT 문에 사용됩니다. 마찬가지로 null이 쿼리 창의 하이 엔드포인트로 전달되는 경우 쿼리 TVF에서 선택할 때 캡처 인스턴스 유효성 간격의 상위 엔드포인트가 사용됩니다.

래퍼 함수에서 반환된 결과 집합에는 요청된 모든 열과 작업 열이 포함되며, 행과 연결된 작업을 식별하기 위해 하나 또는 두 개의 문자로 다시 코딩됩니다. 업데이트 플래그는 요청 시 @update_flag_list 매개 변수에 지정된 순서대로 작업 코드 뒤에 비트 열로 표시됩니다. 생성된 datetime 래퍼를 사용자 지정하기 위한 호출 옵션에 대한 자세한 내용은 sys.sp_cdc_generate_wrapper_function(Transact-SQL)를 참조하세요.

Instantiate a Wrapper TVF With Update Flag 템플릿에서는 생성된 래퍼 함수를 사용자 지정하여 지정된 열에 대한 업데이트 플래그를 순 변경 내용 쿼리에서 반환하는 결과 집합에 추가하는 방법을 보여 줍니다. Instantiate CDC Wrapper TVFs for a Schema 템플릿에서는 지정된 데이터베이스 스키마에서 원본 테이블에 대해 생성된 모든 캡처 인스턴스에 대해 쿼리 TVF의 datetime 래퍼를 인스턴스화하는 방법을 보여 줍니다.

datetime 래퍼를 사용하여 변경 데이터를 쿼리하는 예를 보려면 Get Net Changes Using Wrapper With Update Flags 템플릿을 참조하십시오. 이 템플릿은 래퍼가 업데이트 플래그를 반환하도록 구성된 경우 래퍼 함수를 사용하여 순 변경 내용을 쿼리하는 방법을 보여 줍니다. 기본 쿼리 함수가 업데이트 시 null이 아닌 업데이트 마스크를 반환하려면 행 필터 옵션 '모두 마스크'가 필요합니다. NULL 값은 기본 LSN 기반 쿼리를 수행할 때 캡처 인스턴스에 대한 유효성 간격의 하위 끝점 및 상위 끝점을 사용하도록 함수에 알리기 위해 하한 및 상한 datetime 간격 경계 둘 다에 전달됩니다. 이 함수는 캡처 인스턴스의 유효한 간격 내에서 원본 행이 수정될 때마다 행을 하나씩 반환합니다.

Datetime 래퍼 함수를 사용하여 캡처 인스턴스 간 전환

변경 데이터 캡처는 하나의 추적된 원본 테이블당 최대 두 개의 캡처 인스턴스를 지원합니다. 이 기능의 주요 용도는 DDL(데이터 정의 언어)이 원본 테이블로 변경될 때 추적에 사용할 수 있는 열 집합을 확장할 때 여러 캡처 인스턴스 간의 전환을 수용하는 것입니다. 새 캡처 인스턴스로 전환할 때 기본 쿼리 함수의 이름 변경으로부터 더 높은 애플리케이션 수준을 보호하는 한 가지 방법은 래퍼 함수를 사용하여 기본 호출을 래핑하는 것입니다. 그런 다음 래퍼 함수의 이름이 그대로 유지되는지 확인합니다. 스위치가 발생하면 이전 래퍼 함수를 삭제하고 새 쿼리 함수를 참조하는 이름이 같은 새 래퍼 함수를 만들 수 있습니다. 먼저 생성된 스크립트를 수정하여 동일한 이름의 래퍼 함수를 만들면 더 높은 애플리케이션 계층에 영향을 주지 않고 새 캡처 인스턴스로 전환할 수 있습니다.

관련 콘텐츠