Microsoft Sync Services 1.0 Books Online CTP

Microsoft promised documentation for the Microsoft Synchronizaton Services CTP would be available in "early February" when the Occasionally Connected Services (OCS) team released the Microsoft Synchronization Services for ADO.NET v1.0 CTP (OCS-CTP) on January 22, 2007. Surprise! The OCS team posted the Microsoft Synchronization Services for ADO.NET Books Online Community Technology Preview (OCS-BOL) on February 12, 2007. The download site offers SYNCSVCSBOL-ENU.msi, the 16.7-MB installer for OCS-BOL and SyncServicesCtpSamples.exe, which generates a "Two-Tier Download-Only Sample" application. The sample app synchronizes the Adventure Works sample database's Products table with an SQL Server Compact Edition (SSCE) client database. You need the OCS-CTP, which also installs an updated SSCE v3.5 CTP, the Orcas successor to the SSCE v3.1 RTM bits, which Microsoft released on January 11, 2007. (The prerequisites are the same as those for Rafik Robeal's four OCS demo applications that I've previously described here and here, plus a current version of AdventureWorks.) The SyncComponents_Sample1 Project SyncComponents_Sample1.sln is a C# project that generates a new 276-kB SSCE 3.5 database from AdventureWorks' Customer, Employee, Product, ProductModel, ProductSubcategory, UnitMeasure, SalesOrderHeader, SalesOrderDetail, and Store tables. Synchronization is DownloadOnly, so changes only propagate from the server to the client. Here's an overview of the steps required to test the sample project:

1. Prepare the server tables for synchronization. Execute the ServerDb_CreateSyncObjects.sql script to add InsertOriginatorID, UpdateOriginatorID and CreateDate tracking columns, a TableName_Tombstone table, and a trgDel_TableName delete trigger to each of the nine tables.
2. Create the SSCE database and populate its tables. Build and run the project, and then click the Synchronize button. The DataGridView displays changes, not rows added for initial table population.
3. Add insert, update and delete stored procedures. Execute the ServerDb_DMLSyncObjects.sql script to create uspProductInsertCommand, uspProductUpdateCommand, and uspProductDeleteCommand stored procedures.
4. Make changes to the server data. Execute the stored procedures a few times to generate changes to the Product table.
5. Synchronize the client. Click Synchronize to pull and display the server changes.

The following screen capture reflects inserting 21 rows with the uspProductInsertCommand.

This sample project won't win any awards for comprehensive coverage of Sync Services code. Unlike Rafik's demos, it doesn't support Bidirectional synchronization. On the whole, Rafik's projects provide a much better overview of Sync Services programming techniques. Tip: If you receive an unhandled exception that starts with "Table '[ProductSubCategory]' could not be created," you have an old AdventureWorks version in which the the ProductSubCategoryID had the smallint data type. The GetSchema method attempts to add it to the SSCE ProductSubCategory table as a smallint identity column, which SSCE doesn't suport. Update your AdventureWorks database from the link provided. You must delete the AWClientDB.sdf file in the \Program Files\Sync Services\Samples\SyncComponents_Sample1\SyncComponents_Sample1.Client\bin\Debug folder before you can successfully populate the client database. Technorati tags: SSCE, SQL Server 2005 Compact Edition, SQL Server Compact Edition, SQL Server CE, Orcas, Occasionally Connected Systems, OCS, Microsoft Synchronization Services, Sync Services.