A Lap Around SQL Server 2005 Compact Edition

With a new name, new capabilities, and a new focus, the product formerly called SQL Server Mobile Edition expands its reach to small-footprint desktop applicationsand it's free!

by Michael S. Jones

Nov 8, 2006

Page 1 of 3

n April 6 of this year, Microsoft announced that SQL Server Mobile Edition (SQL Server Mobile) would be replaced later this year with Microsoft SQL Server 2005 Everywhere Edition (SQL Server Everywhere). Later, in early November, Microsoft announced that it would rename the product again to SQL Server Compact Edition (SQLce). As of this writing, SQL Server Everywhere (the download page has not been updated with the new name) has been released as a CTP on Microsoft's Web site.

In the past, SQL Server Mobile focused strictly on mobile devices, but with the release of SQL Server Compact Edition, Microsoft has begun targeting a broader developer audienceanyone looking for a small footprint desktop database engine that provides robust SQL Server functionality.

Note: Don't confuse the name change to SQL Server Compact Edition (SQLce) with the CE platform. Instead, think of it as a compact version of SQL Server.

In its original incarnation, SQL Server Mobile provided a set of tools intended to connect remote databases with centralized SQL Serversregardless of whether the remote application connected to SQL Server indirectly using IIS or directly via ActiveSync synchronization. SQLce extends the platform's reach by providing SQL Server functionality to desktop and embedded applications. While SQL Server Compact still supports all the original SQL Server Mobile functionality, this article concentrates on the product's new desktop application focus.

The Specifics
On the desktop, SQLce targets applications that require robust SQL Server functionality bundled in a small package. Microsoft supports SQLce on all versions of Windows XP, Windows Server 2003, and Windows Vista, and intends to add support for Windows 2000 in an upcoming release. You can access SQLce from all .NET languages and from older languages such as VB6 that use OLEDB. You do not need to have administrative rights to install SQLce, and the installation supports ClickOnce deployment (so you can deploy it with your applications).

SQLce does not run as a service; instead, it includes a small process that runs only when applications access its data files. The process launches very quickly and requires only about 5 MB of memory (at its base level) and less than 1.4 MB on disk. Microsoft claims that it supports databases up to 4GB, although I would urge you to think twice before considering using SQL Server Compact for databases that large.

Each SQLce database deploys as a single .sdf file. You can either ship existing database files with an application, or create new .sdf files using the SQL Server Compact engine. When connecting to the file, the SQLce framework creates the small application that provides multi-user access to the database file.

The biggest downside is that SQLce does not support stored procedures in the upcoming release; however, stored procedure support has been planned for a future release of the product. Currently, SQLce supports multiple connections, transactions, referential integrity constraints, and many other basic SQL Server functions. Developers access SQL Server Compact data through a standard ADO.NET data provider located in the System.Data.SqlServerCe namespace.

The documentation and naming conventions (not surprisingly, given the multiple name changes) could use some work. Except for the product literature published by Microsoft, the SQLce software components all refer to the product as SQL Server 2005 Mobile Edition, or SqlServerCe. It's clear that the product's heritage is the mobile or CE platform, but I would have expected thatas a newly-branded productthe software components would have been re-branded as well. I hope that this will be remedied in the final release.

Author's Note: Again, I'm using the old terminology for many of the links in this article because at publication time Microsoft hadn't yet updated the documentation with the new name. Depending on when you access these links, the names may be updated.

Getting Started
To get started using SQLce, first download the installer from Microsoft's SQL Server Everywhere web site. The web page displays some high-level information about the new version of SQL Server, including a comparison between SQL Server 2005 Express Edition and SQL Server 2005 Everywhere Edition. After completing the installation, you can create your first SQL Server Compact database file.

There are two ways to create these files. First, you can use the Server Explorer panel from within Visual Studio. Second, you can use the object explorer in the Microsoft SQL Server Management Studio. I will walk you through both methods briefly before jumping into the sample code.