"Testing, testing, testing, to get started with automated ETL (ELT) testing have a look here: https://the.agilesql.club/etl-testing/

What steps are there to move to safe automated database deployments?

Database deployments are scary, you have all this data and if you drop the wrong table, run the wrong delete statement or have an error in a stored procedure that forgets to write that critical piece of data then you may never truly recover from that. You may well have backups but what if your backup is corrupt? What if your stored procedure hasn’t been writing the right data for a week? Will you be able to recover that data?

There is the actual deployment itself, what if that multi-billion row table gets updated and we block users for twelve hours, well outside the 15 minute scheduled maintenance window?

Many people put off automating database deployments because of the risk and fear of allowing a tool or set of tools dictate what happens in a database. I say that you can make safe and reliable deployments using a variety of tooling, and actually, using a tool effectively is safer than writing scripts by hand. I started working with SQL server seriously in around 2005 as a DBA and everything we did was manually written. Everything we did was reviewed by other DBA’s and yet mistakes still happened, we implemented processes for dealing with failure such as using transactions, verifying modified code, and writing rollback scripts. The truth is that mistakes still happened. In more recent years I have worked on database systems and implemented continuous integration and continuous delivery/deployment. In these newer systems I have seen time and time again that fewer and fewer issues make it into production.

So, how do we automate deployments without fear?

The first thing to say is that there isn’t a quick fix, you won’t be able to buy a tool, configure it and all of a sudden you have safe deployments. To get to the place where you can deploy safely, even on a Friday, you will need to put some effort in.

Pre-Steps

Step 0

The first step, step 0, is to get your database code into source control. I put it as step 0 as many people already have their database in source control so this isn’t needed for everyone. If you don’t have your database in source control, this is the first step.

Step 0.1

The second first step, step 0.1, is to start writing tests for your databases. If you have business logic in your database then this will be database unit tests. Whether or not you have business logic in stored procedures etc. you will need integration and user acceptance tests. The goal is to be able to check-in changes, have the database deployed and any tests run to prove that the database changes are good before the changes can be promoted up to, eventually, production. A full suite of tests will take time so start today and get ahead of the curve.

Step 0.2

The third first step, step 0.2, is to make sure you have good production monitoring, capture lots of stats, draw lots of graphs, save lots of logs. Have enough monitoring so that you can fix the majority of your issues via the monitoring rather than by logging onto random servers and diagnosing issues. You will need this anyway and once we start deploying the database more often and using tooling, you will want to make sure that if something goes wrong you can diagnose and fix it asap. The first place for monitoring is production but you should also backport this to your cleaner test environments. The reason this is before step 1 is that this is something you should be doing anyway.

Actual Steps

Step 1

Choose a tool. It doesn’t matter which tool you use and you can change your mind later on. If you choose the wrong tool now, you will be able to replace it later on with something else. Please don’t spend days and weeks evaluating tools – just pick one and download it (or buy it if going commercial).

Step 2

Setup your tool so that you can deploy on check-in to an unused development database. That is all, just have the tool running and deploying changes, see when it breaks and fix it. The first step is you learning how the tool works and where it breaks. We need to do two things, the first is learn how the tool works and the second is to gain confidence that the tool is the right one for you. If every check-in breaks the deployment and you are unable to figure out why then it probably isn’t the right tool, go back to step 1 and choose another tool.

Step 3

When you have built up your confidence in the tool, start using it to deploy to your development databases. Get this setup and fit into whatever your CI process is. Fix issues when they come up and learn how the tool works. If you constantly get issues then go back to step 1.

Step 4

When you are confident in your tooling, you have a good suite of tests that has been proven to actually find bugs and when you have used the monitoring to fix issues in production, then you make sure you can restore your backups and everything actually works as expected, a DR test if you will – then it time to start thinking about using the tooling to apply database changes to production. The key to safely deploying databases is knowing your tooling very well, understand what it can and can’t do and where its foibles are. Once you know your tooling make sure you have good tests and awesome monitoring. Everything you need for safer database deployments is what you should be doing anyway, this just forces you down that path.

Non Step

These steps are for legacy databases with data in that can’t be recreated easily – if you are writing a new database or the data can be recreated easily then just choose a tool and implement it 😊