At my current employer, the developers are branching for development per story and merging for release in Git - and frankly I'm jealous. ...but I haven't been able to wrap my head around how a SQL Server DB workflow should work in this manner, especially considering tooling that I am aware of / using. So, I'm curious if anyone has some thoughts.
~Our SQL Server environment
[Prod] <- [Testing] <- [Dev Release] <-> [Dev01],[Dev02],[Dev03]
Each team has a dev server stack where stories are worked. There are 25+ databases per server. When product/project determine which stories are to release, those changes are merged to Dev Release - and eventually flow back to team dev. Dev Release is then used to script the deployment. Thus any ongoing projects (long term / third party integrations / etc) aren't included with the release. This means that there is not a point guaranteed where dev release and any team dev are fully synchronized. Also of note, the sequence of changes made to dev release are not related to the sequence that changes are applied within team dev...
One way (problem): If we have a single branch for each team dev server - it appears to be easy to keep in sync with tooling (RedGate Source Control, etc)... ...but prepping for a release (in Git) either means cherry-picking commits (too much work) or git copying files and losing dev commit history (too much work). (I assume that SVN's file deltas would preserve dev history - but would still be a pain (as its not a merge)... guessing) Although dev history is lost, we would still have a list of release changes - but that doesn't work for me. This feels wrong.
Another way (problem): If we branch per story - then it would be fairly trivial to merge to dev release and preserve object history. However, there wouldn't be a single branch that matches the state of the database, and thus tooling (RedGate, etc) will always be complaining that the DB and the repo are out of sync. This feels annoying.
At my previous employer, we branched for release using SVN. All forward development teams worked towards a common release - thus forward changes were developed against the trunk (or a dev branch reconciled to the trunk), and hotfixes were applied to the release branches. Depending upon the product, we had different workflows for publish - but the idea was similar. Most all the links I've found (even on this site) seem to point to that workflow - as described very well here: http://www.infoq.com/articles/agile-version-control
However, all the teams (excluding hotfixes) were working towards a common release - thus the dev branches had to reconcile / merge. So, if we had a project / integration that wasn't in the release, we'd have to back out changes of the trunk for the freeze - and reapply once the next release cycle started. Annoying - but doable. Of note, the changes to the database could be applied in a serial fashion - as they were (mostly) all related. So, it's no surprise that tooling SSDT / RedGate / etc seem to be geared for this...
So, just curious if anybody has any pointers, helpful URLs, or can point out where my ponderings have gone awry, I'd be grateful for the info.