데이터 변경 내용 추적(SQL Server)

아티클
09/26/2023

적용 대상:SQL Server Azure SQL DatabaseAzure SQL Managed Instance

SQL Server는 데이터베이스의 데이터 변경 내용을 추적하는 두 가지 기능인 변경 데이터 캡처 및 변경 내용 추적을 제공합니다. 이러한 기능은 데이터베이스의 사용자 테이블에 적용된 DML 변경 내용(삽입, 업데이트 및 삭제 작업)을 애플리케이션에서 확인할 수 있게 해줍니다. 변경 데이터 캡처 및 변경 내용 추적은 동일한 데이터베이스에서 사용하도록 설정할 수 있습니다. 특별한 고려 사항은 필요하지 않습니다. 변경 데이터 캡처 및 변경 내용 추적을 지원하는 SQL Server 버전은 SQL Server 2022의 버전 및 지원되는 기능을 참조하세요.

변경 데이터 캡처 또는 변경 내용 추적 사용의 이점

데이터베이스에서 변경된 데이터를 쿼리하는 기능은 일부 애플리케이션의 효율성을 위해 중요한 요구 사항입니다. 일반적으로 데이터 변경 내용을 확인하려면 애플리케이션 개발자는 트리거, 타임스탬프 열 및 추가 테이블을 조합하여 애플리케이션에서 사용자 지정 추적 메서드를 구현해야 합니다. 이러한 애플리케이션을 만들려면 일반적으로 구현해야 하는 작업이 많고, 스키마 업데이트가 발생하며, 종종 고성능 오버헤드가 발생합니다.

애플리케이션에서 변경 데이터 캡처 또는 변경 내용 추적을 사용하여 사용자 지정 솔루션을 개발하는 대신 데이터베이스의 변경 내용을 추적하면 다음과 같은 이점이 있습니다.

개발 시간이 줄어듭니다. SQL Server에서 기능을 사용할 수 있으므로 사용자 지정 솔루션을 개발할 필요가 없습니다.
스키마 변경은 필요하지 않습니다. 열을 사용자 테이블에 추가할 수 없는 경우 삭제된 행을 추적하거나 변경 내용 추적 정보를 저장할 열을 추가하거나 트리거를 추가하거나 사이드 테이블을 만들 필요가 없습니다.
기본 제공 클린up 메커니즘이 있습니다. 변경 내용 추적에 대한 정리는 백그라운드에서 자동으로 수행됩니다. 사이드 테이블에 저장된 데이터에 대한 사용자 지정 클린업은 필요하지 않습니다.
변경 정보를 얻기 위한 함수가 제공됩니다.
DML 작업에 대한 오버헤드가 낮습니다. 동기 변경 내용 추적에는 항상 어느 정도의 오버헤드가 발생합니다. 그러나 변경 내용 추적을 사용하면 오버헤드를 최소화하는 데 도움이 될 수 있습니다. 이 경우 오버헤드는 대체 솔루션, 특히 트리거를 사용해야 하는 솔루션에 비해 더 낮은 경우가 많습니다.
변경 내용 추적이 커밋된 트랜잭션을 기반으로 수행됨 변경 순서는 트랜잭션 커밋 시간을 기반으로 합니다. 이렇게 하면 장기 실행 및 겹치는 트랜잭션이 있을 때 신뢰할 수 있는 결과를 얻을 수 있습니다. 타임스탬프 값을 사용하는 사용자 지정 솔루션은 이러한 시나리오를 처리하도록 설계되어야 합니다.
표준 도구는 구성하고 관리하는 데 사용할 수 있습니다. SQL Server는 표준 DDL 문, SQL Server Management Studio, 카탈로그 뷰 및 보안 권한을 제공합니다.

변경 데이터 캡처와 변경 내용 추적 간의 기능 차이점

다음 표에서는 변경 데이터 캡처와 변경 내용 추적 간의 기능 차이점을 나열합니다. 변경 데이터 캡처의 추적 메커니즘에는 DML 작업 후에 변경 내용을 사용할 수 있도록 트랜잭션 로그의 변경 내용에 대한 비동기 캡처가 포함됩니다. 변경 내용 추적의 추적 메커니즘에는 DML 작업과 함께 동기 변경 내용 추적이 포함되어 있으므로 변경 정보를 즉시 사용할 수 있습니다.

기능	변경 데이터 캡처	Change tracking
추적된 변경 내용
DML 변경 내용	예	예
추적된 정보
기록 데이터	예	아니요
열이 변경되었는지 여부	예	예
DML 형식	예	예

변경 데이터 캡처

변경 데이터 캡처는 DML 변경이 이루어졌다는 사실과 변경된 실제 데이터를 모두 캡처하여 사용자 테이블에 대한 기록 변경 정보를 제공합니다. 변경 내용은 트랜잭션 로그를 읽고 시스템에 미치는 영향이 낮은 비동기 프로세스를 사용하여 캡처됩니다.

다음 그림과 같이 사용자 테이블에 적용된 변경 내용은 해당 변경 테이블에서 캡처됩니다. 이러한 변경 테이블은 시간에 따른 변경 내용의 기록 보기를 제공합니다. SQL Server에서 제공하는 변경 데이터 캡처 함수를 사용하면 변경 데이터를 쉽고 체계적으로 사용할 수 있습니다.

Diagram showing the concept of change data capture.

보안 모델

이 섹션에서는 변경 데이터 캡처 보안 모델에 대해 설명합니다.

구성 및 관리

데이터베이스에 대해 변경 데이터 캡처를 사용하거나 사용하지 않도록 설정하려면 sys.sp_cdc_enable_db(Transact-SQL) 또는 sys.sp_cdc_disable_db(Transact-SQL)의 호출자가 고정 서버 sysadmin 역할의 멤버여야 합니다. 테이블 수준에서 변경 데이터 캡처를 사용하도록 설정하고 사용하지 않도록 설정하려면 sys.sp_cdc_enable_table(Transact-SQL) 및 sys.sp_cdc_disable_table(Transact-SQL) 호출자가 sysadmin 역할의 멤버이거나 데이터베이스 데이터베이스 db_owner 역할의 멤버여야 합니다.

서버 sysadmin 역할의 멤버와 database db_owner 역할의 멤버만 저장 프로시저를 사용하여 변경 데이터 캡처 작업의 관리를 지원할 수 있습니다.

열거형 및 메타데이터 쿼리 변경

캡처 인스턴스와 연결된 변경 데이터에 액세스하려면 연결된 원본 테이블의 캡처된 모든 열에 대한 SELECT 액세스 권한을 사용자에게 부여해야 합니다. 또한 캡처 인스턴스를 만들 때 게이팅 역할을 지정하는 경우 호출자는 지정된 게이팅 역할의 멤버여야 하며 변경 데이터 캡처 스키마(cdc)에는 게이팅 역할에 대한 SELECT 액세스 권한이 있어야 합니다.

메타데이터에 액세스하기 위한 다른 일반적인 변경 데이터 캡처 함수는 공용 역할을 통해 모든 데이터베이스 사용자가 액세스할 수 있지만 반환된 메타데이터에 대한 액세스는 일반적으로 기본 원본 테이블에 대한 SELECT 액세스를 사용하고 정의된 게이팅 역할의 멤버 자격으로 제어됩니다.

데이터 캡처 사용 원본 테이블을 변경하는 DDL 작업

변경 데이터 캡처를 위해 테이블을 사용하도록 설정한 경우 DDL 작업은 고정 서버 역할 sysadmin의 멤버, db_owner 데이터베이스 역할의 멤버 또는 데이터베이스 역할 db_ddladmin 멤버만 테이블에 적용할 수 있습니다. 테이블에서 DDL 작업을 수행하기 위한 명시적 권한이 있는 사용자는 이러한 작업을 시도하면 오류 22914를 받게 됩니다.

변경 데이터 캡처에 대한 데이터 형식 고려 사항

모든 기본 열 형식은 변경 데이터 캡처에서 지원됩니다. 다음 표에서는 여러 열 형식의 동작 및 제한 사항을 나열합니다.

열 형식	변경 테이블에서 캡처된 변경 내용	제한 사항
스파스 열	예	열 집합을 사용하는 경우 변경 내용 캡처를 지원하지 않습니다.
계산 열	아니요	계산 열에 대한 변경 내용은 추적되지 않습니다. 열은 적절한 형식의 변경 테이블에 표시되지만 값은 NULL입니다.
XML	예	개별 XML 요소에 대한 변경 내용은 추적되지 않습니다.
타임스탬프	예	변경 테이블의 데이터 형식이 이진으로 변환됩니다.
BLOB 데이터 형식	예	BLOB 열의 이전 이미지는 열 자체가 변경된 경우에만 저장됩니다.

데이터 캡처 및 기타 SQL Server 기능 변경

이 섹션에서는 다음 기능이 변경 데이터 캡처와 상호 작용하는 방법을 설명합니다.

데이터베이스 미러링
트랜잭션 복제
데이터베이스 복원 또는 연결

데이터베이스 미러링

변경 데이터 캡처가 설정된 데이터베이스를 미러링할 수 있습니다. 미러 캡처 및 클린 자동으로 수행되도록 하려면 다음 단계를 수행합니다.

미러 SQL Server 에이전트 실행 중인지 확인합니다.
보안 주체가 미러 장애 조치(failover)한 후 미러 캡처 작업 및 클린up 작업을 만듭니다. 작업을 만들려면 저장 프로시저 sys.sp_cdc_add_job(Transact-SQL)를 사용합니다.

데이터베이스 미러 대한 자세한 내용은 데이터베이스 미러링(SQL Server)을 참조하세요.

트랜잭션 복제

변경 데이터 캡처 및 트랜잭션 복제는 동일한 데이터베이스에 함께 존재할 수 있지만 두 기능이 모두 설정된 경우 변경 테이블 채우기가 다르게 처리됩니다. 변경 데이터 캡처 및 트랜잭션 복제는 항상 동일한 sp_replcmds프로시저를 사용하여 트랜잭션 로그에서 변경 내용을 읽습니다. 변경 데이터 캡처를 자체적으로 사용하도록 설정하면 SQL Server 에이전트 작업이 호출됩니다sp_replcmds. 동일한 데이터베이스에서 두 기능을 모두 사용하도록 설정하면 로그 판독기 에이전트가 호출합니다 sp_replcmds. 이 에이전트는 변경 테이블과 distribution 데이터베이스 테이블을 모두 채웁니다. 자세한 내용은 복제 로그 판독기 에이전트를 참조하세요.

데이터베이스에서 변경 데이터 캡처를 사용하도록 설정하고 AdventureWorks2022 두 개의 테이블을 캡처할 수 있는 시나리오를 고려합니다. 변경 테이블을 채우기 위해 캡처 작업이 호출됩니다 sp_replcmds. 트랜잭션 복제본(replica) 데이터베이스가 활성화되고 게시가 만들어집니다. 이제 데이터베이스에 대한 로그 판독기 에이전트가 만들어지고 캡처 작업이 삭제됩니다. 로그 판독기 에이전트는 변경 테이블에 커밋된 마지막 로그 시퀀스 번호에서 로그를 계속 검색합니다. 이렇게 하면 변경 테이블의 데이터 일관성이 보장됩니다. 이 데이터베이스에서 트랜잭션 복제본(replica)tion을 사용하지 않도록 설정하면 로그 판독기 에이전트가 제거되고 캡처 작업이 다시 만들어집니다.

참고 항목

로그 판독기 에이전트가 변경 데이터 캡처와 트랜잭션 복제본(replica) 모두에 사용되는 경우 복제본(replica)ted 변경 내용은 먼저 데이터베이스에 distribution 기록됩니다. 그런 다음 캡처된 변경 내용이 변경 테이블에 기록됩니다. 두 작업은 함께 커밋됩니다. 데이터베이스에 서면으로 distribution 대기 시간이 있는 경우 변경 테이블에 변경 내용이 표시되기 전에 해당 대기 시간이 발생합니다.

변경 데이터 캡처를 사용하도록 설정된 데이터베이스 복원 또는 연결

SQL Server는 다음 논리를 사용하여 데이터베이스를 복원하거나 연결한 후 변경 데이터 캡처를 다시 사용할 수 있는지 기본 확인합니다.

데이터베이스가 동일한 데이터베이스 이름으로 동일한 서버로 복원되는 경우 데이터 캡처를 다시 사용하도록 기본 변경합니다.
데이터베이스가 다른 서버로 복원되는 경우 기본적으로 변경 데이터 캡처를 사용하지 않도록 설정되고 모든 관련 메타데이터가 삭제됩니다.

변경 데이터 캡처를 유지하려면 데이터베이스를 복원할 때 KEEP_CDC 옵션을 사용합니다. 이 옵션에 대한 자세한 내용은 RESTORE를 참조하세요.
데이터베이스가 분리되어 동일한 서버 또는 다른 서버에 연결된 경우 데이터 캡처를 다시 사용하도록 기본 변경합니다.

Standard 또는 Enterprise 이외의 버전에 대한 옵션을 사용하여 데이터베이스를 연결하거나 복원 KEEP_CDC 하는 경우 변경 데이터 캡처에 SQL Server Standard 또는 Enterprise 버전이 필요하기 때문에 작업이 차단됩니다. 오류 메시지 932가 표시됩니다.

SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.

sys.sp_cdc_disable_db 사용하여 복원되거나 연결된 데이터베이스에서 변경 데이터 캡처를 제거할 수 있습니다.

Change tracking

변경 내용 추적은 테이블의 행이 변경되었다는 사실을 캡처하지만 변경된 데이터는 캡처하지 않습니다. 따라서 사용자 테이블에서 직접 가져온 최신 행 데이터를 사용하여 변경한 행을 애플리케이션이 확인할 수 있습니다. 그러므로 변경 내용 추적은 시간에 따른 변경 기록을 표시하는 데 있어서는 변경 데이터 캡처와 비교해 볼 때 보다 제한적입니다. 그러나 기록 정보가 필요하지 않은 애플리케이션의 경우 변경된 데이터가 캡처되지 않기 때문에 스토리지 오버헤드가 훨씬 적습니다. 동기 추적 메커니즘은 변경 내용을 추적하는 데 사용됩니다. 이는 DML 작업에 대한 오버헤드를 최소화하도록 설계되었습니다.

다음 그림에서는 변경 내용 추적을 사용하여 이점을 얻을 수 있는 동기화 시나리오를 보여 줍니다. 이 시나리오에서는 애플리케이션에 다음 정보가 필요합니다. 테이블이 마지막으로 동기화된 이후 변경된 테이블의 모든 행과 현재 행 데이터만 필요합니다. 동기 메커니즘을 사용하여 변경 내용을 추적하므로 애플리케이션은 양방향 동기화를 수행하고 발생할 수 있는 충돌을 안정적으로 검색할 수 있습니다.

Diagram showing the concept of change tracking.

ADO.NET 대한 변경 내용 추적 및 동기화 서비스

ADO.NET 위한 동기화 서비스를 사용하면 데이터베이스 간의 동기화를 통해 오프라인 및 공동 작업 시나리오를 대상으로 하는 애플리케이션을 빌드할 수 있는 직관적이고 유연한 API를 제공합니다. ADO.NET 용 동기화 서비스는 변경 내용을 동기화하는 API를 제공하지만 실제로 서버 또는 피어 데이터베이스의 변경 내용을 추적하지는 않습니다. 사용자 지정 변경 내용 추적 시스템을 만들 수 있지만 일반적으로 상당한 복잡성과 성능 오버헤드가 발생합니다. 서버 또는 피어 데이터베이스의 변경 내용을 추적하려면 쉽게 구성하고 고성능 추적을 제공하므로 SQL Server에서 변경 내용 추적을 사용하는 것이 좋습니다.

변경 내용 추적 및 ADO.NET 대한 동기화 서비스에 대한 자세한 내용은 다음 링크를 사용합니다.

변경 내용 추적 정보(SQL Server)

변경 내용 추적에 대해 설명하고, 변경 내용 추적의 작동 방식에 대한 개략적인 개요를 제공하며, 변경 내용 추적이 다른 SQL Server 데이터베이스 엔진 기능과 상호 작용하는 방법을 설명합니다.
Microsoft Sync Framework 개발자 센터

Sync Framework 및 Sync Services에 대한 전체 설명서를 제공합니다.

다음 단계

Task	아티클
변경 데이터 캡처에 대한 개요를 제공합니다.	변경 데이터 캡처 정보(SQL Server)
데이터베이스 또는 테이블에서 변경 데이터 캡처를 사용하거나 사용하지 않도록 설정하는 방법을 설명합니다.	변경 데이터 캡처 설정 및 해제(SQL Server)
변경 데이터 캡처를 관리하고 모니터링하는 방법을 설명합니다.	변경 데이터 캡처 관리 및 모니터링(SQL Server)
변경 데이터 캡처 소비자에 사용할 수 있는 변경 데이터를 사용하는 방법을 설명합니다. 이 문서에서는 LSN 경계, 쿼리 함수 및 쿼리 함수 시나리오의 유효성 검사에 대해 설명합니다.	변경 데이터 작업(SQL Server)
변경 캡처의 개요를 제공합니다.	변경 내용 추적 정보(SQL Server)
데이터베이스 또는 테이블에서 변경 내용 추적을 사용하거나 사용하지 않도록 설정하는 방법을 설명합니다.	변경 내용 추적 설정 및 해제(SQL Server)
변경 내용 추적을 관리하고, 보안을 구성하고, 변경 내용 추적을 사용할 때 스토리지 및 성능에 미치는 영향을 결정하는 방법을 설명합니다.	변경 내용 추적 관리(SQL Server)
변경 내용 추적을 사용하는 애플리케이션이 추적된 변경 내용을 가져오고, 이러한 변경 내용을 다른 데이터 저장소에 적용하고, 원본 데이터베이스를 업데이트하는 방법을 설명합니다. 또한 이 문서에서는 장애 조치(failover)가 발생하고 백업에서 데이터베이스를 복원해야 할 때 수행하는 역할 변경 내용 추적에 대해서도 설명합니다.	변경 내용 추적 사용(SQL Server)