Make Database Code Reusable in SQL Source Control with Deployment Filters

When I first began working with databases, I was lucky to land a job at a little start-up which had solid development and operations processes: all our code, including database code, was in version control. We had a strong database architect who instilled good coding practices into the way we managed database code as well: code was expected to be reusable whenever possible.

For example, there was one set of code which managed error handling and logging for all our databases. It lived in one repository in version control, and was deployed into many databases. This code resided in its own schema, in this case the “EVT” schema. Similarly, there were other bits of “reusable” database code for managing configuration, transferring data between instances, documenting objects and procedures, and other common tasks.

There are huge benefits to establishing reusable pieces of code for your databases

The top five benefits I can think of, are…

• It becomes easy to instill good practices, such as quality error handling, across different teams, without each team needing to learn in-depth about the topic. They simply need to learn how to call the reusable code.
• It is easy to test if core good practices have been implemented, by checking for the existence of the reusable code and whether it is working as part of deployment processes
• It is far more efficient to version and maintain code commonly used across databases by storing it in one repository and creating tests for that code itself
• Those doing production operations gain a consistent way to quickly gather information from databases in production across every environment. For example, 15 years later I still remember that I would run the EVT.LogList procedure to check for error output in databases when diagnosing an issue
• This vastly simplifies writing triage documentation for your databases

But how do you implement this with modern tooling?

Back in the day, our company wrote our own tooling to help us with this, because there simply weren’t any vendors who provided this functionality. Writing and maintaining that tooling required a significant investment on our part.

These days, there are lots more tooling options available (and now I work for a vendor in this area, Redgate Software). However, it can be puzzling to determine how to implement reusable code in modern database tooling, especially “state-based” tooling such as Microsoft SSDT or Redgate SQL Source Control.

That’s because when you do a state-based deployment, tooling of this type compares the object definitions which you have stored in source control with the target database, and generates commands to make the target database look exactly like the objects you have in source control.

A diff occurring between a committed version of database code and a target database. In a state-based deployment, tooling automatically generates the code to be deployed from this differencing task. Without the use of filters or special tooling, any objects not in version control will be scripted to be dropped.

This becomes tricky if you are only deploying part of a database, especially if there are any dependencies which exist between the part of the database you are deploying and the rest of the database. If you do nothing, the state-based solution will assume that you want to make the target database look exactly like what is in version control and only like what is in version control, so it will script DROP commands for anything else.

Deployment filters simplify managing reusable database code

How you solve this problem depends on your tooling. Since I work for Redgate, I naturally invest my time in working with our tooling the most – so from here on out I’ll be talking about deploying SQL Source Control projects with SQL Change Automation, which has features which help with this problem.

SQL Change Automation has graphic extensions/plugins as well as PowerShell cmdlets. I’ll refer to the PowerShell cmdlets when describing how to do this, but know that the very same functionality is available in the plugins:

• If your reusable code is in a repository all by itself, you can use the -IgnoreAdditional option to deploy everything from your build package and simply ignore (aka don’t drop) additional objects in the target database
• If your reusable code is in a shared repository (perhaps with other reusable code, perhaps with something else), and you want to build the whole thing but only deploy part of the code (say, a schema), this can be done by specifying a filter file saved from SQL Compare using -FilterPath when you create the release artifact.

In other words, no matter how you are versioning the code you want to make reusable, there is a path towards being able to safely deploy this code in an automated fashion to target databases as needed.

Reusable code at work

There are other interesting patterns which deployment filter files enable. For example, Chris Unwin and I recently worked on a case where we were asked how to make schema based deployments to a large number of production databases as efficient as possible. This can be done with a variety of orchestrators – in this example the use case is to have Octopus Deploy manage the release orchestration.

We’ve just published a 46 minute YouTube video, “Reusable Schema Deployments with SQL Source Control and Octopus Deploy” which gives you a view of this at work. (To be clear, Chris 100% did all the hard work here and I’m riding his coattails!) If you’d like to check out individual parts of this video, a table of content with links is at the bottom of this post.

• 00:40 Overview of the problem we are solving: finding the most efficient way to deploy individual schemas (which are each stored in version control once) to a large number of production databases (which each may contain a mix of some or all of the schemas)
• 3:00 A discussion of state and migrations approaches, and why we are discussing a state-based solution with SQL Source Control for this problem
• 5:10 An overview of the two parts of the solution: Release Artifacts are reusable, and deployment filters effectively “ignore” anything that is excluded by the filter (it will not script “drop” commands for them)
• 8:05 An overview of the solution workflow
• 10:32 Demo begins! An overview of the databases used for the prototype environment proving this out
• 14:00 An overview of the build pipeline in Azure DevOps Services which builds SQL Source Control and hooks things up with Octopus Deploy – and a brief excursion where Chris de-mystifies YAML and shows Kendra that dealing with it in Azure DevOps is MUCH easier than she thought
• 22:00 A view of how the prototype environment works in Octopus Deploy (including a view of the lovely aquatic dark theme in Octopus now)
• 24:00 A view of the procedural deployment steps in Octopus Deploy using Redgate SQL Change Automation and an explanation of how steps can be configured to run against certain environments
• 28:00 A view of the filters created for schema deployment, and a demo of how to create them in SQL Compare
• 33:15 A view of how managing “environments” as groups of databases makes it easy to perform deployments of a schema to many databases with a minimum of steps in Octopus Deploy
• 35:02 Time to show it at work! Chris kicks off a deployment
• 36:25 A review of the Release Artifacts created, and the changes and code therein
• 40:39 Summary /recap of the benefits of this approach