Building Solutions with SQL Server 2005 Mobile Edition

By Rowland Gosling	Article Rating:
May 10, 2005 12:00 PM EDT	Reads:	19,505

Related
Print
Email
Feedback
Add This
Blog This

Last fall I predicted that the next killer application would be built on the mobile platform, and so I bought a new PDA with a:

624MHz chip
VGA
Two kinds of expansion slots (CF I/II and SD)
192 MB of total memory (128 MB ROM, 64 MB SDRAM)

My prediction was based on the fact that lots of people could now create mobile apps with .NET, and the fact that SQL Server 2005 Mobile Edition would be licensed as part of SQL Server 2000/2005. This meant that more people would be thinking about diminutive devices and sooner or later we'd be wondering how we ever lived without some "gotta-have" new widget.

The night I bought the IPAQ I set it up on the kitchen table and got out my laptop. I wrote and deployed a simple list application in about two hours sans data. Of course that little program wasn't typical of any application. I only wanted to know how hard it would be to create and deploy an applicationThe answer was seductively easy.

That brings me to SQL Server 2005 Mobile Edition. Microsoft's latest data offering for the mobile market is a nice update from its SQL Server 2000 Windows CE Edition product. Noteworthy points include:

An embedded version is underway that will let the engine be stored in ROM.
Synchronization via HTTP
Management through ActiveSync
Interoperability with MicrosoftSQL Server 2000 (SP3A Required)
Free redistribution for existing SQL Server 2000 and 2005 customers with registration

At the highest level, SQL Server 2005 Mobile Edition can be managed as part of Microsoft SQL Manage-ment Studio, the replacement for Enterprise Manager, or when developing in Visual Studio 2005.

New Features

A completely rewritten database engine
Multiuser database support
A new high-performance result set (SqlCeResultSet) that's part of the Compact Framework 2.0

Notice the multiuser feature. It doesn't mean connecting multiple user sessions to the same database - although that could be done, I suppose; it means multiple applications can use the same database without a file-sharing error.

Here's a short list of things that are missing in the SQL Mobile 2005 environment as far as you seasoned developers go:

Triggers
Stored Procedures
Connection Pooling
Distributed Transactions
Encrypted Connection Strings
Use of Integrated Security requires Connection Strings such as Integrated Security=SSPI, User ID=DOMAIN\username, Password=********

Design

Designing applications that run on small devices isn't a trival exercise. If you're going to build and deploy .NET applications using SQL Server 2005 Mobile Edition watch the details. Probably the single biggest decision is which replication strategy to use.

RDA Replication
Merge Replication
Custom Updating or None

One way to decide is to think about the ways the data have to move. If everybody on a sales team needs all the sales data then your answer is Merge Replication. If it's a simple application with casual data access needs, you should strongly consider RDA.

Given a tiny form factor, good design is important. Unlike traditional desktop systems, you simply don't have a lot of room to work in. The performance of handhelds can be impressive, but it takes some doing. Start with something simple and work from there. By good design I mean the widget performs better than expected and all your lookup data is local. It might be a good idea to minimize the number of joins by denormalizing the data to boost performance.

Note: Only Unicode strings are supported on this platform and the smallmoney datatype isn't supported.

Supported Data Types

bigint
binary
bit
datetime
float
image
integer
money
national character
national character varying
ntext
numeric
real
smallint
uniqueidentifier
tinyint
varbinary

Replication

For non-data-speaking folk, replication topics can be daunting because replication has its own jargon and word usage. Replication uses a publishing metaphor to refer to the individual parts and roles. It consists of publications and each publication consists of one or more articles. Servers can be set up to be both a publisher of data and a subscriber of other publications.

Each publication is made up of one or more articles and there are two kinds of articles:

Download-only articles (tables)
Partitioned (filtered) articles

On servers acting as publishers, a distribution database has to be set up. The database keeps track of all the moving parts - publications, article definitions, and all subscribers. What is kept in the metabase depends on the publishing model. Replicating to small databases and devices isn't the same as their larger cousins. Because performance and storage are issues, Microsoft revamped some of the internals around the metabase activity: If you had 500 Pocket PC devices in an office building and each subscribed to a publication, you could find yourself doing a lot of setup work under the older model. Each one would have entries that keep track of exactly where that subscriber's data is. Using the Pull model in RDA eliminates a lot of the record keeping because there's no need to keep up with the details - simply retrieve the current definition and go.

Folks who have pushed SQL replication around pretty hard over the years won't find the Transactional or Snapshot types that are available in the Enterprise product. Given the nature of what's being built I suppose that's okay. Snapshots would only be useful to populate lookup tables while Transactional would give us near real-time results,but at a near real-time price. You get something similar with RDA that works better on the small platform. If you come from a big SQL background, just think Merge Replication scenarios that can handle just about anything.

CIO, CTO & Developer Resources

Merge Replication

You won't want for data, but you might beg for performance. We're in the major leagues of true replication here. Bidirectional (changes from either the server or client are posted on the other) means everyone with a subscription gets pretty much the same data barring the normal delays of replication. But is bidirectional required or desirable? Maybe. There's a performance hit at both ends. So? If you have the horsepower, it makes sense not to do the low-level plumbing code you need with RDA. With hardware so cheap these days it's pretty hard to make a big deal out of the slight difference.

The heart of your design decision rests with the kind of application you're building. If you want a tightly coupled application where data is updated from both the PDA and the server, Merge Replication is your friend. I think of it as a "duplicate data everywhere" strategy. It would make sense to say, add new customers to a CRM application . But even that scenario has flaws starting with the obvious: would you really add a new customer in a form on your PDA? If you did, you'd probably need a BlueTooth keyboard.

I think Web Services is the way to go when possible and might be your best answer. Using Web Services over Merge Replication will work unless there's a compelling reason to use Merge Replication.-There's less to keep up with, it's easier to implement, and there's no mess to clean up if the publication stops working.

The main class in coding for Merge Replication is System.Data.SqlServerCe.SqlCeReplication.

RDA

Remote Data Access (RDA) is great for the semi-disconnected state because the model calls for downloading static data and only uploading the changes. This is great if a SmartPhone won't have access to the network for a while. You'll need to do some kind of lookup (such as inventory) and record data in tables on the device. Once connected, pass that information back to the server.

The main class in coding for RDA replication is System.Data.SqlServerCe.SqlCeRemoteDataAccess.

Installation

SQL Server 2005 Mobile Edition is available:

As part of the SQL Server 2005 product (Yukon)
As part of the Visual Studio.NET 2005 (Whidbey)
As a separate download

If you're installing the product to support Microsoft SQL Server 2000, be sure to update SP3A prior to the installation.

Creating a New Database

Creating a new database is rather awkward in SQL Server Management Studio (MS SQL Server 2005's replacement for our old buddy Enterprise Manager) so I'm going to provide a little guidance here. First right click the server name to get the context menu, then click Connect, as shown in Figure 1.

From the screen in Figure 2 you have to do two things: 1.) change the server type to SQL Mobile and 2.) pull down the New Database choice. Then just follow the prompts.

Enterprise Manager got a makeover and it is now called SQL Server Management Studio.

SqlCeResultSet - The New Scrollable Cursor

The Compact Framework 2.0 (available in Visual Studio 2005/Whidbey) comes with a new tool called SqlCeResultSet. Similar to the ADO.NET DataSet and DataReader, this result set provides better performance over the DataSet without the limitations of the DataReader (forward/read only). It's scrollable, updatable, and results from executing the SqlCeCommand object. Listing 1 shows a short example.

Connection pooling isn't supported on this platform. You might want to leave the connections open instead of closing them when finished at least for the lifetime of the application.

Summary

Microsoft SQL Server 2005 Mobile Edition is an easy-to-use solution providing advanced data support to a wide range of mobile devices. Tight integration to other tools, such as Microsoft Visual Studio.NET 2005 and SQL Server Management Studio, provides a consistent toolset for working on mobile platforms.