Deploy from one source to multiple SQL Server database types using GitHub Actions

Reading Time: 6 minutes

In this post I want to share how to deploy from one source to multiple SQL Server database types using GitHub Actions. Because I did a demo of it at Data Saturday Redmond last weekend.

By the end of this post, you will know more about how to do this using GitHub Actions. If you are used to Azure DevOps, you will find this an interesting comparison.

Previously I did a post about how you can do this using Azure DevOps. You can read that post in detail here.

Later in this post I also mention an older post about using GitHub Actions for Azure SQL Database deployments couple of times, which you can open here. So, it’s worth keeping that open.

Quick recap

Just a quick recap. GitHub Actions is the CI/CD mechanism that has been introduced into GitHub. It purely yaml based. Azure Pipeline users will find some of the concepts of it similar.

You can find out more about GitHub Actions in the GitHub docs site here. In addition, you can also read some of my previous GitHub Actions posts here.

Demo

To show how this works using GitHub Actions I thought I would use the demo I did for Data Saturday Redmond last weekend. Where Sander Stad and I presented ‘GitHub DevOps Duet’.

In GitHub Actions this is called a workflow. Everybody got to see the below workflow run in real time.

Just like I did in Azure DevOps, I deployed to various SQL Server related database types. All completely live.

Before I go any further, I just want to explain a couple of things about the above.

First of all, you will notice that some jobs start with an underscore. I did this because GitHub Actions appears to show the workflow diagram alphabetically and I wanted it to make sense.

Secondly, I have highlighted some icons in the bottom right-hand corner. Due to the fact I want to highlight the fact that by default GitHub Actions shows your entire workflow in the work area.

However, you can zoom in and out of the diagram. In addition, you can also pan around once you have zoomed in. I really like this feature.

SQL Server database types

To give the above pipeline a bit more context the below types of SQL Server databases were updated after being unit tested:

• Three SQL Server 2019 instances in three Docker containers. Representing Integration, Staging and Production environments.
• At the same time the Git repository in GitHub would sync with an Azure Repos Git repository. Which can start an Azure DevOps pipeline to update another database.
• An Azure SQL Database.
• Finally, an Azure Synapse Analytics Dedicated SQL Pool was also updated.

It was able to do this by creating four different dacpacs as you can see highlighted above. When I click on the number of artifacts the screen scrolls down to show all four artifacts as below:

I wanted to show it was possible to update various database types from one single database project in your GitHub repository. To save the hassle of managing a different database project for each type.

It also shows how you can perform unit tests on a dedicated SQL Pool. As you can see above the whole workflow took just over seven minutes.

How?

I was able to do this fairly quickly. Because I had already done it before in Azure DevOps, which you can read about in a previous post here. So all I had to do was transfer the logic over for it to work in GitHub Actions by doing the below:

1. Created a yaml file in the repository. Which has to be in the ‘.github/workflows/’ folder. To do this quickly you can follow my steps in a previous post here. In fact, I mention this post again later in this post.
2. Afterwards, I created a job called ‘BuildAzureSQLDB’ in the yaml file.
3. In this job I wrote some an inline PowerShell which creates a staging directory on the runner and copies the database project in the repository to the staging directory.
4. Replaces the DSP line in the copy of the sqlproj file in the staging area. Setting it to be for an Azure SQL Database instead.
5. Afterwards it then built and published the dacpac ready for deployment to an Azure SQL Database.
6. I then created a job called ‘BuildAzureSQLPool’, repeating the above steps to create a dacpac for use with an Azure Synapse Analytics dedicated SQL Pool.
7. Once I had created those two jobs, I then created two other jobs to do the deployments. To save repeating myself, I have shared before how you can deploy a dacpac to an Azure SQL Database using GitHub Actions in a previous post. You can view that in detail here.

Here’s a sample of how the yaml for the BuildAzureSQLDB job looks in GitHub Actions.

# Build dacpac for Azure SQL Database
  BuildAzureSQLDB:
    # Set the dependency for the build job
    needs: _Integration
    # The type of runner that the job will run on
    runs-on: self-hosted
    # 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
      # Sync Git repository
      - name: Copy files and replace DSP
        run: |
            # copy files
            md AzureSQLDBstaging
            Copy-Item -Path Data\* -Destination AzureSQLDBstaging -Recurse
            # replace DSP
            cd AzureSQLDBstaging
            $Old_DSP = 'Microsoft.Data.Tools.Schema.Sql.Sql140DatabaseSchemaProvider'
            $New_DSP = 'Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider'
            rename-item Data.sqlproj AzureSQLDB.sqlproj
            rename-item Data.publish.xml AzureSQLDB.publish.xml
            (Get-Content "AzureSQLDB.sqlproj") -replace $Old_DSP, $New_DSP  | Set-Content "AzureSQLDB.sqlproj"
 
      # create dacpac using MSBUILD
      - name: Build Data project
        run: |
          cd AzureSQLDBstaging
          msbuild.exe AzureSQLDB.sqlproj /p:Configuration=Release  
      # Publish Unitesting-Data artifact
      - uses: actions/upload-artifact@v2
        with:
          name: AzureSQLDB
          path: ${{ github.workspace }}/AzureSQLDBstaging/bin/Release/ 

To convert it to deploy a dacpac to an Azure Synapse Analytics Dedicated SQL Pool instead simply replace the below line:

$New_DSP = 'Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider'

With the below:

$New_DSP = 'Microsoft.Data.Tools.Schema.Sql.SqlDwDatabaseSchemaProvider'

Advantages

In reality, there are some advantages to using this method.

For instance, you can cater for older versions of SQL Server that do not work well with the SQL Azure V12 database schema provider instead of SQL Server 2019. By changing the DSP line to the relevant version. I provided a tip on how to quickly find the correct syntax for the relevant version here.

In addition, because the build files for the Azure SQL Database deployments are done behind the scenes you don’t have to manage multiple database projects for each database type in your repository.

Doing this saves you having the burden of managing duplicate copies of the schema. In addition, you keep your database schemas consistent because this greatly reduces the chance of drift between the different databases.

Use case

I think its strongest use case is in the workplace. Because you can use this method whilst you start migrating SQL Server 2019 databases to Azure and you want to keep the same database schema. Or any other versions of SQL Server of course.

Especially since mainstream support for SQL Server 2016 ends on July 13th 2021, as you can see here.

For example, during migration you could migrate your database in a staging environment to an Azure SQL Database whilst keeping your Production database on-premises. Once done you can simply change your pipeline and allow them both to be updated from the same source.

Additional logic

Now I must stress again here that this demo is only to give you a basic introduction to the method. You might want to experiment with the order of deployments to reduce the risk of deployments working for SQL Server databases but not Dedicated SQL Pools.

In addition, you can put some additional logic in place to cater for things like unsupported features in Azure SQL Database and Dedicated SQL Pools.

Final word

I hope this post about how to deploy from one source to multiple SQL Server database types using GitHub Actions is useful.

Because I wanted to show how this can be done. In addition, I wanted to highlight how you can transfer logic over if you have used Azure Pipelines before. GitHub Actions can be very useful if your Git repository is already in GitHub.

Of course, if you have any comments or queries about this post feel free to reach out to me. Alternatively, you can vote to view one of the GitHub sessions we have submitted to the GroupBy conference where we demo this pipeline by clicking here.