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

Do not use App_Data for your db. It's good for some quick "how to" or prototype but it is not good for the production server. It can cause you a lot of trouble during maintenance. I've used it once and never ever again...

Yes, performance. Because it always has to attach the db. Not a big issue when you have a tiny web with several visitors a day.

It would be better to ask your hosting service if they count it or not.

Answer 2

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.

Answer 3

All SQL Server Databases are .mdf files. The .mdf files are stored here: *C:\Program Files\Microsoft SQL Server\MSSQL\Data* (Default Location).

You are correct that you would have to detach the SQL Database from the server to move the database to your App_Data folder. You can do that by right clicking on the database in SSMS.

The advantages of moving the database to the App_Data folder is if:

1. The database will be kept there when used in production and you will attach the database to a SQL Server dynamically when you connect to it.
2. You want to publish the website to your server together with the mdf in the App_Data folder (which goes together with the first reason).
3. Keeping all of you project files in one location. Easy to move. Easy to Backup.

In your scenario where you will be uploading your database via a custom control panel, I don't see any advantages to moving the database to the App_Data folder. When you publish the site you can simply detach the DB, grab the mdf file and upload (Your control panel may have other ways to import a DB, e.g. SQL scripts...).

To answer you questions directly...

Does it make sense to move my SQL Server DB out of SQL Server and into the App_Data directory as an .mdf file?

I don't think so.

If so, how can I move it?

Detach. You can do that by right clicking on the database in SSMS.

Are there any performance/security issues that can occur with a .mdf file like this?

I am not sure about security differences but as mentioned there is probably better performance when the database is left attached to the SQL Server.

Would my intended setup work OK with a typical virtual hosting plan?

You would have to check if they count/allow sql databases to be attached dynamically.

I hope this helps. I am no expert.

Answer 4

Honestly, this may not answer your questions directly, but I found that the way you set it up on hosting (not locally) is move/import your db to your hosting company, who will then give you a connection string to your host/db. Once you "go live" (or test, by uploading your site to the host) - change the connection string to the one provided by your host, in your web.config. This is probably oversimplified, but has always worked for me.