Is there a performance hit for using MDF SQL Server files instead of "database"?

Question

Currently my website is written in ASP.NET Webforms using a SQL Server database. I am planning to build a ASP.NET MVC application not because it's better but because I want to learn the technology. My question is more specific to the database. I can create the database and import my SQL table via the import feature of the web interface to the SQL database or I can use a "local" database MDF file. I like the idea of using a MDF file because it will be easier to backup and deal with. My website doesn't get a lot of traffic... matter of fact I could be the only user. :) Now here is the question.

How much of a performance hit will I get by using a local SQL Server MDF file instead for my database?

Answer 1

Effectively none. Or really none more than having the DB run on the same box as the web site. It is still a Sql Server Express database all the same. Production-wise, you probably want the DB running on a separate box dedicated to serving databases. But code-wise, the only difference will be your connection string.

Answer 2

SQL Server uses a MDF file for each database on its server. There is no difference between a MDF file and a 'database' because the 'database' gets stored in a MDF file anyway.

Answer 3

Performance wise you should not see a difference.

The biggest issue is with production deployment and management. It is MUCH easier to manage a standard database, than a dynamically attached .mdf.

Answer 4

Also, don't forget that your web host has to support this as well. And since SQL Server Express is the only SKU that supports "user instance" databases, the host will have to have Express installed for you to use it as-is. OTOH, you can develop with it this way and then just deploy your database and change the connection string when you deploy to the web host.

Answer 5

The only difference beyond the normal resource limits of the Express version of SQL Server is a negligable startup cost while the SQL Express engine connects to the MDF file, does its routine checks for file integrity and transaction log stuff.

This should only happen on application start up, not for every request.