How do you replicate database layout changes on production server?

Question

Let's suppose I want to add a new feature to my ASP.NET MVC application running SQL Server 2008 as a data source. In order to implement this new feature, I need to add a few new columns to existing database tables.

After performing these changes on my development server and implementing the new features, what's the easiest way to perform the same database changes on the production server while deploying the new version of my application? Is there any way to automate this?

Edit: As I just found out, Visual Studio 2008's Server Explorer seems to be able to extract the necessary changes for me by comparing two different database layouts (Right-click database, click on "Compare Schema"). Does this usually cover my requirements or is there any big gotcha when using this feature?

Answer 1

Red Gate's SQL Compare utility might do it for you if your needs are relatively straightforward. If not, a tool like ER-Win or ER-Studio can handle hard-core schema and migrations.

Answer 2

I believe versioning the database using manually generated scripts similar to the approach described by K Scott Allen is well worth the investment in time. But not the automated solution you're asking for.

Answer 3

You should have db and app layer versioning. Period. If you have version db 1.0 and app layer 1.0 in production all the changes which are performed afterwards for versions 1.1 and 1.1.5 should be "upgradable" via scripts. All "alter table" , and "alter proc" statements are runnable via scripts.

Or alternatively: Restore 1.0 db to db_old database. Create the production db from scripts and just copy the data ( if you don't have very complicated database should not be difficult) Automatic deployment for applayer 1:0.

Yet again for the whole process you must train it in DEV , test in TEST verify it in qa and lately perform it in PROD environment.

Edit: I personally think that If the team is not able smoothly to upgrade from version 1.0 to 1.1 on the same time on DEV - smells like bad design and mix in the responsibilities on what should be on the app layer and what on the db server