For the stand-alone application developer targeting the Windows platform -- that increasingly endangered species --  when the need arose for a database backend, the default choice was historically the Microsoft Jet 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 dll libraries callable from C, though interaction greatly improved through the years, to the ease and simplicity of today's ADO.NET.

When deployment time came, you could distribute with the freely redistributable Jet runtime (the only significant limitation being that you couldn't create a direct MS Access competitor, with table designers and the like), and while the client had no need for a Microsoft Access installation or license, if they did have it they could interact with the database directly where the need arose, or for extended functionality (e.g. reporting via Access). These mdb databases could also be accessed through other tools such as SQL Server linked servers, etc.

I write that in past-tense, as Microsoft has been beating Jet to death 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 SQL Server you can't add linked Access databases, nor can you interact with Jet-supported databases from the 64-bit runtime of SSIS (though thankfully in that case you have the fallback of using the 32-bit runtime). And while Access 2007 runs atop a heavily modified version of Jet called ACE, the new library itself isn't redistributable being intended only for use from Access.

So what is the replacement? The primary replacement was the former MSDE (Microsoft Database Engine), called SQL Server Express in the latest iteration. Microsoft really wanted to push developers to the SQL Server platform from the smallest need 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 and interoperation with other instances of SQL Server, taking part in functions such as replication), this is a compelling choice but for the fact that it is a resource-intensive overkill for many simple needs, with a multi-hundred megabyte install, a separate service which itself is far from slim (it is an actual instance of the same SQL Server database product that might be hosting the corporate HR databases, with only a couple of minor hardcoded limits differentiating it), and then the potential administration headaches in the future (the Slammer worm primarily infected MSDE installations 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 supports robust attaching/detaching of databases, but there still is an excessive surface-area for attack if only a basic database was desired).

If all you want is some basic table structures with simple indexing, the bulk of the database management system meant for large-scale corporate data warehouses just isn't reasonable.

Microsoft has another option now, albeit growing from a product path that has been around for a while, called SQL Server CE aka Compact Edition (confusingly you'll find it called Mobile Edition on many of the supporting documents, as that was a prior product name). Supporting a subset of T-SQL, and basic tables (no views, stored procedures, or triggers), it's an in-process, very lightweight option if you want a simple backend database in your application, and want it to easily interoperate with some other Microsoft technologies.

It's primarily unmanaged, but provides excellent .NET interoperability.

It isn't SQL Server, though. The code doesn't come from SQL Server. T-SQL is limited to a subset, the database format is completely different, and basic functionality like full-text search is missing. From an integration perspective, while it is manageable from SQL Server Management studio, the only real consistency is in the object explorer and the query analyzer, while index configuration and schema design occurs in completely new modules.

In its implementation some questionable decisions were made, such as the lack of non-unicode text types (there are a significant number of very legitimate uses for ASCII text. Going UCS-2 makes it more likely that surrogate keys get used where natural keys could have been the better choice at one-half the size, and is just a waste 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 Windows targets, including mobile editions (e.g. smart cellphones and PDAs), including tools to sync between the devices. If you wanted to make a Getting Things Done task-tracking style app atop a reasonably robust, feature-rich-enough database, and you wanted it to be usable from a desktop and a PDA whether connected or not, it presents a very interesting option.

I'm going to play around with it a bit in the coming weeks when I need a distraction from more critical work, and this is just the intro piece to further analysis of this product. I may look at the unfortunately named VistaDB and other embedded, in-process options (I'd love to consider the embedded PostgreSQL -- itself a fabulous RDBMS -- however resources for it are few and far between).