Microsoft Finalizes SQL Compact Edition 4 Next Gen Embedded Database

Microsoft has wrapped up its next generation embedded database which the company dubbed SQL Compact Edition 4.

As additional releases on January 13th, 2010, including ASP.NET MVC 3 RTM and IIS Express 7.5, SQL Compact Edition 4 is also designed for ASP.NET web application development.

Specifically, SQL Compact Edition 4 offers web developers that are building projects on top of Windows the possibility of leveraging database storage via a free, embedded, database engine.

For websites with large databases I would still recommend the fully fledged SQL Server variant over the compact flavor without hesitation.

But at the same time, there are scenarios in which paying for a SQL Server license simply doesn’t make sense, especially if the size of the website doesn’t require a full data platform to deal with large volumes of data.

SQL Compact Edition 4 is however perfect for smaller databases, and it offers the flexibility of an xcopy-deployable solution, which means that developers will not need to trouble themselves in any way with the deployment process.

Once Microsoft will release Visual Studio 2010 SP1, developers will notice that the software giant also added support for SQL CE 4.

Scott Guthrie, Corporate Vice President, .NET Developer Platform highlighted some of the best aspects about SQL Compact Edition 4:

“No Database Installation Required - SQL CE does not require you to run a setup or install a database server in order to use it. You can simply copy the SQL CE binaries into the \bin directory of your ASP.NET application, and then your web application can use it as a database engine.

No setup or extra security permissions are required for it to run. You do not need to have an administrator account on the machine. Just copy your web application onto any server and it will work.

SQL CE runs in-memory within your ASP.NET application and will start-up when you first access a SQL CE database, and will automatically shutdown when your application is unloaded. SQL CE databases are stored as files that live within the \App_Data folder of your ASP.NET Applications.

Works with Existing Data APIs - SQL CE 4 works with existing .NET-based data APIs, and supports a SQL Server compatible query syntax. This means you can use existing data APIs like ADO.NET, as well as use higher-level ORMs like Entity Framework and NHibernate with SQL CE. This enables you to use the same data programming skills and data APIs you know today.

Supports Development, Testing and Production Scenarios - SQL CE can be used for development scenarios, testing scenarios, and light production usage scenarios. With the SQL CE 4 release we’ve done the engineering work to ensure that SQL CE won’t crash or deadlock when used in a multi-threaded server scenario (like ASP.NET).

This is a big change from previous releases of SQL CE – which were designed for client-only scenarios and which explicitly blocked running in web-server environments. Starting with SQL CE 4 you can use it in a web-server as well.”