Deploying SQL database changes automatically through CI/CD pipeline is important and a bit tricky part of deployment pipeline. You don’t want to apply your database changes only manually, because that can lead easily into problems like scripts not working or someone forgot to run one important script into production. Azure DevOps does not have native support for SQL scripts, but there are plenty of good 3rd party tools that can be used. One quite common tool is Liquidbase, but in this tutorial I’m going to use something more lightweight, a tool called Roundhouse. Roundhouse is an OSS database migration tool, that can run SQL scripts from given folder and keep track of what has been run. The Roundhouse is licensed under Apache 2.0 license.
The Roundhouse runs all the scripts from given folder, so you should separate the database scripts into own folder structure/repository. You can use separate repository (create new) for database changes, or do like I did and add Database folder into existing code repository. I have used following kind of structure to keep track of database changes.
Create following folders under database folder (or into repository if you decided to create new one). The most important folders that I mainly use are the Up and RunManually. In Up folder I store all scripts that are doing some schema or data changes. For example alter tables, inserts, updates etc. In RunManually folder I am storing all migration scripts that should be run manually and checked carefully before and after running.
In the Up folder we can store normal SQL scripts that are compatible with Azure SQL. I usually do the scripts, so that they can be run multiple times without issues, but that is not mandatory, because Roundhouse keeps record on what scripts has been run. The record keeping is based on hashes, that are stored inside the database (in their own roundhouse schema and tables). If you change the existing script, so that the hash changes the Roundhouse will run that script again during deployment. This can lead into problems if you have scripts, that works only once. That’s why I add existing checks around the insert statements just for extra safety. I recommend to name the SQL script files so, that they have number prefix in the name. For example 0001-First Script.sql and 0002-Second Script.sql. This is an easy way to order scripts in folder and define their run order.
After setting our repository structure and SQL scripts, we just need to create new Azure DevOps pipeline and add two tasks into it.
The CI/CD architecture uses SQL scripts that are stored into Azure DevOps repository and a Windows build agent to run Roundhouse. You can use Azure DevOps hosted agents without issues. I also use Azure KeyVault to store the database connection settings, so that they are not visible for everyone that has access to Azure DevOps.
In the pipeline we add two Command line tasks
In the Install Roundhouse task we simply run dotnet tool install and install specific version of Roundhouse tool. You want to fix the version to avoid any issues with new releases of the tool.
dotnet tool install -g dotnet-roundhouse --version 1.0.2
The update database with Roundhouse task is a bit longer, because we need to specify the database name (dbname), script folder, connection string (connection string) and environment name (environment if needed). The full list of parameters can be found from this page. The /c= 540 sets command timeout and /w is for warnononetimescriptchanges instead of failing.
rh /d=(dbname) /f="$(System.DefaultWorkingDirectory)/Database" /cs="(connection string)" /env=(environment if needed) /ct=540 /outputpath=..\logs /disableoutput=true /w /noninteractive
After setting tasks we can run the pipeline and monitor the script execution. You should be able to see something like Running RoundhousE v1.0.2.0 against… if everything has gone right. The Roundhouse lists all the scripts and their run status into Azure DevOps release log, which is useful if some scripts has errors.
Summary
Roundhouse is a light weight tool that is easy to setup and use. I like that it does not require any extra XML or JSON files to work and it supports plain SQL scripts that can be run manually or automatically by the tool. I usually first write the new SQL script, run it manually against development database and then store that same script into Git. The Roundhouse will update the test and production environment through the Azure DevOps pipeline automatically after changes are merged into proper branches. I have had absolutely zero issues using the Roundhouse for over three years, so I can easily recommend it.