Need Branching Strategy Advice

tomslickers

We are struggling to come up with a good branching strategy for our databases. We are using SQL Source Control with Git, Jenkins (for builds) and Octopus (for deployments to Test and Production). Only the master branch is pushed through the Jenkins/Octopus processes. 

Suppose we have two pending issues (Issue1 and Issue2) being worked by two different developers. Each developer creates a branch, commits their changes to their respective branches, and performs unit testing. Both decide the changes are ready to be pushed downstream and merge their changes into master. The Jenkins process does a successful build and we use Octopus to push the changes into our Test environment. 

Suppose further that for whatever reason, we decide that only Issue1 is ready for production deployment. How do we unwind the Issue2 change? Is there a better way to handle this?

MikielAgutu

Hello Tom

Your question is difficult to answer in some ways, as it's about your team's processes. However I'll try and answer the best I can.

To me, the main problem is that you're putting your changes through the CI process too late. As you said:

'Only the master branch is pushed through the Jenkins/Octopus processes.' 

This means you'll only be able to fully test your changes once they've been merged to master. If you discover the changes are bad, you'll want to undo them, as you said.

In my opinion, your master branch should always be in a releasable state. That means any changes that go into master must be fully tested and reviewed before they're merged.

Therefore, the simple fix is to ensure that you put all your branches through your Jenkins and Octopus testing process. That way you'll be able to validate if they're ready to be deployed. Only merge a branch into master once you're convinced that branch is in a releasable state.

If you're still in a position where you find a change needs to be 'rolled back', the best way is to introduce the fix as another branch, and go through the testing process over again. In this sense you should 'roll forward', instead of trying to undo things.

I hope that's helpful. As I said, this is a broad question about Agile delivery, and this is only my opinion on the matter based on your description of the situation.

Thanks

Mikiel

way0utwest

As Mikiel answered, you really want to more exhaustively test things. If you need to revert back a particular change that shouldn't go, issue 2. then you'll have to undo the change in your dev branch, and merge back that undo. This could be problematic, as the nature of database development is that the chronology of changes matters.

If Issue 1 is a stored proc/view/function change and issue 2 is as well, this is easy. Revert back to the previous version of issue 2, then merge that in. If issue 2 is a table change, then this could be problematic if other work is built on top of this as you're not affecting data. There isn't a simple way to do this, as undoing table changes is much more complex and really is an "issue 3" change that you roll out.

Having feature toggles/flags and rolling db changes out first are more helpful here if you need to undo something.

I'd also say that your branches ought to be very short lived (hours). If someone is working out things longer than that, they ought to pull from master to merge in other changes and be sure they aren't breaking thing. You also will want to ensure that developers working independently sync up and coordinate to be sure they're not working on dependent objects. If they are, they will potentially cause conflicts. If they edit the same object, it's like two people editing the same method. A recipe for problems.

tomslickers

Thank you both for your replies. Pushing all the branches through our DevOps process does indeed seem to solve this, although I have not finished thinking this through. For example, suppose branch Issue1 is pushed into our Test environment and is approved for Production. Do we just deploy that branch, or do we go back and merge the changes into master and repeat the Test/Production cycle? Either way this will require some coordination between developers but that is the case even without an automated DevOps solution.

way0utwest

Merge to master. The Issue 1 Branch allows the developer to work and test without disturbing releasable software (in master). Once this is "done", then merge it into master. In this way, you have a merge point for Issue 1 and one for Issue 2, which should allow you to undo or revert work if needed.

tomslickers

Thanks again. This is coming together. Do you recommend using Git to do the merge, or SQL Source Control? The first approach works fine although you don't get the visual difference report the way you do with SQL Source Control. That said, I'm having trouble with the latter approach. Let's say I do the following:

Create a new branch named Issue1 from master
Code a change (e.g. add a column) and check it in

At this point Jenkins starts a build and all is well. However, if I then checkout master (using git checkout), SQL Source Control thinks the Git version (in master) is more recent than what's in my database and shows the column deletion on the Get Latest tab. The Commit tab is empty.

This is obviously not what I want. Thoughts?

way0utwest

You should not be moving branches often in SQL Source Control (SOC). This doesn't support things well, and it creates confusion. The preference for SOC is to assume your database is the source of truth. This means you get updates in either Commit or Get Latest that sometimes don't make sense. If you want to commit your source of truth, do that. If you don't, you might need to undo those changes.

The fundamental issue is that we try to treat all objects the same, and for tables, this is a problem. We don't have an easy way to populate new columns or hold data from dropped columns when branches change.

You ought to have a separate database per branch. Ideally, when you abandon a branch, you should drop that db, and then create a new one to reload from a new branch. If you're trying to determine what is different when merging two branches, this is better handled in a VCS tool (GitKrakken, SourceTree, etc). SOC doesn't handle this well and it gets confusing.