Board index » delphi » database versioning best practices

2005-06-07 03:08:36 AM
delphi38
I couldn't figure out the "right" NG to post this in, so here goes...
what are some of the "best practices" techniques that are used to manage a
database's online lifecycle. Up until now, I have only been involved with
small desktop databases, but now am moving up to a mission critical web
service using MS SQL server. The web service's functionality will be rolled
out in incremental steps so I am wondering how other people are doing, for
lack of a better term, database "version control".
Are there tools to help you sync the logic in an online, production instance
of the database from a development/staging instance? Do people just use
scripts? etc.
any and all suggestions are welcomed,
thanks
larry
 

Have a look at RedGate software . it is saved my life
 

Larry,
I recently had my boss buy me the red gate software (SQL Bundle). For about
$395, you get 3 software packages that are "very cool". You can sync a
database from development to production or vice versa. You can sync the
database schema or data within the database. They have a trial version so
you can try it out first.
As to versioning your database, for example, moving from version 1.0 to 2.0
of your database, I have seen two approaches taken. Type 1, have the
original script for all tables in a file. This file gets run thru query
analyzer (or your own program). Then, there is a script (usually smaller
file) that makes version 1.0 move to version 1.5, then another script to
take it to version 2.0, etc.
Type 2, is to create a specially formated file where the first field
contains a version, followed by the text to execute. For example,
1.0, create table xxxxxx
1.0, create table xxxxxx
1.5, alter table xxxxx
1.5, alter table xxxx
1.5, create procedure dbo.xxxx as xxxxxxx
2.0, create table xxxxx
Normally you have a table in your database that contains the current version
of the database. Then to upgrade, you run a program that you create that
reads this file and executes the statements up until the version you want to
get to.
Hope this helps.
Oscar...
"larry" <XXXX@XXXXX.COM>writes

making sure all our customer databases are exactly the same structure. It is
based on some key factors:
- all changes are done after each other in exactly the same sequence on each
database
- all changes are done by running a special program
- everyone runs all scripts to get from version x to version y
You start with a database version 1.0 that contains two extra tables:
<version>and <scripts>. The specialist program (vtwscripter, vtw is dutch
for RFC) looks for a sub-directory with exactly the same name as the
version. If the sub-directory does not exist it displays a message and
enables 'exit' otherwise it enables 'go'. The end-user/dba presses 'go'. The
program checks wether or not it contains script
<version>_0001_<whatever>.sql. If it exists it runs the script, on succes or
failure it stores the script (and the response of the db) in the <scripts>
table. On error it stops displaying the reason. On succes it looks for
script <version>_0002-<whatever>.sql etc. etc. If for example the next
script is numbered <version>_0003_<whatever>.sql it refuses to go on. If the
next script is not present it assumes that it is finished and stops.
To change a version number we use a special numer:
<version>_9999_<whatever>.sql The scripter then resumes at 0001 in the new
sub-directory and so on. If a script is obsolete but other scripts have
already been made
(and thus numbered) the script's statements are replaced with a dummy
statment. All scripts are run in a transaction (DDL statements will not be
rolled back however).
All our programs (web/windows) check upon statup wether or not the database
version it the one they expect, if not they exit.
We now never have to worry wether or not a script has been run, if the
sequence in which the scripts were run was correct etc. etc. Furthermore the
distribution of the scripts etc. is rather easy and installation and running
can be done by almost every end-user.
Of course all kinds of mishap can still happen (somebody changing the
db-structure through another tool etc.) but basically it is simple and fool
proof and every update can be repeated over and over.
hth
Martin
 

"larry" <XXXX@XXXXX.COM>pí¹e v diskusním pøíspìvku
The structure is being changed by using SQL scripts.
These scripts fall within 2 categories -
A) Scripts that may affect the data (table/columns definions)
B) Scripts that do not affect the data (Indices/Triggers/Stored procedures
etc)
The category B) scripts represent no problem.
Every object is first dropped [IF (it) EXISTS] and then its new version is
re-created.
The category A) scripts are written in the way that they can be run
repeatedly
with no data loss. For example - the section that creates the table is
executed only
if the script detects that this table does not exist. Columns are added only
if they
do not exist and are dropped only if they do :-)
In addition to this, the database contains a special table -VERZE -
where the scripts can store various info which can be used in subsequent
scripts run.
This way all the user should do after installing the upgrade is to run
all new scripts he gets with the upgrade, category A) first. Rather simple.
regards
--
Roman
mail: XXXX@XXXXX.COM
URL: www.rksolution.cz