SQL Server Reference Guide

Using Microsoft SQL Server on Handheld Devices

I recently saw a sticker that I really like: "I haven't lost my mind. It's backed up on disk somewhere." I agree with that. I used to carry a calendar book with everything in it – appointments, addresses, notes, and the like. When I began using various Personal Information Manager (PIM) programs (remember Lotus Organizer 2.0?) it replaced the book, but it wasn't very portable. So I jumped on the cell-phone and the Palm Pilot bandwagons.

But now I've got a true convergence device: a PocketPC phone. I bought a big-old memory stick for it, so I've got everything in one place. Now my brain really is backed up somewhere. I know – I'm a true geek!

Since I'm a database architect, and I'm all about the gadgets, it makes sense to combine the two. Who would have thunk it: SQL Server on a cell phone.

Actually, there are better reasons than that to install and use SQL Server on a PocketPC platform. I've used SQL Server on a handheld device in an enterprise time-tracking software package that my company wrote. The DBA group was asked to write an offline module so that the field officers could enter their project time on-site, then sync up when they got back to the office.

In this article, I give you a full primer on Windows SQL Server CE, the edition that runs on the handheld platform. In future articles, I'll show you how to build an application using this software.

The process isn't as simple as are most handheld PC software installations. There are three parts to the installation, each with subsections.

First, let's talk a little about how the whole thing fits together. SQL Server CE works by replicating all or part of a database on a server to a CE device, using a Web service. You develop applications for the device using either ADOCE 3.1 in eMbedded Visual Basic or eMbedded Visual C++ (eMbedded is really spelled like that), or by using the .Net compact framework. You get a license for those with SQL Server Developer edition. Users access the replicated data on the CE device, and the changes they make are replicated back to the server.

To run the software on the server side, you need SQL Server on a regular PC or server, with at least SP1 for SQL Server. In addition, make sure Internet Information Server (IIS) is installed.

On installation of the Windows CE Software (available at http://www.microsoft.com/sql/ce/downloads/default.asp) you also get the SQL Server CE Server Agent, which communicates with the central database and the client agent (sscesa20.dll).

Once the software is installed, the replication part of the process needs to be set up. The software needs a Web Folder for CE Replication, which also contains the server agent DLL, and is used for temporary files. Another replication folder, the Snapshot Folder, contains the files for the initial replication.

You also get the SQL Server Reconciler, which creates initial snapshots of the published data, and handles merging of changes. The Reconciler has other parts as well, such as the SQL Server Replication Provider, used to access data during Replication to other SQL Server databases. It also contains the SQL Server CE Replication Provider, which is used to access data during Replication to SQL Server CE databases.

As part of any replication, you'll set up the Central SQL Server that houses the Publisher and the Distributor databases for the replication. If all you're using is the replication for Windows CE, you can leave this on the main server, you might want to consider another server if you'll replicate to other systems.

The last piece is the client. To use SQL Server CE, you need to meet the platform requirements on your handheld device. The definitive list is at http://www.microsoft.com/sql/ce/productinfo/sysreq.asp, but the PocketPC portion requires:

• Pocket PC 2000
• Pocket PC 2002
• Pocket PC 2003
• Pocket PC Phone Edition
• Handheld PC 2000 (HP/C 2000)
• Other embedded devices that use Windows CE 4.1 and 4.2

How much space does SQL Server CE use on the device? Well, the RAM-based storage for SQL Server CE contains three parts: a file system, a registry, and the Windows CE databases. I've found that you'll need at least a 2-4 MB free to run the client software on the device (not counting the databases), although the official numbers are 800k to 1.3 MB for the engine. I have a 1 GB card in my system, but I also store hundreds of books, and some music.

In addition, you use a program on the device to access the database, and you must plan space for that as well.

To develop this software, you have two choices. The first is a standard include on the earlier versions of ADO. The second is to use the .Net compact framework, which is the newer, more powerful method. The drawback is that using the .Net compact framework requires more software on the handheld device.

The software that is installed on the device includes the SQL Server CE Client Agent, which communicates with the Web server, the SQL Server CE Database Engine and the Application. You'll also get the SQL Server CE Database Engine that provides local database processing. Finally there are the data files, which store the data for SQL Server CE and typically have an extension of .sdf.

Once you develop your application on the server, you install it on the client. When you develop the application, the recordsets it opens are contained in the .sdf file I mentioned earlier. Once the data is replicated to the device, the program accesses, displays, changes and deletes it. The modified data is then sent back to the server during replication. The server absorbs the data, makes changes of its own and sends those changes back to the device.

Performancewise: On my device, an Audiovox Thera with a 200Mhz Strongarm processor, 32 MB of local RAM and a 1GB external card, using more than a couple of thousand records got a little sluggish. Most distributed applications won't contain this much data at one time, since you're usually worried about a few records for each distribution. Replication can be slow as well, depending on the data load.

Configured properly, SQL Server CE is very powerful when combined with a phone handheld device. If the phone software is integrated into the application, the phone's touchpad can be used as an input device for the database.

Online Resources

SQL Junkies has a great article on pre-loading a CE database. Instead of waiting for replication, you can load up the database on your server and then send that to the PocketPC.

InformIT Tutorials and Sample Chapters

You can find out plenty more about mobile technology here on InformIT. As just one example, Bryan Morgan reviews several mobile databases, including SQL Server CE, in his article, Mobile Database Review: Microsoft Databases for Windows CE.