By Tim Landgrave

One of the most common questions asked during .NET architecture presentations involves the need for persisting data locally in a distributed application. Such questions usually focus on using data sets as a local persistence mechanism and Web services as the downloading and synchronization mechanism. I caution architects to avoid considering these tools, unless the data accessed is mostly read-only. Instead, first try more tried and true methods for replication and synchronization. Let’s look at alternative local storage mechanisms and synchronization facilities in detail.

Two options for local data stores
For applications that will download data, process it quickly, and then possibly return results, the use of data sets is ideal. data sets allow fast, in-memory access of complex data structures with limited filtering and sorting capabilities, as well as the ability to persist data as files in the local file system. If the application needs more robust offline data manipulation, however, you’re likely to turn to a more powerful local database mechanism.

Microsoft provides two different versions of SQL Server 2000 for use as lightweight, local database engines. For Windows CE devices, you can use SQL Server CE 2.0 (SQLCE). SQLCE is a version of SQL Server 2000 rewritten specifically for the limited memory and processor speed constraints of the Windows CE environment.

Laptops, tablets, or remote desktops can take advantage of MSDE. MSDE is the actual SQL Server 2000 engine code that’s been throttled to allow a limited number of simultaneous connections (currently, five). In addition, most of the replication functions and all of the Analysis Services functionality have been removed to allow MSDE to operate efficiently on desktops. Microsoft has begun using MSDE in some of its new products, including the new CRM application where it serves as the offline store for CRM data.

These local data stores have two features that make them ideal for offline data access. First, they are well supported by the .NET Framework. The SQLServerCE namespace includes all of the classes necessary to manipulate SQLCE, and the standard SQLClient namespace allows a Windows CE application to manipulate a remote SQL Server database. SQLClient can also be used by a Windows application to manipulate a local copy of MSDE or a remote SQL Server database. Second, both data stores can be subscribers to a remote SQL Server publication, allowing them to participate in a merge-replication scenario.

Remote data stores
SQL Server 2000 (and to a lesser extent SQL Server 7.0) works in tandem with its corresponding local versions to deliver robust replication and synchronization. SQL Server 2000 supports two different mechanisms for managing data that’s been processed by a local engine.

No SQL Server?

If you’re using another database, or if you are processing data from other systems, an option would be to move your data to an intermediate SQL Server and then use standard synchronization mechanisms to deploy the data from there. Doing so will prevent you from spending time coding your own synchronization mechanism if your database vendor doesn’t have a local database engine.

The first mechanism is SQL Server’s Merge Replication facility. Using Merge Replication, both the client and the server track changes to the data and, during synchronization, only the changes go back and forth. Because the server tracks all changes, it may require more substantial server hardware and connectivity to scale effectively. Merge Replication is available only on SQL Server 2000.

SQL Server’s Merge Replication engine is very effective for offline data manipulation scenarios. It allows for vertical and horizontal data partitioning and dynamic horizontal filters to reduce the size of the dataset that’s transferred back and forth. It can automatically create auto-ranged identity columns so that multiple subscribers can create unique identity columns that resolve automatically during synchronization. And its ability to use either standard or custom conflict resolution rules makes it easier for developers to ensure the integrity of data during synchronization.

The second mechanism is Remote Data Access (RDA). RDA is ideal for scenarios where clients make all of the changes. RDA doesn’t have a mechanism to automatically synchronize if changes are made to data on the server while the client has the data “checked out.” RDA also works with either SQL Server 2000 or SQL Server 7.0. In an RDA scenario, your local application should connect to the server, download tables to a copy of SQL Server on the local device, and then disconnect. Once offline, the local SQL Server store (e.g., SQLCE or MSDE) has the ability to track all of the changes made. When reconnecting, the changes are pushed back up to the server. RDA is easy to set up and to use and scales very well because the server isn’t required to track changes to the data.

Deciding on a replication mechanism
The decision to use Merge Replication or RDA need not be mutually exclusive. For example, in an offline-ordering scenario, you may choose to update the catalog with Merge Replication but to process local transactions using RDA. In either scenario, you need to make sure that you understand the licensing restrictions. You must also make sure that you have a Client Access License for each client that participates in replication or that you’ve purchased a CPU license for the SQL Server that serves as the replication hub. The good news is that even though most competing local engine technologies charge you a per-device fee for their local store, as long as you’ve licensed the desktop edition of SQL Server, you don’t have to pay a license fee to use the local SQL engines, regardless of the number of clients you deploy. You can use the money you save to create a better solution for your customers.