How do you version control and manage multiple branches of a Database?

WARNING: LONG QUESTION.

[QUESTION]

If the strategy is to have a branch per database, as described in the problem below, where scripts are version controlled.

How do you manage the data migration issues when trying to consolidate to fewer branches?
Is it just a cost you incur as part of data migration?

Essentially transform scripts will have to be created at the time of migration.

Is there a better way?
Can we have both issues resolved at the same time?
What is the best practice?

[BACKGROUND]

At my work place we have a product which has 3 branches. Mainline having the "LATEST AND GREATEST" changes which is not necessary ready for release.

Version B (names have been changed to protect the guilty)
Version A (names have been changed to protect the guilty)
Mainline

Because of these branches there is effectively 3 versions of the database. Code version control is fairly easy however database version control seems difficult.

Having read http://stackoverflow.com/questions/115369/do-you-source-control-your-databases it seems the best way is to export all the create scripts for each object/table. NOTE: How you manage it, in one big script or multiple scripts or a hybrid, is your preference according to the article.

I agree with this and have inquired as to why it's not done.

Currently the DBAs refuse to branch the scripts into branches. Aside from laziness as an excuse the reason is to save time with data migration. Effectively the database changes are forcibly maintained across all versions.

All the scripts are version controlled and maintain only in mainline. Version A and Version B have their own special file that states which change scripts to run on their respective branch. The problem arises when there is a change script, for instance applied to Version A but Version B only requires part of the changes. It is up to the developer to inform the DBAs to update the file which indicates which patches to apply for each branch. For change scripts which does too much manual intervention is needed to manually apply part of the change script.

To update a database on Version A all patches are extracted with Version A's which patch to apply file.

[SCENARIO]

The 3 versions above exist.
Database changes occur to Version A.
Branch consolidation where the code is merged from Version B to A so that Version B can be removed.
The same needs to happen with the database.

Hope this makes sense.

nikmd23,Thanks for the link. Source Control merging is indeed as you've referenced. However how if multiple database changes have occurred and any script changes are merged across the branches correctly. Is it just a cost the person assigned to consolidate the changes, say months later, occurs in the time they have to invest in writing transform scripts etc. The issue isn't the database script control the issue is the data migration costs. Is there away around it? For example maintaining a script for every change for data migration?Have I missed something?

Gavin Chin 2009-06-16 02:58:10

Every schema change must take data migration into consideration. Sometimes, the schema changes will not require any change in the data. Sometimes, they will require changes in the data. In that case, it's up to these change scripts to also change the data, however that may be necessary.

John Saunders 2009-06-16 03:54:21

ansaurus

tags:

views:

answers:

How do you version control and manage multiple branches of a Database?

related questions