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.