Change Tracking doesn't track deletes in SYS_CHANGE_OPERATION

Question

I'm syncing data from SQL server A towards SQL server B. Sometimes data gets removed in the source, to prevent that I have to sync the full table everytime I would like to track those deleted records.

I don't want to work with triggers since it is our ERP database and I know that the ERP software supplier doesn't like to see this.

That's why I'm checking if I could work with Change Tracking instead. In my test case I've enabled Change Tracking on my test database and enabled it on my test table. It does track Inserts (I) and Updates (U) in the SYS_CHANGE_OPERATION column but it doens't track the Deletes (D).

Is there a setting that I missed and still has to be set? Or am I missing something?

I've tried to remove the record in different ways just to see if they do get removed then. But unfortunately I didn't get the desired result.

Answer 1

You can use a workaround to indirectly track deletions using Change Tracking. You can leverage the fact that Change Tracking does track updates and inserts. You can identify the deleted records by comparing the changed version of the last synchronization with the current change version.

Here's a high-level approach you can follow:

Enable Change Tracking on the table: Make sure Change Tracking is enabled on the database and table you want to track.

Perform initial synchronization: Perform a full synchronization of the table to ensure that all the existing data is captured by Change Tracking.

Track changes during subsequent synchronizations: During each subsequent synchronization, retrieve the latest change version from the target table.

Identify deleted records: Compare the last change version from the previous synchronization with the current change version. Any records that were present in the last synchronization but not in the current synchronization are considered deleted.

Handle deleted records: Process the deleted records as per your requirements. This may involve marking them as deleted in the destination table or performing any necessary actions.

By comparing the change versions, you can effectively identify the deleted records even though they are not explicitly tracked by Change Tracking.

It's worth noting that this approach relies on the assumption that deletions are accompanied by an update or insert operation. If a record is deleted without any accompanying changes, it may not be captured using this method.

  -- Step 1: Enable Change Tracking on the table (if not already enabled)
ALTER DATABASE YourDatabase SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

-- Step 2: Perform initial synchronization (assuming you have existing data in SourceTable)
INSERT INTO TargetTable (id, col1, col2, ...) -- Specify the columns of your table
SELECT id, col1, col2, ...
FROM SourceTable

-- Step 3: Track changes during subsequent synchronizations
DECLARE @lastChangeVersion BIGINT
SELECT @lastChangeVersion = LastChangeVersion FROM SyncStateTable -- Keep track of the last change version

DECLARE @currentChangeVersion BIGINT
SELECT @currentChangeVersion = SYS_CHANGE_VERSION
FROM CHANGETABLE(CHANGES TargetTable, @lastChangeVersion) AS CT
WHERE CT.SYS_CHANGE_OPERATION IN ('I', 'U') -- Include inserts and updates

-- Step 4: Identify deleted records
SELECT id
FROM CHANGETABLE(CHANGES TargetTable, @lastChangeVersion) AS CT
WHERE CT.SYS_CHANGE_OPERATION = 'D'

-- Step 5: Handle deleted records as per your requirements
-- For example, you can mark the deleted records as deleted in the destination table
UPDATE TargetTable
SET isDeleted = 1
WHERE id IN (
    SELECT id
    FROM CHANGETABLE(CHANGES TargetTable, @lastChangeVersion) AS CT
    WHERE CT.SYS_CHANGE_OPERATION = 'D'
)

-- Update the last change version in SyncStateTable
UPDATE SyncStateTable
SET LastChangeVersion = @currentChangeVersion

YourDatabase is the name of your database, SourceTable is the table you want to synchronize from, TargetTable is the table you want to synchronize to, and SyncStateTable is a table that keeps track of the last change version.

Note that you need to create the SyncStateTable and populate it with the last change version during each synchronization. The table can have a single row with a column like LastChangeVersion to store the last change version.

You may need to modify the code to suit your specific table schema and requirements.