Getting Started with Microsoft Synchronization Services for ADO.NET


Data synchronization for occasionally connected applications is no easy task. Smart Client applications represent a highly responsive, rich user interface, but they can lose something when it comes to managing the flow of information across a distributed system. Data synchronization is the key to allowing data to flow between clients that are connected to the Internet/network on an intermittent basis. Microsoft is planning a February 2008 release of Visual Studio 2008; it will include a new synchronization framework with support for ADO.NET. This article will introduce you to this framework using the beta version of Microsoft Synchronization Services for ADO.NET, which works to synchronize an ADO.NET-compatible server database with a SQL Server Compact Edition (version 3.5) client database. The sync framework attempts to simplify a complex problem into manageable pieces that can vary depending on the intricacy of your synchronization scenario. This article will walk you through the steps to creating your first synchronizable application. The example will show you how to configure your application to synchronize with a SQL Server 2005 database via a web service abstraction layer. It will also explain the required synchronization data points as well as how to introduce your own data parameters into the sync framework.

In this article, you will create a sync example to “manage” the favorite items of customers. The items list will be maintained at a server level (presumably by some item-inventory administrator). Therefore, updates will be pulled down from the server to the client, but the client will never push up any additions or updates to the item list. The customers and their favorite items will be managed at a client level. You only want the customers for which a particular user is responsible to be pulled down from and updated to the server (for reasons such as security, network efficiency, size on the client computer, and so forth). The form in my example does not represent a finished management application, but instead allows you to see the difference between what is available in the client computer’s local database and the server database.

Prerequisites and Setup

In this sample, I will use Visual Studio 2005, C#.NET. Microsoft SQL Server 2005 acts as the backend database server. You must have the Microsoft Synchronization Services for ADO.NET. You can download it here and install it. The package includes SQL Server Compact Edition 3.5 (which serves as the client databases) as well as the synchronization libraries for ADO.NET.

The Server Database

For this application, you will define four tables in your backend database. They are User, Customer, Item, and CustomerFavoriteItem. The database model is as follows:

Figure 1: Sync example server database model

Notice that each table has four common fields: UpdateTimestamp, InsertTimestamp, UpdateID, and InsertID. These fields are special sync services fields you must declare to track information that the clients will use to determine what should be updated in the local database. These fields are outlined here:

Field Name Data Type Default Value or Binding
UpdateTimestamp timestamp  
InsertTimestamp binary(8) (@@dbts+(1))
UpdateId Uniqueidentifier (‘00000000-0000-0000-0000-000000000000’)
InsertId Uniqueidentifier (‘00000000-0000-0000-0000-000000000000’)

Table 1: Sync services table fields

For each table, you also must have a “tombstone” table. A tombstone table is a placeholder for deleted entries so that when the client syncs to the server database, it knows which records to delete in the local database. You will create a delete trigger on each table so that it will insert deleted rows into the table’s tombstone. The tombstone table generally consists of the main fields of the original table (minus the four sync services fields) as well as two tombstone-specific sync services fields. These fields are DeleteId and DeleteTimestamp. You will name your tombstone tables the same name as the original table with “_Tombstone” appended to the end (for example, Customer_Tombstone). The fields are outlined here:

Field Name Data Type Default Value or Binding
DeleteId uniqueidentifier (‘00000000-0000-0000-0000-000000000000’)
DeleteTimestamp timestamp  

Table 2: Sync services tombstone table fields

Create the delete trigger on each of the four tables. If you are using SQL Server, you can use the “Delete Trigger Script.sql” script that’s included with the source code to create the triggers.

Note: If you use the same naming conventions for the sync services special fields and the tombstone tables as is outlined here, sync services will work by default. Otherwise, you will have to do some additional configuration in your sync layer.

More by Author

Must Read