News: Don’t Embed That Database!

This week, Microsoft announced that Microsoft SQL Server 2005 Compact Edition (previously referred to as SQL Server 2005 “Everywhere Edition”) was being made available as a release candidate (RC). A release candidate is generally considered a version that is final with the exception of bug fixes. The SQL Server 2005 CE is available at http://www.microsoft.com/sql/compact.

SQL Server 2005 CE provides a smaller footprint version of the SQL Server 2005 database. This scaled-down version uses a footprint of only 2 MB, yet still offers many of the features needed when using an embedded database at no cost. It is a perfect solution for those who need a small-scale database solution regardless of whether you are using a mobile device such as a Tablet PC, Pocket PC, or Smart Phone as well as those using Desktop PCs. If you are writing a portable application, an occasionally connected or offline application that may need to synchronize with a main database, or if you simply need a small embedded database in your application or device, the Compact Edition of SQL Server is likely to be your solution.

If this sounds a bit like the SQL Server Express Edition, that isn’t surprising. As developers, we were told that the Express Edition replaced MSDE (Microsoft Database Engine) that we were using as a small, embedded database in our applications. SQL Server 2005 Compact Edition is not a replacement for the Express Edition. Rather, there are situations when one will make more sense than the other. Some are obvious; such as if you need a device on a mobile device, you are going to go with CE. If you are writing a desktop application that simply needs a small, embedded application, the decision might seem a little grayer.

The simple rule of thumb is that if you want a client-side embedded database, you should use the Compact Edition. If you are dealing with a Server Side database, use the Express Edition.

The Compact Edition uses the same common programming model as the fuller versions of SQL Server 2005. It uses subsets of ADO.NET and Transact-SQL. Additionally, it includes synch technology as well as the SQL Server 2005 Management Studio. Some of the more specific features of this database include:

  • s free
  • 5 MB memory footprint and 2 MB hard disk footprint
  • Max database size of 4 GB
  • Multiple connections allowed for foreground and background operations
  • Full referential integrity with cascading deletes and updates
  • Password protection and 128-bit file level encryption
  • Transaction support to commit and roll back grouped changes
  • Scrollable and updateable cursors to provide fast and easy data access.

If you need an embedded database, you should consider the SQL Server 2005 Compact Edition because it might be the better choice over the SQL Server 2005 Express Edition. The Compact Edition is not a released product yet, but is at the release candidate stage. With the price point of being free, it becomes a relative “no brainer” solution when you need a small embeddable database on the client side for your applications.

I should also make one last comment &md; MSDE won’t be supported on Vista. If you are still using MSDE, then you might want to start seriously considering SQL Server 2005 Express Edition or Compact Editions.

# # #

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read