Using a .MDF SQL Server Database with ASP.NET Versus Using SQL Server

Question

I'm currently writing a website in ASP.NET MVC, and my database (which doesn't have any data in it yet, it only has the correct tables) uses SQL Server 2008, which I have installed on my development machine. I connect to the database out of my application by using the Server Explorer, followed by LINQ to SQL mapping.

Once I finish developing the site, I will move it over to my hosting service, which is a virtual hosting plan. I'm concerned about whether using the SQL Server setup that is currently working on my development machine will be hard to do on the production server, as I'll have to import all the database tables through the hosting control panel.

I've noticed that it is possible to create a SQL Server database from inside Visual Studio. It is then stored in the App_Data directory.

My questions are the following:

• Does it make sense to move my SQL Server DB out of SQL Server and into the App_Data directory as an .mdf file?
• If so, how can I move it? I believe this is called the Detach command, is it not?
• Are there any performance/security issues that can occur with a .mdf file like this?
• Would my intended setup work OK with a typical virtual hosting plan? I'm hoping that the .mdf database won't count against the limited number of SQL Server databases that can be created with my plan.

I hope this question isn't too broad. Thanks in advance!

_{Note: I'm just starting out with ASP.NET MVC and all this, so I might be completely misunderstanding how this is supposed to work.}

Answer 1

The App_Data MDF is a SQL Server database that will be attached just-in-time to a SQL Server instance. The only way to access an MDF is by connecting to a SQL Server. Visual Studio hides what happens behind the scenes, but you are still using a SQL Server instance. This just-in-time attachment of the MDF works only on SQL Express editions. In SQL Express 2005 you would end up with a user instance, see Connecting to SQL Server Express User Instances (ADO.NET). With SQL Express 2008 you can control wether you want a user instance or attach to the service instance.

You will have to check with your hosting provider to confirm if they count this database as the one database in the plan or not. You have to keep in mind that from resource usage point of view, attaching a database by the connection string (which is what happens when you use the App_Data MDF deployment option) is just as expensive as any other way of opening and running database, so I'd be very surprised to hear they allow it and don't count against the number of database allowed in the plan.