email article printer friendly get the code more resources

Update Local Data Caches with Sync Services
The Microsoft Synchronization Services 1.0 API for SQL Server 2005 Compact Edition and the new Sync Designer in the Orcas March 2007 CTP team up to generate a local data cache that synchronizes incrementally and bidirectionally with SQL Server [Express].
by Roger Jennings

May 25, 2007

Technology Toolbox: VB .NET, C#, SQL Server 2005 SP2

Network connectivity isn't ubiquitous, despite recent claims to the contrary. Mobile information workers often end up offline for frustratingly long intervals. "Unable to connect to server" or "Connection dropped" messages when attempting to close an order drive road-warriors nuts.

Microsoft is close to providing a pair of technologies intended to improve mobile users' morale. The first is the Microsoft Synchronization Services (Sync Services) 1.0 Application Programming Interface (API), which is available for VS 2005 as a Community Technology Preview (CTP) and is part of the Orcas March 2007 CTP. The second is the Orcas's preview of the new Local Database Cache template (Sync Designer) for C# and VB projects. The Sync Services and Sync Designer combination makes it easy for developers to add lightweight, high-capacity data caches to smart-client applications. This new cache technology, which depends on SQL Server 2005 Compact Edition (SSCE) v3.5, improves client performance, minimizes server load, and helps reduce data latency. What's more, SSCE v3.5 and the Sync Services API are freely distributable; you can use any relational database or other data source you want as the server. Sync Designer will be a component of VS vNext and will be limited to SQL Server 2000 or 2005 as the server, but will also work with SQL Server 2005 Express. This version of Sync Services won't run on the .NET Compact Framework 3.5 for devices and SSCE v3.5 has a maximum database size of 4 GB. However, Microsoft's sync services developers plan to lift these restrictions in a post-Orcas release.

My "Lighten Up Local Databases" article [VSM March 2007] described SSCE v3.1, which Microsoft released for all Windows platforms on January 12, 2007 (read the article here). Now I'll show you how to implement the Sync Services API with SSCE v3.5 the hard way in VS 2005 and the easy way with the Sync Designer in the Orcas March 2007 CTP running on Windows XP or Windows Server 2003 SP2. Two downloadable VB projects illustrate how to code the Sync Services API and Sync Designer classes (download the projects here). The first project is SyncDesignerTest.sln, a simple example of one-way synchronization of lookup data. The second project is SSCESyncDesigner.sln, a complex, bidirectional Sync Services test harness. The Sync Designer isn't compatible with Windows Vista, and Vista isn't a supported operating system for the March CTP. According to Microsoft's Steve Lasker, who's spearheading Sync Services and Designer development, the problems are the result of changes between the .NET Framework 3.0 bits in Vista and the 3.x bits in Orcas (Additional Resources).

Microsoft's distributed systems architecture has included support for offline computer users since 1997, when Active Data Objects (ADO) 1.5 and Microsoft Data Access Components (MDAC) 1.0 introduced the disconnected Recordset. MDAC 2.0 enabled persisting rowsets as files in Advanced Data Tablegram (ADTG) format, and MDAC 2.5 added XML as a persistence file format. Persisting DataSets in local XML files enabled ADO.NET to store and load much larger tables. WriteXml and ReadXml methods of DataSets or DataTables store lookup or catalog data in the local file system, but these methods don't support incremental changes and the technique scales poorly. The time to load large lookup tables on app startup increases dramatically as the size of their XML files grow. For more information on this effect, see my article, "Store Large Lookup Tables in DataSets" [VSM July 2004] (read the article here).

Keep Local Tables Up-To-Date SQL Server 2000 and 2005's merge replication or Remote Data Access (RDA) lets mobile users keep local SQL Server Compact Edition (SSCE) database tables up-to-date. SQL Server has supported database replication from licensed server instances since 1995's version 6.0 and to desktop instances since the original Microsoft Data Engine (MSDE) 1.0. MSDE 1.0 and 2000 can act as merge replication publishers, but SQL Server 2005 Express supports only merge replication subscriptions. Merge replication requires SQL Server 2000, SQL Server 2005 Workgroup Edition or higher, and replicates to PC clients and devices through LAN or Web connections.