How do you deploy SQL Server 2000 database changes.

Before now, I would make a few database changes, add 3 or 4 stored procedures, a table or two, maybe a column here or there, as I added functionality to a particular application. For small changes like that, I'd just script out the changes I had made and send those scripts to the operations people so that they could apply the changes to the test DB and then, hopefully, to the production DB.

For the last couple of months, I've been tasked with basically rewriting the application from an old VB 6 desktop application to an ASP .NET 2.0 application. In addition to simply converting the front end, I've had to add several new features, most of which required database changes. And in addition to adding stuff to the DB for the new features, I've been changing a lot of existing tables, SPs, etc., hoping to make them less WTF-worthy.

Well, now it's time to deploy everything to test. Deploying the website is simple enough, but I shudder at the tedium of scripting out each table and SP change/addition by hand. Does there exist some magical tool that can compare two databases, figure out how they're different, and then automagically (because it's magical) generate scripts to apply changes from one to the other? If there is such a tool, where can I get it? If there isn't such a tool, then can I at least hear from you all about how you typically deploy database changes?

lpope187

There are tools out there. The MSDN magazine had a basic review of one a few months back from Red Gate Software called SQL Data Compare and SQL Compare. One is for schemas/objects and one is for data. I've never used it so I can't say whether it is a decent product or not.

We've had several conversations here regarding this topic before, and the database structure and all supporting scripts are really part of your source code and should be treated as such. The problem is I haven't yet seen any good integration for database development and source control systems. It's there but it is no where near as streamlined as it is for "real" source code. Therefore it is extremely important that every step in the database migration process is captured in source control. It is manual and it really sucks but it really needs to be done. How you structure it in source control is interesting since you can't really overlay the existing create object statements - you need to capture all the deltas and the order in which they are run.

Some might disagree with me, but for smaller databases < 50 tables, I just start a new script called V1_to_V2.sql and as I alter the database, I just append the script to the end. It preserves the order of operations which is important on some things especially relationships/procs/views. It does get messy for larger databases or massive restructuring. For larger databases/restructuing you'll want to create multiple scripts. How you manage that is might be different on a case by case basis. You might split it by function (Orders versus Inventory) or by object type (Procs vs Tables).

I've been bit before by not scripting/capturing as you go. Going back and trying to recreate what you've should have done from the start is not fun. It makes you a big believer in managing the changes in a more structured way.

[quote user="lpope187"]

There are tools out there. The MSDN magazine had a basic review of one a few months back from Red Gate Software called SQL Data Compare and SQL Compare. One is for schemas/objects and one is for data. I've never used it so I can't say whether it is a decent product or not.

We've had several conversations here regarding this topic before, and the database structure and all supporting scripts are really part of your source code and should be treated as such. The problem is I haven't yet seen any good integration for database development and source control systems. It's there but it is no where near as streamlined as it is for "real" source code. Therefore it is extremely important that every step in the database migration process is captured in source control. It is manual and it really sucks but it really needs to be done. How you structure it in source control is interesting since you can't really overlay the existing create object statements - you need to capture all the deltas and the order in which they are run.

Some might disagree with me, but for smaller databases < 50 tables, I just start a new script called V1_to_V2.sql and as I alter the database, I just append the script to the end. It preserves the order of operations which is important on some things especially relationships/procs/views. It does get messy for larger databases or massive restructuring. For larger databases/restructuing you'll want to create multiple scripts. How you manage that is might be different on a case by case basis. You might split it by function (Orders versus Inventory) or by object type (Procs vs Tables).

I've been bit before by not scripting/capturing as you go. Going back and trying to recreate what you've should have done from the start is not fun. It makes you a big believer in managing the changes in a more structured way.
[/quote]

I'm currently trying out the Red Gate Software stuff now. It looks to be very, very promising, but I'll reserve designating it wonderfulness-incarnate until I get the opportunity to run the scripts it has generated on a clone of the production db (I'm waiting for the db people to set that up for me).

You're right, going back and trying to recreate everything I've done is not fun...at all. I started out doing a fairly good job of tracking my changes, but as the deadlines approached and the pressure increased, documentation became more and more lacking. It's no one's fault but my own, but I'm hoping that the Red Gate stuff will help me out. Either way, I'll be doing a better job of it next time, that's for sure.

By the way, I'm still interested in hearing about anyone else's methods for managing database changes.

lpope187

Here are some of previous threads. I know Alex wrote an article about it - It is in the second link.

http://thedailywtf.com/forums/thread/85073.aspx

http://thedailywtf.com/forums/thread/77617.aspx

[quote user="lpope187"]

Here are some of previous threads. I know Alex wrote an article about it - It is in the second link.

http://thedailywtf.com/forums/thread/85073.aspx

http://thedailywtf.com/forums/thread/77617.aspx[/quote]

Oi...That'll learn me not to search the forums adequately before asking a question. Thank you for pointing me to those threads, and I'm sorry for wasting your time. :-)

lpope187

No problem. I was actually hoping your new post would spawn more dialogue though. It is not a topic that gets much attention and it sorely needs it.

DWalker59

The thing I use for this exact task is SQLDelta. (www.sqldelta.com) It's only $225 and it works wonderfully. I think the Red Gate product is much more expensive.

I like SQLDelta a lot. And I am not affiliated with them in any way. Try it out.

It can compare table structure (with options to include or ignore indexes, etc.) and script the changes, and optionally you can run the script to make the changes.

It can also compare data and synchronize the data between two databases. However, it's not quite as fast as the DTS "export to another SQL server" wizard that's built into SQL 2000 for moving huge amounts of data.

Just for the record, I ended up using Red Gate's product until the trial period expired. I'm now thinking of purchasing it myself if my company won't.

Using the scripts it produced, I was able to move all my changes from the development database to the production test database without any major problems. The software was very easy to use, although it did blow up on me a few times. That wasn't too big of a deal though, as it seems to kind of automatically save everything about the database comparison project your working on as you go. All in all, I recommend it.

Thanks to all of you who offered suggestions. You really saved me a bunch of time.