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.