Sql-action v2 GitHub Action for Azure SQL Database deployments

Reading Time: 4 minutes

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

Back in 2020 I did a post about how to deploy to Azure SQL Database using GitHub Actions. Afterwards, I shared a GitHub repository called GitHub-AzureSQLDatabase. So that people can download it and use it as a template to perform CI/CD for Azure SQL Database.

However, after my previous post about using Azure SQL Deploy v2 for dedicated SQL Pool deployments I thought it was about time that I added a new workflow.

With this in mind, I created one called Azure-SQL-Database-sql-action-v2.yml. Which uses the newer sql-action v2 GitHub Action. You can access the repository by clicking on the previous link or on the image below.

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

About the Azure SQL Database repository

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.

However, the more ambitious of you might want to modify it so that it extracts the schema from an Azure SQL Database in a development environment using GitHub Actions first. Before deploying the database project that contains the schema to production. Like in the below diagram.

Updates in the repository

I want to highlight a few key points about this new workflow file.

As you can see below, I have renamed the stage that builds (creates) the initial dacpac. Plus, I updated all the GitHub Actions to newer versions.

  # Job to build and publish the dacpac
  BuildDacpac:
    # Easier to use Github-hosted runner if updating in GitHub

    runs-on: windows-2019

    # 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@v3.3.0
      # Find msbuild
      - name: Add msbuild to PATH
        uses: microsoft/setup-msbuild@v1.3.1
      # create dacpac using MSBUILD
      - name: Build Database project
        run: |
          msbuild.exe AzureSQLDB.sqlproj /p:Configuration=Release
      # Publish artifact
      - uses: actions/upload-artifact@v3.1.2
        with:
          name: AzureSQLDB
          path: ${{ github.workspace }}/bin/Release/ 

In addition, I renamed the deploy stage and updated all the main GitHub Actions. Which you can see below.

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

    # Steps to deploy the updates to Azure SQL Database
    # To keep this example simple, the Firewall settings in the logical SQL Server allows access from Azure services and resources
    # An alternative is to use the Azure Login GitHub Action https://github.com/marketplace/actions/azure-login
    steps:
      - name: download artifact containing dacpac
        # Downloads Data artifact to dacpac folder
        uses: actions/download-artifact@v3.0.2
        with:
          name: AzureSQLDB
          
      # 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 }}
         

I made the decision not to include the get-keyvault-secrets with this workflow. Due to the fact that it this GitHub Action is now deprecated.

It is now recommended to use the GitHub Action for Azure CLI to get values from Azure Key Vault. However, doing this for a connection string that includes a password can be complex. To keep this workflow simple, I decided to revert to a secret within the repository.

Sql-action v2 GitHub Action update

Which leads me to the end of the deploy job. Where the new version of sql-action is used to deploy the updates from the dacpac file.

      # Installs dacpac
      # For this to work you have a secret specified, default in my repo is blank
      # Requires a secret AZURESQLDB_CONNECTION_STRING which contains connection string to your Azure SQL Database
      - name: Azure SQL Deploy
        uses: Azure/sql-action@v2
        with:
          # The connection string, including authentication information, for the Azure SQL Database.
          connection-string: '${{ secrets.AZURESQLDB_CONNECTION_STRING }}'
          # Path to the dacpac file in the artifact
          path: './AzureSQLDB.dacpac'
          # Action we want it to do, in this case 'Publish' the contents of the dacpac to the database
          action: 'publish'

As you can see, you no longer have to enter the name of the logical SQL Server for the Azure SQL Database. In fact, the all of the syntax has changed in v2 due to significant changes with this version 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‘.

Final words about the Sql-action v2 GitHub Action for Azure SQL Database deployments

I hope this post about using Sql-action v2 GitHub Action for Azure SQL Database deployments raises awareness about the updates for this GitHub Action.

Because at the very least you need to be aware of syntax changes. Even more so now that GitHub is becoming more popular for these types of deployments.

Plus, I hope that my GitHub-AzureSQLDatabase is a good starting point for some of you to start performing CI/CD using GitHub.

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