I have some tables that I need to synchronize from one SQL Server database to another. I'm using SQL Server's "Change Tracking" feature. My program uses Change Tracking to synchronize the latest changes and then checks to verify that the changes were synchronized correctly.
The way that it goes about this is:
- It fetches the current change tracking version at the source together with some statistics about the source table that will later be used to verify that things synched correctly. The query looks like:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN TRANSACTION; WITH T AS ( SELECT [NumericValue] AS [NumericValue] FROM [SourceTable] ) SELECT AVG(CONVERT([NumericValue])) AS Avg, SUM(CONVERT([NumericValue])) AS Sum, COUNT(*) AS Count, CHANGE_TRACKING_CURRENT_VERSION() AS CTVersion FROM T; COMMIT TRANSACTION;
- It fetches all changes between the last version it synchronized to and the source version fetched in step 1. The query to fetch the changes in the desired version range looks like:
SELECT SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION, [SYSKEY_PrimaryKey], [PrimaryKey], [NumericValue] FROM ( SELECT T.*, CT.SYS_CHANGE_OPERATION, CASE WHEN CT.SYS_CHANGE_OPERATION = 'I' THEN CT.SYS_CHANGE_CREATION_VERSION ELSE CT.SYS_CHANGE_VERSION END AS SYS_CHANGE_VERSION, [CT].[PrimaryKey] AS [SYSKEY_PrimaryKey] FROM CHANGETABLE(CHANGES [SourceTable], @startVersion) AS CT LEFT JOIN [SourceTable] AS T ON T.[PrimaryKey] = CT.[PrimaryKey] WHERE CT.SYS_CHANGE_OPERATION = 'D' OR (T.[PrimaryKey] = CT.[PrimaryKey]) AND CASE WHEN CT.SYS_CHANGE_OPERATION = 'I' THEN CT.SYS_CHANGE_CREATION_VERSION ELSE CT.SYS_CHANGE_VERSION END <= @endVersion ) AS [SourceTable]
- It gets the same statistics from the destination table that it did in step 1. The query looks like:
SELECT AVG(CONVERT([NumericValue])) AS Avg, SUM(CONVERT([NumericValue])) AS Sum, COUNT(*) AS Count FROM [DestinationTable]
- It compares the statistics from steps 1 and 2. If they match then all is good, if not then it knows there was a problem with the synchronization
The problem I'm having is that it is intermittently detecting that the values don't match on tables that experience very rapid updates. Investigating, I have found no evidence of anything actually going wrong in the synchronization process, but the destination seems to lag slightly behind where it needs to be to match the source. When the source table updates slow down, the destination always catches up and matches the source. It seems at this point that the problem stems from the version fetched in step 1 not actually matching the statistics that are fetched at the same time. This seems to indicate that the increment of the change tracking version is not performed atomically together with the actual changes to the table. Unfortunately I haven't found anything in the documentation that explicitly confirms or denies this theory, which makes it difficult to know whether there's something else I need to be looking for. Additionally, if it turns out to be true that the change tracking version and the table state aren't guaranteed to be consistent then I don't know how it would be possible for me to accurately perform validation after synchronization.
Any help clarifying whether it's expected for the version number to lag behind the actual state of the table, or suggestions of techniques I could use to get around this problem would be much appreciated.