Skip to content

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.

Method to deploy a dacpac to a serverless SQL pool using GitHub Actions
Deploying a dacpac to a serverless SQL pool using GitHub Actions

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.

    runs-on: self-hosted

    # Steps represent a sequence of tasks that will be executed as part of the job
      # 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
          name: ServerlessDacpac
          path: ${{ github.workspace }}

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

    # 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
      - name: download artifact containing dacpac
        # Dowloads Data artifact to dacpac folder
        uses: actions/download-artifact@v3.0.2
          name: ServerlessDacpac

      # Login using Azure credentials 
      # Required to add a temporary firewall rule for the runner
      - uses: azure/login@v1.4.6
          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
          # 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'

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.

Published inAzure Synapse AnalyticsGitHub


  1. Robert Wright Robert Wright

    Hi Kevin,

    Thanks for this great article. A quick question, what is the contents of your secrets.TargetFile as I can’t locate my uploaded dacpac file.

    path: ‘./${{ secrets.TargetFile }}’

    Many thanks

    • Kevin Chant Kevin Chant

      Hi Robert,

      I will check but you can keep it simple whilst testing. For example, ‘./serverless.dacpac’.


Leave a Reply

Your email address will not be published. Required fields are marked *