Introducing the dbops PowerShell module for Azure DevOps deployments

Reading Time: 4 minutes

In this post I introduce the dbops PowerShell module for Azure DevOps deployments as part of the community tools awareness month. Which is an initiative that was suggested by Brent Ozar for this month.

Brent invites us all this month to introduce a free tool to everybody and to share some things about it.

Years ago I wrote a post about recommended free tools for SQL Server. However, I wanted to focus on a tool that I have found since writing that post. Which is the dbops PowerShell module.

By the end of this post, you will know what the dbops PowerShell module is and how it can help you with Azure DevOps deployments. Along way I also cover the free DbUp .NET framework. Even though I introduce two free tools in this post I focus on dbops.

Plus, I reveal where you can find a new repository that I now share. Which contains a template that you can use to perform CI/CD for Azure SQL Database using dbops. So, keep reading.

Introducing DbUp

Before covering the dbops PowerShell module I want to quickly cover DbUp.

DbUp is a .NET library that you can use to do migration-based deployments. It is open-source and is licensed under the MIT license, which you can read about in the DbUp license file.

According to the official list of supported databases, it allows you to do migration-based deployments to various databases. Such as SQL Server and MySQL. As you will discover later in this post it also works with a newer Azure service as well.

To get started you first create a C# project in Visual Studio. Afterwards, you build up your scripts sequentially. Like in the below example.

When you go to deploy to SQL Server DbUp will check what scripts have already been run and will continue from there. It does this by creating a table in the database. Which by default is called SchemaVersions. As you can see in the example below.

Once you are happy with your project you can upload the C# project into a Git repository in Azure Repos. Which is the service in Azure DevOps which allows you to store your code in repositories.

From there, you can use DbUp by itself in Azure Pipelines for your deployments. I must admit that I did not find this approach that graceful. Which brings me to the dbops PowerShell module.

Introducing the dbops PowerShell module

I first discovered the dbops PowerShell module thanks to a tipoff from Sander Stad (l/t). It is a great PowerShell module that was created by Kirill Kravstov.

It is a fantastic PowerShell module that allows you to perform CI/CD for SQL Server related services. Which also uses DbUp behind the scenes.

Personally, I think that dbops is great. Mostly due to the fact that it makes deployments in Azure DevOps so much more graceful. For example, the below code. Which is an extract from a working YAML pipeline in Azure DevOps.

      - task: PowerShell@2
        displayName: 'Run migration-based scripts'
        inputs:
          targetType: 'inline'
          script: |
            $SecurePw=ConvertTo-SecureString $(AzureSQLpw) –asplaintext –force
            Install-DBOScript -ScriptPath scripts -sqlinstance $(AzureSQLSrv) -Database $(AzureSQLDB) -UserName $(AzureSQLuser) -Password $($SecurePw)

You can find the GitHub repository for dbops in the Data Platform Community organization (previously known as the sqlcollaborative organization). Which is the same GitHub organization where you can find other free tools such as dbatools.

Kirill Kravtsov (l/t) created this PowerShell module. Just like DbUp, it is also open-source and licensed under the MIT license.

I first used dbops to solve an Azure Synapse Analytics dilemma that various members of the Data Platform community were talking about. Which was how to perform CI/CD for serverless SQL Pools. Because you cannot deploy updates to them with a dacpac file.

You can read more about that in my blog post on how to perform CI/CD for serverless SQL pools using Azure DevOps.

Azure SQL Database template for dbops

I created a repository especially for this post which you can find in GitHub. You can use it as a template to perform CI/CD for Azure SQL Database using Azure DevOps. It is called AzureDevOps-AzureSQLDatabase-MigrationBased.

One key point about this new template is that I specify two variable groups that I created inside the YAML file. First variable group contains non-sensitive variables. Second variable group gets secrets from Azure Key Vault. As per the below extract.

variables:
- group: AzureSQLDatabaseMigrationBasedKeyVault
- group: AzureSQLDatabaseMigrationBasedNonSensitive

You can find out more about linking a variable group in Azure DevOps to an Azure Key Vault in a post about how to keep your Azure Synapse secrets secret in Azure DevOps.

Final words about the dbops PowerShell module

I hope this post about the dbops PowerShell module has introduced some of you to this fantastic free PowerShell module. Because I think it is a graceful way to do migration-based deployments using the Azure Pipelines service within Azure DevOps.

If you use the dbops PowerShell module then I highly recommend sponsoring Kirill Kravstov.

It is worth noting that there are other premium tools available that you can use to do migration-based deployments for SQL Server. Of course, these tools tend to come at a cost.

I want to thank Brent again for creating this initiative. Of course, if anybody has any comments or queries about this post feel free to reach out to me.