In this post I want to cover how you can create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using GitHub Actions.

By the end of this post, you will know how to create a dacpac for a dedicated SQL Pool within GitHub Actions for your CI/CD deployments.

Creating database project for Azure Synapse dedicated SQL Pool

In a previous post I covered how you can create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure Data Studio. After somebody asked a question about creating a dacpac on Twitter.

You can refer back to that post to find out more about creating a database project for a dedicated SQL Pool in Azure Data Studio.

I then did a follow up in another post about how to create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOps. With this in mind, I thought it was only fair that I showed how to do the same thing using GitHub Actions.

In this post I will use the same database project in Azure Data Studio. Picking up where I left off in the previous post.

Synchronize to GitHub instead to create dacpac using GitHub Actions

I had to do a few steps in order synchronize the existing Database Project in Azure Data Studio with GitHub. Because it was currently set to sync with Azure DevOps. First of all, I had to create new repository in GitHub.

Once I had done that, I went a back to the Database Project I had created in the previous post in Azure Data Studio. I went back into Source Control and removed the remote I had set up previously.

However, this time around I got the URL from GitHub instead of Azure DevOps.

I called the remote origin again, just for reasons. Once I had done that, I selected the same sync option that I selected in the previous post. Afterwards, I refreshed my GitHub repository to check the sync had worked.

Create dacpac for Azure Synapse dedicated SQL Pool using GitHub Actions

Now for the topic at hand. To create a GitHub Action, you need to create a file in the ‘.github/workflows’ subfolder in your repository.

To demonstrate how to create one quickly I went to the ‘Actions’ section as shown below. From there I created a simple workflow by clicking the highlighted button.

If you are following along you will notice that a new file has been created.

You can rename this file at the top and then select the Start commit button if you want to see the default template in action. However, I decided to make changes at this point.

So, I changed the build job to the below. Note the similarities between the logic used here and the logic I used in the post on how to do this using Azure DevOps.

  BuildDacpac:
    # The type of runner that the job will run on
    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
      - uses: actions/checkout@v2
      
      # Find msbuild
      - name: Add msbuild to PATH
        uses: microsoft/setup-msbuild@v1.0.2
        
      # create dacpac using MSBUILD
      - name: Build dacpac for Database Project
        run: |
          msbuild.exe 'Create dacpac from ADS test.sqlproj' /p:Configuration=Release
                  
      # Publish SQLPool artifact containing the contents of the Build results folder
      - uses: actions/upload-artifact@v2
        with:
          name: SQLPool
          path: ${{ github.workspace }}/bin/Release/  

In this example I used a GitHub-hosted runner that runs the latest version of Windows Server. However, I prefer to use self-hosted runners.

In addition, I do have to thank Greg Low for suggesting I look for setup-msbuild. It basically finds the latest version of MSBuild for your agent to use.

Once I had made the above changes, I made an initial commit of the new workflow.

When I went back to the Actions section, I saw that the workflow had completed.

To view further details, I clicked on the workflow name highlighted above.

Deploying the dacpac forusing GitHub Actions

After testing that the dacpac was created OK I went to deploy it to an existing SQL Pool.

Note: If following along here make sure you have your Azure Synapse Analytics dedicated SQL Pool created first.

Before adding the YAML for the deployment job I first created a secret in GitHub to securely store my connection string.

After adding the secret, I then added the below YAML to the end of the file. Note that it uses the same ‘Azure SQL Deploy’ action that I used in my post about deploying to Azure SQL Database using GitHub Actions.

  DeployDacpac:
    # Set the dependency for the BuildDacpac job
    needs: BuildDacpac
    # The type of runner that the job will run on
    runs-on: windows-latest
    
    # Steps represent a sequence of tasks that will be executed as part of the job
    steps:
    
      - name: Download SQLPool artifact
        # Downloads Data artifact to dacpac folder
        uses: actions/download-artifact@v2.0.9
        with:
          name: SQLPool
          
      # Deploys SQLPool dacpac
      - name: Deploy SQLPool Dacpac
        uses: Azure/sql-action@v1
        with:
          # Name of the SQL Server name
          server-name: '{YOUR SYNAPSE DEDICATED SQL ENDPOINT}'
          # The connection string, including authentication information, for the SQL Server database.
          connection-string: '${{ secrets.AZURESQLPOOL_CONNECTION_STRING }}'
          # Path to DACPAC file. *.dacpac or a folder to deploy
          dacpac-package: 'Create dacpac from ADS test.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' 

When you are adding new actions it’s worth checking that you are using the latest version. For example, the ‘Download SQLPool artifact’ action above. Which you can find in the Marketplace if you search for ‘Download a Build Artifact’.

You will also notice in the above code sample that I specify {YOUR SYNAPSE DEDICATED SQL ENDPOINT}. Just like I did in my previous post about doing this in Azure DevOps.

To clarify, this is the dedicated SQL endpoint that you can find in your Synapse workspace overview page. I showed the overview page before in a post where I did a five-minute crash course about Synapse Studio.

Note that I have used the full name of the dacpac here for certainty. However, as you can see in the comments you can also use other means.

Completed dacpac workflow in GitHub Actions

Once I committed my changes the workflow ran again and completed as below. So, I can update the database project in Azure Data Studio and have the updates deployed to my dedicated SQL Pool using GitHub Actions.

Of course, in real life your workflow can be more complicated than this. I recommend experimenting yourself.

Related video showing GitHub Actions

Last Saturday myself and Sander Stad presented ‘GitHub Actions Duet – LA Edition’ at SQL Saturday LA.

I have added a link for it below in case anybody wants to watch a recent GitHub video that’s related to this post. For those who have seen a version of this session before I can assure you this one will be different.https://www.youtube.com/embed/E5GuzW0n7sU?feature=oembed

Final word about creating dacpac for Azure Synapse dedicated SQL Pool using GitHub Actions

I hope this post about how you can create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using GitHub Actions proves useful.

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

About the Author:

Reference:

Chant, K. (2021). Create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using GitHub Actions. Available at: https://www.kevinrchant.com/2021/06/15/create-a-dacpac-for-an-azure-synapse-analytics-dedicated-sql-pool-using-github-actions/ [Accessed: 8th July 2021].