VSM Cover Story

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
• 05/01/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 described SSCE v3.1, which Microsoft released for all Windows platforms on January 12, 2007. 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.

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

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.

Rafik Robeal is a software-design engineer (a.k.a. "über dev") on the SQL Server Replication and Synchronization team who runs The Synchronizer blog on MSDN and manages the SyncGuru Web site. He describes SQL Server merge replication as, "...a powerful replication solution for synchronizing data between SQL Server Databases [that] evolved over time to cover very complex and demanding replication scenarios." However, managing merge replication requires membership in the system administrator role, developers have little leeway to customize the replication process, and using SQL Server 2005 as a publisher requires at least a Workgroup Edition license. Merge replication needs several triggers and stored procedures added to the tables, which must have a ROWGUIDCOL column to identify rows uniquely, plus more tables to track modifications. Steve Lasker explains: "Merge is very much a DBA focused synchronization technology. It's very feature rich, but limited to SQL Server and doesn't expose as many knobs as Sync Services. In its most simplistic terms, Sync Services are a componentized model of merge replication that can be utilized with any ADO.NET data provider. As one would expect, we'll have the best support with SQL Server as we do know a bit more about it than other databases. Best means we'll have performance enhancements within SQL Server Katmai that will know how to be efficient about tracking changes."

RDA is a much simpler, lighter-weight process, but is limited to push or pull table-at-a-time updates with Internet Information Services (IIS); optionally, RDA can track table changes locally and push the changes back to SQL Server. Full-table pull updates will create and populate the table if it doesn't exist; adding a primary key and indexes is optional, but RDA doesn't support incremental updates. The connection string includes the SQL Server login ID and password, which is sent with each operation request. Steve Lasker says, "RDA is a very simplistic sync technology that doesn't require any server side schema changes. It also doesn't offer very many features. We are not investing in RDA, and would expect it to go away in the future at some point. We actually used RDA as the [Sync Services] model for simplicity and developer productivity. Sync Services essentially replaces the RDA scenarios [for PC clients]." As mentioned earlier, it's a good bet that Sync Services will replace RDA for devices eventually.

Implementing Sync Services requires DBAs to modify server database schemas to accommodate the synchronization process. Each synchronized table requires adding Created and LastEdited (timestamp or datetime) fields and a corresponding tombstone table to identify records deleted. CREATE, UPDATE, and DELETE triggers are required to add timestamp or datetime data to Created and LastEdited fields, as well as to the tombstone tables' Deleted field. Fields to identify the client that modified table rows are optional. Client tables require adding Created and LastEdited fields only. You might also need to add ROWGUIDCOL fields as the primary key for server and client tables if you support bidirectional synchronization with an int identity column to provide serial ID values, such as OrderID. Sync Services requires fewer schema modifications than merge replication, but RDA doesn't require any schema changes. The Sync Designer automates most server schema modifications.

Program the Sync Services API
Writing Sync Services-specific code to manage bidirectional synchronization requires that you understand how the members of the Microsoft.Synchronization classes fit into the grand synchronization scheme. The Sync Services API defines seven primary objects: SyncGroups, SyncTables, ClientSyncProvider, Sync-Agent, ServerSyncProvider, and SyncAdapters and DbCommands collections. In the two-tier configuration, the client side contains all Sync Services components. In the n-tier (distributed) configuration, which isolates the data access layer from the client, the server side contains the ServerSyncProvider, SyncAdapter, and DbCommands. The two-tier configuration also exposes a Windows Communication Foundation (WCF) service; SyncGroups and SyncTables collections on the client side; and a ClientSync-Provider, SyncAgent, and ServiceProxy for the WCF service (Figure 1). You connect the sync components in a manner similar to that for untyped DataSets, but Sync Services' more granular design makes it easier to implement a transport-agnostic, service-based architecture for syncing over VPNs or the Internet.

The Microsoft.Synchronization.Data.dll contains SyncAgent, SyncTables, and SyncGroups; the Microsoft.Synchronization.Data.Client.dll contains the ClientSyncProvider; and the Microsoft.Synchronization.Data.Server.dll contains the ServerSyncProvider and SyncAdapters. Two-tier projects require references to all three DLLs; n-tier projects require the Data and Client DLLs in the client-side project and Data and Server DLLs in the server-side project.

If you're working with the Sync Services API in VS 2005 SP1 or want to skip using Orcas's Sync Designer capability to write all but a few lines of the code for you, you can start two-tier VB projects by adding at least these Import statements:

Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe
Imports Microsoft.Synchronization.Data
Imports Microsoft.Synchronization.Data.Client
Imports Microsoft.Synchronization.Data.Server

Continue a two-tier project by adding a Synchronize button to the default Form1. In the button's Click event handler, create a Sync Agent and then create and attach a ServerSyncProvider and ClientSyncProvider with code like this:

Dim AgentSync As New SyncAgent()

'ServerSyncProvider
Dim ServerSyncProvider As New DbServerSyncProvider()
AgentSync.ServerSyncProvider = ServerSyncProvider
Dim cnnServer As New SqlConnection( _
	My.MySettings.Default.ServerConnectionString)
ServerSyncProvider.Connection = cnnServer

'ClientSyncProvider
Dim strConn As String = _
	My.MySettings.Default.ClientConnectionString
Dim ClientSyncProvider As _
	New SqlCeClientSyncProvider(strConn)
AgentSync.ClientSyncProvider = _
	CType(ClientSyncProvider, ClientSyncProvider)

Note that this code assumes that you have predefined connection strings for the SQL Server [Express] and SSCE databases. The code snippets in this section are slightly modified VB conversions of Rafik Robeal's C# code in the SyncForm.cs file of his OfflineAppDemo-Builder.sln project (here).

Next, create and add a SyncTable for each client table to the SyncTables collection in the order of its primacy in the relational hierarchy: parent, child, grandchild, and so on. The same approach applies to SyncAdapters for the server tables. DropExisting-OrCreateNewTable is one of five TableCreationOptions (Table 1). You also add a Bidirectional SyncDirection property value for each table; SyncDirection has three other choices: DownloadOnly, Snapshot, and UploadOnly (Table 2). The name you assign the SyncGroup, AllChanges for this example, isn't important, but must be unique if you have more than one SyncGroup:

Dim tblOrders As New SyncTable("Orders")
tblOrders.CreationOption = _
	TableCreationOption.DropExistingOrCreateNewTable
tblOrders.SyncDirection = SyncDirection.Bidirectional

Dim tblDetails As New SyncTable("Order_Details")
tblDetails.CreationOption = _
	TableCreationOption.DropExistingOrCreateNewTable
tblDetails.SyncDirection = SyncDirection.Bidirectional

Dim grpOrders As New SyncGroup("AllChanges")
tblOrders.SyncGroup = grpOrders
tblDetails.SyncGroup = grpOrders

AgentSync.SyncTables.Add(tblOrders)
AgentSync.SyncTables.Add(tblDetails)

Now use ADO.NET DataAdapter CommandBuilder-style SqlSyncAdapterBuilders to create the server SyncAdapter for each table and generate eight of the ten DbCommands required for each table (Listing 1 and Table 3). Add the remaining two commands to get the timestamp value for the NewAnchorCommand that SyncServices uses as the latest row modification time for incremental updates, inserts, and deletions:

' Select new anchor command with @@DBTS = the current 
' timestamp value
Dim cmdAnchor As New SqlCommand()
cmdAnchor.CommandType = CommandType.Text
cmdAnchor.CommandText = "SELECT @@DBTS"
ServerSyncProvider.SelectNewAnchorCommand = cmdAnchor

' Set ClientId = 1 (ServerId = 0)
' Sync Designer assigns a GUID to the ClientId value
Dim cmdClientId As SqlCommand = New SqlCommand()
cmdClientId.CommandType = CommandType.Text
cmdClientId.CommandText = "SELECT 1"
ServerSyncProvider.SelectClientIdCommand = cmdClientId

Finally, add event handlers for the SyncProgressEvent of the ClientSyncProvider and ServerSyncProvider, which fire for every change to each table at specific stages of the synchronization process (Table 4). Add another handler for the ClientSyncProvider's ApplyChangeFailed event, from which you can determine the reason for the failure with the value of the e.Conflict.ConflictType property (Table 5) and deal with it by setting the e.Action property value to ApplyAction.Continue, ApplyAction.RetryApplyingRow, or ApplyAction.RetryWithForcedWrite. A handler for the ServerSyncProvider.ApplyChangesFailed event is optional. The SyncAgent.Synchronize method initiates the sync operations you defined in the preceding snippets and returns SyncStatus information when the synchronization completes:

AddHandler ClientSyncProvider.SyncProgress, _
	AddressOf ClientSyncProgress
AddHandler ServerSyncProvider.SyncProgress, _
	AddressOf ServerSyncProgress
AddHandler ClientSyncProvider.ApplyChangeFailed, _
	AddressOf ClientApplyChangesFailed
AddHandler ServerSyncProvider.ApplyChangeFailed, _
	AddressOf ServerApplyChangesFailed 
Dim syncStats As SyncStatistics = AgentSync.Synchronize()

Test Drive the Orcas CTP's Sync Designer
Microsoft's inclusion of the long-awaited Sync Designer in the Orcas March 2007 CTP received little attention, although Steve Lasker mentioned it in his "DEV 343 - Smart Client: Offline Data Synchronization and Caching with SQL Server Everywhere Edition" Tech-Ed 2006 presentation. The fact that it is disguised as a Windows Forms' Local Database Cache template undoubtedly has something to do with the small amount of attention it has received thus far.

The Sync Designer does most of the heavy lifting of coding Sync Services instances for you. The Sync Designer autogenerates a DatabaseName.sync XML document and DatabaseName.Designer.vb or .cs file from the metadata of the server database, and the settings you apply in the designer's UI. The Database-Name.Designer.vb file defines partial public classes for Database-NameClientSyncProvider, DatabaseNameServerSyncProvider, Data-baseNameSyncAgent, and other related objects. These settings also add ClientDatabaseNameConnectionString and ServerDatabaseNameConnectionString connection strings to ProjectName.exe.config. The designer applies a default configuration to all objects from the designer's settings to minimize the amount of code you must add to test the project.

Completing the UI's wizard-like definition process starts the initial synchronization step, creates and populates the client DatabaseName.sdf SSCE database with the tables you mark for synchronization, modifies the server database's tables, creates tombstone tables, and generates a typed DatabaseNameDataSet for the client. The designer sets primary keys for the tables you synchronize, but doesn't add foreign key constraints to the tables or DataSet or indexes to tables. You can add foreign key constraints to tables with T-SQL DDL statements and DataRelations to the DataSet with code. (You can't save changes you make in the DataSet Designer for SSCE tables in the Orcas March 2007 CTP; this problem should be fixed in a later CTP or beta.) If you add a WCF Service Library project to your solution, you can split the server components into the project to create an n-tier application in which the client will have no direct knowledge of the server, its configuration, or connection strings. (Creating an n-tier app with the designer must wait for a later release, too.)

Give the Sync Designer a test drive for caching Northwind lookup data by starting a new VB or C# Windows project with the Windows Forms Template and add a new Local Database Cache item to start the Sync Designer. Change the cache name if you want (the cache file name is NwindClient.sync, in this example) and click on Add to open the designer's main Configuration Data Synchronization dialog. You probably have an existing Server Explorer connection to a SQL Server [Express] instance with the Northwind sample database. If you do, select the connection in the Server Connection list box; otherwise create a new connection to the database. Specifying the Server Connection enables the Client Connection named DatabaseName.sdf (new) and creates a new SSCE database in the project folder (Figure 2). Don't worry about settings in the Synchronization group box; timed synchronization doesn't work in the CTP and won't be included with future releases. Click on the Advanced button to display optional settings to assign server components to a WCF Service Library project.

Next, click on the Add Items button to open the Configure Tables for Offline Use dialog and mark the Customers, Employees, Products, Shippers, and Suppliers tables (Figure 3). The DataDirection list contains Download Changes Only and Download Entire Table choices; specifying Snapshot or Bidirectional synchronization requires adding code (Listing 2). Click on OK to return to the designer's main dialog, which displays the selected tables in the Cached Objects list and the Server Connection as ServerNorthwindConnectionString with a (My Settings) suffix for VB coders. Next, click on OK to create the Northwind.sdf database, add and populate the tables, perform an initial synchronization with a progress message box, and open the Data Source Configuration Wizard's Choose Your Data Source Objects dialog. Mark the Tables checkbox to include all five lookup tables in the Northwind DataSet.

If the Orcas March 2007 CTP's DataSet designer for SSCE databases didn't have a problem saving design changes, you'd add TableAdapters for the Orders and Order Details tables to the designer's surface by choosing Add and selecting the ServerNorthwind-ConnectionString in the TableAdapter Configuration Dialog's Choose Your Data Connection dialog. For this CTP, however, you must create a new ServerDataSet with Orders and Order Details tables to test the lookup tables.

Next, open the Data Sources window and change the control type of the Orders table's CustomerID, EmployeeID, and ShipVia fields and the Order Details table's ProductID field from TextBox to ComboBox. Then use the SmartTag to set the Orders table's Data Source, Display Member, and Value Member property values to the appropriate BindingSource and fields. Open the Order_DetailsDataGridView's Columns collection, change the ProduceID field's Control Type to DataGridViewCombo-BoxColumn, and make similar changes for the Data Source, Display Member, and Value Member property values.

Finally, add these two lines to the Form1_Load event handler before the 'TODO lines that fill the data adapters:

Dim SyncAgent As New NwindClientSyncAgent
Dim SyncStats As Microsoft.Synchronization.Data. _
	SyncStatistics = SyncAgent.Synchronize

Press F5 to build and run your project, and verify that the combo boxes are hooked up correctly. Open the Northwind.sdf database in Server Explorer, right-click on the Customers table, choose Show Table Data, and make a change to the first CompanyName value, such as adding a "GmbH" suffix to Alfreds Futterkiste. Close and restart the project, and verify that Sync Services updated the client table (Figure 4). The final version of this project is included as SyncDesignerTest.sln in the sample code for this article (download the sample code here.) The sample code includes a link to a Web page with a more detailed description and additional illustrations of the steps to create the sample app.

Admittedly, the current state of the Sync Services 1.0 API and Sync Designer is a little rough around the edges, but bear in mind that both were early CTPs when this issue went to press. However, it's clear that Microsoft's substantial investment in ADO.NET synchronization technology will benefit developers and users of data-intensive .NET projects in significant ways. Give the sample code associated with this article a trial run, and I'm sure you, too, will consider using the Sync Services API for future VS 2005 projects and the Sync Designer for Orcas-based projects scheduled for release in 2008 and later.

• Get Code Download