Resco Mobile CRM: Synchronization (a technical deep dive) – Part 3/3

We have taken a look at the difference between online and offline mode in the first part of this technical deep dive. And I have explained how Resco Mobile CRM implements the offline mode in the second part. Now, we are going to inspect the synchronization.When you sync for the first time, you basically download to the device a subset of the server database. This subset refers to the time instant when it was downloaded. (Or synced last time – hence the name LastSync time. Strictly speaking there is no LastSync time for the database as a whole. Instead, each table has its own LastSync time.)
Once the user has the data on the mobile device, he can start using it – view, search, but also create, edit or delete. Of course, the same data items may be changed on the server, too.
The purpose of the synchronization is to

1. Download server changes to the client
2. Upload client changes to the server
3. Resolve eventual conflicts

 
Simple sync algorithm
So how does the sync algorithm work? Here is a simplified form:
For each entity

Start a transaction.

Load the entity LastSync time. (These times are stored in a special DB table.)

Fetch server records that fit the SyncFilter and were changed since the LastSync time, order by record id.

Read local dirty records, order by record id. (This list is empty if the user hasn’t done any editing.)

Reconcile the 2 lists:

– Records found in both list go for conflict resolution.

– The remaining records are either saved to the local database, or

 – Uploaded to server. (Once the upload succeeds, the record dirty flag is cleared.)

Update the entity LastSync time.

Commit the transaction.

Conflict resolution

Conflict resolution (treatment of the records that were updated simultaneously by the client and server) works as follow:
The conflict policy is set (in MCRM customization) as one of:

• ServerWins (local changes are discarded and the server record is saved),
• ClientWins (Server changes are ignored and the record is uploaded), or
• Manual (The conflict remains unresolved; user can decide which side wins)

 
At the end of the day the local database has new data and the changes are applied to the server. There are no local records marked as dirty except the records representing unresolved conflicts. (The user will have to take the decision later.)
Any problem (such as the lost connection, server error, database failure, whatever…) breaks the loop so that the transaction is not committed.
What this means in reality is that the all the changes done to the database during the transaction existence are rolled back. The result is the same as if a few first tables were updated successfully and then the synchronization was terminated.
 

Sync process

Real sync process is much more complex than the algorithm presented above:

• Customization update (MCRM progress dialog displays “Sync: Metadata”.)
  At the sync start MCRM contacts the customization server to see if there is a new customization ready for the download.
  The new customization may describe an UI change, but it also may change the client data structure, such as adding or dropping database tables or columns, or changing the Sync Filter.These changes happen frequently during the testing/deployment of the mobile solution, but occasionally also later.
  In the past any such change was solved by brute force: Delete client data, adapt data structure and finally re-download the data. Today MCRM attempts to do incremental changes, saving thus the end-user a lot of time.*)The entities synced are not ordered arbitrarily. Instead, the order is selected so that parent entities precede child entities. (So that, for example, quotes are downloaded before quotedetails.)This order minimizes a number of conflicts originating from parent-child inter-dependencies.

• Server deletes (MCRM progress dialog displays “Sync: ServerDeletes”.)
  FetchXml protocol (used to communicate with the server) can deliver list of existing records that fit all thinkable criteria, but there is one thing that cannot be done:
  Downloading the list of deleted records. Resco MCRM uses special Dynamics CRM plugin (i.e. a custom code that has to be installed at the server) that collects this info.
  One of the first steps of the sync process is the download of the deletes done since the LastSync time.

• Multi-pass synchronization
  The upload may need several passes. For example when the user modifies quotedetails and this change is uploaded to the server, the server will update the parent quote  and we need to have this change ASAP. In similar cases sync algorithm just makes a note that quotes need to be synced again and once all entities are synced  it returns to the quotes again.

• Cleanup (MCRM progress dialog displays “Sync: Cleanup”.)
  SyncFilter primarily aims at keeping the client database size at a reasonable limit. Imagine a model situation when the SyncFilter is set up as last-4-weeks.
  Let’s say we sync every Monday. Which data do we have after the synchronization completes? The answer is straightforward: Unless this was the very first sync, we have the data from the last 5 weeks. In order to correct this, MCRM has to scan the whole local database and eliminate unneeded records.

• Permissions
  Synchronization process respects permissions. For example entities with no read permissions are not downloaded at all.**)

————————————————————————-
*) If the user edited a record that will lose some column due to the new synchronization, then MobileCRM will upload all changes prior to applying the new customization. This is so-called UploadSync. The user perceives this as an extra synchronization pass.
**) Permissions (read, write, create, delete…) are granted for individual entities and for given level of ownership (user/business unit/organization). For example, a user might be able to fully manipulate “his” accounts, but might have no permissions to the accounts owned by the organization.

Incremental vs. Full Sync

The reader probably realized that there is a difference between the very first synchronization and all subsequent synchronizations.
Indeed, the first sync (called FullSync) simply performs bulk-download and does not care about the local data. Subsequent sync (called IncSync) is incremental as it only takes care of the changes. As a rule IncSync is much faster than the FullSync and as such contributes to the user satisfaction.

Other aspects

In order to limit the amount of technical information let us just briefly list other topics that receive special handling during the sync process:
SharePoint documents, shared entities (entities shared by the user teams the user is member of), images, attachments, NN relationships, multithreaded download,  bulk upload, optimized parsing, optimized database access etc.

SyncLog

SyncLog is accessible from the About form. It contains a lot of performance counters that might explain synchronization behavior.

Sync performance

Large heterogeneity makes it difficult to formulate any general performance-related conclusions:

• Some customers use large multi-column tables (tables with 100+ columns are no exception), while the others are happy with 20-30 columns max.
• Some customers use small database (in the order of 10 MB); the opposite extreme is 600+ MB.
• Most customers use rather stable data with infrequent changes. However, I saw customers performing bulk server operations on a daily basis.
• Sync performance heavily depends on the Dynamics CRM server performance. The server may experience fluctuations due to different load conditions.
•  Some FetchXml queries may be lucky (i.e. fast) because they translate to SQL queries that use optimal indexes.

If, on other hand the server has to scan a huge table, the response will be slow.

Since its first release Resco Mobile CRM has gone a long way in perfecting the sync process

In an ideal case we can expect the speed 1ms/entity record, sometimes even faster.*) This happens when:

• The device has fast Internet connection.
• The entity is not too complex. (If a customer designs entity with 150 columns, then he cannot wonder that download speed will be lower.)
• The server is not too busy. (Server sends entity records in batches – max. 500 records at a time. In order to get the speed 1000 records/second we need the server response times bellow 0.5 sec. This is usually the case – the best response times are ~0.1 sec.)
• The server itself might be fast, but certain entities might require additional processing on the CRM Dynamics side.*)
• There is one special situation that effectively blocks simultaneous download of several entities. It happens when 90% of the client records belong to 1 or 2 entities. This setup slows down the synchronization 2-3 times.

 ————————————————————————-
*) A case study performed on a 350 MB database:
– productpricelevel, product, account ~ 4ms/record. The delays are caused by the server.
– Other entities either don’t have many records or are fast ~0.3 – 0.5ms/record
(The numbers were collected for a W7 desktop with i5, 2.4GHz.)