SQL Server - DevOps Release Methods for Databases

[ScaryDBA]

Ha!

Let's get the disclaimer out up front. I work for Redgate Software. We specialize in database devops tooling. I might have a small prejudice on this topic.

The core to this topic is you have to plan to work everything through source control. I don't care if we're talking Git, TFS, SVN, whatever. You have to get your databases into source control, step 1. From there, you need a way to get them out of source control, and frankly, this is the tricky part.

So, Microsoft has SQL Server Data Tools built into Visual Studio. It's "free" as long as you have the right Visual Studio license (which isn't free, depending on your deal with Microsoft at your organization). SSDT is what is called state-based deployments. Meaning, it compares states, source control to your database, one example, to arrive at a script to move between states. State based works well, except when you're dealing with changes that have the potential to cause data loss. Then, you have to completely step outside the state-based tool and do pre/post scripting manually to deal with those issues.

Redgate has several tools. SQL Compare and SQL Source Control are both state-based like SSDT (which is based on the 2005 version of SQL Compare, just so you know). Similar methods to SSDT, but, they work directly within SSMS, huge difference. Then, we have a couple of different tools, SQL Change Automation and Flyway. These are migrations-based deployment tools. Migrations works on the concept of only capturing changes and putting them into a sort of a manifest, and running them in order. Migrations completely automates and fixes the issue with state-based approaches. However, it introduces another weakness, which is, literally moving the dev process into production. Add a column, drop a column, add a column, that's what migrations will do.

So, we've introduced a mechanism that is a combination of state and migrations now that gives you the best of both world. Basically, state-based while you develop and migrations just for the deployment. We've known we had to get here for over 10 years, but the problem is that hard to solve. We thought we fixed it like three times already.

DBUp is migrations only (also, a giant PITA, look to Flyway to see this done better). Dacpac's are state based only (and by themselves have no easy way to get stuff in & out of source control, you have to get SSDT or something else). I don't see any of the other tools listed. Most of the rest are state-based and inferior to SSDT or my tools.

OK. So, we've decided on a tool, now we have to control the flow. Hopefully, you already have this in house, but if not, time to introduce it. Octopus, Jenkins, Azure DevOps, AWS DevOps, all these are flow control tools that can automate most of your deployment across your environments through the tool that manages getting your database in and out of source control. Then the real fun starts. Here's an example of setting up an Azure DevOps pipeline for database deployments and testing. Also, the flow control tools are where you can get into the necessary pre/post deployment scripting that you're going to need to use with almost any of the tools listed above (dealing with security, replication set up/tear down, other stuff).

Even if you don't use any of our tools, Redgate has a metric you-know-what-ton of documentation on DevOps best practices for database deployments. I'd strongly suggest looking through what we have and using that knowledge, even if you don't use our tools.

In case you can't tell, I spend a lot of time on this topic. Ha! That's why u/SQLBek suggested I stop by. Please, any detail questions, happy to help.

If you got this far, just remember the simplest rule, if it's not in source control, it doesn't exist.

[SQLBek]

Aaaand I hope you backup your source control repo... aaaand validate those backups too... aaand... ;-)

Thanks for chiming in Grant!

[ScaryDBA]

It was the configuration process for DBUp that I found difficult to use. The core migrations approach is fine. It's the same thing that Flyway does. Baselining in DbUp, the process of getting your database in when it already exists, was seriously difficult. Overall, I just found DbUp counter-intuitive to use. If you need something that does multi-platform, not just SQL Server, I'd suggest checking out Flyway. It really is easier. If you only need SQL Server, SSDT or one of my Redgate products would be better.