Starting DB CI - Change automation vs SQL Source Control projects

JeromeL

Hi! I've been looking for some time now to get the DB into our CI system.

We have a pretty common setup, or so I think. Visual Studio and SQL Server of course. We are using SVN for source control and using this branching pattern : one main trunk, branches for developpements, branches are merged back into trunk when ready. Trunk is branched onto release branches once in a while (one release active at a time). bugfixes and new features are commited into trunk but merged into the active release branches, for lot of small releases.

For build and deployment, we use Teamcity and Octopus. For the database, we would use SQL Compare and just create manual scripts when needed. We would deploy manually with SQL Compare and the scripts (order depending of the changes to be made).

For the past weeks, I've been playing a bit with both Sql Source Control and SQL Change Automation (projects part). I understand both let me produce a schema to be built and deployed from teamcity and octopus, which is my goal.

SQL Source Control seems simpler to use, but maybe the tradeoff is less control over migrations. SQL Change Automation projects looks a bit more complex and seems to offer more control over deployment and are migration centered.

I've experimented problems with boths while trying to setup. With source control, adding a blank script to make some data changes would make the build fail. I've been told by support that the most recent version lets you add pre and post migrations, so I could probably use date to make data migrations.

With Change Automation projets, I've also had problem adding a script to make some data change. I found out that since change automation builds from scratch the shadow database, well selecting inexistant data fails. I guess I could script my static data (which I want to modify) and that would probably resolve the problem.

My question has more to do with comparing both products. I'm not sure which one I should choose to go on with. Are there other differences, are they targeted more over this kind or that kind of developpement? Which one would you advise for the situation I described here?

Thanks!

way0utwest

Here's a relatively short answer. I have a longer one in draft and there also is this short description: https://www.red-gate.com/library/redgates-database-lifecycle-management-approaches-a-comparison

First, in either method, branching can be problematic. You want those feature branches for db changes to be as short as possible if there is the chance of conflict. We dont' want anyone building on bad work, so this is a tradeoff, but I'd suggest as soon as you're confident of a change, merge right away and re-pull a branch if you have other things to do.

Now, SQL Source Control (SOC). I like this for simplicity and additive, easy changes. Meaning no data manipulation, no renames, merges, splits. Those are a hole in the state/model/comparison approach. You've seen this with SQL Compare, which powers this process. If you're here, this works well. The migration scripts in SOC are flaky and since you can't control when they run. If you have schema changes depending on these, you can have issues. I would only use these as idempotent, run once scripts and then only if I can assume that order of execution doesn't matter.

Right now there is no pre-post, but I'm hoping this will be added at some point. No news to share, but I really think this solves a lot of SOC issues, especially for the post scripts.

For SQL Change Automation, SCA. This is a proven method that's been used for a long time, and it works well, but the branch thing applies more, because merges across branches are a pain. Chronology matters in migration scripts, so whenever you try to merge these, you need to not merge the code, but sort out the ordering and potentially rename scripts to control execution. At any scale of team members and changes, this is a pain. It's less if developers keep informed of other changes, say at a daily standup, and potentially rename/number their scripts early on. You may still need to idempotent architect the scripts if you get "fixes" in production that might slip by your process. If not, then anything going to prod needs merging into child branches quickly, potentially necessitating rewriting code or renaming files.

The other issue I have with SCA is that I need to watch the things happening in Dev. If I add a column to a table that has 1,000,000,000 rows in prod and move data, then do some stuff, then delete that column. I DO NOT want that flowing to prod. With merges, I might need to make sure I eliminate that script, which also means I'm trying to unwind something that happened in development manually. That can be problematic with numbers of scripts. The big thing here is to watch the "undo" stuff that developers might do and ensure that they don't necessarily just add the undo migration, but really think about wiping out the earlier scripts. Depending on your flow, this creates complexity.

I wish that a bunch of migration scripts gave me a good summary of what's coming. You can get this in the release testing if you flow your changes through the SCA automation process, which may help you summarize the changes and potentially go back to unwind and clean up some scripts that make and undo a change.

Final word from me. I think migrations/SCA is superior to lowering risk and covering lots of scenarios. It's not perfect and there is a higher burden on the developer to capture and manage the change scripts, as opposed to more the DBA, Ops side of DevOps with SOC. However, it covers my scenarios of any change needed better. I would slightly change this if post scripts appear in SOC, but I still prefer SCA.

JeromeL

Thanks for the comprehensive answer. I was discussing with support recently over SOC and SCA and you'll be glad to know that pre/post scripts has just been released (6.2.4.8035).

Here is the doc : https://documentation.red-gate.com/soc6/common-tasks/working-with-pre-post-deployment-scripts

I'll give it a go myself.

JeromeL

JeromeL said:

Thanks for the comprehensive answer. I was discussing with support recently over SOC and SCA and you'll be glad to know that pre/post scripts has just been released (6.2.4.8035).

Here is the doc : https://documentation.red-gate.com/soc6/common-tasks/working-with-pre-post-deployment-scripts

I'll give it a go myself.

released yesterday august 22nd, be sure to check frequent updates!

way0utwest

Whoa, that's cool. And also, #$%##@ developers. I didn't see an update for when this was going live.

I'll have to give it a go as well. Let me know if this helps or you have more questions. If they're related, post here. If not, start a new thread.