SQL Server 2014는 데이터베이스의 데이터 변경 내용을 추적하는 두 가지 기능인 변경 데이터 캡처 및 변경 내용 추적을 제공합니다. 이러한 기능은 데이터베이스의 사용자 테이블에 적용된 DML 변경 내용(삽입, 업데이트 및 삭제 작업)을 애플리케이션에서 확인할 수 있게 해줍니다. 변경 데이터 캡처와 변경 내용 추적은 동일한 데이터베이스에서 사용하도록 설정할 수 있으며, 특별한 고려 사항이 필요하지 않습니다. 변경 데이터 캡처 및 변경 내용 추적을 지원하는 SQL Server 버전은 SQL Server 2014 버전에서 지원하는 기능을 참조하세요.
변경 데이터 캡처 또는 변경 내용 추적 사용의 이점
데이터베이스에서 변경된 데이터를 쿼리하는 기능은 일부 애플리케이션의 효율성을 위한 중요한 요구 사항입니다. 일반적으로 애플리케이션 개발자는 데이터 변경을 확인하기 위해 애플리케이션에서 트리거, 타임스탬프 열 및 추가 테이블의 조합으로 사용자 지정 추적 방법을 구현해야 합니다. 이러한 애플리케이션을 만들기 위해서는 많은 구현 작업과 스키마 업데이트가 필요하며 높은 성능 오버헤드가 수반되는 경우도 많습니다.
사용자 지정 솔루션을 개발하는 대신 애플리케이션에서 변경 데이터 캡처 또는 변경 내용 추적을 사용하여 데이터베이스의 변경 사항을 추적하면 다음과 같은 이점이 있습니다.
개발 시간이 줄어듭니다. SQL Server 2014에서 기능을 사용할 수 있으므로 사용자 지정 솔루션을 개발할 필요가 없습니다.
스키마 변경은 필요하지 않습니다. 열을 사용자 테이블에 추가할 수 없는 경우 삭제된 행을 추적하거나 변경 내용 추적 정보를 저장할 열을 추가하거나 트리거를 추가하거나 사이드 테이블을 만들 필요가 없습니다.
기본 제공 정리 장치가 있습니다. 변경 내용 추적을 위한 정리는 백그라운드에서 자동으로 수행됩니다. 사이드 테이블에 저장된 데이터에 대한 사용자 지정 정리는 필요하지 않습니다.
변경 정보를 얻기 위한 함수가 제공됩니다.
DML 작업에 대한 오버헤드가 낮습니다. 동기 변경 내용 추적에는 항상 어느 정도의 오버헤드가 발생합니다. 하지만 변경 내용 추적을 사용하면 오버헤드를 최소화하는 데 도움이 될 수 있습니다. 오버헤드는 대체 솔루션, 특히 사용 트리거가 필요한 솔루션을 사용하는 것보다 적은 경우가 많습니다.
변경 내용 추적이 커밋된 트랜잭션을 기반으로 수행됨 변경 순서는 트랜잭션 커밋 시간을 기준으로 합니다. 따라서 장기간 실행되고 겹치는 트랜잭션이 있을 때 신뢰성 있는 결과를 얻을 수 있습니다. 값을 사용하는
timestamp사용자 지정 솔루션은 이러한 시나리오를 처리하도록 특별히 설계되어야 합니다.구성 및 관리에 사용할 수 있는 표준 도구를 사용할 수 있습니다. SQL Server 2014는 표준 DDL 문, SQL Server Management Studio, 카탈로그 뷰 및 보안 권한을 제공합니다.
변경 데이터 캡처와 변경 내용 추적 간의 기능 차이점
다음 표에는 변경 데이터 캡처와 변경 내용 추적의 기능 차이점이 나와 있습니다. 변경 데이터 캡처의 추적 메커니즘은 트랜잭션 로그에서 변경 내용을 비동기적으로 캡처하여 DML 작업 후에 변경 내용을 사용할 수 있도록 합니다. 변경 내용 추적의 추적 메커니즘에는 DML 작업과 함께 동기 변경 내용 추적이 포함되어 있으므로 변경 정보를 즉시 사용할 수 있습니다.
| 특징 | 변경 데이터 캡처 | 변경 사항 추적 |
|---|---|---|
| 추적된 변경 내용 | ||
| DML 변경 사항 | 예 | 예 |
| 추적 정보 | ||
| 기록 데이터 | 예 | 아니오 |
| 열이 변경되었는지 여부 | 예 | 예 |
| 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 멤버로 제한됩니다.
열거형 및 메타데이터 쿼리 변경
캡처 인스턴스와 연결된 변경 데이터에 액세스하려면 연결된 원본 테이블의 모든 캡처된 열에 대한 선택 액세스 권한을 사용자에게 부여해야 합니다. 또한 캡처 인스턴스를 만들 때 게이팅 역할을 지정하는 경우 호출자도 지정된 게이팅 역할의 멤버여야 합니다. 메타데이터에 액세스하기 위한 다른 일반적인 변경 데이터 캡처 함수는 공용 역할을 통해 모든 데이터베이스 사용자가 액세스할 수 있지만 반환된 메타데이터에 대한 액세스는 일반적으로 기본 원본 테이블에 대한 선택 액세스를 사용하고 정의된 게이팅 역할의 멤버 자격으로 제어됩니다.
데이터 캡처 사용 원본 테이블을 변경하는 DDL 작업
변경 데이터 캡처를 위해 테이블을 사용하도록 설정한 경우 DDL 작업은 고정 서버 역할 sysadmin의 멤버, database role db_owner멤버 또는 database role db_ddladmin해당 멤버에 의해서만 테이블에 적용할 수 있습니다. 테이블에서 DDL 작업을 수행하기 위한 명시적 권한이 있는 사용자는 이러한 작업을 시도하면 오류 22914를 받게 됩니다.
변경 데이터 캡처에 대한 데이터 형식 고려 사항
모든 기본 열 유형은 변경 데이터 캡처에서 지원됩니다. 다음 테이블에는 여러 열 유형에 대한 동작 및 제한 사항이 나와 있습니다.
| 열 유형 | 변경 테이블에서 캡처되는 변경 내용 | 제한점 |
|---|---|---|
| 드문드문한 열 | 예 | 열 집합을 사용하는 경우 변경 내용 캡처를 지원하지 않습니다. |
| 계산된 열 | 아니오 | 계산 열에 대한 변경 내용은 추적되지 않습니다. 열은 적절한 형식의 변경 테이블에 표시되지만 값은 NULL입니다. |
| XML | 예 | 개별 XML 요소에 대한 변경 내용은 추적되지 않습니다. |
| 시간표시 | 예 | 변경 테이블의 데이터 형식은 이진으로 변환됩니다. |
| BLOB 데이터 형식 | 예 | BLOB 열의 이전 이미지는 열 자체가 변경된 경우에만 저장됩니다. |
변경 데이터 캡처 및 기타 SQL Server 기능
이 섹션에서는 다음 기능이 변경 데이터 캡처와 상호 작용하는 방식에 대해 설명합니다.
데이터베이스 미러링
트랜잭션 복제
데이터베이스 복원 또는 연결
데이터베이스 미러링
변경 데이터 캡처가 설정된 데이터베이스를 미러링할 수 있습니다. 미러에서 캡처 및 정리가 자동으로 수행되도록 하려면 다음 단계를 따릅니다.
미러에서 SQL Server 에이전트가 실행 중인지 확인합니다.
주 서버가 미러로 장애 조치된 후, 미러에서 캡처 작업과 정리 작업을 생성합니다. 작업을 만들려면 sys.sp_cdc_add_job (Transact-SQL) 저장 프로시저를 사용합니다.
데이터베이스 미러링에 대한 자세한 내용은 데이터베이스 미러링(SQL Server)을 참조하세요.
트랜잭션 복제
변경 데이터 캡처 및 트랜잭션 복제는 동일한 데이터베이스에 함께 존재할 수 있지만 두 기능이 모두 설정된 경우 변경 테이블 채우기가 다르게 처리됩니다. 변경 데이터 캡처 및 트랜잭션 복제는 항상 동일한 sp_replcmds프로시저를 사용하여 트랜잭션 로그에서 변경 내용을 읽습니다. 변경 데이터 캡처가 자체적으로 사용하도록 설정된 경우 SQL Server 에이전트 작업은 sp_replcmds를 호출합니다. 동일한 데이터베이스에서 두 기능을 모두 사용하도록 설정된 경우 로그 리더 에이전트가 sp_replcmds를 호출합니다. 이 에이전트는 변경 테이블과 배포 데이터베이스 테이블을 모두 채웁니다. 자세한 내용은 복제 로그 판독기 에이전트를 참조하세요.
AdventureWorks2012 데이터베이스에서 변경 데이터 캡처를 사용하도록 설정하고 두 테이블을 캡처할 수 있는 시나리오를 고려합니다. 변경 테이블을 채우기 위해 캡처 작업은 sp_replcmds를 호출합니다. 데이터베이스가 트랜잭션 복제에 사용하도록 설정되고 게시물이 만들어집니다. 이제 데이터베이스에 대한 로그 판독기 에이전트가 만들어지고 캡처 작업이 삭제됩니다. 로그 판독기 에이전트는 변경 테이블에 커밋된 마지막 로그 시퀀스 번호부터 로그를 계속 스캔합니다. 이렇게 하면 변경 테이블의 데이터 일관성이 보장됩니다. 이 데이터베이스에서 트랜잭션 복제를 사용하지 않도록 설정되어 있으면 로그 판독기 에이전트가 제거되고 캡처 작업이 다시 만들어집니다.
비고
로그 판독기 에이전트가 변경 데이터 캡처와 트랜잭션 복제에 모두 사용되는 경우, 복제된 변경 내용은 먼저 배포 데이터베이스에 기록됩니다. 그런 다음 캡처된 변경 내용이 변경 테이블에 기록됩니다. 두 개의 작업이 함께 커밋됩니다. 배포 데이터베이스에 쓰는 데 대기 시간이 있는 경우 변경 내용이 변경 테이블에 표시되기 전에 해당 대기 시간이 적용됩니다.
변경 데이터 캡처를 사용하도록 설정된 데이터베이스 복원 또는 연결
SQL Server는 다음 논리를 사용하여 데이터베이스를 복원하거나 연결한 후에도 변경 데이터 캡처를 사용하도록 설정할지 여부를 결정합니다.
데이터베이스가 동일한 데이터베이스 이름으로 동일한 서버에 복원된 경우 변경 데이터 캡처를 사용하도록 설정된 상태로 유지됩니다.
데이터베이스가 다른 서버로 복원된 경우에는 기본적으로 변경 데이터 캡처를 사용하지 않도록 설정되며 모든 관련 메타데이터가 삭제됩니다.
변경 데이터 캡처를 유지하려면 데이터베이스를 복원할 때
KEEP_CDC옵션을 사용합니다. 이 옵션에 대한 자세한 내용은 RESTORE를 참조하세요.데이터베이스가 분리되어 동일한 서버 또는 다른 서버에 연결된 경우 변경 데이터 캡처는 사용하도록 설정된 상태로 유지됩니다.
Enterprise 이외의 버전에 대한 옵션을 사용하여 데이터베이스를 연결하거나 복원
KEEP_CDC하는 경우 변경 데이터 캡처에 SQL Server 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를 사용하여 복원되거나 연결된 데이터베이스에서 변경 데이터 캡처를 제거할 수 있습니다.
변경 사항 추적
변경 내용 추적은 테이블의 행이 변경되었다는 사실을 캡처하지만 변경된 데이터는 캡처하지 않습니다. 따라서 사용자 테이블에서 직접 가져온 최신 행 데이터를 사용하여 변경한 행을 애플리케이션이 확인할 수 있습니다. 그러므로 변경 내용 추적은 시간에 따른 변경 기록을 표시하는 데 있어서는 변경 데이터 캡처와 비교해 볼 때 보다 제한적입니다. 그러나 기록 정보가 필요하지 않은 애플리케이션의 경우 변경된 데이터가 캡처되지 않아 스토리지 오버헤드가 훨씬 적습니다. 동기 추적 메커니즘은 변경 내용을 추적하는 데 사용됩니다. 이는 DML 작업에 대한 오버헤드를 최소화하도록 설계되었습니다.
다음 예시는 변경 내용 추적을 사용하여 이점을 얻을 수 있는 동기화 시나리오를 보여줍니다. 이 시나리오에서는 애플리케이션에 다음 정보가 필요합니다. 테이블이 마지막으로 동기화된 이후 변경된 테이블의 모든 행과 현재 행 데이터만 필요합니다. 변경 내용을 추적하는 데 동기 메커니즘이 사용되므로 애플리케이션은 양방향 동기화를 수행하고 발생할 수 있는 모든 충돌을 신뢰성 있게 감지할 수 있습니다.
ADO.NET 대한 변경 내용 추적 및 동기화 서비스
ADO.NET용 동기화 서비스는 데이터베이스 간 동기화를 사용하도록 설정하여 오프라인 및 협업 시나리오를 대상으로 하는 애플리케이션을 구축할 수 있는 직관적이고 유연한 API를 제공합니다. ADO.NET 용 동기화 서비스는 변경 내용을 동기화하는 API를 제공하지만 실제로 서버 또는 피어 데이터베이스의 변경 내용을 추적하지는 않습니다. 사용자 지정 변경 내용 추적 시스템을 만들 수 있지만 일반적으로 상당한 복잡성과 성능 오버헤드가 발생합니다. 서버 또는 피어 데이터베이스의 변경 내용을 추적하려면 쉽게 구성하고 고성능 추적을 제공하므로 SQL Server 2014에서 변경 내용 추적을 사용하는 것이 좋습니다.
변경 내용 추적 및 ADO.NET용 동기화 서비스에 대한 자세한 내용은 다음 링크를 참조하세요.
-
변경 내용 추적에 대해 설명하고, 변경 내용 추적이 작동하는 방식에 대한 개략적인 개요를 제공하며, 변경 내용 추적이 다른 SQL Server 데이터베이스 엔진 기능과 상호 작용하는 방식에 대해 설명합니다.
Microsoft Sync Framework 개발자 센터
동기화 프레임워크 및 동기화 서비스에 대한 전체 설명서를 제공합니다. Sync Services 설명서에서 "방법: SQL Server 변경 내용 추적 사용" 항목에는 자세한 정보 및 코드 예제가 포함되어 있습니다.
관련 작업(필수)
| 과업 | 항목 |
| 변경 데이터 캡처에 대한 개요를 제공합니다. | 변경 데이터 캡처 개요(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)