Deploying to Azure SQL Database using GitHub Actions

Reading Time: 7 minutes

After my last post I wanted to test deploying to Azure SQL Database using GitHub Actions. To check that it all runs smoothly.

By the end of this post, you will have some ideas about how you can deploy Azure SQL Databases using GitHub Actions. Both for a basic test and more complex deployments.

In my last post I showed you how to deploy a free monitoring framework called SQLWATCH to on-premises versions of SQL Server. I thought I would test using the same Azure SQL Deploy Action in GitHub Actions for Azure SQL database deployments.

If following along with an Azure SQL Database

Before I started doing this, I deployed created a blank Azure SQL Database. If you are going to follow this post, I recommend you create all of this in a separate Resource Group in Azure. So that you can simply delete the Resource Group once you have finished.

Another thing you will need if you are going to follow this post is the code for your database project in GitHub. If you already have a database project in GitHub, you are good to go.

Otherwise, to set this up you need to have Git installed locally on your computer. If you have Git installed you can read the Microsoft guide on how to setup Git in Azure Data Studio here. I also gave some pointers on how to import a database into a database project in Azure Data Studio in another post.

GitHub Actions template for single Azure SQL Database deployment

In addition, I have made public a GitHub repository for a single Azure SQL Database deployment called GitHub-AzureSQLDatabase.

You can use it to follow along with this post. It is yours to either fork or import. However, it is slightly different from the example shown below. With the release job being called DeployAzureSQLDB instead.

To use this template for deployments you ideally need to have an empty Azure SQL Database. In addition, the firewall settings for the logical SQL Server has to be set to allow Azure services and resources to access the server.

If you do not want to use this firewall setting you can add the Azure Login GitHub Action to the workflow instead.

If you do use your own version make sure you read the README file first in the repository to add the right secrets. Because the workflow is set to start if any changes are made to the main branch.

Setup GitHub Actions for deployment

For those of you who did not read my last post here, here’s a quick guide to setup GitHub Actions. To setup GitHub Actions you need to create a workflow. You can think of a GitHub Action workflow the same as an Azure DevOps pipeline.

Usually, I would just create a yaml file in the right location. However, since some of you will be new to GitHub Actions, I thought I would direct you where to go in GitHub to view them first.

If you click on the ‘Actions’ section in GitHub as below you will be able to scroll down and select various workflow templates. However, since there’s no template there for SQL Server simply click on the ‘set up a workflow yourself’ option.

If you are following this guide, feel free to delete all the code from the word ‘jobs’ onwards if you intend to copy and paste the below examples.

Basic Azure SQL Database deployment method in GitHub Actions

When you first want to create a dacpac and deploy to a single Azure SQL Database for the first time you want to keep it basic. I thought this I would show a basic method for deploying to a single Azure SQL Database first.

I used a database project that was already configured for Azure SQL Database. So that I didn’t have to make any changes to the sqlproj file.

In addition, I used a GitHub-hosted runner in Azure to do the build this time. Doing it this way saved me having to use my computer as a self-hosted runner. You can read more about GitHub-hosted runners here.

Doing it this way also meant the code I used for the build was a bit simpler than before. I called the msbuild executable using PowerShell.

Because you can run executables installed on the GitHub-hosted runners using PowerShell if you know the location of them. Of course, if you install other applications on a self-hosted runner, you can call them directly using PowerShell as well.

jobs:
  # deploy job
  deploy:
    # Selecting the windows-latest GitHub-hosted runner
    runs-on: windows-latest

    steps:
      # Checks-out your repository under $GITHUB_WORKSPACE, so your job can access it
      - uses: actions/checkout@v2

      # create dacpac using MSBUILD executable
      - name: create dacpac
        run: |
          & 'C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\Bin\msbuild.exe' kcdemodbproject.sqlproj /p:Configuration=Release
      # No need to publish an artifact afterwards as deploy in same job

After checking that the build had completed, I then added the code for the release at the end of the same job.

You cannot automatically select your Azure subscription like you can if you have your own Azure DevOps organization. With this in mind, I checked the Microsoft instructions for using the Deploy Action here to create the Azure Credential for access.

After running the ‘az login’ I created the Azure Credential and added it as a secret within GitHub. I then added the below code to the same deploy job. For the record, I put in the name of ‘MYTESTSERVER’ as an alias in capitals here to remind you all to use your own server’s name.

      # Login using Azure credentials
      - uses: azure/login@v1
        with:
          creds: ${{ secrets.AZURE_CREDENTIALS }}
      # Use the Azure SQL Deploy Action
      - name: Azure SQL Deploy
        uses: Azure/sql-action@v1
        with:
          # Name of the SQL Server
          server-name: 'MYTESTSERVER.database.windows.net'
          # The connection string, including authentication information for the Azure SQL Database 
          connection-string: '${{ secrets.AZURE_SQL_CONNECTION_STRING }}'
          # Path to the dacpac file
          dacpac-package: ${{ github.workspace }}\bin\Release\kcdemodbproject.dacpac
          # Additional arguments 
          arguments: '/p:IncludeCompositeObjects=true'   

Checking Azure SQL Database after deployment

After the workflow had finished, I removed the database. So that I could test that running it again recreated the Azure SQL database. It worked as expected which I confirmed using Azure Data Studio.

More complex Azure SQL Database deployment using GitHub Actions

You will probably want to deploy to multiple databases. For example, a separate Azure SQL Database for unit testing.

You have a lot of options on how to do this.

For example, those of you who have worked with Azure DevOps in the past might want to translate the Azure Pipeline stages you tend to user into separate GitHub Action jobs. For instance, separate jobs for deploying to integration, staging and production Azure SQL Databases.

In the below demo I have split up the build and release into separate jobs. To give you ideas on how you can split up the code logically to deploy to Azure SQL Database.

Build job in GitHub Actions

It was simpler to build the dacpac for this deployment compared to how I did it for SQLWATCH. Because this database project is simpler. You can read about how I did it for SQLWATCH in detail here.

Below you can see just how simple the code for this job is compared to how I did it for SQLWATCH.

  build:
    # 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

      # create dacpac using MSBUILD executable
      - name: create dacpac
        run: |
          & 'C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\Bin\msbuild.exe' kcdemodbproject.sqlproj /p:Configuration=Release
      # Publish an artifact
      - uses: actions/upload-artifact@v2
        with:
          name: AzureSQLDB
          path: ${{ github.workspace }}/bin/Release/  

After it had finished, I checked that an artifact had been created. Afterwards I downloaded the artifact and checked the dacpac was in there. I must admit I do prefer drilling down into the artifact within Azure DevOps.

Release job in GitHub Actions

I used the same secrets I had created for the basic method at the start of this post for the release stage.

release:
    # Set the dependency for the build job
    needs: build
    # 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 AzureSQLDB artifact
        # Downloads artifact to dacpac folder
        uses: actions/download-artifact@v2.0.6
        with:
          name: AzureSQLDB
      # Login using Azure credentials
      - uses: azure/login@v1
        with:
          creds: ${{ secrets.AZURE_CREDENTIALS }}
      - name: Azure SQL Deploy
        uses: Azure/sql-action@v1.2
        with:
          # Name of the SQL Server
          server-name: 'MYTESTSERVER.database.windows.net'
          # The connection string, including authentication information for the Azure SQL Database 
          connection-string: '${{ secrets.AZURE_SQL_CONNECTION_STRING }}'
          # Path to the Azure SQL database dacpac file
          dacpac-package: 'kcdemodbproject.dacpac'
          # Additional arguments 
          arguments: '/p:IncludeCompositeObjects=true'   

When you are deploying to Azure SQL Database it makes sense for you to have to enter the server’s name separately as well. Because the action must configure the firewall on the logical SQL Server to allow the GitHub-hosted runner to work properly.

It doesn’t make sense to have to enter it twice when using it to deploy to on-premises versions of SQL Server though. However, due to the name of the action I suspect it was designed to only be used for Azure SQL database deployments; and the fact you can use it for on-premises instances is just a bonus.

GitHub Action question for you readers

Maybe the Azure SQL Deploy Action can be changed so that if no server name is entered it deploys to an on-premises installation of SQL Server instead of throwing an error. Using only the server name specified in the connect string.

However, is it worth the effort of doing this considering the amount of cloud migrations happening? Do you think a separate GitHub Action should be created specifically for on-premises installations instead?

I would love to hear other people’s views on this before giving this Action my own feedback.

Alternatives for Azure SQL Database

In reality, there are alternatives to doing the above.

For example, you can test using other GitHub Actions for deployments. In addition, you can test calling sqlpackage directly using PowerShell instead.

Tidy up

If you have followed this example, make sure you tidy up afterwards. If you created the Azure SQL database in its own Resource Group, this is a reminder to delete it.

Final words about deploying to Azure SQL Database using GitHub Actions

I hope my tests for deploying to Azure SQL Database using GitHub Actions has given some of you ideas. For those of you who have not used GitHub Actions before I hope this served as a good introduction.

If you have any comments or questions, feel free to reach out to me.