Deploy a dacpac to a serverless SQL pool using GitHub Actions

Reading Time: 4 minutes

In this post I want to cover how you can deploy a dacpac to a serverless SQL pool using GitHub Actions. Yes, you are reading that right.

It is now possible thanks to a SqlPackage update. Which you can download this update on the ‘Download and install SqlPackage‘ page.

In a previous post I covered how to deploy a dacpac to a serverless SQL Pool using Azure DevOps. Plus, a while back I gained a couple of the new GitHub certifications for Azure partners.

So, I thought it was only fair that I did a GitHub version that performs CI/CD by doing the below.

To clarify, a dacpac file is a special file that you can use to deploy database schema updates to SQL Server related databases using a state-based deployment. Plus, when I say serverless SQL pool I mean an Azure Synapse Analytics serverless SQL Pool.

I also want to make it clear that you can only deploy a dacpac to a serverless SQL Pool that has been created especially for serverless SQL Pools. Due to the fact that serverless SQL Pools only support a limited number of objects.

For example, one created with the method that I show in this post. Alternatively, one based on a custom database project like in one of my previous posts.

You can read more about what is supported in detail in the Microsoft page about Transact-SQL features supported in Azure Synapse SQL.

As always, this post contains plenty of links.

Template to deploy a dacpac to a serverless SQL pool using GitHub Actions

I created a GitHub repository to accompany this post that you can use as a template for a GitHub Actions workflow. It is called GitHub-SynpaseServerlessSQLPool-dacpac.

You can find the code in this post in the ‘serverlessSQLPool-sqlpackage.yml‘ workflow file. Which is in the ‘.github/workflows’ subfolder.

Anybody can download it and change as they see fit. All I ask is that you give it a star in GitHub if it proves to be useful.

I showed an examples of the SqlPackage extract and publish commands in my original post about deploying a dacpac to a serverless SQL Pool. So I will not repeat them in this post.

Deploying a dacpac to a serverless SQL pool using GitHub Actions

One key point to remember is that you can only perform CI/CD with GitHub Actions using YAML. Unlike Azure DevOps there is no GUI-based alternative.

All of the below uses a self-hosted GitHub-runner that I have setup locally. Which has the latest version of SqlPackage installed.

I also configured various secrets in the repository before creating the workflow. Which I cover in the template that is available.

In the workflow I first extracted the contents of the database into a dacpac. I then published the dacpac using the below code.

  ExtractDacpac:
    runs-on: self-hosted

    # Steps represent a sequence of tasks that will be executed as part of the job
    steps:
      # Extract schema from an existing database in a serverless SQL Pool into a dacpac file
      - name: Extract Database schema
        run: |
          SqlPackage /Action:Extract /TargetFile:${{ secrets.TargetFile }} /p:VerifyExtraction=true /SourceServerName:${{ secrets.SQLPoolEndPoint }} /SourceDatabaseName:${{ secrets.SourceDB }} /SourceUser:${{ secrets.SQLPooluser }} /SourcePassword:${{ secrets.SQLPoolpw }}
      # Publish artifact
      - uses: actions/upload-artifact@v3.1.2
        with:
          name: ServerlessDacpac
          path: ${{ github.workspace }}

Afterwards, I deployed the contents of the dacpac using the below code.

DeployDacpac:
    # Set the dependency for the build job
    needs: ExtractDacpac
    # Easier to use Github-hosted runner if updating in GitHub
    runs-on: self-hosted

    # Steps to deploy the updates to a SQL Server 2022 Database
    steps:
      - name: download artifact containing dacpac
        # Dowloads Data artifact to dacpac folder
        uses: actions/download-artifact@v3.0.2
        with:
          name: ServerlessDacpac

      # Login using Azure credentials 
      # Required to add a temporary firewall rule for the runner
      - uses: azure/login@v1.4.6
        with:
          creds: ${{ secrets.Azure_credentials }}


      # Installs dacpac
      # For this to work you MUST have two secrets specified, defaults in my repo are blank
      # One called SQLDB_SERVER which contains your logical SQL Server name
      # Requires a secret SQLDB_CONNECTION_STRING which contains connection string to your serverless SQL Pool
      - name: Serverlesss SQL Pool Deploy
        uses: Azure/sql-action@v2
        with:
          # The connection string, including authentication information, for the Azure SQL Database.
          connection-string: '${{ secrets.ServerlessDB_Connection_String }}'
          # Path to the dacpac file in the artifact
          path: './${{ secrets.TargetFile }}'
          # Action we want it to do, in this case 'Publish' the contents of the dacpac to the database
          action: 'publish'
Footer

As you can see, I used the Azure SQL Deploy v2 GitHub Action to do the deployment. Which is the GitHub Action which also known as sql-action v2. You can read more about that in a post I wrote about using Azure SQL Deploy v2 for dedicated SQL Pool deployments.

More GitHub

If you want to learn more about GitHub, myself and Sander Stad (l/t) are co-presenting a session at SQLBits next week. We are presenting a session called ‘Unveiling the magic of CI/CD for SQL Server using GitHub Actions’.

I am pretty excited about presenting this session. Because this years SQLBits venue is ten minutes away from where I graduated in the UK.

So, if you are attending the SQLBits on the Saturday and staying for the raffle towards the end (which I assume is still taking place) feel free to join us. Our session is at 16:00 GMT.

Those interested in certifications relating to GitHub Actions can find out more in my post about recommended certifications for DevOps enthusiasts.

Final words about deploying a dacpac to a serverless SQL pool using GitHub Actions

I hope showing how you can deploy a dacpac to a serverless SQL Pool using GitHub Actions helps some of you. Because I realize that performing CI/CD with GitHub is becoming more popular.

If you prefer a migration-based deployment you can still use the method I covered in the November 2022 edition of the Azure Synapse Analytics and Microsoft MVP series. Where I covered using CI/CD for Serverless SQL Pools in Azure Synapse Analytics.

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