Database promotion life cycle

I need to review schema (table definition, constraints, etc.) inconsistencie
s
between development, test and production environments.
As part of our migration process to dot net, I've identified a number of
inconsistencies that are not typical of the promotion life-cycle. These
issues appear to be the result of an inadequate promotion process, during
which many schema changes have not been propagated. Conversely, there seem
to be many schema elements in production or QA that have not gone through th
e
life-cycle, instead having been implemented directly, rather than promoted.
Is there a document out there that can at least be used as a template to get
my review started? I'm certain this isn't anything new to businesses across
this country.SQL WHEEL wrote:
> I need to review schema (table definition, constraints, etc.) inconsistenc
ies
> between development, test and production environments.
> As part of our migration process to dot net, I've identified a number of
> inconsistencies that are not typical of the promotion life-cycle. These
> issues appear to be the result of an inadequate promotion process, during
> which many schema changes have not been propagated. Conversely, there see
m
> to be many schema elements in production or QA that have not gone through
the
> life-cycle, instead having been implemented directly, rather than promoted
.
> Is there a document out there that can at least be used as a template to g
et
> my review started? I'm certain this isn't anything new to businesses acro
ss
> this country.
>
Couple of suggestions:
1. Use a product like SQL Compare to determine what the differences are
between your environments
2. Starting immediately, do not allow direct modification of database
objects. Every object (table, sproc, trigger, etc) should be scripted
and stored in a version control system. Future modifications and new
objects MUST be done ONLY through version control. To modify an object,
you would first "check out" the script from version control, make the
necessary modifications, check the new script in, THEN deploy to the
development environment. Scripts for new objects are checked in, then
deployed. You can then use the capabilities of your version control
system to generate a changelog, making it easier to "release"
enhancements to testing, and finally production.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I use SQL Compare already to compare DTS, schema a data. We don't have MS
Source Safe in house at present. What is done is the scripts are sent to me
and deployed in DEV. The real problem is that b/c we're on a rapid
development track to get our legacy apps on dot net, I receive scripts both
for DEV and TEST...sometimes PROD as well. That, plus our DEV/TEST
environments do not match PROD. Main reason there is b/c PROD has cross
server calls whereas DEV/TEST are self contained.
I want to put together a best practices doc at least the framework of one to
move forward from this mess. Is there a link out there where someone has
already done some leg work that I can take and modify to make it applicable
to our environment?
"Tracy McKibben" wrote:

> SQL WHEEL wrote:
> Couple of suggestions:
> 1. Use a product like SQL Compare to determine what the differences are
> between your environments
> 2. Starting immediately, do not allow direct modification of database
> objects. Every object (table, sproc, trigger, etc) should be scripted
> and stored in a version control system. Future modifications and new
> objects MUST be done ONLY through version control. To modify an object,
> you would first "check out" the script from version control, make the
> necessary modifications, check the new script in, THEN deploy to the
> development environment. Scripts for new objects are checked in, then
> deployed. You can then use the capabilities of your version control
> system to generate a changelog, making it easier to "release"
> enhancements to testing, and finally production.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||SQL WHEEL wrote:
> I use SQL Compare already to compare DTS, schema a data. We don't have MS
> Source Safe in house at present. What is done is the scripts are sent to
me
> and deployed in DEV. The real problem is that b/c we're on a rapid
> development track to get our legacy apps on dot net, I receive scripts bot
h
> for DEV and TEST...sometimes PROD as well. That, plus our DEV/TEST
> environments do not match PROD. Main reason there is b/c PROD has cross
> server calls whereas DEV/TEST are self contained.
> I want to put together a best practices doc at least the framework of one
to
> move forward from this mess. Is there a link out there where someone has
> already done some leg work that I can take and modify to make it applicabl
e
> to our environment?
>
Your first step needs to get the environments configured the same so
that you can deploy script X to dev, confirm it works, then deploy
UNMODIFIED script X to test, test it, then deploy UNMODIFIED script X to
production. Create self-referencing linked servers in Dev and Test with
the same names that Prod uses, then your scripts don't have to be modified.
Your second step needs to be to establish a FIRM rule that NOTHING goes
directly into production, the process needs to be DEV -> TEST -> PROD,
no exceptions. Until you do that, you don't have a snowball's chance of
keeping things under control.
Tracy McKibben
MCDBA
http://www.realsqlguy.com