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.