Deploy SQLWATCH to SQL Server using Azure DevOps

Reading Time: 5 minutes

In this post I want to cover how to deploy SQLWATCH to SQL Server using Azure DevOps. Because I’ve started experimenting with it.

In addition, I want to show you how you can do it directly from a GitHub repository.

By the end of this post you will know how to deploy SQLWATCH to SQL Server using Azure DevOps. Using a dacpac deployment method.

You will also find out how to enable SQL Server agent within a Docker container running SQL Server 2019 on your computer. Plus, how to configure Azure Pipelines from a GitHub Repository.

About SQLWATCH

SQLWATCH is a popular open source and free SQL Server monitoring framework. It’s been developed by Marcin Gminski and is completely free under the MIT license.

You can find out more about SQLWATCH by clicking on the below image.

Using Azure DevOps

I saw that you can install sqlwatch easily using PowerShell. However, I also saw in ‘Alternative Installation’ section that you can also install from the source.

With this in mind I thought I would try deploying using Azure DevOps straight from its GitHub Repository.

Container

I decided to install SQLWATCH in a Docker container on my computer. Which meant that first of all I had to enable SQL Server agent on there.

In reality, this caused a few issues and I finally figured out how. So, after help from this post here I ran the below command to log into the container as root.

docker exec -it -u root SQL2019-Test "bash"

Once I was logged onto the container I ran the below code to enable SQL Server Agent.

/opt/mssql/bin/mssql-conf set sqlagent.enabled true

After doing this I discovered that systemctl had not been installed on the Docker container. So, my quick fix was to simply restart the container as below.

docker restart SQL2019-Test

Forking sqlwatch

Once SQL Agent was up and running I was able to configure the pipeline. Before I could do anything else I had to fork the SQLWATCH repository here by clicking the icon in the right-hand corner.

Doing this gave me a copy of the SQLWATCH repository under my own GitHub account which I could safely use whilst I tested deploying SQLWATCH to SQL Server using Azure DevOps. In addition, it needed to be under my GitHub account anyway for me to authorize access to it.

I did this because I wanted to submit a change to the original repository easily. However, you can also clone the original repository somewhere instead.

Configuring pipeline

Once I had a copy in my own GitHub account I was able to start creating my pipeline within Azure DevOps. Within Azure Pipelines I selected ‘New pipeline’ and from there stated that my code was in GitHub.

Afterwards, I had to authorize access to my fork of the SQLWATCH repository.

Once that was done I stated that I had a starter pipeline and changed the location of it. From there I was able to create my pipeline.

I started simple and created the stage which built the dacpac. Which worked first time. However, when I created the next stage to deploy the dacpac I encountered a collation issue.

In reality, I tried a few things to resolve this. Finally, I decided to change the collation of the database in the sqlproj file before creating the dacpac. By using the below PowerShell code.

      - task: PowerShell@2
        # This task changes the collation of the sqlwatch database to match yours
        # You must change the one below to match collation your SQL instance uses
        displayName: "Collation fix"
        inputs:
          targetType: 'inline'
          script: |
            # Write your PowerShell commands here.
            
            cd $(projpath)

            $Old_DC = '<DefaultCollation>Latin1_General_CI_AS</DefaultCollation>'
            # You change the collation below to match your SQL instance
            $New_DC = '<DefaultCollation>SQL_Latin1_General_CP1_CI_AS</DefaultCollation>'
            
            (Get-Content "SQLWATCH.sqlproj") -replace $Old_DC, $New_DC  | Set-Content "SQLWATCH.sqlproj"

Doing this resolved the issue. Of course, soon after I did this I discovered it was a known issue with sqlwatch.

Once the deployment was completed I ran the pipeline a few more times. Tables appear to be populating so it appears to be working.

Benefits

Now there are some benefits to installing SQLWATCH this way. For a start, automatic updates as soon as new code enters whichever repository you are using as a source.

Plus, if you work for a large enterprise your Azure DevOps agents might have better access to the SQL Servers than the machine you have dbatools running on.

Whichever method you use is up to you. However, I do recommend using the KISS principle when choosing one.

Azure DevOps Templates

After doing this I decided to add template to my copy of the sqlwatch repository called ‘Azure.DevOps.Templates’. With this template in there.

In addition, I have submitted a pull request to Marcin to include it in the SQLWATCH source. So that others can use that template and also contribute others if they want to.

Just before this post was published I found out the pull request has been approved. So, you can find the new folder in the SQLWATCH GitHub repository here.

Using an existing template

If you intend to use an existing template in this folder with Azure DevOps I recommend you fork this repository.

Once you have done that you can go into Azure Pipelines and select ‘New Pipeline’. From there you can select GitHub as the source again.

After that you can choose to use an existing yaml file as below.

Once you have done that Azure DevOps will show you all the existing yaml files it can find in your GitHub repository and will ask you to select one. Branch 2.6 contains the stable release and does not contain the folder I have created. So, select branch 3.x to access the right yaml file.

After you have done that the yaml file will appear in Azure Pipelines. Change as required and away you test.

Useful posts

Recently I have published a couple of posts that might be useful if looking to do this as below.

• Set up your laptop as an Azure DevOps agent to test SQL Server deployments
• SQL Server installation options to test Azure DevOps deployments locally

Tweet

I did notice that the SQLWATCH twitter account posted the below tweet whilst I worked on this.

SQLWATCH needs your help #SQLFamily https://t.co/juzrehzQeh

— SQLWATCH (@sqlwatch) November 5, 2020

If you do already use SQLWATCH it’s worth providing a testimonial about it.

Because a free open source solution like this can really benefit the SQL Server community. If you have found it useful let others know.

Final word

I hope this way to deploy SQLWATCH to SQL Server using Azure DevOps has given some of you ideas. In addition, I also hope it has introduced some of you to this free open source solution.

Of course, if you have any views about this post feel free to leave a comment.