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.

# # #



About the Author

Bradley Jones

Bradley Jones, in addition to managing CodeGuru, Brad! oversees the Developer.com Newtwork of sites including Codeguru, Developer.com, DevX, VBForums, and over a dozen more with a focus on software development and database technologies. His experience includes development in C, C++, VB, some Java, C#, ASP, COBOL, and more as well as having been a developer, consultant, analyst, lead, and much more. His recent books include Teach Yourself the C# Language in 21 Days, Web 2.0 Heroes, and Windows Live Essentials and Services.
Google+ Profile | Linked-In Profile | Facebook Page

Comments

  • c# Database

    Posted by arith_s on 01/17/2010 04:16am

    Try EffiProz Database http://www.EffiProz.com, EffiProz is a database written entirely in C#. comprehensive SQL support including Stored Procedures, Triggers and Functions. Ideal for embed in .Net applications. Support Silverlight 3 and .Net compact framework as well Include Visual Studio ad-in, ADO.Net provider, Entity Framework provider, etc.

    Reply
  • article title is misleading

    Posted by mkamoski on 12/15/2009 08:17am

    Author -- I appreciate the information at the http://www.codeguru.com/cpp/data/mfc_database/sqlserver/article.php/c12813/ link. However, the article title is misleading. Why does it say "don't"? Why does not it say "do"? Just a thought. I leave it to you. Thanks. -- Mark Kamoski

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • IBM Worklight is a mobile application development platform that lets you extend your business to mobile devices. It is designed to provide an open, comprehensive platform to build, run and manage HTML5, hybrid and native mobile apps.

  • Protecting business operations means shifting the priorities around availability from disaster recovery to business continuity. Enterprises are shifting their focus from recovery from a disaster to preventing the disaster in the first place. With this change in mindset, disaster recovery is no longer the first line of defense; the organizations with a smarter business continuity practice are less impacted when disasters strike. This SmartSelect will provide insight to help guide your enterprise toward better …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds