Guidance for branching, merging and deployments.

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

Moderators: Chris Auckland, David Atkinson, sherr, PhilScrace, andy.campbell.smith

Guidance for branching, merging and deployments.

Postby skuhn » Tue Jan 10, 2012 8:29 pm

Do you have any tutorials, videos, white papers, or other form of guidance on how to handle merges and multiple active branches?

For instance we have a production branch and a development branch in which we have some migrations scripts. It appears that SQL Compare determines the usage of migration scripts based upon the source control version number that is set in the extended properties of the SQL Server database. So, what happens for us, is if a fix is applied to production then the production database has a newer version than what our development branch started with and SQL Compare doesn't determine that a migration script needs to be used. In our testing we have fudged this by either removing or resetting the version number in the target database, but this seem "hacky". So I am curious how Red Gate would recommend handling branching and merging and deployment in a multiple branch environment.

We are using the latest versions of SQL Source Control 3.x and SQL Compare 10.x along with SSMS 2008 R2 against a SQL Server 2005 and 2008 R2 databases. Our source control tool is Vault 5.1.

Thanks in advance,
Scott
skuhn
 
Posts: 19
Joined: Fri Dec 30, 2011 4:45 pm

Postby David Atkinson » Fri Jan 13, 2012 11:09 am

Is it that you want to pick your 'production' branch as the source, and the 'development' branch as the target?

Currently, migration scripts are only picked up when both the source and targets are on the same branch.

Would you be able to describe the nature of the changes that have been made to your production branch? This would help us consider ways to solve this in a future release.

David Atkinson
Product Manager
Red Gate
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby skuhn » Mon Jan 16, 2012 5:40 pm

Thanks for clarifying David.

Our situation is that we have a Production branch that reflects what we currently have in production and we branch from that to create a development branch when we begin working on a new release. During development we use a development database linked to the development branch. Since our development may take some time, we could have some changes required to our production database that must go into production before the release will. As a result, the production branch has progressed at the same time as the development branch. (This could also happen if we had two distinct releases in development at the same time.) Either way we need to resolve this. Typically we would make the same change to the development branch that we applied to the production branch. This is very common for us.

Ultimately, when development is complete and we are ready to deploy we could take one of two approaches:
1. Using the source control tool (vault in our case), merge the development branch to the production branch and then deploy to the production server from the production branch -OR-
2. Deploy to the production server from the development branch and then subsequently bring the changes into the production branch either through merge or through linking in SQL Source Control and committing changes.

This is our first time working with SQL Source Control so it is entirely possible that we are trying to use the tool incorrectly or in a way that was not intended or thought of.
skuhn
 
Posts: 19
Joined: Fri Dec 30, 2011 4:45 pm

Postby David Atkinson » Mon Jan 16, 2012 5:46 pm

I think both options should get your production database in the desired state, so I'm not sure it matters which one you do. If you want to get more opinion from developers, I'd suggest posting your question on www.stackoverflow.com (I'd be curious to see the responses, so please post a link on this thread).

The one thing you'll have to bear in mind if you're generating deployment scripts with your source and targets on different branches, is that your any migration scripts you may have added won't be picked up. The tool has no way of being able to find a 'safe' route from one branch to another. In a future release we hope to put in a feature that will allow you to create a cross-branch migration script, which would allow deployment scripts to traverse branches while picking up migration scripts, but I can't say when this will be implemented.

David
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby skuhn » Mon Jan 16, 2012 7:56 pm

Thanks David,

Okay; that confirms for me that the cross branch migration script won't work, and I understand why. We'll be looking forward to this being supported at some point in the future.

For now, I think we can "cheat" by resetting the version extended property to the version that the development branch started with, or removing it entirely, which seems to work.
skuhn
 
Posts: 19
Joined: Fri Dec 30, 2011 4:45 pm

Postby maturmel » Mon Jan 23, 2012 5:38 pm

Hi,

I have a setup that is very similar to what Scott described, and I recently upgraded from SSC 2.0 to 3.0. I am still evaluating how I need to set things up to take best advantage of the new Migrations feature.

Reading this thread got me asking this question: If you merge changes that contain a migration script from development branch to production branch, but always generate the upgrade script from Production branch, wouldn't that allow the tool to pick up the migration script properly? If so then I could easily work with that constrain as usually we will generate our scripts (and app builds) from the more stable branch.

Thanks
Marc
maturmel
 
Posts: 24
Joined: Tue Jan 25, 2011 9:16 pm

Postby David Atkinson » Mon Jan 23, 2012 6:11 pm

If you generate the deployment scripts from the same branch, it should work, so merging from dev to production branches seems sensible.

I'd encourage you to try it out and post your findings on this thread!

Kind regards,

David
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby maturmel » Mon Jan 23, 2012 7:22 pm

Thanks for the quick reply David, I will set it up accordingly and try it out to see the exact result. I'll post back once I've done my test.

Thanks
Marc
maturmel
 
Posts: 24
Joined: Tue Jan 25, 2011 9:16 pm

Postby maturmel » Mon Jan 23, 2012 8:19 pm

I have setup a simple test scenario, where I have a source-controlled database with two tables. I've linked the database and then committed it, and then immediately branched the result in Vault. I now have two branches of the same DB. I linked and did Get Latest on second branch (B2) and all went well.

I then returned to B1 and did a schema change to which I attached a migration script. I added an INSERT statement at the end of the migration script. Committed the change to B1. Merged the change from B1 to B2. Then I opened SQL Compare to generate my script. The migration script part is not getting picked up. I can see the migration script in B2 through SSC as well as in Vault, but for some reason when trying to generate scripts from version X to latest, it will be ignored even through it is part of the changesets interval selected in SQL Compare. Same thing when initiating the comparison from SSC (with the Deploy/Schema menu).

Not sure if there's something I'm doing wrong or if it just fails in considering the migration scripts to build up the production compare script.

I will try to leave the migration scripts folder as 'shared' between the branches - from the documentation and tooltips when initially installing SSC 3.0 it seems like it was recommended to avoid branching the migration scripts folder. I will see how that reacts in my branched DB scenario.

Any clues/directions/suggestions also appreciated.
Thanks
Marc
maturmel
 
Posts: 24
Joined: Tue Jan 25, 2011 9:16 pm

Postby maturmel » Mon Jan 23, 2012 8:47 pm

Part 2 of my test results

- 'sharing' the migration scripts folder was a bad idea. :) The second branch always try to destroy the scripts added by the first branch (sees it as a pended delete).

- I came back to my original setup and tested some more with SQL Compare. The only case I will see the migration scripts detect properly is when I use the 'B1' branch (my development branch) as the source of comparison. It never seems to work when trying to generate from the production branch.

A bit more of background:
We are working with a 'code promotion' type of branching strategy for our code, and our database needs to follow the same path as well. Most of the database changes are done from our 'DEV' branch. They eventually get merged (promoted) to our QA branch and finally to STABLE branch (production). The latter is where the version upgrade scripts for our system are usually generated from.

Hope this helps
Marc
maturmel
 
Posts: 24
Joined: Tue Jan 25, 2011 9:16 pm

Postby allmhuran » Wed Oct 03, 2012 5:26 am

This is the same pattern I would ideally use. It does, of course, work very well for application code. But I can't find a way of getting the database side integrated.

Without this ability it seems I would need to separate the application side from the database side, making life much more complex and time consuming for the devs, and for overall project management. Alternatively, I could abandon branching entirely. I'm not willing to adopt either option, which basically means the database is once again left out of source control.
allmhuran
 
Posts: 9
Joined: Mon Jul 11, 2011 2:15 am


Return to SQL Source Control 3

Who is online

Users browsing this forum: No registered users and 2 guests