SQL Server CE

For the stand-alone application developer targeting the Windowsplatform — that increasingly endangered species —  when theneed arose for a database backend, the default choice washistorically the MicrosoftJet database engine, usually to interact with an mdb(e.g. Microsoft Access) database file (though Jet supported other,less-used options as well).

This goes all the way back to the terribly awkward DAO dlllibraries callable from C, though interaction greatly improvedthrough the years, to the ease and simplicity of today’sADO.NET.

When deployment time came, you could distribute with the freelyredistributable Jet runtime (the only significant limitation beingthat you couldn’t create a direct MS Access competitor, with tabledesigners and the like), and while the client had no need for aMicrosoft Access installation or license, if they did have it theycould interact with the database directly where the need arose, orfor extended functionality (e.g. reporting via Access). These mdbdatabases could also be accessed through other tools such as SQLServer linked servers, etc.

I write that in past-tense, as Microsoft has been beating Jet todeath as of late.

The first punch was the complete lack of a 64-bit migration path– existing or planned — meaning that in a 64-bit instance of SQLServer you can’t add linked Access databases, nor canyou interact with Jet-supported databases from the 64-bit runtimeof SSIS (though thankfully in that case you have the fallbackof using the 32-bit runtime). And while Access 2007 runs atop aheavily modified version of Jet called ACE, the new library itselfisn’t redistributable being intended only for use from Access.

So what is the replacement The primary replacement was theformer MSDE (Microsoft Database Engine), called SQLServer Express in the latest iteration. Microsoft really wantedto push developers to the SQL Server platform from the smallestneed to the largest need.

With a liberal, free redeployment, a very easy upgrade path to a”real” instance of SQL Server (and the easy integration andinteroperation with other instances of SQL Server, taking part infunctions such as replication), this is a compelling choice but forthe fact that it is a resource-intensive overkill for many simpleneeds, with a multi-hundred megabyte install, a separate servicewhich itself is far from slim (it is an actual instance of the sameSQL Server database product that might be hosting the corporate HRdatabases, with only a couple of minor hardcoded limitsdifferentiating it), and then the potential administrationheadaches in the future (the Slammer worm primarily infected MSDEinstallations that many users weren’t even aware they had running.SQL Server’s default configuration is far more intelligent now,such that by default it only listens on localhost, and it supportsrobust attaching/detaching of databases, but there still is anexcessive surface-area for attack if only a basic database wasdesired).

If all you want is some basic table structures with simpleindexing, the bulk of the database management system meant forlarge-scale corporate data warehouses just isn’t reasonable.

Microsoft has another option now, albeit growing from a productpath that has been around for a while, called SQLServer CE aka Compact Edition (confusingly you’ll find itcalled Mobile Edition on many of the supporting documents, as thatwas a prior product name). Supporting a subset of T-SQL, and basictables (no views, stored procedures, or triggers), it’s anin-process, very lightweight option if you want a simple backenddatabase in your application, and want it to easily interoperatewith some other Microsoft technologies.

It’s primarily unmanaged, but provides excellent .NETinteroperability.

It isn’t SQL Server, though. The code doesn’t come from SQLServer. T-SQL is limited to a subset, the database format iscompletely different, and basic functionality like full-text searchis missing. From an integration perspective, while it is manageablefrom SQL Server Management studio, the only real consistency is inthe object explorer and the query analyzer, while indexconfiguration and schema design occurs in completely newmodules.

In its implementation some questionable decisions were made,such as the lack of non-unicode text types (there are a significantnumber of very legitimate uses for ASCII text. Going UCS-2 makes itmore likely that surrogate keys get used where natural keys couldhave been the better choice at one-half the size, and is just awaste of space and performance if it isn’t actually necessary).

So why am I talking about SQL Server CE at all?

The compelling feature is that it runs on the gamut of Windowstargets, including mobile editions (e.g. smart cellphones andPDAs), including tools to sync between the devices. If you wantedto make a Getting Things Done task-tracking style app atopa reasonably robust, feature-rich-enough database, and youwanted it to be usable from a desktop and a PDA whether connectedor not, it presents a very interesting option.

I’m going to play around with it a bit in the coming weeks whenI need a distraction from more critical work, and this is just theintro piece to further analysis of this product. I may look at theunfortunately named VistaDBand other embedded, in-process options (I’d love to consider theembedded PostgreSQL — itself a fabulous RDBMS — howeverresources for it are few and far between).