DDL Best Practices question

[Woody]

I am looking for some examples of how to manage DDL scripts among
various versions of a production db and development and testing. I
have tried a few things in the past, and it always gets very muddled
and cumbersome.

I need to be able to build any version of the database from scratch,
BUT I also need to maintain an upgrade path from any version to any
later version. So it is not enough to just maintain a master build
script, but I don't want to maintain 2 different things (modify the
master build scripts AND create a new "ALTER" script for each version
change).

I thought I had seen an article somewhere that layed out a process for
managing this, but I can't find it now (I thought it was in SQL Server
Mag). Does anybody know of this article or have a resource they could
point me to that outlines best practices in this area?

Thanks,
Jason Wood, DBA in training.

[Simon Hayes]

"Woody" <ja******@hotmail.com> wrote in message
news:a8**************************@posting.google.c om...

I am looking for some examples of how to manage DDL scripts among
various versions of a production db and development and testing. I
have tried a few things in the past, and it always gets very muddled
and cumbersome.

I need to be able to build any version of the database from scratch,
BUT I also need to maintain an upgrade path from any version to any
later version. So it is not enough to just maintain a master build
script, but I don't want to maintain 2 different things (modify the
master build scripts AND create a new "ALTER" script for each version
change).

I thought I had seen an article somewhere that layed out a process for
managing this, but I can't find it now (I thought it was in SQL Server
Mag). Does anybody know of this article or have a resource they could
point me to that outlines best practices in this area?

Thanks,
Jason Wood, DBA in training.

One possible approach is to maintain only CREATE scripts, and use versioning
in your source control system to ensure that you can always build a given
version from scratch. To generate a upgrade script, you can then create
empty databases for the source and target versions, and use a comparison
tool such as the one from Red Gate to create a migration script. If you have
many versions, then you might do this only on demand; if you have fewer, you
might do it every time you produce a new version.

Whatever approach you take (and I'm sure there are many others which work
fine), a database comparison tool is always a good investment. The Red Gate
one is relatively cheap compared to multi-platform tools like Embarcadero,
and works very well:

http://www.red-gate.com/sql_tools.htm

Simon