What's the difference between the database inside App_Data folder vs connecting to SQL Server?

Question

I'm new to .NET and while starting to learn ASP .NET MVC2 framework I see that you can create a .mdf file inside the App_Data folder and connect to it or you can connect to a SQL Server.

What is the difference between these to methods of interacting with a database? What are the advantages/disadvantages of one over the other?

Answer 1

The "MDF in the App_Data" folder works for web site and web apps, and it works only with SQL Server Express (2005, 2008, 2008 R2). This is the version that's typically installed along with Visual Studio, and which works fine as a dev environment.

SQL Server Express has a few limitations on

• number of CPU used (1)
• max. size of a database (4 GB for 2005/2008, 10 GB for 2008 R2)
• max. amount of RAM used (max. 1 GB)

and more. It's a great and free way to get into SQL Server development.

If you need production level SQL Server, then you're probably going to use a full version - Web, Workgroup, Standard, Enterprise or any of the highest level DataCenter editions.

There's a pretty comprehensive Compare SQL Server 2008 R2 Editions page up at Microsoft - go check it out!

The programming experience should be identical, too - it's really just a question of the ADO.NET connection string (and whether or not you need to have a locally installed SQL Server Express instance present).

The database file format is totally identical, so you can absolutely start with a .mdf file in your App_Data folder, and later move "up" to a full edition of SQL Server - simply attach your MDF file to your server instance, and now use that database. Works seamlessly.