변경 데이터 작업
적용 대상: 
SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance
변경 데이터는 TVF(테이블 반환 함수)를 통해 변경 데이터 캡처 소비자에게 제공됩니다. 이러한 함수의 모든 쿼리에는 반환된 결과 집합을 개발할 때 고려할 LSN(로그 시퀀스 번호)의 범위를 정의하는 두 매개 변수가 필요합니다. 간격을 제한하는 상위 및 하위 LSN 값은 모두 간격 내에 포함된 것으로 간주됩니다.
TVF를 쿼리할 때 사용할 적절한 LSN 값을 결정하는 데 도움이 되는 몇 가지 함수가 제공됩니다. sys.fn_cdc_get_min_lsn 함수는 캡처 인스턴스 유효 간격과 연관된 가장 작은 LSN을 반환합니다. 유효 간격은 캡처 인스턴스에 대해 현재 변경 데이터를 사용할 수 있는 시간 간격입니다. sys.fn_cdc_get_max_lsn 함수는 유효 간격에서 가장 큰 LSN을 반환합니다. 기존 타임라인에 LSN 값을 배치하는 데 도움이 되는 sys.fn_cdc_map_time_to_lsn 및 sys.fn_cdc_map_lsn_to_time 함수를 사용할 수 있습니다.
변경 데이터 캡처는 닫힌 쿼리 간격을 사용하기 때문에 연속된 쿼리 창에서 변경 내용이 중복되지 않도록 하기 위해 다음 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_ ...
net 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")됩니다.
소스 테이블을 사용하도록 설정된 경우 매개 변수 <이(가) 1로 설정되면 cdc.fn_cdc_get_net_changes_>capture_instance@supports_net_changes 함수가 생성됩니다.
참고 항목
이 옵션은 원본 테이블에 정의된 기본 키가 있거나 @index_name 매개 변수를 사용하여 고유 인덱스를 식별한 경우에만 지원됩니다.
netchanges 함수는 수정된 소스 테이블 행당 하나의 변경 내용을 반환합니다. 지정된 간격 동안 해당 행에 대해 둘 이상의 변경 내용이 기록되는 경우 열 값에는 행의 마지막 내용이 반영됩니다. 대상 환경을 업데이트하는 데 필요한 작업을 올바르게 식별하려면 TVF는 간격 동안 행의 초기 작업과 행의 최종 작업을 모두 고려해야 합니다. 행 필터 옵션 'all'을 지정한 경우 net changes 쿼리에서 반환하는 작업은 삽입, 삭제 또는 업데이트(새 값) 중 하나입니다. 이 옵션은 집계 마스크 계산과 관련된 비용이 있기 때문에 항상 업데이트 마스크를 null로 반환합니다. 행의 모든 변경 내용을 반영하는 집계 마스크가 필요한 경우 '모두 마스크 포함' 옵션을 사용합니다. 다운스트림 처리에서 삽입과 업데이트를 구분할 필요가 없는 경우 '모두 병합' 옵션을 사용합니다. 이 경우 작업 값은 두 가지 값만 사용되며, 삭제의 경우 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 경계를 결정합니다. 그런 다음 이 값을 사용하여 쿼리 함수 cdc.fn_cdc_get_all_changes_@from_lsncapture_instance@to_lsn 또는 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 값을 통해 경계가 지정된 슬라이딩 윈도우를 사용하여 변경 데이터를 정기적으로 요청하는 것입니다. 이러한 클래스의 소비자에 대해 변경 데이터 캡처는 변경 데이터 캡처 쿼리 함수에 대한 사용자 지정 래퍼 함수를 만드는 스크립트를 생성하는 sys.sp_cdc_generate_wrapper_function 저장 프로시저를 제공합니다. 이러한 사용자 지정 래퍼를 사용하면 쿼리 간격을 날짜/시간 쌍으로 표현할 수 있습니다.
저장 프로시저의 호출 옵션을 사용하면 호출자가 액세스 권한이 있는 모든 캡처 인스턴스 또는 지정된 캡처 인스턴스에 대해서만 래퍼를 생성할 수 있습니다. 지원되는 옵션에는 캡처 간격의 최상위 끝점을 열어야 하는지 닫아야 하는지, 사용 가능한 캡처 열 중 어떤 열을 결과 집합에 포함해야 하는지, 포함된 열 중 어떤 열에 업데이트 플래그가 있어야 하는지 지정하는 기능도 포함됩니다. 이 프로시저는 캡처 인스턴스 이름에서 파생 가능한 생성된 함수 이름과 래퍼 저장 프로시저의 create 문이라는 두 개의 열이 포함된 결과 집합을 반환합니다. 모든 변경 내용 쿼리를 래핑하는 함수는 항상 생성됩니다. 캡처 인스턴스를 만들 때 @supports_net_changes 매개 변수를 설정한 경우 순 변경 내용 함수를 래핑하는 함수도 생성됩니다.
스크립트 생성 저장 프로시저를 호출하여 래퍼 저장 프로시저에 대한 create 문을 생성하고 결과 생성 스크립트를 실행하여 함수를 생성하는 것은 애플리케이션 디자이너의 책임입니다. 이 작업은 캡처 인스턴스가 생성될 때 자동으로 수행되지 않습니다.
날짜/시간 래퍼는 사용자가 소유하며 호출자의 기본 스키마에서 생성되지 않습니다. 생성된 함수는 대부분의 사용자에게 수정 없이 적합합니다. 그러나 함수를 만들기 전에 언제든지 생성된 스크립트에 추가 사용자 지정을 적용할 수 있습니다.
모든 변경 내용 쿼리를 래핑하는 함수의 이름은 fn_all_changes_ 뒤에 캡처 인스턴스 이름을 붙입니다. 순 변경 내용 래퍼에 사용되는 접두사는 fn_net_changes_입니다. 두 함수는 모두 연결된 변경 데이터 캡처 TVF와 마찬가지로 3개의 인수를 사용합니다. 그러나 래퍼에 대한 쿼리 간격은 두 개의 LSN 값 대신 두 개의 날짜/시간 값으로 제한됩니다. 두 함수 세트의 @row_filter_option 매개 변수는 동일합니다.
생성된 래퍼 함수는 변경 데이터 캡처 타임라인을 체계적으로 탐색하기 위해 다음과 같은 규칙을 지원하며, 이전 간격의 @end_time 매개 변수가 후속 간격의 @start_time 매개 변수로 사용됩니다. 래퍼 함수는 날짜/시간 값을 LSN 값에 매핑하고 이 규칙을 따를 경우 데이터가 손실되거나 반복되지 않도록 처리합니다.
지정된 쿼리 창에서 닫힌 상한이나 열린 상한을 지원하는 래퍼를 생성할 수 있습니다. 즉, 호출자는 커밋 시간이 추출 간격의 상한과 같은 항목을 간격 내에 포함할지 여부를 지정할 수 있습니다. 기본적으로 상한이 포함됩니다.
생성된 쿼리 TVF는 @from_lsn 값 또는 @to_lsn 값에 대해 null 값을 제공하면 실패하지만, 날짜/시간 래퍼 함수는 날짜/시간 래퍼가 모든 현재 변경 내용을 반환하도록 허용하기 위해 null을 사용합니다. 즉, 날짜/시간 래퍼에 쿼리 창의 하단 끝점으로 null이 전달되면 쿼리 TVF에 적용되는 기본 SELECT 문에서 캡처 인스턴스 유효성 간격의 하단 끝점이 사용됩니다. 마찬가지로, 쿼리 창의 최상위 끝점으로 null이 전달되면 쿼리 TVF에서 선택할 때 캡처 인스턴스 유효성 간격의 최상위 끝점이 사용됩니다.
래퍼 함수가 반환하는 결과 집합에는 요청된 모든 열과 그 뒤에 행과 연관된 연산을 식별하기 위해 한두 문자로 리코딩된 연산 열이 포함됩니다. 업데이트 플래그는 요청 시 @update_flag_list 매개 변수에 지정된 순서대로 작업 코드 뒤에 비트 열로 표시됩니다. 생성된 날짜/시간 래퍼를 사용자 지정하기 위한 호출 옵션에 대한 자세한 내용은 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 간격 경계 둘 다에 전달됩니다. 이 함수는 캡처 인스턴스의 유효한 간격 내에서 원본 행이 수정될 때마다 행을 하나씩 반환합니다.
날짜/시간 래퍼 함수를 사용하여 캡처 인스턴스 간 전환
변경 데이터 캡처는 하나의 추적된 원본 테이블당 최대 두 개의 캡처 인스턴스를 지원합니다. 이 기능의 주요 용도는 소스 테이블에 대한 데이터 정의 언어(DDL) 변경으로 추적에 사용할 수 있는 열 집합이 확장될 때 여러 캡처 인스턴스 간에 전환을 수용하는 것입니다. 새 캡처 인스턴스로 전환할 때 기본 쿼리 함수의 이름 변경으로부터 상위 애플리케이션 수준을 보호하는 한 가지 방법은 래퍼 함수를 사용하여 기본 호출을 래핑하는 것입니다. 그런 다음 래퍼 함수의 이름이 그대로 유지되는지 확인합니다. 전환이 발생하면 이전 래퍼 함수를 삭제하고 새 쿼리 함수를 참조하는 동일한 이름의 새 래퍼 함수를 만들 수 있습니다. 먼저 생성된 스크립트를 수정하여 동일한 이름의 래퍼 함수를 만들면 더 높은 애플리케이션 계층에 영향을 주지 않고 새 캡처 인스턴스로 전환할 수 있습니다.