Background Data Synchronization with SQL Server 2005 Compact Edition

6/4/2007

Jim Wilson, JW Hedgehog, Inc.

February 2007

In this month's column, Jim talks about how to take advantage of the new features in SQL Server 2005 Compact Edition to improve continuity between local and server databases in mobile applications running on tablets, notebook computers, and Windows Mobile devices. He also covers how these new features allow you to more easily develop applications that better shield end-users from the details of data synchronization, provide better data synchronization progress feedback, and, when desired, how to cancel a long running data synchronization. As always, the concepts, code, and side-effects are covered in detail. (16 printed pages)

Microsoft® .NET Compact Framework

Microsoft .NET Framework

Microsoft .Windows Mobile® 5.0

Microsoft .Windows Mobile 6.0

Microsoft .Windows® XP

Microsoft .Visual Studio® 2005

Microsoft .SQL Server™ 2005 Mobile Edition

Microsoft .SQL Server 2005 Compact Edition

The introduction is required. If everything goes as expected, this edition of the column should be available shortly after the RTM release of SQL Server 2005 Compact Edition (SQL Server Compact Edition). With such perfect timing, I felt that it made sense to take a short break from our discussion of the State and Notifications Broker to talk about the SQL Server Compact Edition features that help to improve both the user and developer experiences when synchronizing data in mobile applications.

For those of you expecting the third installment of our State and Notifications Broker series, there’s nothing to worry about, Part III of the State and Notifications Broker series will be the subject of next month’s column. If you haven’t had the opportunity to read Part I or Part II of the State and Notifications Broker series, you may want to take a look at them before next month’s column.

Note:
If you’re a little confused by seeing the product name SQL Server Compact Edition, don’t worry, you’re not alone. After changing the product name from SQL Server CE 2.0 (sometimes called SQL Server Compact Edition 2.0) to SQL Server 2005 Mobile Edition in 2005, and then changing the product name from SQL Server 2005 Mobile Edition to SQL Server 2005 Everywhere Edition in early 2006, Microsoft settled on the name of SQL Server 2005 Compact Edition in November 2006. Although the product name has taken a step back in time, the product has not. SQL Server 2005 Compact Edition supports all of the same features that the product has through all of its name changes including integration with SQL Server Management Studio and Visual Studio 2005.

The version of SQL Server Compact Edition released with Visual Studio 2005, called SQL Server Mobile Edition at the time, introduced several improvements over earlier versions of the product including thread safety, support for simultaneous access by multiple applications, support for multiple merge replication subscriptions in a single database, and support for synchronizing merge replication subscriptions on a background thread.

Note:
The main enhancement of SQL Server 2005 Compact Edition over SQL Server Mobile Edition is that SQL Server 2005 Compact Edition makes the lightweight, local database that we’ve long used on Windows Mobile available to other Windows-based platforms such as tablet, desktop, and notebook computers. Everything that we’re going to talk about in this month’s column applies to both SQL Server 2005 Mobile Edition and SQL Server 2005 Compact Edition. I refer to the product as SQL Server 2005 Compact Edition or SQL Server Compact Edition only because it is the most current version.

In this month’s edition of the column we’re going to dig into the details of how to use these new SQL Server Compact Edition features to make merge replication data synchronization less intrusive to the end user and give your application the ability to monitor data synchronization progress, terminate data synchronization on demand, and more closely interact with individual data synchronization steps.

People commonly use the term “mobile application” to refer to any application that runs on a portable computer such as a Windows Mobile device or notebook computer. In reality, many of these applications are simply Web-based applications viewed from the portable computer’s browser. These Web-based applications work well as long as your computer maintains network connectivity. If your computer looses network connectivity, the application is no longer accessible; walking into an elevator or even standing behind a large truck in a warehouse can often cause loss of network connectivity. Even when using network connectivity provided by portable service providers using technologies such as GPRS, CDMA, GSM, EVDO, and others, connectivity can still be intermittent especially when traveling long distances or outside of metropolitan areas.

Note:
For simplicity, I use the generic terms “device” and “mobile device” to refer to any type of portable computer. Everything discussed in this edition of the column applies equally to notebook computers, tablets, and Windows Mobile devices. The technology discussed in this paper even applies to desktop computers but the stationary nature of desktop computers and their tendency to use wired-networks reduces the likelihood that you’ll need to apply such a solution on a desktop computer.

For an application to be truly mobile, the application needs to work even when the computer cannot access the network. For this to be true, the application must maintain a local copy of the data; most commonly the data is stored in a local database such as SQL Server Compact Edition.

Although storing data in the local database solves the problems associated with loss of network connectivity, storing the data locally introduces a new challenge: keeping the local data and server data in sync. The problem is that the locally stored data is not aware of changes that are made to the server data and the server is not aware of changes the user makes to the local data. The solution is to periodically synchronize the device data and server data. You can implement your own custom data synchronization solution but in most cases the easiest way to keep the local and server data in synch is to take advantage of the built-in SQL Server Compact Edition data synchronization solutions: Remote Data Access (RDA) and Merge Replication. In this edition of the column, we’ll focus on merge replication, specifically on performing merge replication data synchronization on a background thread.

Note:
This article assumes a basic understanding of merge replication. If you’re not familiar with merge replication, check out How Merge Replication Works. This edition of the column focuses on merge replication and does not address RDA data synchronization primarily because the changes to SQL Server Compact Edition and the ADO.NET managed provider do not significantly impact RDA data synchronization. If you would like to learn more about RDA, please see Introducing RDA. For a discussion of the differences between merge replication and RDA, take a look at How to Develop an Efficient Architecture based on SQL Server CE Replication Requirements.

Historically limitations in SQL Server CE 2.0 required your application to lock the user out of the database during data synchronization. As a result, data synchronization often required the user to be involved to avoid the application locking the user out of the database, and likely most of the application, at a time that was inconvenient for the user. In many cases, applications simply put the burden of data synchronization on the user, only performing data synchronization in response to a user action such as docking the device or the user selecting an explicit data synchronization menu option in the application. The changes that Microsoft has made to SQL Server Compact Edition and the managed provider now make implementing a background data synchronization solution much more practical then when working with earlier versions of the software. Using a background data synchronization solution can significantly reduce the impact on and the involvement of the user.

In addition to making background data synchronization more practical, SQL Server Compact Edition also simplifies the process of providing user feedback on the progress of data synchronization. No longer does data synchronization have to appear to freeze the application leaving the user to cross her fingers and blindly hope that the synchronization process is going well as has historically been the case.

SQL Server 2005 Compact Edition eliminates the limitations that make performing background data synchronization difficult. As you’ll see shortly, it not only removes the limitations but actually makes background data synchronization a first-class part of the SQL Server Compact Edition ADO.NET provider.

Note:
Just a reminder that all of the features I discuss in this article relate equally to the .NET Compact Framework and the full .NET Framework. For Windows Mobile developers, all of the features also apply equally to SQL Server 2005 Mobile Edition as they do to SQL Server 2005 Compact Edition.

What Changed?

SQL Server CE 2.0 was a single-user database and therefore managed locks only at the database level; once one application opened a database, no other application could open it. SQL Server CE 2.0 did allow multiple threads in the same application to access the same database, but SQL Server CE 2.0 was not thread-safe. SQL Server CE 2.0 did nothing to guarantee database integrity in a multithreaded application. If multiple threads in the same application were going to access the same database, the application was responsible to manage each thread’s access to the database so that only one thread would modify the database at a time.

By putting the burden of thread coordination on the application, SQL Server CE 2.0 made simultaneously performing data synchronization on one application thread while the user is updating the database on another application thread impractical. SQL Server 2005 Compact Edition is a thread-safe, multiuser database; therefore, the burden of thread coordination is now the responsibility of the database. Like many other databases, SQL Server 2005 Compact Edition uses small-granularity locking such as row-level locking and page-level locking to allow multiple threads to efficiently and safely access and modify the database.

Note:
For more information on SQL Server 2005 Compact Edition locking check out SQL Server 2005 Compact Edition Locking and SQL Server 2005 Compact Edition Lock Escalation.

Changes to the SqlCeReplication Class

Microsoft has also made changes to the SQL Server Compact Edition ADO.NET managed provider merge replication component, the SqlCeReplication class, to simplify the task of performing data synchronization on a background thread allowing you to more easily make data synchronization run asynchronously to the main application functionality. The changes to the SqlCeReplication class relate primarily to the Synchronize method. The Synchronize method is the method that performs the actual synchronization between the client data and server data and is therefore the most time consuming method to execute.

Providing asynchronous wrappers over normally synchronous methods is nothing new to the .NET Compact Framework or full .NET Framework; think of when you add a Web reference to a Visual Studio 2005 project. For example, when you add a Web reference to a Web service that exposes a DoWork operation, Visual Studio 2005 generates a Web reference class that exposes not only a DoWork method but also exposes two methods that allow you to access the Web service operation asynchronously: the BeginDoWork and EndDoWork methods. The asynchronous versions of the SqlCeReplication class’ Synchronize method follow this same naming pattern: BeginSynchronize and EndSynchronize.

Just as you expect, the BeginSynchronize method executes the Synchronize method on a background thread and the EndSynchronize method harvests the results of the Synchronize method once the background data synchronization process is complete. The only possible result from the EndSynchronize method is an exception; if an exception occurs on the background thread during the synchronization process, the exception is thrown to your application code when you call the EndSynchronize method. We’ll see some code examples of using these methods shortly.

In addition to the new BeginSynchronize and EndSynchronize methods, the SqlCeReplication class also includes the new CancelSynchronize method. As the method name implies, the CancelSynchronize method allows your application to terminate the synchronization process running on the background thread.

We’ve talked about the changes to SQL Server 2005 Compact Edition and the managed ADO.NET provider that make background data synchronization more practical but nothing provides the complete story better than having the chance to review the application code. As we’ll see as we look at the code, the design of the background data synchronization features of the SQL Server Compact Edition managed ADO.NET provider allow you to decide whether you would like to take a minimalist approach of simply executing the background data synchronization with little involvement from your application code, or you can integrate your code with the background data synchronization process and closely track the status and progress of the synchronization process.

The Basics of Background Data Synchronization

Let’s start by looking at the most simple way to perform background data synchronization. The following code example demonstrates how to start the background data synchronization.

SqlCeReplication _repl = null;
IAsyncResult _savedAsyncResult = null;

private DoBackgroundSync()
{
  _repl = CreateReplicationInstance();
  _savedAsyncResult = _repl.BeginSynchronize(
      new AsyncCallback(OnSynchronizeComplete), null);
}

Prior to calling the BeginSynchronize method, you create the SqlCeReplication instance and populate the required properties just as you do when you use the more familiar Synchronize method.

Note:
The CreateReplicationInstance method shown in the preceding code example creates a new SqlCeReplication instance and sets all of the required parameters. The implementation of the CreateReplicationInstance method is not shown within this paper because it relates to the more general concept of merge replication and is therefore out of scope for the topic being discussed. However, because the method is used so frequently throughout this paper, I have included the method implementation in the appendix of this paper.

As you can see, unlike the Synchronize method which takes no parameters, the BeginSynchronize method accepts two. The first parameter must be an instance of the AsyncCallback delegate. The background data synchronization thread uses this delegate to notify your application when the synchronization is complete. In the case of the preceding example code, the background thread will notify the application’s OnSynchronizeComplete method.

Note:
You must provide an instance of the AsyncCallback delegate when calling the BeginSynchronize method. The current implementation of the BeginSynchronize method does not verify that the parameter is non-null before attempting to execute the delegate; as a result, the background thread performs the full synchronization but then throws a NullReferenceException at the end when it attempts to execute the delegate.

The BeginSynchronize method’s second parameter is an object reference to which you can pass the object of your choice. This parameter is provided as a convenience to the programmer to allow you to attach any application information that you would like to associate with the data synchronization. The BeginSynchronize method makes the object reference part of the background data synchronization state information. We’ll see some examples of how this can be useful shortly.

One other key difference the BeginSynchronize method has from the Synchronize method is that the BeginSynchronize method has a return value. Just as asynchronous delegates do, the BeginSynchronize method returns an IAsyncResult interface reference. The IAsyncResult reference contains important state information about the execution of the background data synchronization that we’ll need when determining the success or failure of the background data synchronization.

Note:
The IAsyncResult interface also provides several properties that are useful in scenarios where an application developer wants to use multithreaded programming techniques to directly manage and interact with asynchronously executing tasks. The IAsyncResult interface is a common part of many asynchronous features of the .NET Framework and .NET Compact Framework; an explanation of how to use the IAsyncResult interface when using multithreaded programming techniques is available in Calling Asynchronous Methods Using IAsyncResult. One important thing to be aware of is that the EndSynchronize method does not conform to the standard IAsyncResult End-method behavior of blocking until the background task (data synchronization in this case) is complete as documented in Blocking Application Execution by Ending an Asynchronous Operation. If you want to block an application thread until the synchronization process completes, you should use the technique documented in Blocking Application Execution Using an AsyncWaitHandle.

Once your application calls the BeginSynchronize method, the main application thread is free to continue doing other work in the foreground while the data synchronization runs in the background. While performing the background data synchronization, your application continues to run smoothly and be responsive to the user.

When the background data synchronization completes, the background thread notifies your application by calling the AsyncCallback delegate that was passed to the BeginSynchronize method. In our case, the AsyncCallback delegate contained the application’s OnSynchronizeComplete method, which is shown in the following code example.

void OnSynchronizeComplete(IAsyncResult asyncResult)
{
  try
  {
    _repl.EndSynchronize(_savedAsyncResult);
    Display("Bkgrnd Sync Complete");
    Display("PubChanges: " + _repl.PublisherChanges.ToString());
  }
  Catch(Exception ex)
  {
    Display("Error: " + ex.Message);
  }
  _repl.Dispose();
  _repl = null;

}

The primary responsibility of the OnSynchronizeComplete method is to call the EndSynchronize method; you must pass the IAsyncResult interface reference returned by the BeginSynchronize method to the EndSynchronize method. Remember that the IAsyncResult interface reference contains important state information about the background data synchronization. By calling the EndSynchronize method, your application can determine whether the background data synchronization completed successfully.

As you can see, the EndSynchronize method doesn’t have a return value nor does it set any error codes; however, notice that the OnSynchronizeComplete method has the call to the EndSynchronize method in a try/catch block. The try/catch block is not there to handle an error that the EndSynchronize method might encounter; rather the try/catch block is there to handle any error that may have occurred during the background data synchronization. During the background data synchronization, the background thread catches any exception that may occur and stores that exception as part of the background data synchronization state information. When the application calls the EndSynchronize method, the EndSynchronize method checks the background data synchronization state information and, if an exception is found within the state information, the EndSynchronize method rethrows the exception on the behalf of the background data synchronization. The call to the EndSynchronize method is your applications opportunity to handle and respond to data synchronization exceptions.

One note of caution: the call to the OnSynchronizeComplete method does not run on the main application thread, instead the method call runs on the background thread that executed the data synchronization. For this reason, it’s important that you do not attempt to directly update the user interface from within this method. Doing so will result in an exception. You must use the Control.Invoke method to marshal the call to the appropriate thread as I’ve done in the Display method, which the OnSynchronizeComplete method calls to add text to the main form’s multiline TextBox.

private void Display(string message)
{
  if (textDisplay.InvokeRequired) // switch to UI thread if necessary
    textDisplay.Invoke(new DisplayDelegate(Display), message);
  else
    textDisplay.Text += message + "\r\n";
}

As the above code example shows, the Display method checks to see if it is safe to update the user interface from the current thread by checking the InvokeRequired property on the TextBox that will display the text. If the InvokeRequired property returns true then the Display method marshals a call back to itself on the correct thread by calling the Invoke method. When the Display method is called on the correct thread, the application TextBox is simply updated with the passed text.

Note:
If you are unfamiliar with the issues associated with interacting with user interface controls from background threads or if you would like a refresher, check out my earlier column Multithreading and the User Interface for a discussion of the topic.

Using Synchronization State

It’s not uncommon for an application to perform multiple background tasks or to execute the same background task several times; for example, our application might perform the data synchronization many times during a single program execution. As mentioned previously in this paper, each call to the BeginSynchronize method creates a state object and returns the state object to your application in the form of the IAsyncResult interface reference. That state reference is tied directly to the background data synchronization started by the call to the BeginSynchronize method. As shown in the preceding OnSynchronizeComplete code example, to retrieve the results of that background process, the application calls the EndSynchronize method using the same SqlCeReplication instance that was used to call the BeginSynchronize method and the same state information, in the form of the IAsyncResult interface reference, that was returned by the call to the BeginSynchronize method.

As an application becomes more sophisticated, the task of storing multiple SqlCeReplication class references and the corresponding IAsyncResult interface references can be complex. To simplify this task, the background thread passes the same state information to the AsyncCallback delegate’s method, the OnSynchronizeComplete method in the preceding example, as the BeginSynchronize method returned. This is shown as the asyncResult parameter in the OnSynchronizeComplete method shown earlier. Remember also, that the BeginSynchronize method allows you to pass an arbitrary object that the BeginSynchronize method automatically associates with the background data synchronization state.

The following example code shows how to use the background data synchronization state to simplify the code associated with starting and handling the completion of the background data synchronization process.

private void DoSimplifiedBackgroundSync()
{
  SqlCeReplication repl = CreateReplicationInstance();
  repl.BeginSynchronize(OnSimplifiedSynchronizeComplete, repl);
}

void OnSimplifiedSynchronizeComplete(IAsyncResult asyncResult)
{
  SqlCeReplication repl = null;
  try
  {
    repl = (SqlCeReplication)asyncResult.AsyncState;
    repl.EndSynchronize(asyncResult);
    Display("Bkgrnd Sync Complete");
    Display("PubChanges: " + _repl.PublisherChanges.ToString());
  }
  Catch(Exception ex)
  {
    Display("Error: " + ex.Message);
  }
  If (repl != null)
    repl.Dispose();
}

The preceding code example takes the burden of tracking the background data synchronization state from your application code and instead takes advantage of the state management that the background data synchronization provides. The preceding code example eliminates the two class member fields, _repl and _savedAsyncResult, that were used in the earlier code examples. The _repl member field is no longer required because the SqlCeReplication instance that is calling the BeginSynchronize method passes itself as the second parameter to the method. By doing so, the SqlCeReplication instance becomes part of the background data synchronization state and is therefore stored within the IAsyncResult interface reference that the BeginSynchronize method creates.

When the background data synchronization completes, it calls the OnSimplifiedSynchronizeComplete method. The OnSimplifiedSynchronizeComplete method’s asyncResult parameter refers to the same state instance that is returned by the call to the BeginSynchronize method. Using the OnSimplifiedSynchronizeComplete method’s parameter asyncResult, the application retrieves the SqlCeReplication instance that made the original call to the BeginSynchronize method and uses this SqlCeReplication instance to call the EndSynchronize method.

Note:
The preceding code example takes advantage of the C# version 2.0 compiler’s ability to infer the delegate type when calling the BeginSynchronize method. Using this technique, you can simply pass the method name as the first parameter to the BeginSynchronize method, the OnSimplifiedSynchronizeComplete method in this case; you do not have to explicitly call the delegate constructor and pass the method name as the parameter to the constructor. The earlier code example containing a call to the BeginSynchronize method explicitly called the AsyncResult delegate constructor to clarify the delegate’s involvement. This same compiler feature of inferring the delegate type could have been used in that earlier code example without affecting the application’s behavior.

When initiating background data synchronization, neither the technique just shown of using the background data synchronization state nor the technique shown in the first code example of explicitly managing the state through class members is the “correct” solution. Each technique is useful for different situations. Storing the SqlCeReplication class instance and IAsyncResult interface reference is necessary when your application needs access to these values outside of the AsyncResult delegate method; however, if your application only uses these members within the AsyncResult delegate method, you can take advantage of the background data synchronization built-in state management to simplify your application code.

Tracking Merge Replication Progress

We’ve all used applications where the user interface freezes or becomes unresponsive during a long-running task (some of us may have also written them). The cause, of course, is that the main application thread is not able to update the user interface or respond to user input when it is busy doing something else; for example a merge replication data synchronization. Moving the task to a background thread allows the application to continue to update the user interface and respond to user input. By having the long-running task on a background thread, the main thread is also available to display progress information about the long-running task.

The SqlCeReplication class provides an alternate version of the BeginSynchronize method that accepts delegate references that are called during the synchronization process to inform your application of progress information. Table 1 lists the three delegates, their parameters, and a short description of each.

Table 1 Synchronization Progress Delegates

Delegate Parameters Description

OnStartTableUpload

  • IAsyncResult containing synchronization state
  • String containing the name of the table whose changes are being uploaded

Called as changes to a local table are being uploaded to the server. It is called once for each local table that has changed since the last synchronization.

OnStartTableDownload

  • IAsyncResult containing synchronization state
  • String containing the name of the table whose changes are being downloaded

Called as changes to a server table are being downloaded to the device. It is called once for each server table that has changed since the last synchronization.

OnSynchronization

  • IAsyncResult containing synchronization state
  • Int32 containing the reconciliation completion percentage

Called periodically during the server reconciliation process to provide the reconciliation completion percentage.

As shown in shown in Table 1, each delegate accepts two parameters. The first parameter is the background data synchronization state information; again, this is the same state information returned by the BeginSynchronize method. The second parameter is specific to the delegate: a table name in the OnStartTableUpload and OnsStartTableDownload delegates and an integer progress percentage in the OnSynchronization delegate. The following example code demonstrates using the synchronization progress delegates to display data synchronization progress information to the user.

private void DoMonitoredBackgroundSync(object sender, EventArgs e)
{
  SqlCeReplication repl = CreateReplicationInstance();
  repl.BeginSynchronize(
     OnSimplifiedSynchronizeComplete,
     SqlCeReplication_OnStartTableUpload,
     SqlCeReplication_OnStartTableDownload,
     SqlCeReplication_OnSynchronization, 
     repl);
}

public void SqlCeReplication_OnStartTableUpload(IAsyncResult ar, string tableName)
{
  Display("Table(upload): " + tableName);
}

public void SqlCeReplication_OnStartTableDownload(IAsyncResult ar, string tableName)
{
  Display("Table(download): " + tableName);
}

public void SqlCeReplication_OnSynchronization(IAsyncResult ar, int percentComplete)
{
  Display("Progress: " + percentComplete.ToString() + "%");
}

As the preceding code example shows, handling the three synchronization progress events is as simple as passing the appropriate delegate handler methods to the BeginSynchronize method. All three of the BeginSynchronize method’s delegate parameters are optional; pass null for the parameter corresponding to any delegate you do not want to handle.

You can safely access the SqlCeReplication properties from within the progress event delegates. Properties related to statistics about the data synchronization such as PublisherChanges, and SubscriberChanges can be especially helpful to determine the results of the reconciliation phase of the synchronization process. Prior to the completion of the reconciliation phase these properties will be zero; therefore, you’ll find that the properties have meaningful values when accessed within the OnSynchronize event handler as long as the completion percentage has a value of 100 percent. The values are also meaningful when accessed within the OnStartTableDownload event handler and the AsyncCallback delegate event handler that is called at the completion of the synchronization; the properties are valid within these event handlers because these events occur after the completion of the reconciliation phase. These values are never valid within the OnStartTableUpload event handler because this event occurs prior to the execution of the reconciliation phase.

Merge Replication Progress Implementation

The data synchronization progress delegates are helpful and provide your application with basic information about the activity of data synchronization. As helpful as this information is, the progress delegates do not give the complete picture of data synchronization.

The following list shows the steps involved in data synchronization and the relationship between those steps and the progress notification delegates.

  1. Identify the client tables that have been changed since the last synchronization.
  2. Execute the OnStartTableUpload delegate identifying the table whose changes are to be uploaded.
  3. Upload the changes for the table.
  4. Repeat steps 2 and 3 for each client table that has changed since the last synchronization.
  5. Client sends synchronization request to the server agent running within Internet Information Services (IIS).
  6. Server agent initiates the change reconciliation phase on SQL Server.
  7. Client sends synchronization status check message to server agent.
  8. When server responds to status check, client executes the OnSynchronization delegate with the returned reconciliation progress percentage.
  9. Repeat steps 7 and 8, until reconciliation finishes.
  10. Execute the OnStartTableDownload delegate identifying the server table whose changes are to be downloaded.
  11. Download server table changes to client.
  12. Repeat steps 10 and 11 until all server changes are downloaded.
  13. When all changes applied to client, execute AsyncCallback delegate to indicate that the synchronization process is complete.

One key point to recognize in the preceding list is that the OnSynchronization delegate provides progress information only for the change reconciliation phase of the data synchronization process; the OnSynchronization delegate does not provide progress information for the overall synchronization process.

You’ll find that the percent progress that the OnSynchronization delegate reports is not particularly useful for tracking the exact progress of change reconciliation. In most cases, the OnSynchronization delegate executes a number of times passing a 0 percent progress value and then suddenly jumps to 50 percent or even 100 percent. It is not uncommon for the OnSynchronization delegate to execute passing 100 percent two or three times.

Rather than relying on the OnSynchronization delegate’s percentage value, you should instead use the delegate as a confirmation that the reconciliation process is executing and that there are no connectivity or server problems. Although having the exact progress percentage of the overall data synchronization would be more helpful, the fact that the OnSynchronization delegate at least provides confirmation that things are healthy and progressing still provides a great deal of value.

Like concurrency in any other scenario, performing merge replication data synchronization while other threads in the same application or other applications modify the same database that is being synchronized introduces the opportunity for conflicts. SQL Server 2005 Compact Edition prevents data corruption during concurrent access but it is your responsibility to deal with any exceptions or lock conflicts that may occur.

The best way to deal with the challenges created by concurrency is to minimize concurrent access as much as you reasonably can. Even though SQL Server Compact Edition allows your application to perform merge replication while other aspects of your application are accessing the database, you’ll still want to manage concurrent access to the same data. Like any other concurrent data access situation, if data access is highly contentious, there will be problems.

When merge replication data synchronization attempts to apply updates from the server to the local database, data synchronization honors any locks that other threads or applications may hold. If other aspects of your application (or any other application) have locked data that data synchronization needs to update, data synchronization waits for the lock(s) release. If those locks are held for too long, the data synchronization fails.

Historically, we prevent these concurrency issues by preventing the user from performing any actions that require that the application interact with the database during data synchronization but that’s exactly what we’re trying to avoid; one of the main goals of this paper is to identify ways that your application can perform data synchronization while still allowing the user to interact with the application and the data. A solution that works for many situations is to take advantage of the SQL Server Compact Edition’s added support for multiple merge replication subscriptions in the same database.

Rather than create one big subscription for all of the data in the database, create several subscriptions that correspond to the data access patterns of the various parts of your application. In this way, when the user is working in an area of the application that affects a specific subset of data, you can quietly synchronize the subscription corresponding to another segment of the data without affecting the user. In some cases it may be difficult to infer what data is safe to synchronize based on the user’s current activity, so you may find it more practical to simply disable certain features of the application when data synchronization is executing for the merge replication subscription corresponding to that part of the application. This way the application can keep data up to date while allowing the user to remain productive using other sections of the application.

You’ll want to be creative when working out the replication data partitioning. You’ll also need to have a good understanding of your data and your application’s data access patterns. Remember that a merge replication subscription does not have to be for an entire table. Using horizontal (limiting synchronized records) and vertical (limiting synchronized columns) partitioning you can limit the records and columns a specific subscription affects. Horizontal partitioning is useful when synchronizing with either SQL Server 2000 or SQL Server 2005. Vertical partitioning is only useful when synchronizing with SQL Server 2005. The difference is that when synchronizing with SQL Server 2005, only those columns that have changed are exchanged between the server and client databases; SQL Server 2000 always exchanges the entire row.

Note:
For more information on partitioning merge replication data see Filtering Published Data for Merge Replication

What Happens If a Conflicting Change Occurs During Data Synchronization?

In many applications, although it may be reasonable to partition the data to minimize the likelihood of user actions making data modifications that overlap with the data that the application is synchronizing with the server, it may not be possible to guarantee that such overlapping modifications never occur. Fortunately, a change to the local data during data synchronization does not lead to synchronization failure or data loss, provided that the change does not hold long-lasting locks. If the application makes a change to the data during data synchronization, SQL Server Compact Edition tracks the local change just as it normally does and then sends the change to the server on the next synchronization.

It’s not uncommon for data that is synchronized between two databases to contain conflicts, that is, situations where the same data has been changed in both places. Most of the time, the actual data changes that create the conflict occur prior to data synchronization; in this case the conflict is simply resolved by the server during the next data synchronization.

Note:
As you may know, SQL Server 2000 and SQL Server 2005 have very powerful conflict resolution engines that allow you to configure how you would like the database to handle conflicts that occur during merge replication data synchronization. You may want the server version of the data to always prevail, or you may want the most recent version of the data to prevail, or you might even want to write a custom conflict resolution component; SQL Server provides many options for handling conflicts. For information on SQL Server conflict resolution you may want to checkout Merge Replication Conflict Detection and Resolution or Advanced Merge Replication Conflict Detection and Resolution.

Conflict resolution may require a little more work on your part when the application makes a change to the local database during data synchronization that creates a conflict with a server change that has yet to be applied to the local database. The opportunity for this kind of conflict to occur is when the application makes a change to the local database after data synchronization uploads the local table changes to the server but before data synchronization applies all of the server changes to the local database. If the server change were applied, the server change would overwrite the application change. Fortunately, merge replication data synchronization is smart enough to handle this situation correctly; when applying the server change would overwrite a client change that occurred after the client changes were sent to the server, data synchronization does not apply the server change to the data and instead notes that a conflict has occurred. This situation is considered a subscriber conflict.

You can check the SqlCeReplication.SubscriberConflict property after data synchronization completes to determine if any such conflicts have occurred. In the case of a subscriber conflict, the local database always contains the local version of the data at completion of the data synchronization process; to determine whether the local or server version of the data should ultimately be stored requires the assistance of the conflict resolution engine. SQL Server Compact Edition does not have a conflict resolution engine and therefore is not able to apply conflict resolution logic. To resolve the conflict, SQL Server Compact Edition needs the assistance of SQL Server; therefore, the application should perform another merge replication data synchronization. During this data synchronization, the list of local changes, including the data from the subscriber conflict, are sent to server, changes and conflicts are resolved on the server (including any new local and server changes since the last data synchronization), and the results are applied to both the local and server databases just as they normally are during data synchronization.

As a general rule, your application should always check the value of the SqlCeReplication.SubscriberConflict property at data synchronization completion. If the SqlCeReplication.SubscriberConflict property has a non-zero value, the application should perform data synchronization again to resolve the conflicts.

Note:
For more information on data conflicts during merge replication data synchronization, see Multiuser Access and Synchronization.

As you can see, SQL Server 2005 Compact Edition (and SQL Server 2005 Mobile Edition) has come a long way from SQL Server CE 2.0. The SQL Server 2005 Compact Edition features we’ve discussed this month include multiuser and multithread safety, support for multiple merge replication subscriptions in a single database, the addition of background data synchronization support to the SQL Server Compact Edition ADO.NET managed provider, and the ability to cancel and track data synchronization progress. These features open up new opportunities for improving the user experience of our applications.

Each of these features alone is important but their value becomes most apparent when you combine them together. To go one step further, by taking advantage of the State and Notifications Broker API’s (discussed in the previous two editions of this column) ability to notify your application when network connectivity is available, you can create powerful data synchronization solutions that keep your mobile application data up to date while minimally impacting the user experience.

Whether you’re a Windows Mobile developer or you develop applications that target the other Windows-based platforms such as tablet and notebook computers, I encourage to take the time to really understand the power of SQL Server 2005 Compact Edition and the important role it plays in creating mobile applications.

That's it for this month. We’ll be picking up with the third and final part of the State and Notifications Broker API series next month. Don’t forget to have a look at Part I and Part II of that series if you haven’t already done so. If you have any questions about what we’ve covered, or if you have ideas for things that you would like to see me cover, please contact me through my blog.

Appendix A

The CreateReplicationInstance method shown in the code examples of this paper is provided here for your reference. As noted earlier, creating a SqlCeReplication instance is identical whether you are performing data synchronization as a foreground task using the SqlCeReplication.Synchronize method or as a background task using the SqlCeReplication.BeginSynchronize method.

private static SqlCeReplication CreateReplicationInstance()
{
  SqlCeReplication repl = new SqlCeReplication();

  repl.InternetUrl =
    @"http://server_name_or_ip/AdvWorksMobile/sqlcesa30.dll";
  repl.Publisher = @"HEDGYDEV02";
  repl.PublisherDatabase = @"Northwind";
  repl.PublisherSecurityMode = SecurityType.DBAuthentication;
  repl.PublisherLogin = "sa";
  repl.PublisherPassword = "sapassword";
  repl.Publication = publication;
  repl.Subscriber = publication;
  repl.SubscriberConnectionString = LocalDbConnectionString;

  return repl;
}
Show: