Initial table synchronisation

[jeancz]

It would be nice, perform initial table synchronization (maybe optional in config) if the minimal version of the source table doesn't meet the version of the destination.

[fkierzek]

I get

ERROR|Cannot replicate table [dbo].[MonitoringParameter] to destination TestFK because minimum source version 238 is greater than destination version 2

Looking at the code it looks like the SyncInfo is not created because the call to check existance/create is after the above error message is shown which stops the execution.

I would be very helpful if the project Wiki said a word on how to go about it.

[mganss]

If the SyncInfo table does not exist (i.e. no successful replication has been completed yet), the code determines the destination's current version using the SQL Server function CHANGE_TRACKING_CURRENT_VERSION() which yields the database's current version from SQL Server's change tracking point of view. When we execute the replication statements, the version in the destination database increases, but does not stay in sync with the version in the source (because we do not execute the exact same steps that were carried out in the source DB). That's why we need the SyncInfo table to record the source version that we replicated in the destination.

That said, in your specific case the source DB has already evolved beyond the point where the changes before version 238 have already disappeared from the source's change tracking (see https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-server). You need to make sure that the replication process is executed periodically before the change retention time window is expired. If you have missed replication window, you will need to restore a backup of the current source in the destination and then make sure that the replication process is executed before the source's next retention window expires.

[fkierzek]

Hi,

Thanks for the throught explanation.

I think what I stumbled upon was a bug. Basically if SyncInfo table wasn't present in the destination database - it wouldn't be created and nothing would happen.

pozdrawiam

Filip Kierzek T: +48502304434

On Tue, 28 Jun 2022 at 19:13, Michael Ganss @.***> wrote:

If the SyncInfo table does not exist (i.e. no successful replication has been completed yet), the code determines the destination's current version using the SQL Server function CHANGE_TRACKING_CURRENT_VERSION() which yields the database's current version from SQL Server's change tracking point of view. When we execute the replication statements, the version in the destination database increases, but does not stay in sync with the version in the source (because we do not execute the exact same steps that were carried out in the source DB). That's why we need the SyncInfo table to record the source version that we replicated in the destination.

That said, in your specific case the source DB has already evolved beyond the point where the changes before version 238 have already disappeared from the source's change tracking (see https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-server). You need to make sure that the replication process is executed periodically before the change retention time window is expired. If you have missed replication window, you will need to restore a backup of the current source in the destination and then make sure that the replication process is executed before the source's next retention window expires.

— Reply to this email directly, view it on GitHub https://github.com/mganss/SyncChanges/issues/29#issuecomment-1169003788, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABKDNZB3DWCJS5M5D7LJ7JLVRMXEBANCNFSM4JADULFQ . You are receiving this because you commented.Message ID: @.***>

[mganss]

I think what I stumbled upon was a bug. Basically if SyncInfo table wasn't present in the destination database - it wouldn't be created and nothing would happen.

I don't think it's a bug but expected behavior. The SyncInfo table is created once the initial replication has been successfully completed. In your specific case I believe this has not happened yet because of the reason I outlined above in the second paragraph. Try restoring a fresh backup in the destination, then run the synchronization process again.