변경 내용 추적 작업(SQL Server)

아티클
07/12/2024

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

변경 내용 추적을 사용하는 애플리케이션은 추적된 변경 내용을 가져와서 다른 데이터 저장소에 적용하고 원본 데이터베이스를 업데이트할 수 있어야 합니다. 이 문서에서는 이러한 작업을 수행하는 방법과 장애 조치(failover)가 발생하여 백업에서 데이터베이스를 복원해야 하는 경우 변경 내용 추적이 수행하는 역할에 대해 설명합니다.

변경 내용 추적 함수를 사용하여 변경 내용 가져오기

변경 내용 추적 함수를 사용하여 데이터베이스에 수행된 변경 내용 및 변경에 대한 정보를 얻는 방법에 대해 설명합니다.

변경 내용 추적 함수 정보

애플리케이션은 다음 함수를 사용하여 데이터베이스에 적용된 변경 내용과 해당 변경 내용에 대한 정보를 가져올 수 있습니다.

CHANGETABLE(CHANGES ...) 함수
이 행 집합 함수는 변경 내용 정보를 쿼리하는 데 사용됩니다. 이 함수는 내부 변경 내용 추적 테이블에 저장된 데이터를 쿼리합니다. 이 함수는 변경된 행의 기본 키와 함께 해당 행의 작업, 업데이트된 열 및 버전과 같은 다른 변경 정보가 포함된 결과 집합을 반환합니다.

CHANGETABLE(CHANGES ...)은 마지막 동기화 버전을 인수로 사용합니다. 마지막 동기화 버전은 @last_synchronization_version 변수를 사용하여 가져옵니다. 마지막 동기화 버전의 의미 개체는 다음과 같습니다:

호출 클라이언트는 변경 내용을 가져왔으며 마지막 동기화 버전까지의 모든 변경 내용을 알고 있습니다.
따라서 CHANGETABLE(CHANGES ...)이 마지막 동기화 버전 이후 발생한 모든 변경 내용을 반환합니다.

다음 그림에서는 CHANGETABLE(CHANGES ...)을 사용하여 변경 내용을 가져오는 방법을 보여줍니다.

이 예에서 클라이언트 A는 오전 9:30에 마지막으로 동기화되고 클라이언트 B는 오전 10:30에 마지막으로 동기화되었습니다. 오전 10:00시와 오전 11:00시에 데이터에 몇 가지 변경이 이루어졌습니다. 이러한 추적된 변경 내용은 아래에 요약되어 있습니다.

CHANGETABLE(CHANGES...) 출력 - 오전 11:30

클라이언트 A는 오전 9시 30분에 마지막으로 동기화되었습니다.

제품 ID 연산 열

139 엽데이트 이름, 가격

140 삭제 -

141 삽입 -

클라이언트 B는 오전 10시 30분에 마지막으로 동기화되었습니다.

제품 ID 연산 열

139 엽데이트 Price

140 삭제 -

141 엽데이트 Price

제품 ID	연산	열
139	엽데이트	이름, 가격
140	삭제	-
141	삽입	-

제품 ID	연산	열
139	엽데이트	Price
140	삭제	-
141	엽데이트	Price

CHANGE_TRACKING_CURRENT_VERSION() 함수
변경 내용을 쿼리할 때 다음에 사용할 현재 버전을 가져오는 데 사용됩니다. 이 버전은 마지막으로 커밋된 트랜잭션의 버전을 나타냅니다.

CHANGE_TRACKING_MIN_VALID_VERSION() 함수
클라이언트가 보유할 수 있는 최소 유효 버전을 구하는 데 사용되며 여전히 CHANGETABLE()에서 유효한 결과를 얻을 수 있습니다. 클라이언트는 이 함수에서 반환된 값에 대해 마지막 동기화 버전을 검사해야 합니다. 마지막 동기화 버전이 이 함수에서 반환된 버전보다 작은 경우 클라이언트는 CHANGETABLE()에서 올바른 결과를 가져올 수 없으며 다시 초기화되어야 합니다.

초기 데이터 가져오기

애플리케이션이 처음으로 변경 내용을 가져오기 전에 애플리케이션은 초기 데이터와 동기화 버전을 가져오기 위한 쿼리를 보내야 합니다. 애플리케이션은 테이블에서 직접 적절한 데이터를 가져온 다음 CHANGE_TRACKING_CURRENT_VERSION()을 사용하여 초기 버전을 가져와야 합니다. 처음 변경 내용을 가져오면 이 버전이 CHANGETABLE(CHANGES ...)에 전달됩니다.

다음 예시는 초기 동기화 버전과 초기 데이터 집합을 가져오는 방법을 보여줍니다.

declare @synchronization_version bigint;

-- Obtain the current synchronization version. This will be used next time that changes are obtained.
SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();

-- Obtain initial data set.
SELECT
    P.ProductID, P.Name, P.ListPrice
FROM
   SalesLT.Product AS P;

변경 내용 추적 함수를 사용하여 변경 내용 가져오기

테이블의 변경된 행과 변경에 대한 정보를 가져오려면 CHANGETABLE(CHANGES...)을 사용합니다. 예를 들어, 다음 쿼리는 SalesLT.Product 테이블의 변경 내용을 가져옵니다.

declare @last_synchronization_version bigint;

SELECT
    CT.ProductID, CT.SYS_CHANGE_OPERATION,
    CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
    CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT;

일반적으로 클라이언트는 행의 기본 키만 가져오는 것이 아니라 행의 최신 데이터를 가져오려고 합니다. 따라서 애플리케이션은 CHANGETABLE(CHANGES ...)의 결과를 사용자 테이블의 데이터와 조인합니다. 예를 들어 다음 쿼리에서는 SalesLT.Product 테이블과 조인하여 Name 및 ListPrice 열에 대한 값을 가져옵니다. 여기에서는 OUTER JOIN이 사용되었습니다. 이는 사용자 테이블에서 삭제된 행에 대해 변경 내용 정보가 반환되도록 하는 데 필요합니다.

SELECT
    CT.ProductID, P.Name, P.ListPrice,
    CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
    CT.SYS_CHANGE_CONTEXT
FROM
    SalesLT.Product AS P
RIGHT OUTER JOIN
    CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT
ON
    P.ProductID = CT.ProductID;

다음 변경 내용 열거에 사용할 버전을 가져오려면 다음 예와 같이 CHANGE_TRACKING_CURRENT_VERSION()을 사용합니다.

SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();

애플리케이션이 변경 내용을 가져올 때는 다음 예와 같이 CHANGETABLE(CHANGES...)과 CHANGE_TRACKING_CURRENT_VERSION()을 모두 사용해야 합니다.

-- Obtain the current synchronization version. This will be used the next time CHANGETABLE(CHANGES...) is called.
SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();

-- Obtain incremental changes by using the synchronization version obtained the last time the data was synchronized.
SELECT
    CT.ProductID, P.Name, P.ListPrice,
    CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
    CT.SYS_CHANGE_CONTEXT
FROM
    SalesLT.Product AS P
RIGHT OUTER JOIN
    CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT
ON
    P.ProductID = CT.ProductID;

버전 번호

변경 내용 추적을 사용하도록 설정된 데이터베이스에는 추적된 테이블이 변경될 때마다 버전 카운터가 증가합니다. 변경된 각 행에는 해당 행과 연결된 버전 번호가 있습니다. 변경 내용을 쿼리하기 위해 애플리케이션에 요청이 전송되면 버전 번호를 제공하는 함수가 호출됩니다. 이 함수는 해당 버전 이후 변경된 모든 변경 내용에 대한 정보를 반환합니다. 여러 가지 측면에서 변경 내용 추적 버전은 개념상 rowversion 데이터 형식과 유사합니다.

마지막으로 동기화된 버전 유효성 검사

변경 내용에 대한 정보는 제한된 시간 동안 유지됩니다. 시간은 ALTER DATABASE의 일부로 지정할 수 있는 CHANGE_RETENTION 매개 변수로 제어됩니다.

CHANGE_RETENTION에 지정된 시간에 따라 모든 애플리케이션이 데이터베이스에서 변경 내용을 요청해야 하는 빈도가 결정됩니다. 애플리케이션에 테이블에 대한 최소 유효한 동기화 버전보다 오래된 last_synchronization_version 값이 있는 경우 해당 애플리케이션은 유효한 변경 내용 열거를 수행할 수 없습니다. 이는 일부 변경 정보가 정리되었을 수 있기 때문입니다. 애플리케이션이 CHANGETABLE(CHANGES ...)을 사용하여 변경 내용을 가져오기 전에 애플리케이션은 CHANGETABLE(CHANGES ...)에 전달할 last_synchronization_version의 값을 확인해야 합니다. last_synchronization_version의 값이 유효하지 않으면 해당 애플리케이션은 모든 데이터를 다시 초기화해야 합니다.

다음 예시는 각 테이블에 대해 last_synchronization_version 값의 유효성을 확인하는 방법을 보여줍니다.

-- Check individual table.
IF (@last_synchronization_version < CHANGE_TRACKING_MIN_VALID_VERSION(
                                   OBJECT_ID('SalesLT.Product')))
BEGIN
  -- Handle invalid version and do not enumerate changes.
  -- Client must be reinitialized.
END;

다음 예와 같이 데이터베이스의 모든 테이블에 대해 last_synchronization_version 값의 유효성을 검사할 수 있습니다.

-- Check all tables with change tracking enabled
IF EXISTS (
  SELECT 1 FROM sys.change_tracking_tables
  WHERE min_valid_version > @last_synchronization_version )
BEGIN
  -- Handle invalid version & do not enumerate changes
  -- Client must be reinitialized
END;

열 추적 사용

애플리케이션에서 열 추적을 사용하면 전체 행 대신 변경된 열에 대한 데이터만 가져올 수 있습니다. 예를 들어 테이블에 크지만 거의 변경되지 않는 열이 하나 이상 있고 자주 변경되는 다른 열도 있는 시나리오가 있을 경우 열 추적을 사용하지 않으면 애플리케이션이 행이 변경되었다는 사실만 확인할 수 있으므로 큰 열 데이터를 포함하는 데이터를 모두 동기화해야 합니다. 그러나 열 추적을 사용하면 애플리케이션에서 큰 열 데이터의 변경 여부를 확인하고 변경된 경우에만 데이터를 동기화할 수 있습니다.

열 추적 정보는 CHANGETABLE(CHANGES ...) 함수에 의해 반환되는 SYS_CHANGE_COLUMNS 열에 나타납니다.

열 추적을 사용하면 변경되지 않은 열에 대해 null이 반환되도록 할 수 있습니다. 열이 null로 변경될 수 있는 경우 별도의 열을 반환하여 열이 변경되었는지 여부를 표시해야 합니다.

다음 예시에서 CT_ThumbnailPhoto 열은 해당 열이 변경되지 않은 경우 NULL이(가) 됩니다. 또한 이 열은 NULL 로 변경되었으므로 NULL 이 될 수 있습니다. 애플리케이션은 CT_ThumbNailPhoto_Changed 열을 사용하여 해당 열이 변경되었는지 여부를 확인할 수 있습니다.

DECLARE @PhotoColumnId int = COLUMNPROPERTY(
    OBJECT_ID('SalesLT.Product'),'ThumbNailPhoto', 'ColumnId');

SELECT
    CT.ProductID, P.Name, P.ListPrice, -- Always obtain values.
    CASE
           WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK(
                     @PhotoColumnId, CT.SYS_CHANGE_COLUMNS) = 1
            THEN ThumbNailPhoto
            ELSE NULL
      END AS CT_ThumbNailPhoto,
      CHANGE_TRACKING_IS_COLUMN_IN_MASK(
                     @PhotoColumnId, CT.SYS_CHANGE_COLUMNS) AS
                                   CT_ThumbNailPhoto_Changed,
     CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
     CT.SYS_CHANGE_CONTEXT
FROM
     SalesLT.Product AS P
INNER JOIN
     CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT
ON
     P.ProductID = CT.ProductID AND
     CT.SYS_CHANGE_OPERATION = 'U';

일관적이고 정확한 결과 가져오기

테이블에 대해 변경된 데이터를 가져오려면 여러 단계를 수행해야 합니다. 특정 문제를 고려하고 처리하지 않으면 일관되지 않거나 잘못된 결과가 반환될 수 있습니다.

예를 들어, Sales 테이블과 SalesOrders 테이블에 적용된 변경 내용을 가져오려면 애플리케이션에서 다음 단계를 수행합니다:

CHANGE_TRACKING_MIN_VALID_VERSION()을 사용하여 마지막으로 동기화된 버전의 유효성을 검사합니다.
다음 번에 변경 내용을 가져오는 데 사용할 수 있는 버전을 CHANGE_TRACKING_CURRENT_VERSION()을 사용하여 가져옵니다.
CHANGETABLE(CHANGES ...)을 사용하여 Sales 테이블의 변경 내용을 가져옵니다.
CHANGETABLE(CHANGES ...)을 사용하여 SalesOrders 테이블의 변경 내용을 가져옵니다.

데이터베이스에서 이전 단계에서 반환된 결과에 영향을 줄 수 있는 두 가지 프로세스가 발생하고 있습니다.

정리 프로세스가 백그라운드에서 실행되어 지정된 보존 기간보다 오래된 변경 내용 추적 정보가 제거됩니다.

정리 프로세스는 데이터베이스에 대한 변경 내용 추적을 구성할 때 지정한 보존 기간을 사용하는 별도의 백그라운드 프로세스입니다. 문제는 마지막 동기화 버전의 유효성이 검사된 시점과 CHANGETABLE(CHANGES...)을 호출한 시점 사이에 정리 프로세스가 발생할 수 있다는 것입니다. 유효했던 마지막 동기화 버전이 변경 내용을 검색할 때 더 이상 유효하지 않을 수 있습니다. 따라서 잘못된 결과가 반환될 수 있습니다.
Sales 및 SalesOrders 테이블에서 다음 작업과 같은 진행 중인 DML 작업이 수행 중입니다.
- 다음 버전을 가져온 후 CHANGE_TRACKING_CURRENT_VERSION()을 사용하여 테이블에 변경을 수행할 수 있습니다. 따라서 예상보다 더 많은 변경 내용이 반환될 수 있습니다.
- Sales 테이블에서 변경 내용을 가져오기 위한 호출과 SalesOrders 테이블에서 변경 내용을 가져오기 위한 호출 사이의 시간에 트랜잭션이 커밋될 수 있습니다. 따라서 SalesOrder 테이블에 대한 결과에 Sales 테이블에 없는 외래 키 값이 포함될 수 있습니다.

앞에서 나열된 문제점을 해결하려면 스냅샷 격리를 사용하는 것이 좋습니다. 스냅숏 격리를 사용하면 변경 내용 정보의 일관성을 유지하고 백그라운드 정리 태스크와 관련된 경합 상태를 방지할 수 있습니다. 스냅샷 트랜잭션을 사용하지 않는 경우 변경 내용 추적을 사용하는 애플리케이션을 개발하려면 훨씬 더 많은 노력이 필요할 수 있습니다.

스냅샷 격리 사용

변경 내용 추적은 스냅샷 격리와 원활하게 작동하도록 설계되었습니다. 데이터베이스에 대해 스냅샷 격리를 사용하도록 설정해야 합니다. 변경 내용을 가져오는 데 필요한 모든 단계가 스냅샷 트랜잭션 내에 포함되어야 합니다. 이렇게 하면 변경 내용을 가져오는 동안 데이터에 수행되는 모든 변경 내용이 스냅샷 트랜잭션 내부의 쿼리에 표시되지 않습니다.

스냅샷 트랜잭션 내의 데이터를 가져오려면 다음 단계를 수행합니다.

트랜잭션 격리 수준을 스냅샷으로 설정하고 트랜잭션을 시작합니다.
CHANGE_TRACKING_MIN_VALID_VERSION()을 사용하여 마지막 동기화 버전의 유효성을 검사합니다.
CHANGE_TRACKING_CURRENT_VERSION()을 사용하여 다음에 사용할 버전을 가져옵니다.
CHANGETABLE(CHANGES ...)을 사용하여 Sales 테이블의 변경 내용을 가져옵니다.
CHANGETABLE(CHANGES ...)을 사용하여 SalesOrders 테이블의 변경 내용을 가져옵니다.
트랜잭션을 커밋합니다.

변경 내용을 가져오는 모든 단계가 스냅샷 트랜잭션 내에 있으므로 다음 사항에 유의해야 합니다.

마지막 동기화 버전의 유효성이 검사된 후 정리가 수행되는 경우 정리로 수행된 삭제 작업이 트랜잭션 내부에 표시되지 않으므로 CHANGETABLE(CHANGES ...)의 결과는 여전히 유효합니다.
다음 동기화 버전을 얻은 후 Sales 테이블 또는 SalesOrders 테이블에 수행된 모든 변경 내용은 표시되지 않으며, CHANGETABLE(CHANGES ...) 호출은 CHANGE_TRACKING_CURRENT_VERSION()이 반환한 버전보다 이후 버전의 변경 내용을 반환하지 않습니다. CHANGETABLE(CHANGES ...) 호출 사이에 커밋된 트랜잭션은 표시되지 않으므로 Sales 테이블과 SalesOrders 테이블 간의 일관성도 유지됩니다.

다음 예시는 데이터베이스에 스냅샷 격리를 사용하도록 설정하는 방법을 보여줍니다.

-- The database must be configured to enable snapshot isolation.
ALTER DATABASE AdventureWorksLT
    SET ALLOW_SNAPSHOT_ISOLATION ON;

스냅샷 트랜잭션은 다음과 같이 사용됩니다.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
  -- Verify that version of the previous synchronization is valid.
  -- Obtain the version to use next time.
  -- Obtain changes.
COMMIT TRAN

스냅샷 트랜잭션에 대한 자세한 내용은 SET TRANSACTION ISOLATION LEVEL(Transact-SQL)을 참조하세요.

정리 및 스냅샷 격리

동일한 데이터베이스 또는 동일한 인스턴스 내의 서로 다른 두 데이터베이스에서 스냅샷 격리와 변경 내용 추적을 모두 사용하도록 설정하면 스냅샷 격리가 있는 데이터베이스에 열려 있는 트랜잭션이 있을 때 정리 프로세스가 sys.syscommittab에 만료된 행을 남기게 될 수 있습니다. 이는 변경 내용 추적 정리 프로세스가 정리를 수행할 때 인스턴스 전체의 로우 워터 마크(안전한 정리 버전)를 고려하기 때문에 발생할 수 있습니다. 이는 변경 내용 추적 자동 정리 프로세스가 스냅샷 격리를 사용하도록 설정된 데이터베이스에서 열려 있는 트랜잭션에 필요할 수 있는 행을 제거하지 않도록 하기 위해 수행됩니다. 읽기 커밋된 스냅샷 격리 및 스냅샷 격리 트랜잭션을 가능한 한 짧게 유지하여 sys.syscommittab에서 만료된 행이 적시에 정리되도록 하세요.

스냅샷 격리의 대안

스냅샷 격리를 사용하는 대안이 있지만 모든 애플리케이션 요구 사항이 충족되는지 확인하기 위해 더 많은 작업이 필요합니다. last_synchronization_version이 유효한지, 변경 내용이 적용되기 전에 정리 프로세스에 의해 데이터가 제거되지 않았는지 확인하려면 다음 단계를 따릅니다.

CHANGETABLE()을 호출한 후 last_synchronization_version 을 확인합니다.
각 쿼리의 일부로 last_synchronization_version을 확인하여 CHANGETABLE()을 사용하여 변경 내용을 가져옵니다.

다음 열거형에 대한 동기화 버전을 얻은 후에 변경 내용이 발생할 수 있습니다. 이 상황을 처리하는 방법에는 두 가지가 있습니다. 사용되는 옵션은 애플리케이션과 각 접근 방식의 부정적인 영향을 처리할 수 있는 방법에 따라 다릅니다.

새 동기화 버전보다 큰 버전이 있는 변경 내용을 무시합니다.

이 접근 방식은 새 동기화 버전 이전에 생성되거나 업데이트되었지만 이후에 업데이트된 행이 있는 경우 새 행 또는 업데이트된 행이 건너뛰는 부정적인 영향이 있습니다. 새 행이 있는 경우, 건너뛴 행을 참조하는 다른 테이블에 생성된 행이 있는 경우 참조 무결성 문제가 발생할 수 있습니다. 업데이트된 기존 행이 있는 경우 해당 행은 건너뛰고 다음 번까지 동기화되지 않습니다.
새 동기화 버전보다 큰 버전이 있는 변경 내용도 모두 포함됩니다.

새 동기화 버전보다 큰 버전을 포함하는 행은 다음 동기화에서 다시 얻게 됩니다. 이를 예상하여 애플리케이션에서 처리해야 합니다.

앞의 두 옵션 외에도 작업에 따라 두 옵션을 결합한 접근 방식을 시도해 볼 수 있습니다. 예를 들어, 행이 생성 또는 삭제된 다음 동기화 버전보다 최신의 변경 내용은 무시하는 것이 가장 좋지만 업데이트는 무시하지 않는 애플리케이션을 원할 수 있습니다.

참고 항목

변경 내용 추적(또는 사용자 지정 추적 메커니즘)을 사용할 때 애플리케이션에 적합한 접근 방식을 선택하려면 상당한 분석이 필요합니다. 따라서 스냅샷 격리를 사용하는 것이 훨씬 간단합니다.

변경 내용 추적이 데이터베이스의 변경 내용을 처리하는 방법

변경 내용 추적을 사용하는 일부 애플리케이션은 다른 데이터 저장소와 양방향 동기화를 수행합니다. 즉, SQL Server 데이터베이스에서 변경된 내용은 다른 데이터 저장소에서 업데이트되고, 다른 저장소에서 변경된 내용은 SQL Server 데이터베이스에서 업데이트됩니다.

애플리케이션이 다른 데이터 저장소의 변경 내용으로 로컬 데이터베이스를 업데이트할 때 애플리케이션은 다음 작업을 수행해야 합니다:

충돌을 확인합니다.

충돌은 두 데이터 저장소에서 동일한 데이터가 동시에 변경될 때 발생합니다. 애플리케이션은 충돌을 확인하고 충돌을 사용하도록 설정할 수 있는 충분한 정보를 얻을 수 있어야 합니다.
애플리케이션 컨텍스트 정보를 저장합니다.

애플리케이션은 변경 내용 추적 정보가 있는 데이터를 데이터 저장소에 저장합니다. 이 정보는 로컬 데이터베이스에서 변경 내용을 가져온 경우 다른 변경 내용 추적 정보와 함께 사용됩니다. 이 컨텍스트 정보에 대한 일반적인 예는 변경 내용의 원본인 데이터 저장소에 대한 식별자입니다.

이전 작업을 수행하기 위해 동기화 애플리케이션은 다음 기능을 사용할 수 있습니다.

CHANGETABLE(VERSION...)

애플리케이션이 변경할 때 이 함수를 사용하여 충돌을 확인할 수 있습니다. 이 함수는 변경 내용이 추적된 테이블의 지정된 행에 대한 최신 변경 내용 추적 정보를 가져옵니다. 이 변경 내용 추적 정보에는 행이 마지막으로 변경되었을 때의 버전이 포함되어 있습니다. 이 정보를 통해 애플리케이션은 애플리케이션이 마지막으로 동기화된 이후에 행이 변경되었는지 여부를 확인하도록 설정할 수 있습니다.
WITH CHANGE_TRACKING_CONTEXT

애플리케이션은 이 절을 사용하여 컨텍스트 데이터를 저장할 수 있습니다.

충돌 확인

양방향 동기화 시나리오에서 클라이언트 애플리케이션은 애플리케이션이 마지막으로 변경 내용을 가져온 이후 행이 업데이트되지 않았는지 여부를 확인해야 합니다.

다음 예시는 별도의 쿼리 없이 가장 효율적인 방법으로 충돌을 확인하기 위해 CHANGETABLE(VERSION ...) 함수를 사용하는 방법을 보여줍니다. 이 예에서 CHANGETABLE(VERSION ...) 은 SYS_CHANGE_VERSION 에서 지정한 행에 대한 @product id을 확인합니다. CHANGETABLE(CHANGES ...)도 동일한 정보를 얻을 수 있지만 효율성이 떨어집니다. 행에 대한 SYS_CHANGE_VERSION의 값이 @last_sync_version의 값보다 크면 충돌이 있는 것입니다. 충돌이 발생하면 행이 업데이트되지 않습니다. 행에 사용할 수 있는 변경 정보가 없을 수 있으므로 ISNULL() 확인이 필요합니다. 변경 내용 추적을 사용하도록 설정한 이후 또는 변경 정보가 정리된 이후 행이 업데이트되지 않은 경우 변경 정보가 존재하지 않을 수 있습니다.

-- Assumption: @last_sync_version has been validated.
UPDATE SalesLT.Product
SET ListPrice = @new_listprice
FROM SalesLT.Product AS P
WHERE ProductID = @product_id
    AND @last_sync_version >= ISNULL((
            SELECT CT.SYS_CHANGE_VERSION
            FROM CHANGETABLE(VERSION SalesLT.Product, (ProductID), (P.ProductID)) AS CT
            ), 0);

다음 코드는 업데이트된 행 수를 확인하고 충돌에 대한 자세한 정보를 식별할 수 있습니다.

-- If the change cannot be made, find out more information.
IF (@@ROWCOUNT = 0)
BEGIN
    -- Obtain the complete change information for the row.
    SELECT
        CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_CREATION_VERSION,
        CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS
    FROM
        CHANGETABLE(CHANGES SalesLT.Product, @last_sync_version) AS CT
    WHERE
        CT.ProductID = @product_id;

    -- Check CT.SYS_CHANGE_VERSION to verify that it really was a conflict.
    -- Check CT.SYS_CHANGE_OPERATION to determine the type of conflict:
    -- update-update or update-delete.
    -- The row that is specified by @product_id might no longer exist 
    -- if it has been deleted.
END

컨텍스트 정보 설정

애플리케이션은 WITH CHANGE_TRACKING_CONTEXT 절을 사용하여 변경 정보와 함께 컨텍스트 정보를 저장할 수 있습니다. 이 정보는 CHANGETABLE(CHANGES ...)이 반환하는 SYS_CHANGE_CONTEXT 열에서 가져올 수 있습니다.

컨텍스트 정보는 일반적으로 변경의 출처를 식별하는 데 사용됩니다. 변경의 출처를 식별할 수 있는 경우, 데이터 저장소에서 해당 정보를 사용하여 다시 동기화할 때 변경 내용을 가져오지 않도록 할 수 있습니다.

-- Try to update the row and check for a conflict.
WITH CHANGE_TRACKING_CONTEXT (@source_id)
UPDATE
  SalesLT.Product
SET
  ListPrice = @new_listprice
FROM
  SalesLT.Product AS P
WHERE
  ProductID = @product_id AND
    @last_sync_version >= ISNULL (
    (SELECT CT.SYS_CHANGE_VERSION FROM CHANGETABLE(VERSION SalesLT.Product,
    (ProductID), (P.ProductID)) AS CT),
       0);

일관적이고 정확한 결과 보장

애플리케이션은 @last_sync_version 값의 유효성을 검사할 때 정리 프로세스를 고려해야 합니다. 그 이유는 CHANGE_TRACKING_MIN_VALID_VERSION()이 호출된 이후 업데이트가 적용되기 전에 데이터가 제거되었을 수도 있기 때문입니다.

스냅샷 격리를 사용하고 스냅샷 트랜잭션 내에서 변경을 수행해야 합니다.

-- Prerequisite is to ensure ALLOW_SNAPSHOT_ISOLATION is ON for the database.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
    -- Verify that last_sync_version is valid.
    IF (@last_sync_version <
CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('SalesLT.Product')))
    BEGIN
       RAISERROR (N'Last_sync_version too old', 16, -1);
    END
    ELSE
    BEGIN
        -- Try to update the row.
        -- Check @@ROWCOUNT and check for a conflict.
    END;
COMMIT TRAN;

참고 항목

스냅샷 트랜잭션 내에서 업데이트 중인 행이 스냅샷 트랜잭션이 시작된 후 다른 트랜잭션에서 업데이트되었을 가능성이 있습니다. 이 경우 스냅샷 격리 업데이트 충돌이 발생하여 트랜잭션이 종료됩니다. 이러한 경우가 발생하면 업데이트를 다시 시도하세요. 그러면 변경 내용 추적 충돌이 감지되고 변경되는 행이 없습니다.

변경 내용 추적 및 데이터 복원

동기화가 필요한 애플리케이션은 변경 내용 추적을 사용하도록 설정한 데이터베이스가 이전 버전의 데이터로 되돌아가는 경우를 고려해야 합니다. 이는 백업에서 데이터베이스를 복원하거나, 비동기 데이터베이스 미러로 장애 조치(failover)를 수행하거나, 로그 전송을 사용할 때 장애가 발생한 경우에 발생할 수 있습니다. 다음 시나리오는 이 문제에 대한 예시를 제공합니다.

테이블 T1은 변경 내용 추적 대상이며 테이블의 최소 유효 버전은 50입니다.
클라이언트 애플리케이션은 버전 100에서 데이터를 동기화하고 버전 50과 100 사이의 모든 변경 사항에 대한 정보를 가져옵니다.
버전 100 이후에 테이블 T1에 추가 변경이 이루어집니다.
버전 120에서 실패가 발생하고 데이터베이스 관리자가 데이터 손실과 함께 데이터베이스를 복원합니다. 복원 작업 후 테이블에는 버전 70까지의 데이터가 포함되며 동기화된 최소 버전은 여전히 50입니다.

즉, 동기화된 데이터 저장소에는 기본 데이터 저장소에 더 이상 존재하지 않는 데이터가 있습니다.
T1은 여러 번 업데이트되어서 그러면 현재 버전은 130이 됩니다.
클라이언트 애플리케이션이 다시 동기화하여 마지막으로 동기화된 버전인 100을 제공합니다. 클라이언트는 100이 50보다 크므로 이 숫자의 유효성을 검사하는 데 성공합니다.

클라이언트는 버전 100과 130 사이의 변경 내용을 가져옵니다 이 시점에서 클라이언트는 70과 100 사이의 변경 내용이 이전과 동일하지 않다는 것을 인식하지 못합니다. 클라이언트와 서버의 데이터가 동기화되지 않았습니다.

데이터베이스가 버전 100 이후의 시점으로 복구되었다면 동기화에는 문제가 없을 것입니다. 클라이언트와 서버는 다음 동기화 간격 동안 데이터를 올바르게 동기화합니다.

변경 내용 추적은 데이터 손실에 대한 복구를 지원하지 않습니다. 그러나 이러한 유형의 동기화 문제를 감지하는 데는 두 가지 옵션이 있습니다.

데이터베이스 버전 ID를 서버에 저장하고 데이터베이스가 복구되거나 데이터가 손실될 때마다 이 값을 업데이트합니다. 각 클라이언트 애플리케이션은 ID를 저장하고, 각 클라이언트는 데이터를 동기화할 때 이 ID의 유효성을 검사해야 합니다. 데이터 손실이 발생하면 ID가 일치하지 않게 되고 클라이언트가 다시 초기화됩니다. 한 가지 단점은 데이터 손실이 마지막으로 동기화된 경계를 넘지 않았다면 클라이언트가 불필요한 재초기화를 수행할 수 있다는 것입니다.
클라이언트가 변경 내용을 쿼리할 때 서버에서 각 클라이언트의 마지막 동기화 버전 번호를 기록합니다. 데이터에 문제가 있는 경우 마지막으로 동기화된 버전 번호가 일치하지 않을 수 있습니다. 이는 다시 초기화해야 함을 나타냅니다.

다음을 통해 공유

변경 내용 추적 작업(SQL Server)

변경 내용 추적 함수를 사용하여 변경 내용 가져오기

변경 내용 추적 함수 정보

초기 데이터 가져오기

변경 내용 추적 함수를 사용하여 변경 내용 가져오기

버전 번호

마지막으로 동기화된 버전 유효성 검사

열 추적 사용

일관적이고 정확한 결과 가져오기

스냅샷 격리 사용

정리 및 스냅샷 격리

스냅샷 격리의 대안

변경 내용 추적이 데이터베이스의 변경 내용을 처리하는 방법

충돌 확인

컨텍스트 정보 설정

일관적이고 정확한 결과 보장

변경 내용 추적 및 데이터 복원

참고 항목

피드백

추가 리소스