I've read plenty of posts about the significance of database version control. However, I possibly could not look for a simple solution how to see if database is within condition that it ought to be.
For instance, I've got a databases having a table known as "Version" (version number has been saved there). But database could be utilized and edited by designers without altering version number. If for instance developer updates saved procedure and doesn't update Version database condition isn't synchronized with version value.
How you can track individuals changes? I don't need to track what's transformed but only have to see if database tables, sights, methods, etc. have been in sync with database version that's held in Version table.
Why I want this? When you are performing deployment I have to make sure that database is "correct". Also, not every tables or any other database objects ought to be monitored. Can you really check without needing triggers? Can you really be achieved without third party tools? Do databases have checksums?
Allows state that we use SQL Server 2005.
I believe I ought to provide a little more details about our current atmosphere - you will find there's "baseline" with all of scripts required to create base version (includes data objects and "metadata" for the application). However, you will find many installations of the "base" version with a few additional database objects (additional tables, sights, methods, etc.). Whenever we have alternation in "base" version we have to update some installations (not every) - in those days we must make sure that "base" is within correct condition.
We use DBGhost to version control the database. The scripts to produce the present database are saved in TFS (together with the origin code) after which DBGhost can be used to develop a delta script to upgrade an atmosphere to the present version. DBGhost may also create delta scripts for just about any static/reference/code data.
It takes a mind change in the traditional method but is an excellent solution that we cannot recommend enough. Although it's a third party product they fit effortlessly into our automated build and deployment process.
You appear to become breaking the foremost and second rule of "Three rules for database work". Using one database per developer along with a single authoritative source for the schema would already be very convenient. Then, I am unsure that you've a Baseline for the database and, much more important, that you're using change scripts. Finally, you will probably find another solutions in Sights, Saved Methods and so on as well as in Branching and Merging.
Really, each one of these links are pointed out within this great article from Shaun Atwood: Get Your Database Under Version Control. Essential read IMHO.
I am utilizing a simple VBScript file according to this codeproject article to create drop/create scripts for those database objects. Then i put these scripts under version control.
To check whether a database expires-to-date or has changes that have been not put in version control, I actually do this:
- obtain the latest version from the drop/create scripts from version control (subversion within our situation)
- execute the SqlExtract script for that database to become checked, overwriting the scripts from version control
- now I'm able to seek advice from my subversion client (TortoiseSVN) which files don't complement the version under version control
- now either update the database or place the modified scripts under version control