Downloads
20930.zip

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.

So, how can you use replication if your Windows CE device doesn't have a network card? You have three alternatives. First, if your device has a modem, you can connect to a server that's running RRAS. The service is available by default on Windows 2000 Server and is an add-on for NT 4.0. After you establish a modem connection to the RRAS server, you can initiate synchronization with the Server Agent running on IIS on the same network. Second, if your IIS server is connected to the Internet and you can establish a modem connection to an ISP, you can initiate synchronization over the Internet. The Client Agent even supports Secure Sockets Layer (SSL) encryption in case you need to use the Internet as your network.

A new alternative for SQL Server CE 1.1 is to use your desktop computer's network connection through the SQL Server CE Relay Agent, which runs on your desktop computer. This ActiveSync-aware application takes data you send to the desktop through the ActiveSync connection and relays it to a particular Server Agent. The Relay Agent relays data the Server Agent sends back to the client across the same connection. Each instance of the Relay Agent can support one connection to each Server Agent. The Relay Agent also includes a command-line option that configures ActiveSync to load the agent when a particular Windows CE device makes a connection. That way, the client can initiate synchronization without the user needing to run a separate program on the desktop computer.

When using the Relay Agent, you need to use the string "ppp_peer:port number" (where port number is the number of an available port on the desktop computer) in the SQL Server CE replication object's InternetProxy-Server property. The replication object will then redirect all synchronization requests through the ActiveSync connection to the port number you specified.

When you use the Relay Agent approach, the Windows CE device doesn't need a network card or a modem. All devices that adhere to the Pocket PC standard include both a serial or USB connection and an Infrared Data Association (IrDA) standard transmitter/receiver. Both the handheld PC (H/PC) 2.x and H/PC 2000 standards include provisions for at least one serial connection as well. By using these alternative connections, you can synchronize a database on a palm-sized or handheld device without using one of the CompactFlash (CF) or PC Card slots for a network card or modem. The Pocket PC devices currently on the market have only one CF slot or one PC Card slot, and using that slot for data storage is more appropriate for a database application than using it for a network card or modem. The addition of the Relay Agent in SQL Server CE 1.1 means that the size of the database your device can hold is limited only by the size of the storage card.

The Sample Application


To help you get started with SQL Server CE replication, I wrote a sample application that's more straightforward than the sample application that comes with SQL Server CE. (You can download the source code for this sample application by following the instructions in "More on the Web," page 42.) The application is a simple mileage-logging program that stores in a SQL Server CE database the date, origination point, starting mileage, destination, and ending mileage for each trip. The application downloads the initial database through replication and sends updates back to SQL Server 2000 during synchronization. As Figure 2 shows, users can select the origination and destination locations through combo boxes, which the application populates with data from the TripLocations table. The actual trip log data resides in the TripLogs table.

I should mention that the application doesn't filter data based on the name of the handheld device or the user. If you use this application as the basis for a program that supports multiple users, you'll need to add code that limits the replicated records to the ones a particular user creates, as I noted earlier. For example, you could use a dynamic filter in the publication.

Before digging into the details of configuring replication, I need to make a comment about security. Because of the replication architecture that SQL Server CE uses, authentication occurs at four points, and permissions are checked at three points. I've found that diagnosing security problems is difficult because they can occur on up to four different computers. Therefore, I recommend that you set up a test environment in which you can permit unrestricted access to the data and files you use for replication. When you have everything working correctly, you can tighten security until you get the level you need.

To create the test environment, install Win2K or NT 4.0 and create at least one NTFS partition (or volume, if you're using Win2K). If you're using NT, use the NT 4.0 Option Pack to install Internet Information Server (IIS) 4.0 and place the InetPub folder on the NTFS partition. If you're using Win2K, install Internet Information Services (IIS) 5.0 and place the InetPub folder on the NTFS volume. Install SQL Server 2000, ensuring that SQL Server permits both SQL Server- and Windows-authenticated logins. Also, make sure that the TCP/IP network library is installed on the server.

On the machine you plan to develop SQL Server CE applications on, install ActiveSync 3.1, the Windows CE Software Development Kit (SDK), and Embedded Visual Studio (VS)—in that order. Then, install the client and server components for SQL Server CE. (You can host IIS, SQL Server, and the development tools on one machine.)

Now, you're ready to configure IIS to support replication. The first step is to create either a Web site or a virtual directory on a server that's running IIS. When you install the server components during SQL Server CE installation, Setup places the DLL that implements the Server Agent in the Server subfolder in the SQL Server CE installation folder on the desktop computer. In the \inetpub\wwwroot folder, create a folder named SSCE, and copy the sscesa10.dll and sscerp10.dll files from the SQL Server CE installation folder to the new folder. (The sscero10.dll file handles remote data access.) Ensure that the Everyone local group has Full Control permission on both the files and the SSCE folder.

Next, create a virtual directory named SSCE that uses the \inetpub\wwwroot\ssce folder as the home directory, and give it the Scripts and Executables execute permission in the SSCE virtual directory Properties dialog box. To test your installation, open Microsoft Internet Explorer (IE) on the machine that's running IIS and type http://localhost/ssce/sscesa10.dll in the address box. If everything is set up correctly, the word Body will appear in your browser. If you get a dialog box asking where you want to download the file to, you need to set the execute permissions for the virtual directory to allow executables. If you get a login dialog box, you need to grant the Full Control permission to the Everyone local group on the \inetpub\wwwroot\ssce folder and its contents.

Configuring the Publisher


The next step in the setup process is to create the TripLog database on SQL Server 2000 and use its tables to create a publication. The sample application includes a script (TripLog.sql) that creates the database and the two tables that the application uses, then inserts seven rows into the TripLocations table.

After you create the database, right-click the Publications folder in Enterprise Manager, then select New Publication. That selection starts the Create Publication Wizard, which takes you through the steps of creating a new publication. For this publication, select the TripLog database, a Merge publication, the Devices running SQL Server CE option—as Figure 3 shows—and the TripLocations and TripLogs tables as articles, in that order.

After you name the publication, you can customize the publication's properties. Choose the option Yes, I will define data filters, enable anonymous subscriptions, or customize other properties. On the Allow Anonymous Subscriptions page, select Yes, allow anonymous subscriptions. On the Set Snapshot Agent Schedule page, change the schedule to create a new snapshot every hour and be sure to select the Create Snapshot Immediately check box. When you click Finish on the last page, the wizard creates a publication for you and starts a job for the Snapshot Agent to create the initial snapshot.

As you create the publication, you might see a dialog box explaining that the snapshot folder's default location requires administrative privileges for the subscriber accounts. SQL Server 2000 typically uses the C$ or D$ hidden administrative shares as its default for sharing snapshots. Because SQL Server CE's Server Agent uses the virtual directory's anonymous account, the easiest option (for testing purposes) is to share the snapshot folder on the network, grant the Everyone local group Full Control permission, and make the shared folder the default destination for the publication's snapshots. Just remember to tighten security when you move out of the testing environment.

Configuring the PDA


Installing the SQL Server CE components on the Windows CE device is the easiest part of the configuration process. When you reference the components in the eMbedded Visual Basic (eVB) project you're using to develop your CE application, eVB copies the appropriate files to the device and registers them in Windows CE.

Whenever I install the Windows CE development tools, I always create a simple "Hello, World" application in eVB and run it on my Windows CE device to make sure I can download applications. Furthermore, I always turn on all the Update Components options for new projects in the eVB Project Properties dialog box. That way, the first program that uses the SQL Server CE components that run on the Windows CE device will install them when it runs.

Configuring the Program


To run the sample program, you need to change some properties that the SQL Server CE replication object uses to match your environment, as Figure 4, page 42, shows. The InternetURL property needs to contain the full URL for your IIS server—for example, http://myserver/ssce/sscesa10.dll, where myserver is your server name. If you're using the SQL Server CE Relay Agent, you need to enter "ppp_peer:81" as the InternetProxyServer property. Don't forget to run the Relay Agent on your desktop machine also, as I described earlier. The SubscriberConnectionString property must contain your SQL Server 2000 server's OLE DB connection string, which the Server Agent will use to connect to SQL Server 2000. If you use the sample database, the Publication property will contain "TripLog" (the name of the publication). Finally, because SQL Server CE uses an anonymous subscription, the Subscriber property can contain any string.

Because you gave the Everyone local group permission to run the Server Agent, the SQL Server 2000 login account has at least DBO privileges on the publication database. The Everyone local group also has Full Control permission on the snapshot folder, so you don't have to set the InternetLogin or InternetPassword properties. You can set those properties, as well as the InternetProxyLogin and InternetProxyPassword properties, when you're ready to implement tighter security.

To test your installation, run the sample program on your Windows CE device. If the screen that Figure 5 shows appears on your device, you've successfully installed ActiveSync and the eMbedded Visual Toolkit. If you select File, Synchronize and no errors occur, you've successfully installed SQL Server CE, the Server Agent, and a merge replication publication. If you have errors, check the most likely points of failure. Specifically, make sure you set the Update Components options in the Project Properties dialog box. Also, check the accuracy of the URL for the server running IIS and the account and password information for SQL Server 2000. If all this information is correct, a problem with the publisher or distributor configuration probably exists. In that case, the most likely problem is that the account IIS is using doesn't have appropriate permissions for the snapshot folder.

Go Ahead, Take It with You


The power of having a part of your company's database in the palm of your hand is more than worth the effort that the initial replication setup takes. The range of possible applications includes data entry, inventory management, customer contact management and lookup, ordering, product information lookup, poll-taking, and so on. You can port any application that facilitates sharing data with other users to a device that's easier to carry than a laptop, and you can create applications that are better suited to PDAs than to laptops. When you see how much SQL Server CE can do, your laptop might be taking fewer trips out of the office.

The release of the Pocket PC standard for Windows CE 3.0 devices and of SQL Server 2000 Windows CE Edition (SQL Server CE) in 2000 gave developers a Windows NT—compatible environment on a palm-sized platform. This powerful database engine is syntax-compatible with SQL Server 2000. SQL Server CE lets you take advantage of Windows CE devices' unique hardware options, such as bar code scanning, signature capture, touch-sensitive tablets, and so on. Let's look at how to set up and use merge replication to send data between your company's database and a Pocket PC device.