OakLeaf Systems: A Sync Services Bidirectional Test Harness

Friday, March 23, 2007

A Sync Services Bidirectional Test Harness

The Microsoft Synchronization Services (Sync Services) 1.0 API (runtime) requires a substantial amount of developer-authored code to define the basic elements required to perform bidirectional synchronization between an SQL Sever 2005 Compact Edition (SSCE) client and an SQL Server 2005 [Express] or other RDBMS server. The VB code (without empty lines or comments) to implement bidirectional synchronization for a pair of simple tables is about 95 lines if you take advantage of the runtime's CommandBuilder or about 165 lines if you don't.

Beta 1 Update May 15, 2007: The test harness was built and tested with the Orcas March 2007 CTP. Upgrading the project to Beta 1 exposed some new Sync Designer and SSCE v3.5 problems that prevent its operation. There is no effective workaround available at this time. (See my Sync Designer/SSCE Version Problems with Orcas Beta 1 post in The Microsoft Synchronization Services for ADO.NET forum.) The Sync Services team says that the Sync Designer problems are "fixed in a later build" and "most changes to runtime and designer are coming in beta 2.0." The SSCE team hasn't replied regarding fixes for compatibility problems with SQL Server Management Studio and Server Explorer, which another forum participant has experienced. I'll update this post when an upgraded test harness becomes available for download from the Visual Studio Magazine site.

Background

The Sync Services Designer that debuts in the Orcas March 2007 CTP greatly reduces the effort required to get a simple unidirectional service up and running. Completing a couple of simple forms generates a DatabaseName.sync XML document and VB or C# class file with all but two lines of code required to produce a simple one-way, download-only synchronization project. The price you pay for automating the service design process is the requirement to use SQL Server 2005 [Express] (or SQL Server/MSDE 2000) as the server RDBMS. The Sync Services 1.0 runtime is server-agnostic, as demonstrated by Rafik Robeal's use of Oracle 10g Express in his Demo V: Offline Application - Oracle Backend C# project.

All of Rafik's Sync Services runtime demos use simple orders and order_details tables with random primary key values. The orders table has order_id and order_date columns and order_details has order_id, order_details_id, product (name), and quantity columns. Both tables use order_id (int) as the sole primary key column, which has as PK_orders or PK_order_details (PK, Unique, Non-clustered), and UQ__orders__##### or UQ__order_details__##### (Unique, Non-clustered) indexes. This selection of keys prevents establishing a one-to-many relationship between orders and order_details tables, so there are no foreign key fields.

Update: 3/24/2007: Rafik added a Deep in Sync: Handling PK-FK Constraints post to The Synchronizer blog yesterday. This post explains Rafik's reason for not including PK/FK relationships and details the workings of—and settings for—these relationships in detail. Sync Services interprets the first table in the addition of client-side SyncTables and server-side SyncAdapters to their respective collections as the parent table. Adding related SyncTables to the SyncGroup that's attached to the SyncAgent assures that Sync Services processes the table changes as a unit.

A Sync Services Bidirectional Test Harness (Work in Progress)

Here's a preview of my Sync Services test harness, which (as usual) uses the Northwind Orders and Order_Details tables to emulate pseudo "real world" order and line items data. There's a one-to-many relationship (FK_Order_Details_Orders) between the identity primary key (OrderID) on the Orders table and composite primary key (OrderID, ProductID) on the Order_Details tables of both the client and server database. One of the purposes of the test harness is to determine whether it's practical to use Sync Services to replace merge replication for master/child tables. (This question remains unanswered at present, but the ability to replace RemoteDataAccess (RDA) seems assured).

The test harness project is in development at present; the downloadable sample code will accompany an article for Visual Studio Magazine's May 2007 issue. Click the images to display a full-size version.

Figure 1 - SQL Server 2005 Compact Edition Client Cache Page

The Client above and Server (below) pages enable selecting automated UPDATE, INSERT, or DELETE operations and provide rapid comparison of the latest additions to the client and server tables. You type the number of Orders and Order Details records in the text boxes and then click the Random Insert/Update/Delete button to apply the changes to the SSCE tables. Updates randomly alter the EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, and Freight field values of the Orders table and ProductID, Quantity, Unit Price, and Discount of the Order_Details table. Inserts add a random selection of a CustomerID value from the Customers table.

Clicking Synchronize starts the synchronization process. You can select from three methods of handling synchronization data conflicts on the client page.

Clicking the Add FK Constraints button adds a DataRelation between the Orders and Order_Details tables to the database and the NorthwindDataSet. (By design Sync Services doesn't add DataRelation(s) during the database and table creation process, and a problem with the DataSet Designer prevents persisting changes.) Code adds or updates the LastEditDate value to the Orders table (not shown) and the Order_Details table.

Figure 2 - SQL Server 2005 Express Server Data Source Page

Figure 3 - Client Schemas and Sync Statistics Page

The schemas and statistics page has a DataGridView control to display SSCE INFORMATION_SCHEMA "views" (actually tables). The Client ID combo box and Set button are for testing behavior of SSCE's identity feature. Text boxes display sync statistics and CommandText property values for all operations. You can copy the commands to Notepad for better visibility.

Figure 4 - Sync Payload Page