Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Working on a project with multiple branches, where each branch is eventually merged back to the main branch, and in essence is isolated in order to develop a new feature.

The database, which is MS SQL Server, has a shared schema, however each branch makes changes to the schema as it progresses.

My primary inquiry is what are good ways to deal with sharing the schema from the main branch down to the derived branch, such that changes that are made to the main branch are easily merged into the derived branch, without stepping on new changes in the derived branch?

share|improve this question

migrated from stackoverflow.com Dec 1 '11 at 20:32

This question came from our site for professional and enthusiast programmers.

1  
Just the merging should be handled like any other code: Automatic merge, with user-intervention fallback, and inspection/testing of the result. (I prefer the way the VS Database Project handles schemas with one object per file.). The tricky bit comes with how forward-migrations of existing databases work ;-) –  pst Dec 1 '11 at 20:20
1  
This is heavily dependent on how you are versioning the schema. Are you storing create scripts for the base objects plus alter scripts? Are you using a schema comparison tool to generate alter scripts to migrate between versions? VS2010 database projects? –  Mark Storey-Smith Dec 1 '11 at 20:23
    
Relevant discussion: dba.stackexchange.com/questions/2/… –  Nick Chammas Dec 1 '11 at 20:40
    
Also relevant: martinfowler.com/articles/… –  Nick Chammas Dec 1 '11 at 22:05

4 Answers 4

I have successfully used the following methodology, elaborated in Version Control and your Database:

  • maintain a version number in metadata (I use a database extended property)
  • any schema change is coded as a script that updates from current version to next version
  • application ships with all the scripts to upgrade from version 0 (initial deployment) all the way to current version
  • Every change is done through a script. Including 'system' data changes like dictionaries and lookup table entries.
  • when deployed, the application checks the on-disk schema version, then runs all the upgrade steps to bring the schema to current required version

I often hear the opinion of 'how is this different from just keeping the object definition scripts under source control?'. The difference is huge, because when you deploy a new version of your app you're not going to simply create a new database. Most times your app will have to upgrade the existing database, including the existing data. This is a crucial difference, your upgrade steps need to ensure the integrity and consistency of existing data during the upgrade. Some operations are trivial in code (add a non-nullable column with default value to the table object definition script, done), but they are in fact hugely painful at actual deployment (the table has 1.5 Billion rows, the add column would run out of log space if done the 'simpleton' way).

How does this works with branching:

  • when the branch is created, it snaps the current schema version, say version 1.6
  • as the team start working on the branch, it adds a new version, 1.7, and then it starts coding the upgrade step from 1.6 to 1.7
  • the upgrade step gets changed as modifications are done in the branch. It always runs the script that upgrade from v 1.6 to 1.7, but what exactly those scripts do, is subject to the normal code iterations and check-ins in the branch
  • branch finishes development, it prepares for the reverse integration (to be merged back into baseline)
    • it does a new forward integration from the baseline to the branch. If the integration does not bring any changes to the schema version, all things are good, the branch can reverse integrate as-is. version 1.7 becomes the new baseline version.
    • the interesting stuff is when another branch has reverse integrated into base in the meantime and now the base schema version has changed to, say, 1.7. In this case our branch has to bump it's deployment target schema version to 1.8 and do a review of the upgrade step that was formerly from 1.6 to 1.7 to see how it operates in the new environment, upgrading from 1.7 to 1.8. Logical schema conflicts have to be resolved, script may require changes, testing has to be done. Once completed, the branch can reverse integrate into base. The deployed target version of the product now becomes 1.8.
    • when another branch that has forked at schema version 1.6 wants to reverse-integrate, it needs to it will have to bump it's schema version to 1.9, test the upgrade script from 1.8 to 1.9, then it can integrate back into the base.

Notice that there is no tool involved, no magic schema diff scripting, no wizards and no right-button-click-generate-script involved. This is a 100% developer driven process, based on source (scripts). Many find this whole process elaborate, but it works. In fact, as a SQL Server user, you have already leveraged the results of this process in your daily use of SQL Server: SQL Server itself uses a very similar database upgrade process and, as you probably expect, the product development process makes extensive use of branching and the problem you mentioned is a very real problem that has to be solved.

BTW, how the branching/integration actually occurs differs between source control products, I'm using the terms familiar from the perforce integrate mode of operation.

share|improve this answer
    
+1, especially for Every change is done through a script –  a_horse_with_no_name Dec 1 '11 at 23:45

While my answer might not be as lengthy as Remus', I found this to be a really good solution. I haven't got it set up in production just yet, so YMMV*.

Liquibase

Essentially it is an XML file where you make schema changes to your database as new elements inside the XML file. For example:

<createTable tableName="department">
            <column name="id" type="int">
                <constraints primaryKey="true" nullable="false"/>
            </column>

It has a fully fleshed out syntax so you can pretty much do anything you want to your database.

You also specify in your Liquibase installation what database you want to be versioning. Then you "run" the .xml with the included Java executable (jar file). This essentially recreates those changes specified in the XML to your database.

The real kicker is that you store this XML file in the same versioned folder as your code. So in my instance that was Git. I had this XML file in my project folder (same level as /.git) and then whenever I switched branches the XML file would change to that branch version and I would run the .jar file and my database would now reflect that branch.

*Note: I haven't finished implementation because I had trouble connecting Java to SQL Server. Needs some jdbc drivers and such and I wasn't in the mood. Hence, your mileage may vary.

share|improve this answer

Here at Red Gate we're soon releasing a database versioning solution that leverages both SQL Compare and SQL Source Control. This uses a migration scripts upgrade approach and stamps the database with a version extended property that corresponds to a source control revision.

We're hoping to release in mid-December. There is a release candidate available now. For more information, visit:

http://www.red-gate.com/products/sql-development/sql-source-control/entrypage/migration

We're hoping to build on this solution in the coming months so please let us know what you think.

share|improve this answer

If you and handling your schema changes by generating scripts and keeping those scripts under source control, then you should be able to treat the changes as you would any other code merge. You can chose to auto merge or to take more manual intervention.

share|improve this answer
    
Not really no. Manual merge of base object create scripts is viable but alter, reference data inserts and data motion scripts get very messy, very quickly. –  Mark Storey-Smith Dec 1 '11 at 21:27
    
Agreed. Here at Red Gate we believe that creation scripts will merge pretty well and could be automated. However, the migration scripts between versions will have to be hand merged to avoid incorrect dependency ordering and duplication of change code. –  David Atkinson Dec 5 '11 at 23:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.