CI/CD for serverless SQL pools using GitHub Actions

Reading Time: 4 minutes

I want to cover one way you can do CI/CD for Azure Synapse Analytics serverless SQL pools using GitHub Actions in this post. For various reasons.

For a start, in a previous post I wrote about how you can CI/CD for serverless SQL pools using Azure DevOps. So, I thought I would balance things out and show how you can do the same thing within GitHub.

In addition to this, there have been a few discussions about using GitHub Actions instead of Azure Pipelines within the Microsoft Data Platform community recently. For example, the topic came up during the DataWeekender conference.

With this in mind, I want to show how easy it can be to migrate an Azure DevOps pipeline to GitHub Actions.

Just like before I will use the DBOps PowerShell module. You can find the GitHub repository for this in the Data Platform Community organization. Which is the new name for the the sqlcollaborative organization that hosts the popular dbatools PowerShell module.

It is a great open-source module for implementing migration-based deployments for various types of databases. For example, Oracle and SQL Server.

You can find a copy of the repository that I created in GitHub; it is called GitHub-SynapseServerlessSQLPool. If it proves useful for you, please give it a star in GitHub.

CI/CD for serverless SQL pools video

Before I go any further I want to point out that you can see the contents of this post in the video for the November 2022 edition of the Azure Synapse Analytics and MVP series.

It is worth noting that there are other solutions available to do this. Which are either open-source or come at a cost because they provide more functionality.

CI/CD for serverless SQL pools using GitHub Actions

First, I imported the Azure DevOps version into a new GitHub repository. Afterwards, I created four encrypted secrets based on the four variables I had used for the pipeline in Azure DevOps.

I also had to enable Actions to be used in the settings section. Which was a first for me.

From there, I created a new file in the repository in a subfolder called ‘.github/workflows/’ , called ‘Single-Serverless-Pool.yml’. I then added the below code.

name: Update Azure Synapse Serverless SQL Pool

# Sets the trigger to update when update is pushed to main branch
on:
  push:
    branches: 
      - main

jobs:

  # Job to install the scripts
  ServerlessPool1:
    # Easier to use GitHub-hosted runner if updating in GitHub
    runs-on: windows-latest

    # Steps represent a sequence of tasks that will be executed as part of the job
    steps:
      # Checks-out your repository under $GITHUB_WORKSPACE, so your job can access it
      # Note that I am using latest version of action
      - uses: actions/checkout@v2.4.0 

      # install dbops PowerShell module
      - name: Install dbops module
        run: 'Install-Module -Name dbops -Force -PassThru'
        
      # Run migration-based scripts
      - name: Run migration-based scripts
        run: |
          $SecurePw=ConvertTo-SecureString ${{ secrets.Pw }} –asplaintext –force
          Install-DBOScript -ScriptPath scripts -sqlinstance ${{ secrets.sqlinstance }} -Database ${{ secrets.database }} -UserName ${{ secrets.UserName }} -Password $SecurePw -SchemaVersionTable $null

Note that even though the syntax is different from the yaml used for the Azure Pipelines template the logic is still the same. In reality, there are a lot of differences.

To help with this, GitHub provides a guide for the workflow syntax in GitHub Actions. Plus, there are also various converters available online. For example, the Pipelines To Actions converter.

Anyway, as you can see below the script worked and I was able to do CI/CD for serverless SQL pools using GitHub Actions.

I had created a new database in Azure Synapse Analytics for this test. To confirm that it worked I checked that an external table that was in one of the scripts had been created in Azure Synapse Analytics.

Extending workflow for serverless SQL pools

You can extend this workflow for serverless SQL Pools easily a couple of ways.

You can just copy the final run command so that you can use it to deploy to other serverless SQL Pools.

Alternatively, you can copy the entire Serverless1 job and name it accordingly like I have done for the below workflow.

One key point about the above workflow is that the second job depends on the first one. Which is ideal if looking to do deployments using deployment rings. To do this in GitHub Actions I used the ‘needs’ syntax after the job name.

  # Job to install the scripts
  ServerlessPool2:
    # Set the dependency for the ServerlessPool1 job
    needs: ServerlessPool1

Final words

I hope this post about CI/CD for Azure Synapse Analytics serverless SQL pools using GitHub Actions has inspired some of you.

Because the aim of this post was to show how easy it can be to translate a yaml pipeline created in Azure Pipelines to a yaml workflow for use with GitHub Actions.

In addition, I hope the GitHub-SynapseServerlessSQLPool repository introduces some of you to the DBOps module.

Of course, if you have any comments or queries about this post feel free to reach out to me.