How do you Move Dev Database Changes to Production Database?

views:

answers:

+10 Q:

How do you Move Dev Database Changes to Production Database?

I have been working on a project and gotten it through the first stage. However, the requirments ended up changing and I have to add new tables and redo some of the foriegn key references in the DB.

The problem I have is my lack of knowledge of dealing with doing this kind of change to a staging then production database once I get the development done on dev database.

What are some strategies for migrating database schema changes and maintaining data in the database?

About as far as my knowledge is on doing this is open up Sql Server Management Studio and starting adding tables manually. I know this is probably a bad way to do it so looking for how to do it properly while realizing I probably started out wrong.

+2 A:

I typically use something like the SQL Server Publishing Wizard to produce SQL scripts of the changes. That is a rather simple and easy approach. The major downside with that tool is that the produced will drop and recreate tables that are not changed but used by procedures that have changed (and I can't understand why), so there is some manual labour involved in going through the script and remove things that don't need to be there.

Note that you don't need to download and install this tool; you can launch it from within Visual Studio. Right-click on a connection in the Server Explorer and select "Publish to Provider" in the context menu.

Fredrik Mörk 2010-06-29 07:03:41

This seems like a good option since it allows you to do more incremental changes and to make sure you know what is going on.

percent20 2010-06-29 21:01:18

I marked this one as the answer since this is how I did it today on a small test and it seemed to work out easiest.

percent20 2010-06-30 02:29:56

I would worry about any tool that wants to drop and recreate tables on prod when they already have data.

HLGEM 2010-06-30 13:34:07

+1 A:

There are some tools available to help you with that.

If you have Visual Studio Team edition, check database projects (aka DataDude aka Visual Studio Team for Database Professionals) See here and here

It allows you to generate a model from the dev/integration database and then (for many, but not all cases) automatically create scripts which update your prod database with the changes you made to dev/integration.

For VS 2008, make sure you get the GDR2 patches.

2010-06-29 07:04:24

This seems good, but if you don't have Ultimate or Team Edition then some of the things that would make this fit more for me aren't there like Schema Compare or am I missing something?

percent20 2010-06-29 21:02:52

I believe the important features - Schema Compare - are only in the Team or Ultimate editions. I like the tool very much. There are similar tools from other vendors - Redgate - as well.

2010-06-30 06:40:50

+3 A:

You have to have something called as a "KIT". Obviously, if you are maintaining some kind of a source control, all the scripts for the changes that you do in the development environments should be maintained in the source control configuration tool.

Once you are done with all the scripts/changes that you deem certified to move to next higher environment. Prepare the kit with having all these scripts in folders (ideally categorized as Procedures, Tables, Functions, Bootstraps) And then have a batch files that could execute these scripts in the kit in a particular order using OSQL command line utility.

Have separate batch files for UAT/ Staging/ production so that you can just double click on the batch file to execute the kit in the appropriate server. Check for OSQL options.

This way all your environments are in sync!

Baaju 2010-06-29 07:07:41

This seems to have a high overhead of you having to write all the scripts personally.

percent20 2010-06-29 21:04:22

Right ! I didn't know what was the source control tool that you were using. So i had to probably go with thee generic answer that would suit any situation but then you can narrow down your options and make it easier.

Baaju 2010-06-30 05:15:58

@percent20. It doesn't take much time (if any) to write the scripts if you do it as you go rather than using the GUI. You only think it is time-consuming because you aren't practiced at doing it and you try to do a bunch at a time. And in some databases (SQL Server for one) the GUI does not make the changes efficiently and should not be used. To alter a table for instance it will create a temp table inthe new structure, copy all the data (yep all 100,000,000 records) to it then drop the old table and rename the new one rather than using the far faster ALTER TABLE.

HLGEM 2010-06-30 13:33:13

@Baaju I understand what you are saying. @HLGEM I can see what you are saying. However, I have to ask if you are talking about Sql Server Managment studio or built in too in VS. I ask because the scripts that the VS generate use alter table and not dropping tables. Even with that said I should invest more time into learning to write and writing SQL Scripts instead of relying so much on tools. THanks.

percent20 2010-06-30 19:09:34

+1 A:

You might want to check out a tool like Liquibase: http://liquibase.org/

jordekorre 2010-06-29 07:10:06

We have found the best way to push changes is to treat databases changes like code. All changes are in scripts, they are in source control and they are part of a version. Nothing is ever under any circumstances pushed to prod that is not scripted and in source control. That way you don't accidentally push changes that are in dev, but not yet ready to be pushed to prod. Further you can restore prod data to the dev box and rerun all the scripts not yet pushed and you have fresh data and all the dev work preserved. This also works great when you have lookup values to tables that are chaging that you don;t want pushed to prod until other things move as well. Script the insert and put it with the rest of the code for the version.

It's nice to use those tools to do a compare to see if something is missed in the scripts, but I would NEVER rely on them alone. Far too much risk of pushing something "not yet ready for prime time" to prod.

HLGEM 2010-06-30 13:26:23

I think the big reason I like the tools is it does so many little optimizations that seem like they should be done, but I don't know to, or how to, do them. However, taking the time to learn and write out the scripts then slowly over time work on what to add besides basic stuff would probably be a smart thing to do in the long run.

percent20 2010-06-30 19:12:15

+1 A:

Red Gate SQL Compare and SQL Data Compare all the way. Since my company bought it, it saved me tons of time staging our databases from DEV to TEST to ACCEPTANCE to PRODUCTION.

And you can have it synchronize with a scripts folder too for easy integration in a source control system.

http://www.red-gate.com

tjeuten 2010-07-04 21:05:19

ansaurus

tags:

views:

answers:

How do you Move Dev Database Changes to Production Database?

related questions