Introduction
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.