Programming Microsoft Synchronization Services for ADO.NET (Devices)

4/7/2010

Andy Wigley, APPAMundi Limited

June 2009

Summary

One of the most significant new features in Microsoft® SQL Server® Compact Edition 3.5 SP1 is the addition of support for Microsoft Synchronization Services for ADO.NET. This data synchronization technology lets you synchronize data from different sources over two-tier, n-tier, and service-based architectures.

Synchronization Services provides a powerful new option for offline data caching and data synchronization in both .NET Smart Client and mobile and embedded applications using the .NET Compact Framework. Microsoft Visual Studio® 2008 SP1 includes updates to Synchronization Services and support for SQL Server Compact Edition 3.5 SP1 for desktop clients, and an additional update extends support to Windows Mobile® devices. Synchronization Services expands the options for offline relational data caching in a .NET Compact Framework application beyond approaches using Merge Replication, Remote Data Access, and custom Web services.

Microsoft Synchronization Services for ADO.NET v1.0 Service Pack 1 (devices)

Microsoft SQL Server Compact 3.5 Service Pack 1 for Windows Mobile

Sample Code

Applies To

Microsoft SQL Server Compact Edition 3.5 SP1

Microsoft SQL Server 2008

Microsoft Visual Studio 2008 SP1

Microsoft Visual C#

Windows Mobile 5.0 and later

Microsoft .NET Compact Framework 3.5

Programming Microsoft Synchronization Services for ADO.NET

Getting Started with Synchronization Services

Understanding SQL Server 2008 Change Tracking Configuration

Limitations of Synchronization Services on Devices

Implementing Batching to Reduce Data Transfer Volumes

Handling Initial Synchronization and Expired Subscriptions

Using Compression to Reduce Data Transfer over WCF

Introduction

Microsoft Synchronization Services for ADO.NET provides a new option for relational data synchronization in mobile applications. This paper discusses how to use the wizards in Visual Studio 2008 SP1 to add data synchronization capabilities to a .NET Compact Framework application to synchronize a SQL Server Compact Edition 3.5 SP1 database on the device with a SQL Server 2008 database on the server. You learn how to configure communications between device and server over Microsoft Windows® Communication Foundation Classes (WCF) and how to use batching and compression to improve the reliability of the application.

Programming Microsoft Synchronization Services for ADO.NET (Devices)

Synchronization Services supports snapshot, download-only, upload-only, and bidirectional synchronization.

• Snapshot synchronization is typically used to download reference data, such as a product list, to a client. A complete copy of the data is downloaded every time that the client synchronizes.
• Download-only synchronization resembles snapshot synchronization. However, the first time that the client synchronizes, a complete copy is downloaded and on later synchronizations, only incremental data changes that you make on the server are downloaded.
• Upload-only synchronization is used to transfer data from client to server, such as a sales order, created on a client. Inserts and other data changes that were made in the client database are uploaded to the server during synchronization.
• Bidirectional synchronization is typically used for data, such as customer contact information, that can be updated at the client and server. Any conflicting changes must be handled during synchronization.

In Synchronization Services, the database used on the client-side is SQL Server Compact Edition 3.5 SP1. Synchronization Services provides an infrastructure to track incremental changes in the client database. The server-side database can be any database for which there is an ADO.NET provider that includes SQL Server 2005, SQL Server 2008, Oracle, MySQL, and many other database products. The method for tracking changes differs from database to database. In most database products changes typically involve adding columns to a tracked table to record some or all of creation time, last changed time, the client ID that performed the last modification, and the addition of tombstone tables to record deleted records. However, the easiest way to implement change tracking in a server database is to use SQL Server 2008 which has change tracking ability built in.

For more information about how to support change tracking on different databases, see Microsoft Synchronization Services for ADO.NET Books Online.

Getting Started with Synchronization Services

This section of this paper discusses the development of a simple data synchronization solution by using the Occasionally Connected Systems (OCS) Designer included in Visual Studio 2008.

Updating Visual Studio 2008 SP1

Visual Studio 2008 SP1 includes updates to Synchronization Services and SQL Server 3.5 SP1, but only for desktop clients. You have to download and install updates for mobile devices separately. After you have installed Visual Studio 2008 SP1, you also have to download the following from MSDN Downloads:

• Microsoft Synchronization Services for ADO.NET v1.0 Service Pack 1 (devices)
• Microsoft SQL Server Compact 3.5 Service Pack 1 for Windows Mobile

Installing the Demo Database in SQL Server 2008

In the sample code for this article, you can find the instnwnd_withCustomerChangeTracking.sql script that you can use to create a Northwind database on your SQL Server 2008 database server, with change tracking enabled for the database and for the Customers table.

1. To execute it, open SQL Server Management Studio, connect to your database server, and then use Windows Explorer to browse to where you have installed instnwnd_withCustomerChangeTracking.sql, and double-click it.
   The script opens in SQL Server Management Studio.
2. Choose the Execute icon on the toolbar to create the database.

Executing the OCS Designer in a Visual Studio Solution

Visual Studio 2008 SP1 includes the OCS Designer for creating and configuring Synchronization Services solutions, and it supports code generation for projects built for the .NET Compact Framework. To start the OCS Designer, on the Project menu, choose Add New Item and then select the Local Database Cache template.

Many device developers cannot move past this very first step, because if you create a smart device application, and then open the Add New Item window for the device project, you find that Local Database Cache is not listed in the available templates. To use the OCS Designer with .NET Compact Framework clients, you must create a solution that contains two projects, one which is a smart devices Windows Forms project to contain the client-side code, and one which is a WCF Service Library which contains the server-side code. You can then add the Local Database Cache item to the WCF Service Library project to start the OCS Designer, and configure it to generate the client-side code in the Smart Device project, as described in the following steps:

1. In Visual Studio 2008, create a new Smart Device Windows Forms project, targeting the .NET Compact Framework 3.5.
   
2. In Solution Explorer, right-click the solution, and then choose Add New Project.
   You are adding a second project to the same solution, which must be a WCF Service Library project. Enter a suitable name for the WCF Service Library project, such as MobileSyncServiceLibrary.
   
3. After the WCF Service Library is added, delete the project files IService1.cs and Service1.cs. Those files are the example WCF service implementation files the wizard created for you. Next, use the OCS Designer to generate the WCF Service interface and implementation.
4. In Solution Explorer, right-click the WCF Service Library project, and then choose Add New Item. Select Local Database Cache and give it a suitable name, such as NorthwindCache.sync.
   
5. The OCS Designer displays the Configure Data Synchronization window. First, use the Server connection drop-down list to select an existing database connection to the back-end database server where the Northwind database is located, or choose New to create a new connection. The OCS Designer automatically generates the client database connection for you.
   
6. Next, choose Add underneath the Cached Tables pane. Here is where you select which tables to copy to the client database cache. For this example, just select the Customers table. In the Data to download drop-down list, select New and incremental changes after first synchronization. This option causes Synchronization Services to only transfer incremental changes between the client and server when synchronizing, instead of transferring a complete new snapshot of the table every time.
   
7. After you choose OK, you return to the main OCS Designer window. Here you must choose Advanced for additional configuration options. Make sure that you select the WCF Service Library project for the Server Project location, and the Smart Device project as the Client project location.
   
8. Now choose OK. The wizard generates the code for synchronization between the SQL Server Compact 3.5 SP1 database on the client side, and the SQL Server 2008 database on the server side. It also performs an initial synchronization and adds the synchronized SQL Server Compact 3.5 SP1 database to your client-side project.
   
9. Next, Visual Studio 2008 displays the Data Source Configuration window. It prompts you to set up a typed DataSet in your client-side project to work with the data in the client-side database. You can cancel that if you intend to write your own data access code to use the synchronized data in the SQL Server Compact Edition database in your client-side application, but for this walkthrough you can generate a typed DataSet by selecting the Tables check box to select all tables (there is only one, of course, the Customers table which was synchronized from the master copy on the server) and accept the default name of NorthwindDataSet, and then choose Finish.

At this point, the OCS Designer has generated most of the code to synchronize the tables that you selected, and has generated the required code in your client-side smart device, and in your server-side WCF Service Library. You still have some work to do to create a working solution that works over a network, but before you do that, it is worth pausing to examine the code that the OCS Designer has generated for you.

Note

One usage of the OCS Designer is as a tool to generate a fully populated database for test or demo purposes. Using the OCS Designer in the way just described is an excellent and easy way of copying a snapshot of data from a server and inserting it into a SQL Server Compact Edition database for testing, or where synchronization is not required.

Understanding the Architecture of a Synchronization Services Solution

The OCS Designer generates code in both the server project and the client project. It is worth spending some time to examine the designer-generated code to understand the objects it has created and the role each one plays.

The following illustration represents the architecture.

In an n-tier architecture for devices, synchronization components are used as follows:

• The server synchronization provider (DbServerSyncProvider) resides on the server or another tier.
• The server synchronization provider aggregates synchronization adapters (SyncAdapter), which is an object that contains the definition of the commands to select, insert, update, and delete records in the server database. You use one synchronization adapter for each table that you are synchronizing.
• The client synchronization provider (SqlCeClientSyncProvider) and the synchronization agent (SyncAgent) both reside on the device. The synchronization agent orchestrates the overall synchronization process. The client synchronization provider interacts with the SQL Server Compact 3.5 SP1 database to update data records and store synchronization metadata.
• The SyncAgent owns a collection of synchronization table (SyncTable) objects. You have one SyncTable for each table in the database that you want to synchronize. You set properties on these objects to determine the kind of synchronization to perform.
• You can also group SyncTable objects into synchronization groups (SyncGroup), typically where a parent/child relationship exists between tables. Synchronization Services synchronizes changes to tables in a SyncGroup all together to make sure that referential integrity between the tables in the group is maintained.
• Communication between the server synchronization provider and the synchronization agent is handled by the server synchronization provider proxy (ServerSyncProviderProxy) and a service. Synchronization Services does not specify the kind of service that you must use. However, applications typically use a Web service or a WCF service.

Configuring the WCF Service for .NET Compact Framework Clients

The OCS Designer has generated most of the code that you must have for this Synchronization Services solution. But there is still quite a lot more work that you have to do so that this solution works over a network.

Configuring DataSet Serialization

The OCS Designer added a file to your WCF Library project named NorthwindCache.SyncContract.cs. This contains the definition of the interface your WCF Service exposes to clients. Synchronization Services uses DataSet objects to communicate changed data from client to server and from server to client. These DataSet objects must be serialized for communication and by default a WCF Service uses the System.Runtime.Serialization.DataContractSerializer class for this which does not have an equivalent in the .NET Compact Framework to (de)serialize to the same format. In order to force this service to use XML serialization instead, which is supported by the .NET Compact Framework, edit the NorthwindCache.SyncContract.cs file to add the XmlSerializationFormat attribute to this interface definition. The following code example shows the added attribute in the interface definition.

    [ServiceContractAttribute()]
    [XmlSerializerFormat()]
    public interface INorthwindCacheSyncContract {
        [OperationContract()]
        SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, 
                                 DataSet dataSet, 
                                 SyncSession syncSession); 

        [OperationContract()]
        SyncContext GetChanges(SyncGroupMetadata groupMetadata,  
                               SyncSession syncSession); 

        [OperationContract()]
        SyncSchema GetSchema(Collection<string> tableNames, 
                             SyncSession syncSession); 

        [OperationContract()]
        SyncServerInfo GetServerInfo(SyncSession syncSession);
    }

Configuring the Service

If you scroll up to the top of this file, you see a large comment block which suggest to you how to set up the app.config configuration file for the WCF service.

To configure the service

1. Replace the <service> element definition in app.config with that suggested by the comment block.
2. There are two important changes that you must make to the XML you just added:

In the <endpoint address=""…> element, change the binding attribute from binding="wsHttpBinding" to binding="basicHttpBinding". The .NET Compact Framework does not support "wsHttpBinding".

• In the <baseAddresses> element, change the URL that is specified in the <add baseaddress…> statement from localhost:8080 to the correct host name and port where the service will be running, for example some_server:8088.
• Replace the <behavior> element definition in app.config with that suggested in the comment block in NorthwindCache.SyncContract.cs.

The following code example shows what the app.config code should look like.

<?xml version="1.0" encoding="utf-8" ?> 
<configuration>
  ... 
  <system.serviceModel>
    <services>
      <service  
         name="MobileSyncServiceLibrary.NorthwindCacheSyncService" 
         behaviorConfiguration=
          "MobileSyncServiceLibrary.NorthwindCacheSyncServiceBehavior">
        <host>
          <baseAddresses>
            <add baseAddress=
              "https://myServer:8088/NorthwindCacheSyncService/"/>
          </baseAddresses>
        </host>
        <endpoint address ="" binding="basicHttpBinding" contract=
            "MobileSyncServiceLibrary.INorthwindCacheSyncContract"/>
        <endpoint address="mex" binding="mexHttpBinding" contract=
            "IMetadataExchange" />
      </service>
    </services>
    <behaviors>
      <serviceBehaviors>
        <behavior name=
          "MiddleTierServiceLibrary.NorthwindCacheSyncServiceBehavior">
          <serviceMetadata httpGetEnabled="True" />
          <serviceDebug includeExceptionDetailInFaults="False" />
        </behavior>
      </serviceBehaviors>
    </behaviors>
  </system.serviceModel>
</configuration>

Using NetCFSvcUtil to Generate the Service Proxy for the Client

The Power Toys for .NET Compact Framework 3.5 which you can download from MSDN Download includes the NetCFSvcUtil tool. After installation, you find it located in the following directory: <drive>:\Program Files\Microsoft.NET\SDK\CompactFramework\v3.5\Bin. To make this tool easier to use at a command prompt, add this path of the PATH environment variable of your computer.

To generate the WCF service proxy for the client

1. Build the WCF Service Library project.

2. Start a command prompt, move to the Bin\Debug directory of the WCF Service Library project, and then run the following commands in sequence:

   netcfsvcutil.exe  WCF_Service_Library_name.dll
   netcfsvcutil *.wsdl *.xsd /language:C# /out:MobileSyncServiceProxy.cs
   

   The first command generates the metadata documents for Service Contracts and associated types in the WCF Service assembly and the second uses the metadata documents as input to generate the C# code for the client proxy. If you are working in Microsoft Visual Basic®, use /language:VB /out:filename.vb arguments instead.

3. There will be two source files generated, CFClientBase.cs/.vb and MobileSyncServiceProxy.cs/.vb. Copy these files to the Smart Device project directory and add them to the project.

4. Add references to System.Runtime.Serialization and System.ServiceModel to the Smart Device project.

5. Before you can use these classes, you must edit the generated MobileSyncServiceProxy.cs/.vb and remove some class definitions. The problem is that the NetCFSvcUtil tool has generated classes for every type exposed by the Service interface. This includes many types such as SyncGroupMetadata, SyncAnchor, and so on, that are also defined in the Microsoft.Synchronization.Data.dll assembly which is already referenced by the client-side project.

6. Edit this file and delete all classes that use names that begin with Sync and the ConflictType enum. Add a using Microsoft.Synchronization.Data; (Imports Microsoft.Synchronization.Data for Visual Basic) statement to the top of this file and then you can compile successfully.

Coding the Client

It is left as an exercise for the reader to build a suitable user interface to display and enable editing of records in the Customer table. A simple example is included in the sample code accompanying this article.

The code that you use to synchronize on the client is fairly simple. The code that the OCS Designer generated and added to the client-side project includes a class that inherits from Microsoft.Synchronization.SyncAgent. It is an instance of this class that you use to orchestrate the synchronization on the client-side.

To write the code for the synchronization on the client-side

1. Create an instance of the service proxy.
2. Pass the instance to the constructor of a Microsoft.Synchronization.Data.ServerSyncProviderProxy instance. This object wraps the service proxy and provides a facade that the Sync Service classes can work with.
3. Create an instance of the SyncAgent-derived class, and set its RemoteProvider property to the ServerSyncProviderProxy instance.
4. Call the Synchronize method of the SyncAgent-derived instance to perform the synchronization.

The following C# code example shows these steps for the example described in this paper.

private static void Sync()
{
    Cursor.Current = Cursors.WaitCursor; 

    // Create WCF Service proxy
    System.ServiceModel.BasicHttpBinding basicBinding =
        new System.ServiceModel.BasicHttpBinding();
    System.ServiceModel.EndpointAddress endPoint =
        new System.ServiceModel.EndpointAddress(
         "https://my_server:8081/NorthwindDataCacheSyncService/");
    NorthwindDataCacheSyncContractClient proxy =
        new NorthwindDataCacheSyncContractClient(basicBinding, 
                                                 endPoint); 
    // Wrap in a ServerSyncProviderProxy
    Microsoft.Synchronization.Data.ServerSyncProviderProxy syncProxy =
        new Microsoft.Synchronization.Data.ServerSyncProviderProxy(
            proxy); 

    // Create a SyncAgent
    NorthwindDataCacheSyncAgent syncAgent = 
        new NorthwindDataCacheSyncAgent();
    syncAgent.RemoteProvider = syncProxy; 

    // Set sync to be bidirectional
    syncAgent.Customers.SyncDirection =
        Microsoft.Synchronization.Data.SyncDirection.Bidirectional; 

    // Synchronize
    Microsoft.Synchronization.Data.SyncStatistics syncStats =
        syncAgent.Synchronize();

    // Show synchronization statistics
    MessageBox.Show("Changes downloaded: " + 
        syncStats.TotalChangesDownloaded.ToString()
        + "\r\nChanges uploaded: " + 
        syncStats.TotalChangesUploaded.ToString());

    Cursor.Current = Cursors.Default; 
} 

Setting the Synchronization Direction

You may have noticed that the code just shown includes the following:

    // Set sync to be bidirectional
    syncAgent.Customers.SyncDirection =
        Microsoft.Synchronization.Data.SyncDirection.Bidirectional; 

By default, the code that the OCS Designer generates is download-only. Therefore, if you want bidirectional or snapshot synchronization, you must explicitly request it. In the Synchronization Services architecture, synchronization is orchestrated by an instance of a SyncAgent-derived class. That object exposes a collection of SyncTable objects in the Configuration.SyncTables property, which contains a SyncTable instance for each table in the source database that you want to synchronize. It is the SyncDirection property of the SyncTable instance you set to alter the sync direction and you must do that for each table that you are synchronizing.

The SyncAgent-derived class created by the OCS Designer exposes a property for each SyncTable it is managing so that in this case you can get syncAgent.Customers to access the Customers SyncTable to set the SyncDirection property.

It is a good idea to spend some time studying the code created by the OCS Designer and reading the Synchronization Services documentation to understand the relationship between objects in a Synchronization Services solution, and the many configuration options.

Testing the Application

Now you are ready to test the program. First, make sure that you have network connectivity between the device or device emulator and the server, for example by connecting the device through Microsoft ActiveSync®. A good way to check whether you have connectivity is to start the WCF Service running, which you can do by right-clicking the WCF Library project in Solution Explorer, choose Debug, and then choose Start New Instance. Then start Internet Explorer Mobile on the device, and enter the URL of the service, for example: https://myserver:8080/NorthwindDataCacheSyncService/.

Tip

: The trailing ‘/’ is important for success, and if you have connectivity, you see the standard service Home page that the following screen shows.

When you have confirmed that you have network connectivity, you can run the application. The simple test application that is included in the sample just reports the results of synchronization to the screen by using a MessageBox message window. Because the database was already synchronized when you ran the OCS Designer, not surprisingly it reports that no records were transferred.

If you change the application to update one or more records in the client database (the sample application includes a menu button that you can press to insert a new record), or use Visual Studio or SQL Server Management Studio to alter some Customers records on the server, and then synchronize again, you see the changed records being transferred in order to keep both databases in sync.

Avoiding the Schema Exchange Bug When You Use NetCFSvcUtil-Generated WCF Proxies

The example program just described works as long as you have an active network connection between the device and the server. However, there is an incompatibility between the SyncAgent class and a WCF proxy that you generate by using NetCFSvcUtil which is only revealed when you run the application that uses an empty database on the client.

In that case, the first thing that the client does is to call the GetSchema method on the service interface to download the schema of the tables it wants to synchronize. The signature of the DbServerSyncProvider.GetSchema method is SyncSchema GetSchema(Collection<string> tableNames, SyncSession syncSession), and naturally the OCS Designer generates a method in the service contract with the same signature. However, when the SyncAgent class on the device calls the WCF service proxy, instead of passing the names of the tables it is interested in as a Collection<string>, it passes them as an object array. There is no implicit conversion from object array to Collection<string> so that the application fails with a runtime error at this point. Be aware that you do not see this error that occurs with a solution generated by the OCS Designer because it populates the client database when you run the wizard, so the tables to be synchronized already exist in the client side database and no schema exchange is necessary at run time.

The following code example shows a workaround for this problem. Change the GetSchema method in the service interface to take a string[] to which an object array can be implicitly converted, instead of a Collection<string>.

    [ServiceContractAttribute()]
    [XmlSerializerFormat()]
    public interface INorthwindDataCacheSyncContract {
        
        [OperationContract()]
        SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, 
                                 DataSet dataSet, 
                                 SyncSession syncSession); 
        
        [OperationContract()]
        SyncContext GetChanges(SyncGroupMetadata groupMetadata, 
                               SyncSession syncSession); 
        
        [OperationContract()]
        SyncSchema GetSchema(string[] tableNames, 
                             SyncSession syncSession); 
        
        [OperationContract()]
        SyncServerInfo GetServerInfo(SyncSession syncSession); 
    }

Then, in the implementation of this method, which is in the NorthwindDataCacheSyncService class in the example described in this paper, convert the string[] to the Collection<string> instance required by the DbServerSyncProvider class.

public virtual SyncSchema GetSchema(
    string[] tableNames, SyncSession syncSession) 
        {
            // Convert the string array to Collection<string>
            Collection<string> tableCollection = 
                new Collection<string>(tableNames); 
            return this.serverSyncProvider.GetSchema(
                tableCollection, syncSession); 
        }

Remember to run NetCFSvcUtil again against this revised service and include the generated files in your client-side project.

Of course, the techniques described in this paper are only one way to build a Synchronization Services solution. If you create the service proxy by adding a Web Reference to your project instead of generating a proxy by using NetCFSvcUtil, the generated proxy code does not experience the same problem.

Understanding SQL Server 2008 Change Tracking Configuration

Before you examine some potential problems with a Sync Services solution, it is helpful to examine how you configure change tracking in a SQL Server 2008 database.

SQL Server 2008 has support for change tracking built in. However, by default, it is disabled. You enable change tracking for a SQL Server 2008 database by using a Transact-SQL command. The following code example shows such a command.

ALTER DATABASE database_name
SET CHANGE_TRACKING = ON
 (AUTO_CLEANUP = ON, CHANGE_RETENTION = 14 DAYS)

The change retention value specifies the time period for which change tracking information is kept. Change tracking information that is older than this time period is removed periodically. When you are setting this value, you should consider how frequently applications synchronize with the tables in the database. The specified retention period must be at least as long as the maximum time period between synchronizations. See "Handling Initial Synchronization and Expired Subscriptions" later in this paper for a discussion on how to handle the situation where a client attempts to synchronize but the time that has elapsed because its previous synchronization was greater than the change retention period of the database server.

After you have enabled change tracking on the database server, you must enable change tracking for any tables that you want to synchronize. The following code example shows how to use such a command.

ALTER TABLE "table_name"
ENABLE CHANGE_TRACKING
WITH ( TRACK_COLUMNS_UPDATED = OFF )

When the TRACK_COLUMNS_UPDATED option is set to ON, the SQL Server Database Engine stores additional information about which columns were updated to the internal change tracking table. Column tracking can enable an application to synchronize only those columns that were updated. This can improve efficiency and performance. However, because maintaining column tracking information adds some additional storage overhead, by default, this option is set to OFF.

The script that you ran to install the Northwind database at the beginning of this tutorial includes these commands within it. Notice that the OCS Designer automatically enables change tracking in the server database if it is not already enabled.

Limitations of Synchronization Services on Devices

The example of a Synchronization Services solution that has just been described may leave you with the impression that Synchronization Services solutions are fairly easy to set up and program. However, there are some pitfalls waiting to catch the unwary mobile application developer, because of the way that Synchronization Services performs its work and the solution that the OCS Designer creates for you.

• The first problem is that by default, when a client synchronizes Synchronization Services sends all changes that have occurred since the clients’ previous synchronization in one go. By default, no throttling or batching is implemented. Therefore, if there are many incremental changes, you may find that the Dataset sent from server to client or vice versa is very large and can easily lead to out-of-memory exceptions on the mobile device when it struggles to deserialize the data on receipt, or serialize it before sending.

• The second problem is that change history is not retained indefinitely on the server or on the client. You have to make sure that clients synchronize frequently enough that they can be refreshed before the change data expires and is removed. The Transact-SQL commands that the OCS Designer generates to select changed records from the SQL Server database include a check for expired subscriptions; if you study the NorthwindDataCache.Designer.cs file in the example project, you find the definition of the Transact-SQL commands that are executed to select incremental changes from the server database. The following code example shows part of those commands.

  ...IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.Customers')) > @sync_last_received_anchor RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. To recover from this error, the client must reinitialize its local database and try again',16,3,N'dbo.Customers') ";
  

  You have to configure change tracking with an appropriate change retention period that is longer than the longest period between synchronizations of clients. You also have to write code to detect synchronization failure because of this error being raised and you need a strategy to reinitialize such clients.

• The third problem is one of efficiency. Synchronization Services uses the DataSet object as the vehicle for sending changes back and forth. This serializes to XML so can be sent easily over HTTP network links. However, it is also a verbose format for sending data. Transmitting unnecessary data over a mobile phone network is costly due to the charges you pay to your mobile operator, but also leads to more frequent failures if the network connection is of poor quality. The smaller the quantity of data that you can send in these situations, the better your chance of a successful transmission. Because of this, you should consider implementing compression in the WCF channel, although remember that if you use compression you increase the processing the mobile device must perform. Therefore you will decrease battery life.

The next sections describe some possible remedies for these problems.

Implementing Batching to Reduce Data Transfer Volumes

Synchronization Services supports a limited batching capability which performs multiple transfers of the changed data in smaller batches from server to client, instead of all the changed rows in one go. In order to understand how batching works, you have to understand how SQL Server 2008 change tracking tracks changes, and how Synchronization Services uses that information.

Synchronization Services Using Change Tracking Version Numbers

When you enable change tracking on a SQL Server 2008 database, it starts to maintain a change version number which increments from zero. Also it maintains a special internal ‘changetable’ for each table for which you have enabled change tracking. When a row in the table is updated, a record is written to the internal changetable that contains the primary key of the changed record and the associated change version number.

When a client synchronizes, the server returns to the client the current value of its server-wide change version number. This is available by calling the SQL Server 2008 CHANGE_TRACKING_CURRENT_VERSION function. You define the Transact-SQL command to use to retrieve this server-wide change version number by setting the SelectNewAnchorCommand property of a Microsoft.Synchronization.Data.Server.DbServerSyncProvider instance, which exists in the server-side code of your sync solution. The following code example shows the standard way to define this, which is similar to the code that the OCS Designer generates in the DbServerSyncProvider class initialization logic.

  // selectNewAnchorCmd command. 
  SqlCommand selectNewAnchorCommand = new SqlCommand();
  selectNewAnchorCommand = new System.Data.SqlClient.SqlCommand();
  selectNewAnchorCommand.CommandText = 
"Select @sync_new_received_anchor = CHANGE_TRACKING_CURRENT_VERSION()";
  selectNewAnchorCommand.CommandType = System.Data.CommandType.Text; 
  System.Data.SqlClient.SqlParameter selectnewanchorParameter = 
    new System.Data.SqlClient.SqlParameter(
          "@sync_new_received_anchor", 
          System.Data.SqlDbType.BigInt); 

  selectnewanchorParameter.Direction = 
    System.Data.ParameterDirection.Output; 
  selectNewAnchorCommand.Parameters.Add(selectnewanchorParameter); 

  // Set to the DbServerSyncProvider
  this.SelectNewAnchorCommand = selectNewAnchorCommand;

The client also sends to the server the change tracking version it received from the server the previous time it synchronized or 0 (zero) if this is the first synchronization. Therefore, now all the server-side code has to do is select all changed rows with a change version later than the change tracking version the client received at the previous synchronization, and less than or equal to the databases current change version number. This actual logic required is more complex than this, because this article does not describe how the logic to track inserts, updates, and deletions separately is implemented, nor how the ID of the client that made the last update is tracked, but this article simplifies to make the general process easier to understand. Refer to the SQL Server 2008 Documentation for full details.

Implementing Batching in Synchronization Services

The batch mechanism in Synchronization Services lets you limit the range of change versions to be sent to the client in each response to a client synchronization request. For example, if the client previously synchronized to version 50, the servers’ change tracking version is currently 120, and you define a batch size of 30, then the changed rows is sent in three separate batches: 51-80 in the first transfer, 81-110 in the second, and 111-120 in the third.

One way of enabling this behavior is to define a stored procedure such as the following. This procedure takes five parameters which are required by Synchronization Services:

• @sync\_last\_received\_anchor which is the change version associated with the clients’ previous synchronization
• @sync\_batch\_size which is the requested range of change version numbers requested for each batch
• @sync\_max\_received\_anchor which is an ‘out’ parameter which returns the current change version on the server
• @sync\_new\_received\_anchor which returns the actual maximum change version number of the set of records selected for the current batch
• @sync\_batch\_count which returns the number of batches that are required to complete the synchronization

The following code example shows the logic of the stored procedure described in the comments included in the script.

USE [Northwind] 
GO

/**** Object:  StoredProcedure [dbo].[usp_GetNewBatchAnchor] ****/
CREATE PROCEDURE [dbo].[usp_GetNewBatchAnchor] (
@sync_last_received_anchor bigint, 
@sync_batch_size int, 
@sync_max_received_anchor bigint out, 
@sync_new_received_anchor bigint out,            
@sync_batch_count int output)            
AS            
       -- Set a default batch size if a valid one is not passed in. 
       IF  @sync_batch_size IS NULL OR @sync_batch_size <= 0
     SET @sync_batch_size = 1000    

   -- Before selecting the first batch of changes, 
   -- set the maximum anchor value for this synchronization           
         -- session. After the first time that this procedure is 
   -- called, Synchronization Services passes a value for 
         -- @sync_max_received_anchor to the procedure. Batches of 
         -- changes are synchronized until this value is reached. 
       IF @sync_max_received_anchor IS NULL
         SELECT  @sync_max_received_anchor = 
                    change_tracking_current_version()
       
       -- If this is the first synchronization session for a database, 
       -- get the lowest change version value from the tables. By 
       -- default, Synchronization Services uses a value of 0 for 
       -- sync_last_received_anchor on the first synchronization. If        
       -- you do not set @sync_last_received_anchor, 
       -- this can cause empty batches to be downloaded until the 
       -- lowest change version value is reached. 
       IF @sync_last_received_anchor IS NULL OR 
              @sync_last_received_anchor = 0
       BEGIN
SELECT @sync_last_received_anchor = 0

        -- Changes are only retained in the change table for a limited 
        -- period of time set by the CHANGE_RETENTION parameter 
        -- (on ALTER DATABASE). 
        -- Check that we haven't had changes cleaned up on this table
        -- (i.e. CHANGE_TRACKING_MIN_VALID_VERSION returns > 0)        
IF CHANGE_TRACKING_MIN_VALID_VERSION(
               object_id(N'dbo.Customers')) > 
                   @sync_last_received_anchor 
  RAISERROR (N'SQL Server Change Tracking has cleaned up 
tracking information for table ''%s''. To recover from this error, the 
client must reinitialize its local database and try 
again',16,3,N'dbo.Customers') 

SET @sync_new_received_anchor = 
                @sync_last_received_anchor + @sync_batch_size

-- Determine how many batches are required during the 
            -- initial synchronization. 
IF @sync_batch_count <= 0
  SET @sync_batch_count = (
                 (@sync_max_received_anchor / @sync_batch_size) - 
                 (@sync_last_received_anchor /  @sync_batch_size) 
              ) 
       END
     ELSE
       BEGIN
         SET @sync_new_received_anchor = 
           @sync_last_received_anchor + @sync_batch_size

         -- Determine how many batches are required during subsequent 
         -- synchronizations. 
IF @sync_batch_count <= 0
              SET @sync_batch_count = (
                 (@sync_max_received_anchor / @sync_batch_size) - 
                 (@sync_new_received_anchor /  @sync_batch_size)) + 1 
 
 END

     -- Check whether this is the last batch.           IF @sync_new_received_anchor >= @sync_max_received_anchor
     BEGIN
       SET @sync_new_received_anchor = @sync_max_received_anchor        
       IF @sync_batch_count <= 0
         SET @sync_batch_count = 1
     END
GO

The following code example shows how to activate batch support by configuring the SelectNewAnchorCommand property of the Microsoft.Synchronization.Data.Server.DbServerSyncProvider instance to call the stored procedure, specifying the requested batch size.

       SqlCommand selectNewAnchorCommand = new SqlCommand();
    selectNewAnchorCommand.CommandText = "usp_GetNewBatchAnchor";
    selectNewAnchorCommand.CommandType = CommandType.StoredProcedure; 
    selectNewAnchorCommand.Parameters.Add("@" + 
      SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt, 8); 
    selectNewAnchorCommand.Parameters.Add("@" + 
      SyncSession.SyncMaxReceivedAnchor, SqlDbType.BigInt, 8); 
    selectNewAnchorCommand.Parameters.Add("@" + 
      SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt, 8); 
    selectNewAnchorCommand.Parameters.Add("@" + 
      SyncSession.SyncBatchSize, SqlDbType.Int, 4); 
    selectNewAnchorCommand.Parameters.Add("@" + 
      SyncSession.SyncBatchCount, SqlDbType.Int, 4); 
    selectNewAnchorCommand.Parameters["@" + 
      SyncSession.SyncMaxReceivedAnchor].Direction = 
        ParameterDirection.Output; 
    selectNewAnchorCommand.Parameters["@" + 
      SyncSession.SyncNewReceivedAnchor].Direction = 
        ParameterDirection.Output; 
    selectNewAnchorCommand.Parameters["@" + 
      SyncSession.SyncBatchCount].Direction = 
        ParameterDirection.InputOutput;
    // Set to the DbServerSyncProvider
    this.SelectNewAnchorCommand = selectNewAnchorCommand; 
    // Set the requested batch size
    this.BatchSize = 10;

Batching for Client Incremental Changes

There is no support in Synchronization Services for sending changes from a client up to the server in smaller batches. You should make sure that the client code synchronizes with the server frequently enough so that the volume of changes cached on the client does not grow to a large number. Many changes cached on the client could lead to out-of-memory exceptions when you try to serialize the DataSet to be sent to the server, and to problems in transmitting large amounts of data over slow connections such as cell data networks.

Why Batching Might Not Work After Enabling SQL Server 2008 Change Tracking on Populated Tables

There is one particular problem that may occur with batching. If you enable change tracking on a database table before any data is inserted in that table, then, as each row is added to the source table, the change is recorded in the internal changetable and is assigned a new, unique change version number. In this case, batching can limit the number of rows that are sent to the client on each communication.

However, if you enable change tracking on a table that already has many rows of data, then the initial state of the table is recorded by adding records to the changetable for all rows with the same initial change version number. Therefore, even with batching implemented, when a client synchronizes for the first time, the Synchronization Services server-side code attempts to return all the rows in the requested table.

In this circumstance, you must initialize the client in a different way, as described in the next section.

Handling Initial Synchronization and Expired Subscriptions

There are some situations where you cannot synchronize a client database by using the techniques described to this point in this paper.

When a client synchronizes for the very first time starting with an empty client database and you do not implement batching, the dataset the server builds that contains the initial snapshot of all rows in the tracked tables will be too large for the device to process given its limited resources. Even if you do implement batching, the process will be slow and prolonged, and not possible over a communications link with poor quality. In this circumstance, you cannot use standard synchronization to create the initial client database.

When synchronization of a client fails because the time interval since the client’s previous synchronization is greater than the change retention period configured on the server, you must reinitialize the client database.

Reinitializing the Client Database

In order to reinitialize the client database, you must create a synchronized SQL Server Compact Edition database. Because this cannot realistically be done by running a process on the client device because of its limited resources, it must be performed on the server.

SQL Server Compact Edition is supported on desktop and server versions of Windows. Therefore, you can easily write a simple program to run on a computer to synchronize a client database. You can even run it one time, and use the same copy of the database on multiple clients. If you are using download-only synchronization where no changes on the client are synchronized back to the server, then you do not even have to worry about how to set a unique GUID client ID for each client. The server does not store metadata for each of its clients. Therefore, for download-only scenarios, the only piece of client data the server is interested in is the SyncLastReceivedAnchor value, the change version number associated with the clients’ previous synchronization.

If you synchronize changes from client to server, then you must make sure each client database has a unique client ID. The server needs this because the change data that is stored for each row includes the ID of the client that made the change. It needs to know the identity of the client that made the change, because, when synchronization occurs, the data changed on the client is uploaded first and inserted into the server database, and then the changed rows on the server are downloaded to the client. The server has to make sure on the download that it does not send back the same changed rows that the client just sent it, and it does that when you select changed rows by filtering out all changes with a client ID that is the same as that of the requesting client.

You can reset the client ID associated with an existing client database by setting the SqlCeClientSyncProvider property of the Microsoft.Synchronization.Data.SqlCeClientSyncProvider class to a new GUID. The following code example shows that in an OCS Designer-generated solution, the instance of this class you must change is exposed in the LocalProvider property of the SyncAgent.

((SqlCeClientSyncProvider)(syncAgent.LocalProvider)).ClientId = 
     Guid.NewGuid();

You have to think of some method of transferring a newly-initialized client database on to the intended client. There are many ways of doing this, such as using FTP, or a custom solution over a WAN, or returning the device to base and copying over an ActiveSync connection, or a wireless LAN.

Using Compression to Reduce Data Transfer Volumes over WCF

One other optimization you can introduce is to compress the data in transit. This does not reduce the memory requirement for processing data on the device, and in fact it requires additional processing and so uses more battery power. But because you transfer fewer bytes, it can improve the chances of a successful synchronization if you are operating over poor-quality cell data connections.

With WCF, you use a custom binding to perform the compression and decompression. There are two samples available for download from MSDN that you can use to get started with building compression bindings for your solution:

• Custom Message Encoder sample is an example of a custom encoder for desktop and server platforms. You can read how it works at Custom Message Encoder: Compression Encoder where there is also a link to download a collection of samples that includes this encoder. After installation, you can find the gZipEncoder project at <installation path>\WCF Samples\WCF_WF_CardSpace_Samples\WCF\Extensibility \MessageEncoder\Compression\CS\Server. Copy this project and include it in your solution, then add a reference to this library to the server-side project.
• You can find a GZip encoder sample for the .NET Compact Framework at .NET Compact Framework Technology Samples: GZip Encoder Sample. Copy the IndigoGZipEncoder project to your solution, and add a reference to this project to the client-side code project.

The following code example shows how to start using the GZip encoders in your solution, where you first change the configuration in the app.config file of the server project.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  ... 
  <system.serviceModel>
    <extensions>
      <bindingElementExtensions>
        <add name="gzipMessageEncoding" 
       type="Microsoft.ServiceModel.Samples.GZipMessageEncodingElement, 
GZipEncoder, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
      </bindingElementExtensions>
    </extensions>
    <services>
      <service 
         name="MobileSyncServiceLibrary.NorthwindDataCacheSyncService"           
         behaviorConfiguration=
      "MobileSyncServiceLibrary.NorthwindDataCacheSyncServiceBehavior">
        <host>
          <baseAddresses>
            <add baseAddress=    
                "https://myserver:8080/NorthwindDataCacheSyncService/"/>
          </baseAddresses>
        </host>
        <endpoint 
          address ="" 
          binding="customBinding" 
          bindingConfiguration="BufferedHttpSampleServer"
          bindingName="BufferedHttpSampleServer" 
          contract=
          "MobileSyncServiceLibrary.INorthwindDataCacheSyncContract"/>
        <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
      </service>
    </services>
    <bindings>
      <customBinding>
        <binding name="BufferedHttpSampleServer">
          <gzipMessageEncoding 
              innerMessageEncoding="textMessageEncoding" />
          <httpTransport hostNameComparisonMode="StrongWildcard"
                         manualAddressing="False"
                         maxReceivedMessageSize="65536"
                         authenticationScheme="Anonymous"
                         bypassProxyOnLocal="False"
                         realm=""
                         useDefaultWebProxy="True" />
        </binding>
      </customBinding>
    </bindings>
    <behaviors>
      <serviceBehaviors>
        <behavior name=
   "MobileSyncServiceLibrary.NorthwindDataCacheSyncServiceBehavior">
          <serviceMetadata httpGetEnabled="True" />
          <serviceDebug includeExceptionDetailInFaults="False" />
        </behavior>
      </serviceBehaviors>
    </behaviors>
  </system.serviceModel>
</configuration>

Read the description of this custom encoder at Custom Message Encoder: Compression Encoder to understand the meaning of these configuration elements.

On the client, configuration through config files is not supported. Therefore, use of the custom binding by the WCF proxy must be configured in code. The following code example shows how to do this.

    // Create a CustomBinding
    System.ServiceModel.Channels.CustomBinding customBinding = 
        new System.ServiceModel.Channels.CustomBinding();
    // Create a compression binding element
    CompressionMessageEncodingBindingElement compressionBindingElmnt = 
        new CompressionMessageEncodingBindingElement();
    // ..and add to the custom binding
    customBinding.Elements.Add(compressionBindingElmnt); 
    
    // Create an HttpTransportBindingElement and add that as well
    HttpTransportBindingElement httpBindingElement = 
        new HttpTransportBindingElement();
    customBinding.Elements.Add(httpBindingElement); 

    System.ServiceModel.EndpointAddress endPoint =
        new System.ServiceModel.EndpointAddress(
            "https://myServer:8080/NorthwindDataCacheSyncService/");

    // Create the WCF proxy using the custom binding
    NorthwindDataCacheSyncContractClient proxy =
        new NorthwindDataCacheSyncContractClient(
                customBinding, endPoint); 

By using a compression binding such as this, you can achieve large reductions in the volume of data transferred over the network.

Conclusion

Microsoft Synchronization Services for ADO.NET provides a developer-centric data synchronization solution. It is very flexible and enables synchronization with any server database for which there is an ADO.NET provider. The Occasionally Connected Systems Designer in Visual Studio 2008 SP1 is a wizard that helps build Synchronization Services solutions.

Whereas Synchronization Services solutions are fairly easy to build, developers have to be aware of the need to implement batching to reduce the data to be processed to a size that is suitable for mobile clients. Also, compression should be considered on the communication link to reduce the volume of data being transferred. Implementers also have to think of a strategy to handle new client initialization and expired subscriptions by existing clients.

Whereas it is undoubtedly a useful data synchronization tool, Microsoft Synchronization Services is best suited to data where the rate of change is fairly low. It is highly customizable so it is useful for custom synchronization solutions and can be used with any database for which there is an ADO.NET provider. For most synchronization solutions involving mission-critical data on a SQL Server database, SQL Server merge replication provides a more reliable and efficient solution.

Author Bio

Andy Wigley, Device Application Development MVP, is a co-founder of APPAMundi Limited and is a technical project leader and consultant at the company. He has been a Device Application Development MVP since 2002, and is an author and frequent speaker at major conferences. He is the co-author of several books including the Microsoft Mobile Development Handbook, published by Microsoft Press®. He can be contacted at andy.wigley@appamundi.com.

Additional Information

Main Microsoft SQL Server Compact 3.5 product Web site at this Web site.

Microsoft Synchronization Services for ADO.NET in SQL Server Compact 3.5 SP1 Books Online at this Web site.

Microsoft SQL Server Compact Edition 3.5 Books Online at this Web site.