Sync parameters after upgrading to 0.9.6

[MaTToX3]

Hello!

After upgrading Dotmim to 0.9.6, the sync is acting a little bit weird. Of course I had to refactor the way I am adding the parameters to the sync since they are no longer added directly to the agent but rather passed as a parameter to the agent.SynchronizeAsync() method. However, here is where it gets interesting. Calling this method like this:

var s1 = await _agent.SynchronizeAsync(_currentScope, type, _syncParameters);

will result in full sync being executed - meaning it looks like the reinitialize has been done but the database was not really deleted (also nothing was uploaded). Syncing over and over again results in full sync being made every time. However, if I call the sync method this way:

var s1 = await _agent.SynchronizeAsync(_currentScope, type); // without parameters

only the first sync is "full", all the other ones are normal (and also data is uploaded to the server). What could cause such behavior? I dug into the source code a little bit and found nothing special that is being done in "SynchronizeAsync" method when parameters are set.

[Mimetis]

Did you see #802 (comment) ?

[MaTToX3]

Its a brand new project on a clean database. I still have to do a whole upgrade process?

[Mimetis]

No, if it's a new project, there is no "migration" to do, since you are starting with the latest DMS version.

• Can you share your code ?
• Are you using a direct link to server database, or are you using the web proxy ?

[MaTToX3]

I am using a web proxy. Cant share anything at the moment but I could prepare a solution for you to see. Will see if I will have time tomorrow.

Thank you for your time. :)

[MaTToX3]

I have a progress: everytime parameter value changes, a full sync is executed. We are sending a parameter called "LastSyncDate" which holds the information about last successfull sync and obviously, this is constantly changing.

[Mimetis]

That's intentional.

DMS is able to handle multiple parameters Values, but you should think a parameter value as a critical distinction between all your sync clients.
Usually, a sync parameter value is tight to the client database, and never changes.
It allows you to filter rows from the server database to only a subset to clients.
As an example, a parameter value can be for example the ClientId, that never changes on a specific client device. And of course, this value is never the same on each device.

But as DMS is able to handle parameter values changes (since version 0.9.6), you need to understand why each time you are changing the value (for a new one) DMS makes a full sync.

If you look the scope_info_client table, you will see that each time the Sync Parameter value is changing, a new sync client row is created in this table.

A new row in this table means a new sync with its own timestamp, that is completely separated from the other lines.
That's completely normal.
If you are changing the value of your Sync Parameter, DMS needs to do a full sync to download all rows from the beginning that fits with this new value.

First Remark On You Scenario

Why are you passing a last sync date LastSyncDate as a parameter, since DMS is handling it for you, by default ???

Default Scenario

Here is an explanation on the AdventureWorks database with the ProductCategory table:

First of all, we are applying a filter to get all the product categories that are part of the Components category.
We are creating this filter:

Filter clause here is complicated and uses AddCustomWhere() method, but it's just for explanation purpose, and will not change anything if you are using a simple filter.

setup = new SyncSetup("ProductCategory");

SetupFilter filter = new SetupFilter("ProductCategory");
filter.AddParameter("ProductCategoryId", DbType.Guid);
filter.AddCustomWhere("([base].[ParentProductCategoryID] = @ProductCategoryId) " +
                        "OR ([base].[ProductCategoryID] = @ProductCategoryId) " +
                        "OR [side].[sync_row_is_tombstone] = 1");
setup.Filters.Add(filter);

We are taking the Components row and all the categories where parent id is Components

var parameters = new SyncParameters(("ProductCategoryId", 
    new Guid("C657828D-D808-4ABA-91A3-AF2CE02300E9"))) ; // Guid for Components rows

var s = await agent.SynchronizeAsync(setup, parameters, progress: progress);
Console.WriteLine(s);

From that point we get all the product categories tight to the Components categories.

Synchronization done.
        Total changes  uploaded: 0
        Total changes  downloaded: 15
        Total changes  applied on client: 15

Now we decide to change the parameter value to get all product categories under the Accessories category.

var parameters = new SyncParameters(("ProductCategoryId", 
    new Guid("2BE3BE36-D9A2-4EEE-B593-ED895D97C2A6"))) ; // Guid for Accessories rows

var s = await agent.SynchronizeAsync(setup, parameters, progress: progress);
Console.WriteLine(s);

DMS is now making a NEW sync from start to get all product categories for the Accessories name.

Synchronization done.
        Total changes uploaded: 0
        Total changes downloaded: 13
        Total changes applied on client: 13

And to get all these rows, DMS needs to start from 0.

If DMS started from the last time you synced Components, no Accessories would have been retrieved, since no rows has been modified or deleted since the last time you synced Components.

Your Scenario

In your case, you have a scenario where you want to start a new sync for a new parameter, but from the starting time where the last sync with the OTHER parameter value has ended.

TL; DR: We are going to use the ShadowCopy() method.

Let's see a new example on the same table.

I'm starting from a new server database with no DMS metadata installed (I've reset the server database)

If I try to reproduce your scenario, it could be something like that (it does not make any sense, but it's just for example)

• I need all Components product categories from the beginning
• I need all Accessories product categories, but only the rows that have changed since the last time I synced Components categories.

var componentParameters = new SyncParameters(("ProductCategoryId", 
    new Guid("C657828D-D808-4ABA-91A3-AF2CE02300E9"))) ;

var s = await agent.SynchronizeAsync(setup, componentParameters, progress: progress);
Console.WriteLine(s);

From that point we get all the product categories tight to the Components categories.

Synchronization done.
        Total changes  uploaded: 0
        Total changes  downloaded: 15
        Total changes  applied on client: 15

Now for the Accessories, since DMS will create a new scope, we will use the ShadowCopy method to get the boundaries used in the Components scope

// new parameters for "Accessories"
var accessoriesParameters = new SyncParameters(("ProductCategoryId", 
    new Guid("2BE3BE36-D9A2-4EEE-B593-ED895D97C2A6")));

// Create manually the new accessories scope client
var accessoriesScopeInfoClient = await agent.LocalOrchestrator.GetScopeInfoClientAsync(
    syncParameters: accessoriesParameters);

// get the existing scope info client for Components categories
var componentScopeInfoClient = await agent.LocalOrchestrator.GetScopeInfoClientAsync(
    syncParameters: componentParameters);

// copy all information about time from component scope client to accessories scope client
// and save
accessoriesScopeInfoClient.ShadowScope(componentScopeInfoClient);
await agent.LocalOrchestrator.SaveScopeInfoClientAsync(accessoriesScopeInfoClient);


// now sync with the new accessories scope info client
s = await agent.SynchronizeAsync(setup, accessoriesParameters, progress: progress);
Console.WriteLine(s);

Since ShadowCopy will copy all time information from component scope to accessories scope, and since no rows has been modified or deleted since that last sync, well obviously no rows will be downloaded to the client for this new scope:

Synchronization done.
        Total changes  uploaded: 0
        Total changes  downloaded: 0
        Total changes  applied on client: 0

Make sense ?

[MaTToX3]

Understood. It's not something I wanted to do on purpose. It was copied from the other project we have in production and it's using an older version of Dotmim (0.94). Let me try to explain, what is the purpose of my "LastSyncDate" variable.

So, all of our applications use soft deleteing of data - meaning, we have an "Active" column in all of our tables and 1 means record is not deleted, 0 means record is deleted. Now, we want our users to use active records only - meaning, our filter is made out something like that:

setup = new SyncSetup("ProductCategory");
SetupFilter filter = new SetupFilter("ProductCategory");
filter.AddCustomWhere("[base].Active = 1");
setup.Filters.Add(filter);

So, users should only get Active records when syncing. Now, let's imagine some ProductCategory is deleted via web interface. What happens, when user executes a sync next time? Filter will ignore this record as it is in-active and filter only takes active records - meaning, this record will stay active until next reinitialize (When a whole dataset will be re-downloaded). So, to fix that issue, we improved all of our filters that way:

setup = new SyncSetup("ProductCategory");
filter.AddParameter("LastSyncDate", DbType.DateTime2);
SetupFilter filter = new SetupFilter("ProductCategory");
filter.AddCustomWhere("[base].Active = 1 OR [base].Active = 0 AND [base].DateModified >= @LastSyncDate");
setup.Filters.Add(filter);

Now the record will be updated the next time user executed a normaln sync. And this worked perfectly, however we recently started a new project and all of this was just moved over - and of course, we used a new Dotmimsync version.

For now, I deleted the check for Active records, meaning we will sync everything and then locally filter the data by Active column. However I will eventually have to upgrade the existing project to the next Dotmim version so I will have to solve this issue a little bit different (existing project is syncing a lot (and I mean a lot) of data so syncing inactive records will be overkill).

[Mimetis]

Be careful, when playing with AddCustomWhere, you usually have to add the clause [sync_row_is_tombstone] = 1 to get all deleted records.
(In your case, you seem to not have any deleted rows, but just in case ...)

For your scenario, I try to understand...

Your users need all active rows, and not the "non active ones".

Downloading active rows without the non-active ones is occurring during the initialization of a client database.
Once the database is initialized with only active rows, all rows tracked that becomes active to non-active should be downloaded or uploaded (ie synced), right?

If I summarize, I will say that:

• On first sync, during initialization of a new client database (or ReinitializeAsync call) you need to filter active rows only.
• On next syncs, you need to track all rows that are marked active or non-active. You need to get all rows, whatever the flag is.

In that case, I would use a different strategy.

Create an IsActive parameters (that allows null to get active or not active) and remove the LastSyncDate parameter.

setup = new SyncSetup("ProductCategory");
setup.Filters.Add("ProductCategory", "IsActive", null, true); // ALLOW NULL 

Now make 2 syncs:

• First sync when initialize a new client, with parameter set to "1"
• All others sync set with "Null" (means all rows "1" or "0")

// Only active rows
var activeOnlyParameters = new SyncParameters(("IsActive", 1));

// FIRST SYNC ONLY, to init client database
var s = await agent.SynchronizeAsync(setup, activeOnlyParameters, progress: progress);
Console.WriteLine(s);

// New parameter value to DBNull.Value to allow active and non-active rows
var allRowsParameters = new SyncParameters(("IsActive", null));

// Create manually the new scope client
var allRowsScopeInfoClient = await agent.LocalOrchestrator.GetScopeInfoClientAsync(
    syncParameters: allRowsParameters);

// get the existing scope info client for all active rows
var activeOnlyScopeInfoClient = await agent.LocalOrchestrator.GetScopeInfoClientAsync(
    syncParameters: activeOnlyParameters);

// copy all information about time from first scope client to new created scope client
// and save
allRowsScopeInfoClient.ShadowScope(activeOnlyScopeInfoClient);
await agent.LocalOrchestrator.SaveScopeInfoClientAsync(allRowsScopeInfoClient);

// ALL SYNC NOW should use this new sync topology
s = await agent.SynchronizeAsync(setup, allRowsParameters, progress: progress);
Console.WriteLine(s);

Do I miss something here ?