First time partial sync on database with more than 1 billion rows of records

[bearyung]

Hi,

I am running a database for storing retail sales transactions which sync bidirectionally between the cloud and on-premise terminal (sqlite), for the cloud database, it has more than 1 billion rows of records.

So i have come to an issue when I have to reinstall the client app (or replacing the terminal hardware) that during first time data sync after I've reinstalled my App, the agent will download ALL the existing sales transaction data for that particular store (using row filter) but still more than 10 million if that retail store has run for business more than 2 years.

I am looking for any solution if I can partial sync the sales transaction data from the cloud to the sqlite in first time data sync (e.g. only sync the latest 3 months data).

In the past when I was using MS Datasync Framework, I can control what to be sync or not by removing the data in tracking tables, those rows not exist in _tracking table will not sync to client database.

But MS Datasync Framework have no further development nor any support, do not support .NET core and Dotmim.Sync is the only solution we can find which help us to migrate from MS Datasync Framework (otherwise we have to stick with windows platform).

Thank you for any help or advice.

Best regards,
Michael Yung

[Mimetis]

Hi,

Do you have a Created / Modified Date column in this table?
If yes, I suggest using it as a Filter.

If this table is really big, you can also use Snapshot (Be careful if you are using a filter, [snapshot should be filtered as well).

And if you want to separate the sync of this particular table from the others, I will rather go for 1 scope with only this table and 1 scope with all the other tables (See multi scopes documentation)

Let us know if it's matching your requirement

[bearyung]

Hi Mimetis,

Thank you for your quick response and suggestion, I've tried to use the Filter method but have come across another problem as details below:

I applied the filter for account id, shop id (fixed for that terminal) and also the TxDate to get the latest xx days records, and the first sync success without any issue.

the total records download is same as what I query in SQL Server:

Now, when I change the TxDate from 2021/09/01 to 2021/08/01, the expected result should be the delta in that month (between 2021/09/01 and 2021/08/01), the total difference should be 27-24 = 3.

However, when I change the parameter in filter setting, the sync agent seems download all the records later than 2021/08/01 (total 27) and resolve the conflict of the 24 records (as that's already download from previous sync) and finally add the non-conflict 3 records.

Seems it don't care I've already downloaded the records for the Tx Date >= '2021-09-01', and redownloaded all records again.

My idea was to change the TxDate everyday so the application always only sync the past 30 days records but seems the result is not I expect, I am wondering if I setup the filter in an incorrect way.

Would you please help to take a look if my setting is incorrect?

Thank you for your help.

Best Regards,
Michael Yung

[Mimetis]

When you are changing the parameters "values", DMS will create a new scope with its own timestamp (see your scope_info and scope_info_client tables)
That means, you have now two distinct sync: One for values x,y,z, and one for values x,y,p
On your second run, DMS has created a second sync, and synced everything from 0 to Now, with these parameters

Usually, you don't change parameters values for a particular client database.

[bearyung]

Thank you for pointing me to the right direction, I've solved the problem by creating a new table (sync_param) to save the values of retention day(s) for each shops, and put that in the "where" filter, so the parameter values remain unchanged but the filter range is keep moving forward everyday.

And I can even control the length by updating the value I stored in that table, e.g. for Shop A, the retention period is 30 days, and for Shop B, the retention period is 7 days.

[Mimetis]

Nice !!!