SQL Server CE can put your database in the palm of your hand
Unlike transactional replication, in which only the publisher has the authoritative copy of the data, merge replication lets you regularly share changes in an environment in which each subscriber has an equal right to change the data. In the absence of conflicts, merge replication is simple: It shares a change made on one replication partner with all the other partners during an operation known as synchronization. One difference between SQL Server 2000—based subscribers and SQL Server CE subscribers is that SQL Server 2000—based subscribers use the Merge Agent to send local changes directly to the subscribers that need updating. SQL Server CE subscribers, however, send updates to the publisher and receive changes from other subscribers through the publisher. SQL Server CE can't publish its own databases, but it can receive changes from non—SQL Server CE replication partners through the publisher.
Conflicts occur when two or more subscribers are updating the same row in a table. (Whether the conflict occurs at the column level or the row level depends on how you configured the publication.) When a conflict occurs, SQL Server 2000 invokes a conflict resolver, which can be either the default resolver, a custom resolver you write for your own rules, one of the prebuilt resolvers that ships with SQL Server 2000, or an interactive resolver. (For more information about merge replication in SQL Server 2000 and 7.0, see the sidebar "Finding What You Need to Use SQL Server CE," page 40.) Whichever option you choose, conflict resolution occurs on the publisher, not on the SQL Server CE subscriber.
SQL Server CE always uses anonymous subscriptions, thereby eliminating much of the complexity of conflict resolution. For an anonymous subscription, the publisher takes ownership of the change and sends updates to the other subscribers as if the publisher had made the change instead of the SQL Server CE subscriber. The default conflict-resolution policy is that updates at the publisher always take precedence over updates at subscribers. Therefore, the first subscriber to merge a change with the publisher wins the conflict, and that subscribers' changes overwrite any changes the other subscribers made to that row.
Understanding how and when subscribers update the publisher is important because Pocket PC devices are highly mobile by design. Predicting when a device will be connected to the network is essentially impossible. In fact, that high degree of autonomy is what makes Personal Digital Assistants (PDAs) so popular. Merge replication, therefore, is an ideal solution for a database running on mobile devices because the publishers and subscribers can stay unconnected for long periods of time.
When designing applications that use merge replication, remember that each subscriber can make changes independently of all other subscribers and the publisher. Typically, the results of conflict resolution during synchronization are easier to predict if you segment your tables into subsets of rows that only a particular device or user will modify.
An easy way to implement filtering based on the identity of the subscriber is to use dynamic filters. Available only with SQL Server 2000 merge replication, dynamic filters use a system function such as suser_sname or suser_sid in the WHERE clause of a SELECT statement to limit which rows a subscriber receives. If each user will be working with a different subset of the data, dynamic filters are a good way not only to minimize the amount of data sent to the mobile device but also to simplify conflict resolution by eliminating the potential for conflicts.
The Replication Architecture
Figure 1 shows a block diagram of the architecture SQL Server CE uses for replication. Notice that SQL Server CE replication uses Microsoft IIS as the intermediate layer between the client and the database server. To get around the domain-authentication support limitations in early versions of Windows CE, the SQL Server CE developers chose to route all replication requests through IIS. Also, the IIS server can act as a buffer to store any changes if the connection between IIS and SQL Server CE fails. Because Windows CE offers a highly dynamic environment for applications, you must anticipate that resources (e.g., network or modem cards, power, storage cards) could disappear with little warning.
The primary components in the replication process are the SQL Server CE Server Agent and the SQL Server CE Client Agent. The Server Agent (sscesa10.dll) is an Internet Server API (ISAPI) DLL file that runs on IIS and routes all replication traffic between SQL Server 2000 and SQL Server CE. When a client initiates the synchronization process, the Client Agent connects to the Server Agent and tells it the SQL Server 2000 server's address, which publication to replicate, and the account name and password for the server. The Client Agent also sends to the database any changes that have occurred on the client since the last synchronization. The Server Agent then connects to the SQL Server 2000 server, synchronizes the client's changes with the publisher, and gathers all the changes to send to the client. Rather than sending those changes immediately, the Server Agent buffers them on the IIS server until it has received all the changes, then disconnects from the publisher.
When the synchronization between the Server Agent and the publisher is complete, the Server Agent sends each change to the Client Agent, which applies the change to the local database. If the connection between the Server Agent and the Client Agent is disrupted, the Server Agent keeps the changes in a file on the IIS server's hard disk until the Client Agent contacts the Server Agent again. The next time the Client Agent synchronizes, the Server Agent finishes sending all the changes before starting a new synchronization cycle. When the Server Agent has sent all the changes to the client, it terminates the connection and deletes the file it used as a temporary buffer.
Two DLLs implement merge replication on the client. The database engine (ssce10.dll) uses triggers to track which rows have changed and when those changes occurred. Each time a user makes a change, the triggers write information to special tables that the Client Agent uses to determine what to send to the Server Agent during synchronization. The Client Agent (ssceca10.dll) is in memory only when an application instantiates the replication object to synchronize with the Server Agent. The Client Agent collects all the changes made on the client, sends them to the Server Agent, then applies all the changes the Server Agent sends.
Because of SQL Server CE's architecture, all subscriptions are pull subscriptions that the client initiates. If you want replication to occur automatically, you need to set up a timer object to trigger replication at regular intervals. Or, you can have the application respond to the events that Windows CE sends to applications when new resources, such as network cards, become available. For the sample application in this article, I included a menu item that lets the user manually initiate synchronization.
Prev. page
[1]
2
3
next page 
