Blog

Occasionally Connected Database Applications with Microsoft Sync Services

Occasionally Connected Database Applications with Microsoft Sync Services

It is often desired to have "occasionally connected" applications where a device may not always be connected to a network and that makes the task of maintaining a central data store such as SQL Server 2008R2 consistent across multiple devices and users an extremely challenging task. Fortunately, Microsoft has created Microsoft Sync Services (sync services) to address this problem. Sync services is a way of using a local database cache built on Microsoft SQL Server Compact Edition. The desired data is stored in the local database and synchronized back to a central data store. This often means a variant of SQL Server but it is possible to write a synchronization provider for any data source.

If you are using Visual Studio 2010, much of the setup is created for you and is outlined in this walkthrough on Creating an Occasionally Connected Application. I found the lack of consistency in sample databases between the various walkthroughs on this topic confusing but most of the information is there. It is not obvious from the walkthrough that you should connect to the local copy cached copy of your database and then use the synchronize command to synchronize the data back to the central store.

The second thing that wasn't obvious was the need to enable upload explicitly in the code-behind of the sync provider by setting bidirectional mode for the tables which need two-way synchronization. Fortunately, there is a walkthrough for this problem as well in Extending the Local Database Cache to Support Bidirectional Synchronization. Pay particular attention to the section on "Removing Server Tracking Columns from Synchronization Commands" if you are not using SQL Server 2008 or above as you will have to add extra code to prevent synchronization errors.

Finally, you have to handle data conflicts and errors. The first step is to design your architecture such that conflicts are unlikely to begin with but there are still cases in any application where conflicts can occur.  Understanding Data Conflicts and Errors outlines the steps necessary for handling these problems when they arise in your application.

Our latest application to use this functionality is a timer replacement for the Quickbooks timer application. By creating our own application to replace the mid-90s application we were able to give our engineers the ability edit time offline (something the alternative web-based solutions could not do), write our own export code directly into Quickbooks using QODBC which saves an hour a week of management time, and provide custom user interface functions that dramatically improved the time entry process for all of our staff.

Learn more about DMC's Microsoft consulting services.

Comments

Rafael Azevedo
# Rafael Azevedo
Hi, Leon.

My dream is finding some article if there is some way to design an occasionally connected application where both client / server can insert/update/delete data using some kind of primary key that doesn't make me losing performance. I have tried int pks using pks ranges for each client, but it is hard manage ranges. I have tried GUID - unique idenfiers - but it results unnaceptable lost of performance.

Can you talk more about your experience designing this kind of application. I would be very thankful!

Post a comment

Name (required)

Email (required)

CAPTCHA image
Enter the code shown above:

Related Blog Posts