Using Azure SQL Deploy v2 for dedicated SQL Pool deployments

Reading Time: 3 minutes

In this post I want to cover using Azure SQL Deploy v2 for dedicated SQL Pool deployments using GitHub Actions. Which is the GitHub Action that is also known as sql-action.

This post provides an overview of what is new and provides plenty of links along the way.

I wanted to do this post due to the fact that I have updated my GitHub-SynapseDedicatedSQLPool public GitHub repository. It now contains a new workflow file called Dedicated-SQL-Pool-sql-action-v2.yml.

To help with some jargon here, a workflow file is basically a yaml file you use to define what you want to automate in GitHub.

Some of you might have seen me showing this file in the CI/CD for Dedicated SQL Pools video. Which I did for the January 2023 edition of the Azure Synapse Analytics and Microsoft MVP series

You can use this repository as a template to perform CI/CD for Azure Synapse Analytics dedicated SQL Pools using GitHub Actions. Like in the below diagram.

Anybody can download or clone this template and do with it as you please.

For example, the more ambitious of you might want to modify it so that it extracts the schema from a dedicated SQL Pool in a development environment using GitHub Actions first. Before deploying the database project to production. Like in the below diagram.

One key point I must stress here is that you can also use this GitHub Action for SQL Server related deployments as well. As I showed in an older post about how to deploy to an Azure SQL Database using GitHub Actions.

Azure SQL Deploy v2 updates in the repository

I want to highlight two main differences that are in the new workflow file.

First one relates to how this workflow works with Azure Key Vault. Which is a topic that I covered before in a post on how to keep your Azure Synapse secrets secret in GitHub.

In the new workflow only one secret from Azure Key Vault is required, as below:

      # This gets the connection string from your Key Vault called DedicatedEndPoint and
      - uses: Azure/get-keyvault-secrets@v1
        with: 
          keyvault: '${{ secrets.AZURE_KEYVAULT }}'
          secrets: 'DedicatedSQLPool-Connection-String'
        id: myKeyVaultSecrets 

It only needs to get one secret from Azure Key Vault now because with the latest version of sql-action you only need to pass through the connection string.

Second difference is the syntax used in this workflow file to call the sql-action v2 GitHub Action.

      - name: Dedicated SQL Pool Deploy
        uses: Azure/sql-action@v2
        with:
          # The connection string, including authentication information, for the dedicated SQL Pool.
          connection-string: '${{ steps.myKeyVaultSecrets.outputs.DedicatedSQLPool-Connection-String }}'
          # Name of the dacpac file in the artifact
          path: './SQLPool.dacpac'
          action: 'publish'

In reality, the syntax has changed due to the fact that there are significant changes with v2 of the Azure SQL Deploy GitHub Action.

For instance, it has been updated to align with recent changes to SDK-style database projects and updates to sqlpackage. In addition, when using sql-action v2 you can now use Azure Active Directory passwords, Service Principles and default authentications in the connection strings.

Drew Skwiers-Koballa from Microsoft covered these updates in a blog post called ‘Recently Released: Updates to SqlPackage, DacFx, and GitHub sql-action‘.

More GitHub

If you want to learn more about GitHub, myself and Sander Stad (l/t) are co-presenting a session at SQLBits next month. 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.

Final words about Azure SQL Deploy v2

I hope this post about using Azure SQL Deploy v2 for dedicated SQL Pool deployments raises awareness about the updates for this GitHub Action. Which you can use for both SQL Server and dedicated SQL Pool deployments.

Because I am aware of how popular GitHub is becoming for these types of deployments.

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