Ech row marked as updated during first sync

[karolgt1]

Hi, i have client db (on different server than server db) which was created by copying mdf file. When i'm calling GetEstimatedChangesCountAsync it shows number of changes (in server db) equal to the number of all rows from all tables taking part in sync process. From Ms SQL SMS both dbs looks the same. Due to big amount of data i want to avoid long time copying of data which is not needed. Maybe should i copy the data in some other way?

[Mimetis]

Hi @karolgt1,
You have mainly 2 ways to achieve this:

DMS Snapshots

DMS can use "snapshots" to allow clients to bootstrap their sync with a lot of data coming from the server.
The idea is to create on the server a "kind of" directory containing all rows for all tables, that will be sent to the client as a "zip" file.

You can read more about this feature here: https://dotmimsync.readthedocs.io/Snapshot.html

Sql Server to Sql Server : Using a backup

If clients and server are both Sql Server, you can use backups, but it's not included "out of the box" in DMS.
You need to do some extra job to be sure everything is correctly done and no rows are lost in the process.

In your case, as there is no row marked as tracked in the server database, as well as in the client database, DMS "thinks" it's a first synchronization.

First of all, if you are making a copy of the server database restored to the client, you need to be sure no rows will be added or removed to the server, before the client has restored the backup and synchronization has been initialized.
DMS is tracking changes on rows thanks to the tracking tables and the timestamp registered to each row.

So, if you want to use this kind of "technic" to start a new sync mechanism, based on a server backup, restored on a client, you can do it that way:

• Make a backup of the server database (if DMS is already configured, be sure you are not backup any tracking tables)
• Get the server database timestamp, that will be used on the client to get new / modified / deleted rows since the backup.
• Provision the server database with DMS, to be sure all new / modified or deleted rows are now tracked.
• Restore the backup on the client
• Provision the client with DMS to be sure all new / modified or deleted rows are now tracked
• Save the timestamp from the server, that will be used by client on next sync
• Make a sync to get all rows from the backup to now.

Here is a piece of code, that you can use as exploration:

setup = new SyncSetup("ProductCategory");
var localOrchestrator = new LocalOrchestrator(clientProvider);
var remoteOrchestrator = new RemoteOrchestrator(serverProvider);

// 1) Make a backup of the server database
// ....
// 

// 2) Provision server database
var serverScope = await remoteOrchestrator.ProvisionAsync(setup);

// 3) Get the timestamp to use on the client
var serverTimeStamp = await remoteOrchestrator.GetLocalTimestampAsync();

// Once you have done the first step (1), you need to make Provision (2) and get timestamp (3)
// really quickly to be SURE you don't have any new / modified or deleted rows in the server 
// database that are not in the backup and not tracked.
// Once (2) and (3) are done, you are safe and you have all the time for next steps...


// 4) Restore backup on client
// ...
//

// 5) Provision client
await localOrchestrator.ProvisionAsync(serverScope);

// 6) Get the local timestamp
var clientTimestamp = await localOrchestrator.GetLocalTimestampAsync();

// 7) Get scopeinfoclient
// ScopeInfoClient table contains all information fro the "next" sync to do (timestamp, parameters and so on ...)
var scopeInfoClient = await localOrchestrator.GetScopeInfoClientAsync();

// As we have some existing lines, we say it's not a new sync
scopeInfoClient.IsNewScope = false;

// Affecting the correct timestamp, the local one and the server one
scopeInfoClient.LastServerSyncTimestamp = serverTimeStamp;
scopeInfoClient.LastSyncTimestamp = clientTimestamp;
await localOrchestrator.SaveScopeInfoClientAsync(scopeInfoClient);

// We're done
// We can sync safely now
var agent = new SyncAgent(clientProvider, serverProvider, options);
var r = await agent.SynchronizeAsync(setup);

Console.Write(r.ToString());    

Let me know if it's what you are looking for

[karolgt1]

maybe do you have list of all permissions which sql user need to have? If no i think that it would be valuable to have this information in documentation

[Mimetis]

DMS needs to be able to create / delete / alter tables, stored procedures, triggers and so on...
Depending on the options you are using, DMS could potentially needs to disable constraints...

I don't know exactly the list of permissions needed, but I think that using a user with the db_owner role is required.
If you need a detailed list of permissions, it should require some investigations.
I will probably look into it, but not sure in the near future.

[karolgt1]

Next question, is it possible to configure the schema name, within which all the objects (TVPs, procedures, tables etc.) will be created?

[karolgt1]

I mean new different schema than the one which contains tables with data to sync

[Mimetis]

nop