As a database developer or administrator of a data warehouse or custom application database, deploying changes is a regular occurrence. Whether it is a bi-weekly, monthly, or even quarterly event, a repeatable method is needed, especially for less frequent releases. Even with well-defined written procedures, manual deployments could result in issues coming up. A new column was missed in a schema comparison, or a handful of lookup records for the next release made it into this release. If a database deployment process is running a schema comparison between development and production, look at using Visual Studio SQL Server Database projects, which will be the first stop in this blog journey. If you manage all schema changes in a series of scripts as you go, you may be interested in Flyway from Red Gate to execute those scripts and help with deployments.

Source control is also needed, and a little later in the series, there will be enough information to choose between Azure DevOps and GitHub. Depending on which product you choose can (but doesn’t have to) help decide which tool(s) to use to “orchestrate” the release cycle, managing deployments in each environment between dev and production.

Even as this series gets to the database instance for dev/test/production, different deployment backend processes must be considered between SQL Server and Azure SQL DB/Managed Instance. This series will cover how to deploy schema and data changes to each environment, whether needing a service principal for Azure DevOps to log into Azure SQL Managed Instance or a “build box” on your network to deploy changes to SQL Server.

Getting started can be overwhelming…

Roadmap

Throughout this series, one or more posts will dive into the tools listed in the table below. The information being shared will be from documentation of the tools and primarily from observations made while working with them. I like to learn new things and share what I’ve learned with others. Based on what we know in 2023, my goal in building this series is to provide a reasonably in-depth look at what it takes to create the processes necessary to automate builds and deployments.

“Hey, Erin! You are calling this automating deployments. Why aren’t you calling it CI/CD?” 

Regarding databases, I need help seeing changes being rapid enough to call this Continuous Integrations and Continuous Delivery (or CI/CD). When I hear CI/CD, I think of an application that has multiple changes every week or even every day. As databases are the foundation for enterprise applications or a data warehouse, I see changes made less frequently. As I noted at the start, my thought is to have a set of changes every couple of weeks or once per quarter. It is semantics, but for this series, I will stick with automated builds and deployments.

Please stay tuned as this series develops. There is an Automated Deployments menu item on the top of the page. This will take you to various posts on this topic.