SQL CE 4.0 or SQL Express? Is Orchard right?

Question

We are about to implement new practices for our mvc web applications and although all our larger projects end up on SQL Server when live, most start life during development on SQLite or SQL Express (depending on the dev). I want to streamline the dev to live cycle and for that reason I would like to ditch SQLite and choose either SQL CE 4.0 or SQL Express as our starting point. I am wary of SQL CE due to the fact that it is not designed for ASP.NET projects (apparently) but then Orchard defaults to using SQL CE 4.0.

CE is very simple to bootstrap with new projects but should it be used for web projects (small and dev ones)? There are other DBType limitations too, but does anyone have any other experience with CE 4, good or bad?

Thanks for your thoughts on this.

Answer 1

SQL Compact Edition 4.0 is definitely suitable for ASP.NET projects! Read Scott Gu's blog post on VS 2010 SP1 and SQL CE 4 including some walkthroughs.

SQL Server Express and SQL Server Compact are quite different beasts:

• SQL Server Express is really a server - you can install it locally and all, but in the end, it's a server that handles the database for you; SQL Server Compact Edition is a file-based data storage and doesn't require any installation - the data access code libraries are linked into your app

• SQL Server CE has some limitations: no stored procedures, no views, no triggers, no blob columns (NVARCHAR(MAX), NVARBINARY(MAX)). If you can live with that - fine, great! But it's definitely quite scaled down....

  On the other hand, it does support Entity Framework, and you can password-protect your .sdf files...