using SQL Server Compact Edition in ASP.NET websites

Question

I know next to nothing about databases, but need to store and present user input on my ASP.NET tiny website (up to several thousand records). Should I consider SQL Server Compact Edition? In Microsoft docs I found:

SQL Server Compact 3.5 is not currently optimized to serve as a database for Web sites. By default, connections from ASP.NET-connected applications are blocked in SQL Server Compact 3.5. SQL Server Compact 3.5 is optimized for use as an embedded database within applications. Using SQL Server Compact 3.5 as a database for Web sites requires support for multiple users and concurrent data changes. This can cause performance problems. Therefore, these scenarios are not supported. Other editions of SQL Server, including SQL Server 2005 Express Edition and later versions, are optimized to serve as a database for Web sites.

But then I remember reading some user comment that the 4.0 version of SQL Server CE is finally working OK in ASP.NET scenario. Anyone care to share his experience? I would like to try CE first as SQL server requires additional fee on my hosting.

Answer 1

From the download site for SQL Server Compact 4.0:

Microsoft SQL Server Compact 4.0 is a free, embedded database that software developers can use for building ASP.NET websites and Windows desktop applications.

And:

SQL Server Compact 4.0 enables new scenarios and includes a host of new features, including the following:

• SQL Server Compact 4.0 is the default database for Microsoft WebMatrix, which is a stack of web technologies for easily building and deploying websites on the Windows platform.

(emphasis mine)

In conclusion - it has specifically been enhanced for web scenarios.

Answer 2

Yes, SQL Server Compact 4.0 has been designed with scenarios similiar to yours in mind.

New Embedded Database Support with ASP.NET

SQL CE is a free, embedded, database engine that enables easy database storage.

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. This is true even of medium-trust applications running in a web hosting environment.

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.

Visual Studio 2010 SP1 includes new tooling support for SQL CE