DevOps for SQL databases

In August 2024's T-SQL Tuesday, Mala Mahadevan (b|t) asks us to reflect on how we manage database code. Thanks for hosting, Mala! 😊

One of the great privileges of being a consultant is experiencing the diversity of approaches taken by technical teams. Frequently I join teams for a relatively short time, and often don't have scope or time to influence change control processes. The time I spent in 2020-22 working for a startup provides happy memories in this area – I worked with a brand new data engineering team to create a data platform from scratch, and we were able to build a database code management process that is still my preferred pattern today.

For me, there are two important principles at work when considering code management:

• We're doing our best, but we all make mistakes. Let's build processes that protect us from ourselves!

• Getting code safely into production is frequently painful, but as the DevOps folks say, “if it hurts, do it more often”. Let's build processes that make code deployment safe, repeatable and fast.

Of course, our data platform was made up of a variety of data ingestion and transformation technologies. In this post I'm talking about the features we relied on to manage Azure SQL Databases specifically:

• State-based database definition. Each database was defined in a SQL Server Data Tools (SSDT) project for Visual Studio.

• Source control in Git. Each database project was stored in a Git repository, in our case Azure DevOps repos.

• Unit tests. Each database had an associated set of unit tests implemented in tSQLt.

• Continuous deployment. Approved changes were automatically and immediately promoted into production. This may sound terrifying 😱 – bear with me!

Supported by the features I described above, our development and deployment process looked like this.

1. Create a feature branch. Proposed changes can only be made in feature branches, isolated from the main branch containing the authoritative definition.

2. Make your changes. Modify objects in the SSDT project as required. The database definition says “this is what the database should look like” – it doesn't say how to migrate an existing database into that shape, but that's a problem for later 😊.

3. Test your changes. Write tSQLt tests to demonstrate that your changes work as expected, that they handle unexpected situations gracefully, and that you haven't broken anything else! Tests are implemented in separate SSDT project, linked into a composite project with the core database definition – this allows us to run the tests without having to deploy them into production.

4. Open a pull request. Open a PR to request that your changes be pulled into main.

1. Review the pull request. A colleague in the team – not the engineer proposing the change! – reviews the changes in your PR and comments on the code as required. Broadly, a PR reviewer is looking for two things:

   • do the changes do what they're supposed to?

   • do the tests exercise the changes correctly and sufficiently?

   If the answer to both questions is “yes”, the PR can be approved.

2. Run the tests. A branch policy on main triggers an Azure DevOps Pipeline run. This pipeline run builds the SSDT composite project – tests included – and deploys it to a suitable environment for testing. The deployed tests are executed and the results reported in Azure DevOps Test Plans. If any test fails, the pipeline fails.

The main branch policy requires two conditions to be met before a PR can be merged: the changes have to pass peer review, and the test pipeline has to run without error.

1. Merge. When the PR is approved and the automated tests all pass, complete the PR to merge the feature branch into main.

2. Build. A pipeline run is triggered for main. This pipeline run builds the core SSDT project – excluding tests – and publishes pipeline artefacts for deployment (usually a DACPAC file and publish profiles for the target environment(s)).

3. Deploy. Deploy the DACPAC into target environments using the SqlAzureDacpacDeployment@v1 task. Unlike changes to software, database modifications are implemented by migrating a target database from its current state into the desired state – the deployment task compares the current (database) and desired (DACPAC) states, and automatically determines what changes are required.

The result of this process was that the team were able to make daily (sometime more frequent) deployments of database changes into production. This makes engineers – and stakeholders! – happy because they're seeing continuous delivery of value.

A key observation here is that every change is small. Small changes are quicker to write, quicker to test, quicker to review, quicker to deploy – and quicker to undo when something goes wrong. Making small changes, more often, makes every change less painful! The tooling is important, but what's more important is the process – smaller reviews are likely to be more thorough, and good test coverage provides confidence and protection against regressions.

And if your change is just really big, here's a challenge: Are you sure you can't break it into a series of smaller ones? 😊