Deploying database for ASP .NET Website

Question

I was just browsing through questions on stack overflow, and i've come up to a post where it suggests of deploying database by simply copying the mdf file in the app_data folder and modifying your connection string.

I know that some people do create an mdf file in the app_code during development, but for going life, Is this realy a viable way and a good practice to deploy your database?

What I usualy do during development time is to have written my own sql script file to build the database, and run it on my local sql server. When site is about to go life I ran the script on the target server and set my site to talk to the database. To be honest I've never utilize the app_code folder for storing the database, I usually use it to store my data access layer logic..

Am I doing the wrong thing here? Is it really a good practice to utilize the app_data folder to store your database? One problem that I can see with this method is that, deployment going to be slow. Transfering mdf file accross the internet will definately be much slower than running my sql script files. Looking forwards to hear your thoughts and experience on this matters. Cheers.

RWendi

Answer 1

I personally prefer your method of deploying Databases and I see one big advantage with this: usually the Web and the DBServer should not be one machine (Security, Maintainability, ...) and utilizing the app_code folder to hold your Database seems a little credulously.

Answer 2

Another drawback is that an MDF file deployment will only work the first time. It will be inadequate once you are live and need to keep the data.

Answer 3

The app_data deployment scenario is useful for websites where you don't have a distinct database server (a lot of the free/less expensive hosts enable you to do this).

This is similar in theory to the old method of using access as the database for small classic ASP websites.

Answer 4

In my experience, the best practice involves using some sort of build process (perhaps automated or a script like you have mentioned). Then you divide your database into the following: 1) One time events - typically you would create the database once; you might insert data once or initial deployment; schema changes - these are handled outside of the build process. 2) Stored procedures, user functions and other repeatable events - handled by the build process.

The build process then deploys the database constructs when it deploys the code.

Stored procedures, etc are written such that if they exist, they are first deleted and then created again.

These scripts are then stored in your code repository - not the mdf. Now you can do versioning, history, control your build process, etc. As to folder - I would create a separate project folder for the core database constructs - these are different from "code" and should be treated as such. But they probably should be part of your solution.

Answer 5

Actually in my opinion the DB deployment can be useful for site tools, mass deplyments, etc. Deploying a premade application that includes the db can be a simple process. If successive versions of this get updated, all it needs to do is know if its an upgrade or not. If it is, run the script that updates the local DB, otherwise copy in the new database. Can simplify certain limited scenarios.