Moving changes from test server to production server?

We're starting out some development efforts here at work (with SQL Server
2000) and I'd like to know where I can find some best practices regarding
migrating changes from your test sql server to your production server. The
basic iteration as I see it is:
1) Create initial database on production server (at the time, this is
probably the datbase from your test server - after testing is complete).
2) Next phase of development starts where you will alter the database in
response to bug fixes, additional requirements, etc.
3) That development effort is finished, complete with testing the test
datbase. So, now you want to move those "changes" to the production server.
4) Repeat steps 2-3 as required.
As most companies new to SQL Server, we are learning the product via
Enterprise Manager for the most part. I believe we'll have to take more of
a script-based approach (meaning creating sql statements to alter the
database as opposed to using the EM GUI) to properly address this issue.
I see a couple of issues here that need I am not quite clear on:
A) The physical steps of updating your test server incrementally (as you
discover changes that need to be made) and then upgrading a set of those
incremental updates to your production server once you're ready. I assume
the best way to do this is through sql statements (that alter the database)
that you could store in files. I'm not sure if Enterprise Manager has the
concept of "versioning" with regards to changes in the database and offers a
way to automate this type of migration.
B) Keeping up with these changes in some organized manner like you would
with versioning a software product. My assumption is that I could keep a
file (or maybe a table) that would contain each logical/incremental change
to the database and a date or a version to identify the change. That
version/date could be stored somewhere in the database (maybe another table)
so you could determine the "version" of the database and know what updates
you might need to apply.
I'm sure I'm missing many things and that this has been thought through many
times. I just don't want to reinvent the wheel here, so any help would be
much appreciated.
Thanks!SQL Server Central has a group of 4 articles on this process which are
pretty good.
Here is a link for the 4th article. You can see the other 3 articles
(parts) at the bottom of the article.
http://www.sqlservercentral.com/columnists/DFuller/changemanagementachievinganautomatedapproachpart4.asp
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||like every shop I've ever worked in, you require a change management process.
Everyone has such a process even if you consider that not having a process is
a process in itself. Most however get bogged down in the complexity and spend
an age making their processes work and this is where I created the tool DB
Ghost which is a database change management utility designed to rid the IT
staff of the complexities of having a robust process to develop and deploy
changes quickly, reliably and with complete control and auditability.
The articles Rick Sawtell mentions are in fact the architectural theory
behind the DB Ghost software. If you bother to investigate you may well find
what you are looking for. It may however mean you have to change the way in
which you think about change management as the concepts portrayed seem to be
very rare although no-one has ever been able to disprove them and evidence is
bountiful in regards to disproving every other method.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"epigram" wrote:
> We're starting out some development efforts here at work (with SQL Server
> 2000) and I'd like to know where I can find some best practices regarding
> migrating changes from your test sql server to your production server. The
> basic iteration as I see it is:
> 1) Create initial database on production server (at the time, this is
> probably the datbase from your test server - after testing is complete).
> 2) Next phase of development starts where you will alter the database in
> response to bug fixes, additional requirements, etc.
> 3) That development effort is finished, complete with testing the test
> datbase. So, now you want to move those "changes" to the production server.
> 4) Repeat steps 2-3 as required.
> As most companies new to SQL Server, we are learning the product via
> Enterprise Manager for the most part. I believe we'll have to take more of
> a script-based approach (meaning creating sql statements to alter the
> database as opposed to using the EM GUI) to properly address this issue.
> I see a couple of issues here that need I am not quite clear on:
> A) The physical steps of updating your test server incrementally (as you
> discover changes that need to be made) and then upgrading a set of those
> incremental updates to your production server once you're ready. I assume
> the best way to do this is through sql statements (that alter the database)
> that you could store in files. I'm not sure if Enterprise Manager has the
> concept of "versioning" with regards to changes in the database and offers a
> way to automate this type of migration.
> B) Keeping up with these changes in some organized manner like you would
> with versioning a software product. My assumption is that I could keep a
> file (or maybe a table) that would contain each logical/incremental change
> to the database and a date or a version to identify the change. That
> version/date could be stored somewhere in the database (maybe another table)
> so you could determine the "version" of the database and know what updates
> you might need to apply.
> I'm sure I'm missing many things and that this has been thought through many
> times. I just don't want to reinvent the wheel here, so any help would be
> much appreciated.
> Thanks!
>
>