CI/CD for SQL Server 2022 using GitHub Actions

Reading Time: 6 minutes

In this post I want to cover performing CI/CD for SQL Server 2022 using GitHub Actions. For a couple of reasons.

First one is due to the fact that last week I was answering a query on the well known SQLHelp hashtag about deploying updates to SQL Server using GitHub Actions. That is when it dawned on me that I had never shared a repository to solely perform CI/CD for SQL Server using GitHub Actions.

Second reason is due to the fact that I wanted to show how to create an SDK-style database project for SQL Server 2022. By Using the ‘Microsoft.Build.Sql‘ .NET SDK for database projects. Which you can use to perform state-based deployments.

Since I covered how I had created an SDK-style database project for my homemade serverless SQL Pool Database Project.

By the end of this post you will know how to create an SDK-style database project for SQL Server 2022. Plus, how to deploy that database project using GitHub Actions.

Along the way I share plenty of links.

I have made a repository available to go along with this post in GitHub. Which you can download and use as a template to perform CI/CD. It is called GitHub-SQLServer2022-SDK-DBProject.

You can download it and do whatever you want with it. All I ask is that if it proves to be useful please give it a star in GitHub.

In order to do the below I configured a self-hosted GitHub runner on my laptop. Which has the latest versions of .NET framework, SqlPackage and Visual Studio 2022 installed.

Creating SQL Server 2022 SDK-style database project

First I created a new folder on my laptop and initialized it with an empty Git repository. You can do this a variety of ways. Including the below method in the source control section in Azure Data Studio.

I then added a new GitHub repository as a remote. Again, there are many different ways to do this including the below in Azure Data Studio.

To create a SQL Server 2022 SDK-style database project I ran the below code from the command line.

dotnet new sqlproj -n "GA-SQLServer2022-SDK-DBProject" -tp "sql160"

Which created the below two files for me:

• GA-SQLServer2022-SDK-DBProject.sqlproj
• README.md

For those interested, I moved the original contents of the readme file created to a wiki page in GitHub.

In reality, I could have left out the -n option. Which would have created a project with the same name as the current folder. You can find out more about the commands in detail on the ‘Microsoft.Build.Sql.Templates‘ page in GitHub.

Anyway, as you can see below the sqlproj file that gets created is a lot smaller than the usual sqlproj files.

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="0.1.9-preview" />
  <PropertyGroup>
    <Name>GA_SQLServer2022_SDK_DBProject</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
  </PropertyGroup>
</Project>

Since I know the latest .NET framework is installed on my laptop I add the below line underneath the collation.

<TargetFrameworkVersion>v4.8</TargetFrameworkVersion>

After saving the changes I then committed the updates in Azure Data Studio.

Once done I clicked on the blue ‘Publish Branch’ button. After it had finished, I went to the GitHub repository and confirmed the updates were there.

I then went back to the repository and added three more sql files. Which are the three sql scripts that I use in my GitHub-AzureSQLDatabase repository to create tables.

I then committed the changes and synchronized the repository with GitHub.

CI/CD for SQL Server 2022 using GitHub Actions

Once the database project was in GitHub I setup my laptop to be a self-hosted runner for the repository. By going to the ‘Settings’ tab, selecting ‘Actions’ and then ‘Runners’. As below.

In addition, I added the below two secrets:

• SQLDB_SERVER – the name of the SQL Server.
• SQLDB_CONNECTION_STRING – The connection string to the SQL Server database

I used the below connection string format due to a known error after upgrading SNAC applications:

Server=localhost,1451;User ID=*Redacted*;Password=*Redacted*;Initial Catalog=TestDB;Trust Server Certificate=true

Afterwards, I went to the ‘Actions’ tab to set up a workflow.

I first wanted to test building(creating) a dacpac with this project. So, I first tested running the msbuild command which worked fine.

  build:

    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@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 GA-SQLServer2022-SDK-DBProject.sqlproj /p:Configuration=Release
      # Publish artifact
      - uses: actions/upload-artifact@v3.1.2
        with:
          name: SQLDB
          path: ${{ github.workspace }}/bin/Release/ 

However, since this was an SDK-style project I wanted to test making it more streamlined with the dotnet build command.

  # Job to build and publish the dacpac
  build:
    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@v3.3.0
      # Build dacpac using dotnet build
      - name: Build Database project
        run: |
          dotnet build
      # Publish artifact
      - uses: actions/upload-artifact@v3.1.2
        with:
          name: SQLDB
          path: ${{ github.workspace }}/bin/Debug/ 

Which worked great and means that potentially I no longer have to find the latest version of msbuild. So, I was now ready to deploy the dacpac. In order to do that I added the below code.

# Deploy dacpac job
  DeploySQL2022DB:
    # Set the dependency for the build job
    needs: build
    # Easier to use GitHub-hosted runner if updating in GitHub
    runs-on: self-hosted

    # Steps to deploy the updates to SQL Server 2022 Database
    steps:
      - name: download artifact containing dacpac
        # Downloads Data artifact to dacpac folder
        uses: actions/download-artifact@v3.0.2
        with:
          name: SQLDB
                    
      # Installs dacpac
      # For this to work you MUST have two secrets specified, defaults in my repo are blank
      # One called SQLDB_SERVER which contains your logical SQL Server name
      # Requires a secret SQLDB_CONNECTION_STRING which contains connection string to your SQL Server 2022 database
      - name: Azure SQL Deploy
        uses: Azure/sql-action@v1.3
        with:
          # The logical SQL Server name
          server-name: '${{ secrets.SQLDB_SERVER }}'
          # The connection string, including authentication information, for the Azure SQL Database.
          connection-string: '${{ secrets.SQLDB_CONNECTION_STRING }}'
          # Name of the dacpac file in the artifact
          dacpac-package: 'GA-SQLServer2022-SDK-DBProject.dacpac'

One key point I want to highlight is that I have used the sql-action v1.3 GitHub Action to do deploy to a local Docker container. Due to the fact that I appear to be experiencing issues with using sql-action v2 for local deployments. Which I have raised a GitHub issue about.

Anyway, once it had completed I checked and my three tables were there.

In addition, I tested changing a column and that ran fine as well.

More GitHub

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

So, if you are attending the SQLBits on the Saturday and staying for the raffle at the end (which I assume is still taking place) feel free to join us. Our session is at 16:00 GMT.

Final words about CI/CD for SQL Server 2022 using GitHub Actions

I hope this post about performing CI/CD for SQL Server 2022 using GitHub Actions is of interest to some of you. Especially the part about using sql-action v1.3 if you encounter an unusual issue.

Because it is the first SQL Server repository I shared and I wanted to make sure it was unique. You can test and tweak my shared repository how you see fit.

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