Azure DevOps CI/CD with SQL Change Automation
dsrofe
Posts: 4 New member
I am looking for some best practise. We have a git repo set up for our database with two standard branches develop and master with appropriate branch policies for each. We use feature branches off of develop for new development. New development is done locally in the features branch and local db. When the features branch is pushed to the server, it is built and deployed to a development database in Azure for other developers to use during development. This is the first problem I see. Since the features branch is pushed to the server since it is not ready to be merged into develop via a pull request, there is no forced validation of any migration scripts (as would be done in a pull request) being released to the Azure development database which could cause additional migration scripts to be created and deployed. Now the developer could certainly request an informal code review from the dba before doing all of this, but that is on the honor system. And once a migration script is deployed, it can't be changed without causing drift. See the issue?
So without creating additional long lived branches that could have branch policies applied, how are people handing this situation to ensure migration scripts are as good as they can be in early stages of development?
This is a lot simpler in the .net world!
Thanks for any insight anyone can provide. Thanks
So without creating additional long lived branches that could have branch policies applied, how are people handing this situation to ensure migration scripts are as good as they can be in early stages of development?
This is a lot simpler in the .net world!
Thanks for any insight anyone can provide. Thanks
Comments
Here are some thoughts, mostly distilled from experience (pre-SCA, DLM) and from working with some clients.
First, branching is problematic in databases, mostly because chronology matters. The order of operations for single objects matters, but also potentially across objects (dependencies, name resolution, etc.). Therefore we need to manage the order of migration scripts.
Second, switching branches for a state based entity, like a table, is an issue. This is because we may have holes (columns /tables removed in the new branch) or buckets (columns added in the new branch) and we have to decide what to do with data. How do we populate or save the data. Switching back is a bigger issue.
Therefore, branches for databases need to be super short lived. We limit .NET branches to 1 week, but I'd really limit database branches to 1 day or less. Otherwise, the changes made by other developers can grow to be unmanageble in merges.
My advice here would be this.
FB1 -> commit triggers FB1 build. -> failure, let developer know
0-> success, initiate PR for dev branch.
on success, close branch, re-initiate, with new db build if new work needed.
FB2 -> commit triggers FB2 build. -> failure, let developer know
0-> success, initiate PR for dev branch.
Dev PR approved, meaning code review -> triggers integration build
-> failure, notify developers to resolve potential merge conflicts. This could be altering the FB1 or FB2 commit or renaming/renumbering migrations scripts. Must trigger new PR/DevBranch build.
--> success, scripts now are fairly immutable. I say fairly, as there might be a need to cherry pick commits from DevBranch-> Master for release. Why? Perhaps I need an index rebuild moved before/after other changes. Or I want a risky change separated.
When I move from DevBranch -> Master, I pull over (merge) certain changes as needed to move inflight work to a release.
Personally, I think we should really develop on master, with branches pulled for features AND releases. At points in time we create a release branch, potentially chrry picking, but we know which branches give us a point in time review of either work being done (short lived) or release state (medium lived). This potentially lets me go back to a release branch for a hot fix, while there are still in-flight changes in master.
Does this make sense? I think you need a second CI build to manage this.