Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Choosing the Back End
- The DBA's Toolbox, Part 1
- The DBA's Toolbox, Part 2
- Scripting Solutions for SQL Server
- Building a SQL Server Lab
- Using Graphics Files with SQL Server
- Enterprise Resource Planning
- Customer Relationship Management (CRM)
- Building a Reporting Data Server
- Building a Database Documenter, Part 1
- Building a Database Documenter, Part 2
- Data Management Objects
- Data Management Objects: The Server Object
- Data Management Objects: Server Object Methods
- Data Management Objects: Collections and the Database Object
- Data Management Objects: Database Information
- Data Management Objects: Database Control
- Data Management Objects: Database Maintenance
- Data Management Objects: Logging the Process
- Data Management Objects: Running SQL Statements
- Data Management Objects: Multiple Row Returns
- Data Management Objects: Other Database Objects
- Data Management Objects: Security
- Data Management Objects: Scripting
- Powershell and SQL Server - Overview
- PowerShell and SQL Server - Objects and Providers
- Powershell and SQL Server - A Script Framework
- Powershell and SQL Server - Logging the Process
- Powershell and SQL Server - Reading a Control File
- Powershell and SQL Server - SQL Server Access
- Powershell and SQL Server - Web Pages from a SQL Query
- Powershell and SQL Server - Scrubbing the Event Logs
- SQL Server 2008 PowerShell Provider
- SQL Server I/O: Importing and Exporting Data
- SQL Server I/O: XML in Database Terms
- SQL Server I/O: Creating XML Output
- SQL Server I/O: Reading XML Documents
- SQL Server I/O: Using XML Control Mechanisms
- SQL Server I/O: Creating Hierarchies
- SQL Server I/O: Using HTTP with SQL Server XML
- SQL Server I/O: Using HTTP with SQL Server XML Templates
- SQL Server I/O: Remote Queries
- SQL Server I/O: Working with Text Files
- Using Microsoft SQL Server on Handheld Devices
- Front-Ends 101: Microsoft Access
- Comparing Two SQL Server Databases
- English Query - Part 1
- English Query - Part 2
- English Query - Part 3
- English Query - Part 4
- English Query - Part 5
- RSS Feeds from SQL Server
- Using SQL Server Agent to Monitor Backups
- Reporting Services - Creating a Maintenance Report
- SQL Server Chargeback Strategies, Part 1
- SQL Server Chargeback Strategies, Part 2
- SQL Server Replication Example
- Creating a Master Agent and Alert Server
- The SQL Server Central Management System: Definition
- The SQL Server Central Management System: Base Tables
- The SQL Server Central Management System: Execution of Server Information (Part 1)
- The SQL Server Central Management System: Execution of Server Information (Part 2)
- The SQL Server Central Management System: Collecting Performance Metrics
- The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
- The SQL Server Central Management System: Reporting the Data and Project Summary
- Time Tracking for SQL Server Operations
- Migrating Departmental Data Stores to SQL Server
- Migrating Departmental Data Stores to SQL Server: Model the System
- Migrating Departmental Data Stores to SQL Server: Model the System, Continued
- Migrating Departmental Data Stores to SQL Server: Decide on the Destination
- Migrating Departmental Data Stores to SQL Server: Design the ETL
- Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
- Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
- Tracking SQL Server Timed Events, Part 1
- Tracking SQL Server Timed Events, Part 2
- Patterns and Practices for the Data Professional
- Managing Vendor Databases
- Consolidation Options
- Connecting to a SQL Azure Database from Microsoft Access
- SharePoint 2007 and SQL Server, Part One
- SharePoint 2007 and SQL Server, Part Two
- SharePoint 2007 and SQL Server, Part Three
- Querying Multiple Data Sources from a Single Location (Distributed Queries)
- Importing and Exporting Data for SQL Azure
- Working on Distributed Teams
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
Using Microsoft SQL Server on Handheld Devices
Last updated Mar 28, 2003.
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.
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.