SqlSyncChangeTrackingProvider and OnOutdated-Interceptor

[Christian-Fenzl]

Hi!

I'm using the SqlSyncChangeTrackingProvider on the server side to sync a database to local Sqlite databases and running into the following problem:

A record is inserted into a parent table with a corresponding record in a child table and successfully synced to the server database. Another client doesn't sync in time of the CHANGE_RENTATION days with the server and therefore did not get the this parent/child records when he syncs later on.

Then another client which is in sync, changes the child record and syncs. If the out of sync client now syncs within the CHANGE_RETENTION days for the change a sync error will be thrown, because it tries to inserts the child row without already having the parent record (FOREIGN KEY constraint failed).

So one problem is that after the CHANGE_RETENTION days there could be missing data if a client syncs too late and another problem is that the data is not consitent anymore and probably results in a sync error.

I was expecting that the OnOutdated-Interceptor will called if the last sync was too long ago for a client and the database will be completely recreated witht he following code:

 syncAgent.LocalOrchestrator.OnOutdated(args =>
 {
     args.Action = OutdatedAction.ReinitializeWithUpload;
 });

But this is not called. Do I miss something beside increasing the CHANGE_RETENTION days on the server? But this only would increase the time frame it works...

[Mimetis]

I think there is a problem with the way the SqlSyncChangeTrackingProvider is updating the last clean up timestamp.
I've made a correction in the last commit.

Until you have upgraded your version, you can try to manually update the scope_info table on the server side, by using this small script:

DECLARE @maxVersion int;
SELECT @maxVersion = MAX(CHANGE_TRACKING_MIN_VALID_VERSION(T.object_id)) 
FROM sys.tables T 
WHERE CHANGE_TRACKING_MIN_VALID_VERSION(T.object_id) is not null;

UPDATE scope_info SET sync_scope_last_clean_timestamp = @maxVersion;

Let me know if it's fixing your issue

[Christian-Fenzl]

I think there is a problem with the way the SqlSyncChangeTrackingProvider is updating the last clean up timestamp. I've made a correction in the last commit.

Until you have upgraded your version, you can try to manually update the scope_info table on the server side, by using this small script:

DECLARE @maxVersion int;
SELECT @maxVersion = MAX(CHANGE_TRACKING_MIN_VALID_VERSION(T.object_id)) 
FROM sys.tables T 
WHERE CHANGE_TRACKING_MIN_VALID_VERSION(T.object_id) is not null;

UPDATE scope_info SET sync_scope_last_clean_timestamp = @maxVersion;

Let me know if it's fixing your issue

Maybe I'm wrong, but why do you need the last clean timestamp? Wouldn't it be better to check against the current one all he time instead of the one from the last sync? This would be more or less exactly what the script does when I applied it, which helped. With the added condition from your commit the value is updated less often, which leads to more missing synced records in my opinion because the needed tracking data can be removed already and this must be determined at the actual moment.

[Mimetis]

Indeed, but this is not how others providers works.
DMS is handling the last cleanup timestamp regardless the provider used.

But I think we can handling it in a better way, I guess.
Let me think a little bit about it, I will see if I can have a more accurate version of the last clean up, not when we are saving but also when we are needing it.

[Christian-Fenzl]

I understand. Would be much better to update the timestamp directly at the beginning before the checks, not at the end. Thanks for your time.

As an external workaround I can save my own last sync time on each client and if the next sync is later than the CHANGE_RETENTION days I can trigger a ReinitializeWithUpload instead of a Normal sync from the client side. But I would prefer that DMS can handle it truthfully by its own.

Please keep me informed. Until I can upgrade to the next version my workaround will be to manually apply the UPDATE script around every 10 days (CHANGE_RETENTION days is 14 days).

[Mimetis]

I've made a couple of changes in this commit where I dynamically get the
MAX(CHANGE_TRACKING_MIN_VALID_VERSION) of all tables, each time I try to get a scope_info record, that happens every time a sync starts.

[Christian-Fenzl]

This is great. Thank you so much!

Looking forward to the release of the next version...