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

  • On-demand Event Event Date: December 18, 2014 The Internet of Things (IoT) incorporates physical devices into business processes using predictive analytics. While it relies heavily on existing Internet technologies, it differs by including physical devices, specialized protocols, physical analytics, and a unique partner network. To capture the real business value of IoT, the industry must move beyond customized projects to general patterns and platforms. Check out this webcast and join industry experts as …

  • Today's agile organizations pose operations teams with a tremendous challenge: to deploy new releases to production immediately after development and testing is completed. To ensure that applications are deployed successfully, an automatic and transparent process is required. We refer to this process as Zero Touch Deployment™. This white paper reviews two approaches to Zero Touch Deployment--a script-based solution and a release automation platform. The article discusses how each can solve the key …

Most Popular Programming Stories

More for Developers

RSS Feeds