Simple Talk is now part of the Redgate Community hub - find out why

Some Data with your Application Take-away, Sir?

Prasanna is reminded of pizza in cardboard boxes when he explores the possibilities of Synchronization Services for ADO.NET. Data applications are becoming increasingly mobile, and often disconnected, and data is often wrapped up to be consumed later. Miccrosoft's Merge Replication was their first, and most heavyweight, solution, but now comes a different tack, based on SQL Server 2008 and ADO.

Offline data for occasionally connected applications


Whenever I go into a fast food centre to order food, I get a couple of choices: ‘to-go’ (take-away) or ‘have it here’ (eat-in). Because I usually need to get back to work quickly, I mostly choose to-go (take-away) and take the meal back to my home or work place. Most of the smart client applications you develop nowadays are to-go. You take the application with you on a laptop or other mobile device that is sometimes disconnected from the corporate network where your data resides. The requirement to access data anytime anywhere, immaterial of whether you are connected or disconnected is increasingly important. This article talks about Synchronization Services for ADO.NET. This allows you to take your data ‘to-go’ along with your application. This article describes the version of Synchronization Services for ADO.NET that ships along with Visual Studio.NET 2008 SP1.

Synchronization Services for ADO.NET

There are a few technologies that are already available for managing a local database along with your application. Microsoft has Remote Data Access and Merge Replication for the purpose of maintaining a local database and keeping it synchronized with a server database. Merge Replication is an important  server-based synchronization technology that supports many advanced features. Remote Data Access is a rapid application development technology that can be used to quickly create synchronization to a database. Synchronization Services for ADO.NET brings in most of the rapid application development features of Remote Data Access and at the same time supporting many advanced scenarios for synchronizing data.

Synchronization Services for ADO.NET is part of the Microsoft Synchronization Framework. Microsoft Synchronization Framework is a broader platform that is useful for more than just database access. It provides synchronization capabilities for many other services: For example using the Synchronization Framework you can programmatically synchronize folders, outlook contacts, feeds etc. For the purpose of this article we will be looking at Synchronization Services for ADO.NET.

Create a Local Database Cache

We’ll work with the a simple example of a database table named Customer that stores customer details and is available in a SQL Server 2008 database in the server.

The smart client application that we create will not always have access to the customer data in the server. So we need to provide a way for the data to be available not only for offline usage to the smart client application, but also to sync the data with the server database when online. To accommodate this, we can make our smart client application rely on an intermediate source to retrieve and update data instead of the server database.  So we’ll go ahead and add a Local Database Cache, named LocalDataCache.sync, to our smart client application. When you add a Local Database Cache to your application, the Sync Designer would be prompted to enable you to configure the settings for the data synchronization.


In the above window we have configured the server database connection to the SQL Server 2008 database. We have also configured the client database connection that indicates a new SQL Server Compact Edition database will be created to store data locally. We have also indicated that we want to use the SQL Server change-tracking feature to track changes on the data. SQL Server change-tracking is a new feature in SQL Server 2008 that allows us to track changes in the database tables without altering our server database schema so as to keep track of inserts, updates and deletes. We can also opt to use our own columns in the tables rather than to use SQL Server change-tracking. Next we can add tables from the server database that we want to make available offline


Since we opted to use SQL Server change-tracking, the options to configure change-tracking for the Customer table is disabled since it would be internally handled by SQL Server. If we do not use SQL Server change-tracking, we need to create columns in the server database to identify updates and inserts and we need to create a table within the database to move all deleted items to.

The Local Database Cache creates a SQL Server Compact Edition database to store data for offline use and generates the following code to synchronize the local database with the server database.

C# Code:

The generated code includes a sync provider for the SQL Server CE database (LocalDataCacheClientSyncProvider) and a sync agent (LocalDataCacheSyncAgent) for the client end. It also includes a sync provider for the SQL Server database (LocalDataCacheServerSyncProvider) and sync adapters for each table (CustomerSyncAdapter) we made available offline at the server end. The designer also generates SQL scripts for the changes made on the server database, for example, in this instance, enabling change-tracking for the Customer table.

To synchronize data we can use the LocalDataCacheSyncAgent as shown below

C# Code:

The above code will synchronize the data and provide statistics on the synchronization providing information such as the start time, completion time and the changes applied. By default the synchronization downloads the changes that have happened on the server database from that database  to the client. You can set the SyncDirection for the Customer table to be bidirectional to indicate that the sync process should download and upload changes to and from the local SQL Server CE database.

C# Code

 The LocalDataCacheSyncAgent performs the synchronization by using an instance of a local provider (the generated LocalDataCacheClientSyncProvider) and an instance of a remote provider (the generated LocalDataCacheServerSyncProvider). The LocalDataCacheSyncAgent collects the changes from both databases and applies the changes using the providers at either end.

We can get a reference to these providers to intercept the data sent for synchronization or to monitor the progress of the synchronization process or to resolve conflicts during synchronization. For example the following code gets a reference to the providers at the client and server ends

C# Code:

When we invoke the Synchronize method of the LocalDataCacheSyncAgent, the incremental changes from both the providers would be downloaded and applied at either ends. The providers also expose events that can be used to add custom event handling code during synchronization. For example the following code hooks an event handler to the ApplyChangesFailed event handler of the provider at the server end to log any conflicts during synchronization

C# code

Create a SOA Friendly Data Cache

One big advantage of the ADO.NET sync services is that it is service-oriented architecture (SOA) friendly and can be easily altered to support different types of application. Let’s, for example, assume that the smart client application we created will not have access to the server database directly and we need to perform the synchronization through the HTTP protocol when the smart client has connectivity. We can easily create an additional layer between the smart client application and the server database to support this scenario. So let us add a WCF Service application as the server application and alter our smart client application to sync the data using the WCF service instead of syncing directly with the server database.

After adding the WCF service to the same solution as the smart client application, let us open the sync designer for the LocalDataCache.sync in our smart client application. When you expand the advanced section in the designer you will notice we can designate separate applications for the client and the server portions of the sync components. We’ll configure it, as shown below, to include both the server components in the WCF service application and the client components in the smart client application.


This will create the following service contract and service implementation in our WCF service to handle the synchronization

C# Code:

The designer will also move the CustomerSyncAdapter and the LocalDataCacheServerSyncProvider from the smart client application to the WCF service. The generated code for the WCF service implementation wraps the LocalDataCacheServerSyncProvider and provides its functionalities through the service contract. This would mean that the RemoteProvider property of the LocalDataCacheSyncAgent in the client application that had a reference to the LocalDataCacheServerSyncProvider earlier would have a null reference now since the LocalDataCacheServerSyncProvider was moved to the WCF service.

To enable synchronization from the client, we need to set the remote provider to invoke the WCF proxy from our smart client to communicate with the service for synchronization. Once we add the reference to the WCF service, we can alter the synchronization code as follows:

C# Code:

In the above code we are setting the remote provider of the LocalDataCacheSyncAgent to use the LocalDataCacheSyncContractClient client proxy as the provider for synchronization. When we call the Synchronize method, the WCF service would be invoked to retrieve changes from and apply changes to the server database.


Smart client applications these days require offline capabilities so as to continue to work even when disconnected. An ADO.NET synchronization service provides an extensible framework to easily create local databases and keep them synchronized with server databases. It also makes it easier to change the synchronization architecture to support different types of synchronization such as synchronizing through a WCF service.

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.


Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.