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

  • Live Event Date: December 11, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Market pressures to move more quickly and develop innovative applications are forcing organizations to rethink how they develop and release applications. The combination of public clouds and physical back-end infrastructures are a means to get applications out faster. However, these hybrid solutions complicate DevOps adoption, with application delivery pipelines that span across complex hybrid cloud and non-cloud environments. Check out this …

  • On-demand Event Event Date: October 29, 2014 It's well understood how critical version control is for code. However, its importance to DevOps isn't always recognized. The 2014 DevOps Survey of Practice shows that one of the key predictors of DevOps success is putting all production environment artifacts into version control. In this webcast, Gene Kim discusses these survey findings and shares woeful tales of artifact management gone wrong! Gene also shares examples of how high-performing DevOps …

Most Popular Programming Stories

More for Developers

RSS Feeds