Major Improvements Have Been Made to SQL Server Compact Edition

Posted by Jonathan Allen on Jul 02, 2010

Sections

Operations & Infrastructure,

Architecture & Design,

Development

Topics

.NET ,

Data Access ,

Database ,

Programming ,

SQL Server Compact Edition

Share |

RELATED Research

In process databases are often preferred for installed applications and small websites. When properly designed they are easy to install and require no maintenance on behalf of the user. Unfortunately the current version of SQL CE has flaws that essentially make both scenarios untenable.

Poor engineering made SQL CE unusable in hosted environments. Microsoft was so concerned about this that they used a combination of licensing restrictions and hard-coded blocks to prevent ASP.NET from even reading a SQL CE database. This not only made it useless for hosting small internal websites, it also prevented interesting scenarios like creating downloadable SQL CE files from a website.

Unlike other embedded databases that simply required shipping the DLLs, SQL CE required administrator access to install. This isn’t much of an issue for home users, but large companies offering ClickOnce deployments aren’t too keen on giving all the users administrator access.

SQL CE 4.0 is supposed to remedy both of these flaws. The work has been done to make it safe for hosted environments and the new version doesn’t require installation. And for users worried about out-growing it there is a migration tool that converts it into a standard SQL Server database.

CE is still a “safe” file format. That means it doesn’t contain any embeddable code such as stored procedures. All access has to be done via inline SQL or an ORM such as Entity Framework. It does however have full ACID compliance including basic transaction support. Advanced techniques such as save points and nested transactions are not supported.

SQL Server CE 4.0 will be released as a beta next week. You can read more about SQL CE on Scott Guthrie’s blog.

RelatedVendorContent

Want to know how software releases can be stress-free and happen with one click? Try Go free!

Improving Software Delivery Cycles: Pre-requisites and Inhibitors

Visual Studio vNext: ALM features for Agile Planning, Team Collaboration

Troubleshoot Java/.NET performance while getting full visibility in production

RDBMS to NoSQL: Managing the Transition

Related Sponsor

Go: Agile Release Management Solutions. Go enables predictable, defect-free and timely software releases.

1. Back to top

   Not so hardcoded block

   by Francois Ward

   The "block" that prevented you to use SQL Server CE in ASP.NET wasn't all that "hard coded". There was a documented application variable you could set in ASP.NET to bypass it.
   
   It was just a "warning: this isn't officially supported. If it blows up, your problem. Do you wish to proceed?" kind of mechanic. And most of the time it worked fine for small installs. Not ideal, sure, but it worked, and was documented.

   Reply

2. Back to top

   Re: Not so hardcoded block

   by Jonathan Allen

   Thank you for the correction.

   Reply