Best practice for updating database structure?

[redlam]

Hi there.
I am working for a software company that releases product upgrades to their clients twice a year. I am using SQL Server 2000 and I'm currently using ERWin to create the necessary scripts to upgrade one version of the database to the next. I am interested in finding out how others accomplish this task and I am wondering what is considered to be the 'best' practice.
Thanks in advance for any/all input.

 

[redlam]

Hi again.
Bringing this back up to the top of the list... hoping maybe it just got lost in the shuffle (ya know, with the holiday and all)

Here's my problem:
I have always used ERWin (or other db modeling tools like it) to compare database schemas. In practice, every time my company has a new release for their software which requires database schema changes, I compare the most recent version (from production) to the new release and script out the changes using ERWin. It has recently come to my attention that our clients do not necessarily keep up with our release schedule and therefore may not be using the most recent version of our product. In this case, my database script will fail. I've been tasked with creating a 'seamless' update script that will account for all versions of the database. The clients do not want to run multiple scripts to make their software current even if they are several releases behind.

My questions are:
Has anyone else encountered this issue? What can I do differently to accomodate for 'multiple version jumping'?
Is there a better way to automate schema changes while also preserving existing data?

 

[dbmsguy]

I think you have quite a challenge....

Firstly, I worked for a company that received updates from a software vendor....

It worked something like this.

the version to version updates were compartmentalized. A script was created for each version to the next. When upgrading the app, the user (me) would specify the from version and the script would run upgrading me to current.

under the covers, all that was happening was that they were delivering all scripts and just executing them in order. what this really meant was that the 'upgrade' process was executing iteratively until you hit the current version.

it was not very elegant and was also a unix env, but in the end it worked reasonalbly well.

If all you were doing was updating schemas, then i think you would be able to do a little better, but if you need to do data conversions as well, then the practice used by that company might do you. Paul

http://www.dbmsguy.com

 

[foxdev]

Paul's vendor's approach sounds like the most reasonable approach, if you can trust the user to select the correct starting version.

The only other approach that comes to my feeble mind is for your scripts to examine the existing schemas (i.e., does column "X" exist), and branch to the appropriate scripts as needed.

I've had to face a similar issue, but I addressed it outside of T-SQL, though you might be able to use the same technique within a script:

I make a cross-reference of what has changed in each version, then check for one thing that is new in each version. IOW, if a column DateChanged was added to table Customers in version 3.1, and that column exists now, then they are at least in that version, and I move forward; if it is not there, then they are in some earlier version, and I move backward - with each version number, checking one element until I arrive at what version they are at. This assumes (reasonably, I think) that all patches/DDL scripts for a given version would have been applied. Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[redlam]

thank you. you both have given me great ideas to get started with...