CI/CD for serverless SQL pools using GitHub Actions

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.

I created a sample GitHub repository to accompany this post. that I created in GitHub; it is called GH-SynapseServerlessSQLPool-migration. Which you can freely clone or import. 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, I then went to the ‘Actions’ tab in GitHub and ran my workflow. Which was able to perform CI/CD for serverless SQL pools using GitHub Actions sucessfully.

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 job and add it to the existing workflow. However, I do recommend using the ‘needs’ syntax in your second job. So that the second job depends on the first one completing. As shown in the below code.

  # 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 this post 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.