Deploy SQLWATCH to SQL Server using GitHub Actions

Reading Time: 8 minutes

Since I showed how to deploy SQLWATCH to SQL Server using Azure DevOps it’s only fair I cover how to deploy SQLWATCH to SQL Server using GitHub Actions as well.

In the post here I showed how you can to deploy SQLWATCH using Azure DevOps. However, since the source for SQLWATCH is in GitHub I thought it would be good to do it using GitHub Actions as well.

Plus, I thought it was about time I did a post relating to GitHub Actions. Especially since I had some questions about it at SQLBits after another speaker mentioned that I have used it.

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

For some of you this will also be an introduction to GitHub Actions.

For options to setup SQL Server locally before doing this you can read a post about some options here. It says Azure DevOps in the title however the same ideas apply.

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.

About GitHub Actions

GitHub Actions is how you can do CI/CD within GitHub itself. It can be useful if you want to do CI/CD with existing repositories in GitHub.

For those of you who have worked with Azure DevOps you can think of it along the lines of Azure Pipelines. You can find out more about GitHub Actions here.

Container

I decided to install SQLWATCH in a Docker container on my computer again. Which meant that first of all I had to enable SQL Server agent on there. You can read how to do that in a previous post here.

Importing sqlwatch

Before I forked the sqlwatch repository to use it within Azure DevOps. So, this time around I decided to import sqlwatch into a repository of my choice instead.

One reason is because I did not want my Azure DevOps pipeline that uses my existing repository to also run with every update. In reality, I could also have simply disabled the trigger.

You can import a repository easily by selecting ‘import a repository’ at the top of the ‘Create a new repository’ screen.

Once you have done that you can fill out the below details and click ‘Begin import’.

Change default branch

After it had finished I changed the default branch to 3.x. So that the workflow demo will run smoother because when I deployed from Azure DevOps I used the 3.x branch.

Of course, it’s easy to do this in a clone because I do not intend to submit a pull request to the original SQLWATCH repository. In addition, this makes this demo easier to duplicate yourselves if you clone the original repository instead of forking it.

Self-hosted runner

Because I want to deploy to a local Docker container I thought it would be a good idea to set my laptop up as a self-hosted runner. You can read how to do that yourself here.

However, I do recommend having a sensible folder structure for Azure DevOps Agents and GitHub Actions runners if you intend to deploy many on your laptop.

You can think of these as being the GitHub equivalent of Azure DevOps self-hosted agents which I covered in a previous post here. In fact, you can use the same ‘GitHub Actions Virtual Environments’ repository to create self-hosted images for both.

Creating a GitHub Action

Now, let’s begin to setup GitHub Actions. To setup GitHub Actions you need to create a workflow. You can think of a GitHub Action workflow the same as an Azure DevOps pipeline.

Usually I would just create a yaml file in the right location. However, since some of you will be new to GitHub Actions I thought I would direct you where to go in GitHub to view them first.

If you click on the ‘Actions’ section in GitHub as below you will be able to scroll down and select various workflow templates. However, since there’s no template there for SQL Server simply click on the ‘set up a workflow yourself’ option.

If you do this with a clone of the SQLWATCH repository the below template will appear in the Code section. To give you the ability to create a new yaml file in a new folder structure called ‘/.github/workflows/’. I highly recommend changing the name from ‘main.yml’ to something more relevant.

As you can see below GitHub has chosen the 3.x branch within SQLWATCH to use as a trigger. Because I changed it to the default branch.

In addition, I added a variable for the job and changed the type of runner to be used. I changed it to self-hosted as below since I have it running on my laptop.

  build:
    env:
      SQLWATCH_PATH: SqlWatch.Monitor/Project.SqlWatch.Database
    # The type of runner that the job will run on
    runs-on: self-hosted

I left the checkout option as below. Because I am going to need the code locally to create a dacpac.

- uses: actions/checkout@v2

After this I did an initial commit to make sure the workflow worked with my self-hosted runner. When I went back to the Actions section I could see that the file had been committed and the workflow had finished as well.

When I clicked on the workflow itself I could see it completed fine.

Creating a dacpac

Once I had tested the initial workflow I had to create the dacpac. First of all, I removed all the default text under the checkout action.

Next, I checked the marketplace to see if any new SQL Server tasks had been created which would help. You can do this easily within GitHub by going to edit your file and searching using the Marketplace on the right-hand side.

Because there were no actions to hand I had to create the dacpac from command line. I decided to use MSBuild.exe to do this.

Before creating the dacpac I had to change the sqlproj file to the right collation. As discussed in my post about deploying SQLWATCH using Azure DevOps.

In order to do this, I added the below to change the collation in the sqlproj file.

      # fix collation issue
      - name: fix collation
        run: |
          cd $env:SQLWATCH_PATH
          $Old_DC = '<DefaultCollation>Latin1_General_CI_AS</DefaultCollation>'
          $New_DC = '<DefaultCollation>SQL_Latin1_General_CP1_CI_AS</DefaultCollation>'          
          (Get-Content "SQLWATCH.sqlproj") -replace $Old_DC, $New_DC  | Set-Content "SQLWATCH.sqlproj"

Afterwards, I added the below SQL to initiate creating a dacpac.

      # create dacpac using MSBUILD
      - name: create dacpac
        run: |
          & 'C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\Bin\msbuild.exe' SQLWATCH.sqlproj /p:Configuration=Release

Once that was done I was able to create an artifact using the below syntax.

      # Publish an artifact
      - uses: actions/upload-artifact@v2
        with:
          name: sqlwatch
          path: ${{ github.workspace }}/SQLWATCHDB/bin/Release/   

Afterwards I checked the workflow and saw that I had a published artifact.

Deploying SQLWATCH

In order to deploy SQLWATCH I had to create a release job. It’s worth noting here that when you do this you need to make sure you have at least one stage in place for the yaml to be validated properly.

I decided to download the artifact into the default location using the below yaml.

      - name: download sqlwatch artifact
        # Downloads sqlwatch artifact to dacpac folder
        uses: actions/download-artifact@v2.0.6
        with:
          name: sqlwatch

After adding the below, I committed the change to check the new release job worked as expected. I clicked on the latest result and went to the release job. So that I could check the files had downloaded.

Once I confirmed the new job worked I went to deploy SQLWATCH to an existing Docker container.

Azure SQL Deploy Action

I decided to do this using the ‘Azure SQL Deploy‘ action. However, you can also run the sqlpackage command locally as well.

Before writing the yaml to do the release I created a secret which contained the connection string to the SQL database I wanted to use. Which you can do in the settings section.

I entered my secret in the below format and called it SQLWATCH_CONNECTION_STRING. For some reason you have to have the server in the connection string for it to parse properly.

Server=tcp:localhost,1443;Initial Catalog=sqlwatch;User ID=sa;Password={A PASSWORD};Trusted_Connection=True;Encrypt=False;

To get the action to work with a local Docker container I changed the action to be the below. Note that for some reason the server name has to be in both the connection string and the action itself if deploying to a local instance. I have tested this to confirm.

      - name: Azure SQL Deploy
        uses: Azure/sql-action@v1
        with:
          # Name of the SQL Server
          server-name: 'localhost,1443'
          # The connection string, including authentication information, for the SQL Server database.
          connection-string: '${{ secrets.SQLWATCH_CONNECTION_STRING }}'
          # Path to the SQLWATCH dacpac file
          dacpac-package: 'sqlwatch.dacpac'
          # In case DACPAC option is selected, additional SqlPackage.exe arguments that will be applied. When SQL query option is selected, additional sqlcmd.exe arguments will be applied.
          arguments: '/p:IncludeCompositeObjects=true'   

After I committed this change the workflow completed successfully as you can see below.

Example

You can see an example of this in my version of the SQLWATCH repository called sqlwatchGHA. I cloned it from the original SQLWATCH repository. You can find my version of it here.

In addition, the original SQLWATCH repository can be found here. Use this one if you wish to fork or clone the SQLWATCH repository. Mine is public to show this demo only.

Alternatives

In reality, you have alternative options for doing the above.

For example, you could create a workflow for the 2_6 branch instead and use the dacpac that is already there for deployments. Instead of doing a build. However, that would mean having a workaround for the potential collation issue.

As I said above, you can also run sqlpackage instead of the ‘Azure SQL Deploy’ Action as well. In fact, I have done this before in GitHub Actions. However, I wanted to get the Action to work for this post.

Another option is to deploy to virtual machines or containers in the cloud instead.

Some of you will want to do this within an editor instead of inside GitHub itself. For example, using Visual Studio Code with a local copy of the repository. With this in mind, there are various extensions you can use to make editing the yaml files easier. Like the one here.

Of course, the above is a basic introduction. You can expand on this example.

Final word

I hope this basic guide to on how to deploy SQLWATCH to SQL Server using GitHub Actions is useful.

Of course, I realise for a lot of you this will be the first time you have seen GitHub Actions. Feel free to use this post as a starting point. If you have any questions or comments feel free to reach out to me.