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

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

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

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

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

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

• 개발 시간이 단축됩니다. SQL Server에서 기능을 사용할 수 있으므로 사용자 지정 솔루션을 개발할 필요가 없습니다.

• 스키마 변경이 필요하지 않습니다. 사용자 테이블에 열을 추가할 수 없는 경우 열을 추가하거나, 트리거를 추가하거나, 삭제된 행을 추적하거나 변경 내용 추적 정보를 저장하기 위한 사이드 테이블을 만들 필요가 없습니다.

• 기본 제공 정리 메커니즘이 있습니다. 변경 내용 추적을 위한 정리는 백그라운드에서 자동으로 수행됩니다. 사이드 테이블에 저장된 데이터에 대한 사용자 지정 정리가 필요하지 않습니다.

• 변경 정보를 얻기 위한 함수가 제공됩니다.

• DML 작업에 대한 오버헤드가 적습니다. 동기 변경 내용 추적에는 항상 어느 정도의 오버헤드가 발생합니다. 하지만 변경 내용 추적을 사용하면 오버헤드를 최소화하는 데 도움이 될 수 있습니다. 이 경우 오버헤드는 대체 솔루션, 특히 트리거를 사용해야 하는 솔루션에 비해 더 낮은 경우가 많습니다.

• 변경 내용 추적이 커밋된 트랜잭션을 기반으로 수행됨 변경 순서는 트랜잭션 커밋 시간을 기준으로 합니다. 따라서 장기간 실행되고 겹치는 트랜잭션이 있을 때 신뢰성 있는 결과를 얻을 수 있습니다. 타임스탬프 값을 사용하는 사용자 지정 솔루션은 이러한 시나리오를 처리하도록 설계해야 합니다.

• 구성 및 관리에 사용할 수 있는 표준 도구를 사용할 수 있습니다. SQL Server는 표준 DDL 문, SQL Server Management Studio, 카탈로그 뷰 및 보안 권한을 제공합니다.

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

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

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

변경 데이터 캡처

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

다음 예시와 같이 사용자 테이블에 대한 변경 내용은 해당 변경 테이블에 캡처됩니다. 이러한 변경 테이블은 시간 경과에 따른 변경 내역에 대한 기록 보기를 제공합니다. SQL Server에서 제공하는 변경 데이터 캡처 기능을 사용하면 변경 데이터를 쉽고 체계적으로 사용할 수 있도록 설정할 수 있습니다.

보안 모델

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

구성 및 관리

데이터베이스에 대해 변경 데이터 캡처를 사용하도록 설정하거나 사용하지 않도록 설정하려면 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 역할의 멤버이거나 데이터베이스 database db_owner 역할의 멤버여야 합니다.

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

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

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

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

변경 데이터 캡처를 사용하도록 설정된 원본 테이블을 변경하는 DDL 작업

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

변경 데이터 캡처를 위한 데이터 형식 고려 사항

모든 기본 열 유형은 변경 데이터 캡처에서 지원됩니다. 다음 테이블에는 여러 열 유형에 대한 동작 및 제한 사항이 나와 있습니다.

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

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

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

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

데이터베이스 미러링

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

1. 미러에서 SQL Server 에이전트가 실행 중인지 확인합니다.

2. 보안 주체가 미러로 장애 조치된 후 미러에서 캡처 작업 및 정리 작업을 만듭니다. 작업을 만들려면 sys.sp_cdc_add_job (Transact-SQL) 저장 프로시저를 사용합니다.

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

트랜잭션 복제

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

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

참고 사항

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

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

SQL Server는 다음 논리를 사용하여 데이터베이스를 복원하거나 연결한 후에도 변경 데이터 캡처를 사용하도록 설정할지 여부를 결정합니다.

• 데이터베이스가 동일한 데이터베이스 이름으로 동일한 서버에 복원된 경우 변경 데이터 캡처를 사용하도록 설정된 상태로 유지됩니다.

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

  변경 데이터 캡처를 유지하려면 데이터베이스를 복원할 때 KEEP_CDC 옵션을 사용합니다. 이 옵션에 대한 자세한 내용은 RESTORE를 참조하세요.

• 데이터베이스가 분리되어 동일한 서버 또는 다른 서버에 연결된 경우 변경 데이터 캡처는 사용하도록 설정된 상태로 유지됩니다.

• 표준 또는 엔터프라이즈 이외의 버전에 KEEP_CDC 옵션을 사용하여 데이터베이스를 연결하거나 복원하는 경우 변경 데이터 캡처에 SQL Server 표준 또는 엔터프라이즈 버전이 필요하므로 작업이 차단됩니다. 오류 메시지 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를 사용하여 복원되거나 연결된 데이터베이스에서 변경 데이터 캡처를 제거할 수 있습니다.

변경 내용 추적

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

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

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

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

변경 내용 추적 및 ADO.NET용 동기화 서비스에 대한 자세한 내용은 다음 링크를 참조하세요.

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

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

• Microsoft Sync Framework 개발자 센터

  동기화 프레임워크 및 동기화 서비스에 대한 전체 설명서를 제공합니다.

다음 단계

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

참고 항목

• 변경 데이터 캡처 함수(Transact-SQL)
• 변경 내용 추적 함수(Transact-SQL)
• 변경 데이터 캡처 저장 프로시저(Transact-SQL)
• 변경 데이터 캡처 테이블(Transact-SQL)
• 데이터 캡처 관련 동적 관리 뷰 변경 (Transact-SQL)