Using and configuring the Microsoft sync framework 2.1 for a real O.C.A

Standard

Editors note: Today’s article comes from one of my colleagues, Nic Cogotti, describing his experience in deploying the Microsoft Sync framework to synchronise a large customer database enabling an enterprise mobility scenario for one of UI Centric’s customers.

In the following article I will illustrate a real application of Microsoft Sync Framework to implement the Synchronization of an OCA based on a large database.

During development, one of my biggest problems was that all the documentation and examples I found on the internet regarding the Sync Framework were actually too simple and could not be applied to the scenario I was facing.

In particular the main challenges in front of me were:

  • Synchronize up to 120 users at the same time
  • Each user had an average database to be initially synched of 250 MB
  • The online database was very big (around 4GB) with some tables containing around 27 million rows
  • Ideally each user should be able to synchronize in around 20 minutes

With these requirements I could not find any similar situation on internet and many problems I encountered were difficult to solve because of this.

For this reason I will not be focused on describing how the Sync Framework works and what it does (there are very good articles out there that already treat this argument much better than I could possibly do like for example this one) but I will illustrate how I had to use it in the situation previously described and what I had to do in order to get the results I wanted.

In my last project I had to redevelop the Synchronization mechanism of an OCA application based on a large database.

The first version of synchronization was based on downloading all the data locally in a (complex) folder structure and files. This cache is for client use only and contains the data the UI will use in offline mode but does not have any relation (structurally speaking) with the remote DB.

Once the client is fully synchronized the user will be able to work in offline mode and the application will record each operation that updates the local cache. At this point the users will be able to upload the changes to the remote server but they will not be able to download changes that other users have performed.

This is because the old Sync architecture does not have a change tracking system so the user will be forced to re-sync the client completely from scratch in order to be updated with the latest changes made by other users.

A schema of how the overall solution is structured could be demonstrated by the following diagram (client-side):

nic pic 2

 

In order to achieve the requirements we have discussed previously, the server side of the sync framework has been deployed on a Virtual machine in Azure with this configuration:

  • Intel Xeon CPU E5-2660 @2.20GHz (8 core processor)
  • 28 GB Memory
  • SSD 500GB

The server requires a discrete amount of memory when a client sends a sync request in order to calculate the changes (for this reason I decided to provision a good amount of memory as this phase for a single request can reach peaks of 1GB of memory).

To relax the operation I have enabled the batch mode on the service in order to send the changes in batch of 204 Kb to the client (we will speak more about the batch mode in the sync framework later in this article).

nic pic 1

Figure 1OCA diagram with sync framework

After deciding the server hardware configuration, the most important thing is to adapt the online database to be Sync framework friendly. This operation is strictly related to the application requirements.

This adaption of the database is also the most time consuming operation especially when we deal with large databases, this because in order to set up the framework the first thing to do is provision it and here I had to face my very first big problem with the Sync Framework.

I was unable to provision the database with the Sync Framework toolkit as the size required was too big, causing the toolkit to constantly fail with a timeout exception during the provision operation.

In order to avoid and fix this problem I had to open the source code of the toolkit and modify it increasing the timeout property to allow the app to sync big databases.

To do so open the sync framework toolkit source code, go in SyncSvcUtil project and look for SyncSvcUtil.cs, open it and go line 185 and add prov.CommandTimeout to a value big enough to provision your database without connection time out (in this example one hour or 3600 seconds).

if (selectedConfig.SelectedSyncScope.IsTemplateScope)
{
	if (!prov.TemplateExists(selectedConfig.SelectedSyncScope.Name))
        {
        	Log("Provisioning Database {0} for template scope {1}...", selectedConfig.SelectedTargetDatabase.Name, selectedConfig.SelectedSyncScope.Name);
                prov.CommandTimeout = 3600;
                prov.Apply();
	}
	else
	{
		throw new InvalidOperationException(string.Format("Database {0} already contains a template scope {1}. Please deprovision the scope and retry.", selectedConfig.SelectedTargetDatabase.Name,
		selectedConfig.SelectedSyncScope.Name));
	}
}

Now you will be able to happily provision your big database.

Once the database has been provisioned is very difficult to change it and most of the time the easiest and fastest solution (relatively fast as it takes up to one hour) is to de-provision and re-provision the entire database.

This part of the process, as said before, is very specific but after many tests I found some guidelines that were very useful to me.

  • Divide the Database in 2 big categories: Data common to every users (I call it universal data database) and specific to the user (I call it user data database)
  • Create a scope for each category found
  • For the user data database try to use simple filters (even if this requires adding a column to the database in order to keep the reference for the filter). Remember that the filters are resolved during the GetChanges event and this is the heaviest (computationally speaking) part of the sync process.
  • If you have to use complex filters (I had to use custom filtering) NEVER USER THE IN statement to perform a join, It is easy and it comes natural to think about it but the performance is terrible. The use of EXISTS is much faster especially for big tables.
  • All the tables that the client can write to must have a GUID as primary key, not an auto generated id. This to avoid problems when the client needs to upload changes.

 

These 5 guidelines allowed all the users to successfully sync my 4 GB Database

After days spent studying the best way to provision the database and after deploying the service on my virtual machine the results I was getting were far from satisfactory. Here what I was facing:

  • The synchronization process was not reliable. Sometimes the synchronization was successful other times I could not pass the GetChanges stage.
  • I found that, when I was getting stuck in GetChanges If I refreshed the synchronization I was able to download the changes.
  • The batch mode was not very clear and the choice of one value was more a guess than an aware decision

The cause of these problems was mainly the amount of data that I needed to synchronize but I could not tell where the problem was. The only thing I could say was that the sync Framework was correctly installed (the diagnostic console was up showing that everything was correct) and that I could sync even if sometimes I was getting stuck at GetChanges.

Looking the source code of the server side of the sync framework I have notices a class called SyncTracer. Looking on internet I found that this class was used (as it was clear from the code) to trace any operation that was happening on the server during the synchronization process.

The only problem was that I could not get it working. I tried every single configuration I found on internet (included the one that Microsoft says to use) but I could not get the SyncTracer to log the operations.

Finally one day I found the right configuration to put in the web.config file.

<system.diagnostics>
   <sources>
      <source name="System.ServiceModel" switchValue="Information, ActivityTracing" propagateActivity="true">
         <listeners>
            <add name="traceListener" type="System.Diagnostics.XmlWriterTraceListener" initializeData="C:\MyAppName\SYNCService\syncTraces.svclog"></add>
         </listeners>
      </source>
   </sources>
   <switches>
      <add name="SyncTracer" value="1"/>
   </switches>
   <trace autoflush="true">
      <listeners>
         <add name="SyncListener" type="System.Diagnostics.TextWriterTraceListener" initializeData="C:\MyAppName\SYNCService\syncinf.txt"/>
      </listeners>
   </trace>
</system.diagnostics>

Make sure that the path specified in the initializeData property will exist in your VM and restart the service. At the next Synchronisation request we will see the log in C:\MyAppName\SYNCService\syncinf.txt.

Note the line <add name="SyncTracer" value="1"/>, this specify the level of information we want from the tracer, possible values are:

0 off No messages to trace listeners.
1 error Only error messages to trace listeners.
2 warning Error and warning messages to trace listeners.
3 info Informational, warning, and error messages to trace listeners.
4 verbose All messages to trace listeners.

 

Setting the tracing at level VERBOSE I could see basically every step the sync service was doing as soon as it receives a request of synchronization.

Analysing the report I could get very interesting information regarding the configuration of the sync service especially for the batch size.

The batch size is the minimum row size of our database (in sync framework view the database is the scope or the template scope we are synchronising at that moment), during the “get changes” step, the service populates in memory (that why we reach picks of 1 GB of memory for one request) the information according to the batch size and when it will reach the limit specified by the batch size, writes these information on a file.

Many rows can be contained in the same batch but 2 different batches cannot have the same row (in other worlds a row is atomic and cannot be divided in 2 different files) that’s why we have batches of different sizes.

Changing the batch size does not improve the time of calculating the changes or the download time, it only changes the size of every single batch, therefore the ratio at they are sent to the client.

Apart from this important information, the tracer gives a better understanding of how the sync framework works and in my case revealed why sometimes the synchronisation process was getting stuck during the GetChanges phase.

This phase is not only the most complex and the one that requires the most memory, but it is also the one that involves the database the most.

It is at this point that all the GetChanges stored procedures created by the sync framework utility at the end are called.

The problem (for me) was that during this phase the connection with the database timed out when the GetChanges stored procedure of a particularly big table was called.

This table was 50,000 rows (after the filtering) and the connection time out of the sync framework is set to 30 seconds by default.

With the version 2.1 of the sync framework there is the possibility to overcome this problem setting the CommandTimeout of the syncprovider in the server side.

Opening the source code of the server side I was able to set this property when the syncprovider was created.

Class SqlSyncProviderService in SqlProvider folder

private static SqlSyncProvider CreateSqlSyncProviderInstance(string clientScopeName, string serverConnectionString, string syncObjectSchema)
        {
            var sqlSyncProvider = new SqlSyncProvider(clientScopeName, new SqlConnection(serverConnectionString));

            if (!String.IsNullOrEmpty(syncObjectSchema))
            {
                sqlSyncProvider.ObjectSchema = syncObjectSchema;
            }
            sqlSyncProvider.CommandTimeout = 3600;
            return sqlSyncProvider;
        }

After Rebuilding the SyncServiceLib and deploying to the VM I was able to synchronise my scope without any problem and without the need of any timer to overcome the GetChanges problem I have illustrated before.

In regards to the client side of the sync framework I used a wonderful library written by Mimetis and that is available to be used as Visual Studio 2013 package.

The only thing I can say is this is a very well made library that makes the client part of the synchronisation very easy and almost configuration free for the client application. If you have to sync a Windows modern app or a Windows Phone app with Sync framework toolkit this is the library you should be using for sure.

Your other option would be to write your own custom sync client provider, which of course would take substantially more time and effort compared to using a library you know already works.

LINKS:

Download client library

How Sync Framework works and creating your own sync provider

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>