Guidance for branching, merging and deployments.
skuhn
Posts: 27 Bronze 3
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
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
Comments
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
Product Manager
Redgate Software
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.
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
Product Manager
Redgate Software
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.
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
I'd encourage you to try it out and post your findings on this thread!
Kind regards,
David
Product Manager
Redgate Software
Thanks
Marc
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
- 'sharing' the migration scripts folder was a bad idea.
- 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
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.