sql server misc: Due to the nature of our development, we have to modify our database
btn successive releases ( all of them are simple enough like adding a
column). I am wondering how to effectively control the sync btn
release and database.
For exampe our release 2.1 and release 2.2 might not run with the same
database because of the table changes, in that case how do we know if
the database we have is for release 2.1 or 2.2. Right now, we have a
table for version information and updating whenever there is a change
in database thru scripts. I am wondering if there are other approaches
that developers are following. I would like to hear other ideas.

Regards,

The usual approach is a version table on the DB, and a version field within
the application ( which increments with every release).

Immediatly upon connecting to the database, the application reads the
version table using the version field in the app as the key, and if it finds
a row, and the expires date on that row is not yet reached, the application
continues to run, else it issues the user a message indicating it is not
compatible and a newer version of the application is needed.

You can embellish this with a field in the version table that refers to the
version that has replaced the specified version.

example...

VersId VersDate VersExpDate VersUpGrd VersComments
1 2004-03-01 2004-05-15 1.1 Initial
release
1.1 2004-05-15 2004-06-16 1.2 Upg to add
feature xyz...
1.2 2004-06-16 1.3 Ver
1.3 is optional, thus no exp date on 1.2
1.3 2004-06-25
Optional upgrade, has performance enhancements...

....time passes, 1.4 comes out and obsoletes all previous versions..so 1.2
and 1.3 get an expiration date

VersId VersDate VersExpDate VersUpGrd VersComments
1 2004-03-01 2004-05-15 1.1 Initial
release
1.1 2004-05-15 2004-06-16 1.2 Upg to add
feature xyz...
1.2 2004-06-16 2004-07-31 1.3
1.3 2004-06-25 2004-07-31 1.4
1.4 2004-07-31
Replaces all prev versions.....

This approach also gives you a nice history of upgrades including the point
at which each version was obsoleted.


Again, its up to the application to NOT continue if it recognizes that its
version has expired.


As an enhancement to the above, you might consider associating a version of
the database with certain functional parts of the application. For
instance, if you have a Human Resources component that is affected by a
database change, and the rest of the application is unaffected, you can do a
version table check and if the application version is not yet expired you
can continue. Then when the user attempts to navigate into the HR
component, it compares its own version with the DB version and make a
decision for itself whether it will run, based on the DB version. This way,
the HR users will find that they have to upgrade to continue, but the rest
of the user population can wait until later. This is a suggestion, not a
firm recommendation. It can get messy because you are going to have to be
sure that you fully understand all of the the impacts of each upgrade. You
have to weigh the tradeoffs and make your own call as to whether this 2nd
level of version checking has value for you.


[quoted text, click to view]