SQL Server on GitHub Actions

Besides SFML, I’ve also taken on the maintenance of Jobbr, a .NET JobServer developed by some co-workers some time ago. With the help of some more people, we managed to migrate the whole machinery from .NET Framework to .NET 6. At the end of the migration, I went ahead and set up GitHub Actions for all the different components on all three major platforms, which is, where I ran into the problem of running integration tests against a Microsoft SQL Server database.

GitHub Actions Service Option

“We’re living in a containerized world!”, I thought and while true, GitHub Actions somehow haven’t exactly arrived there yet. While they offer some nice services container option on a job level, turns out this is only available for Linux runners. So my first attempt, which looked something like the following, worked, but wasn’t usable on Windows or macOS.

jobs:
  build:
    name: ${{ matrix.platform.name }} ${{ matrix.dotnet.name }}
    runs-on: ${{ matrix.platform.os }}
    strategy:
      fail-fast: false
      matrix:
        platform:
        - { name: Windows VS2022, os: windows-2022 }
        - { name: Linux, os: ubuntu-20.04 }
        - { name: MacOS, os: macos-12 }
        dotnet:
        - { name: .NET 6, version: '6.0.x' }
        - { name: .NET 7, version: '7.0.x' }
    
    services:
      sqlserver:
        image: mcr.microsoft.com/mssql/server:2019-latest
        ports:
          - 1433:1433
        env:
          ACCEPT_EULA: Y
          SA_PASSWORD: 1StrongPwd!!

    steps:
      ...

If you just need/want to build on Linux, then this is totally an option you should consider. My own search however continued.

Running Docker in GitHub Actions

The Linux Option

If we can’t make use of the services section, then lets just run Docker directly on the runners. This is in theory quite simple and again works well on Linux runners, with a step like the following:

- name: Run SQL Server
  run: docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=1StrongPwd!!" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

The Windows Option

Turns out, that while Windows runners have Docker installed, they are set to Windows Docker images. One might assume since SQL Server is a Microsoft product, that they would still provide such images and their repository and documentation do mention them, but when you check DockerHub, they don’t exist anymore. Neither are they listed on their own artifact storage. I did try some third-party image, but it failed with an architecture mismatch and the images themselves were also rather old.

So I decided to directly install SQL Server, after all this is a Windows machine and it should run SQL Server just fine. To make my life easier, I use Chocolately as package manager, so I don’t have to somehow manually download the massive setup file.

- name: Install SQL Server on Windows
  if: runner.os == 'Windows'
  run: choco install sql-server-2019 -y --params="'/Q /SUPPRESSPRIVACYSTATEMENTNOTICE /IACCEPTSQLSERVERLICENSETERMS /ACTION=Install /SkipRules=RebootRequiredCheck /FEATURES=SQLENGINE /SECURITYMODE=SQL /TCPENABLED=1 /SAPWD=1StrongPwd!!'"

It’s great that the specific parameters for the setup are nicely documented, well mostly. After a successful initial run, I started to get failures on the install, with the information that the GitHub Actions runner requires a reboot. Searching a bit online led me to the undocumented option /SkipRules=RebootRequiredCheck, unfortunately that didn’t actually help, so maybe it’s rightfully undocumented? Either way, while the installation works in most cases, if you’re unlucky and get a runner that requires a reboot, you won’t be able to install SQL Server and have to retry again at a later point.

The macOS Option

The macOS runners don’t have Docker installed at all and due to the whole Docker licensing fiasco, it needs a bit of a workaround. Luckily for me, someone else had this already documented pretty well.

Instead of Docker itself, we can use Colima which is a container runtime, that lets you run Docker without running into licensing issues. Plus it’s relatively lightweight, so great for installing as part of a CI run. All you need is a simple brew call and start up of Colima.

- name: Install Docker on MacOS
  if: runner.os == 'macOS'
  run: |
    brew install docker
    colima start

Then we can actually reuse the Docker command from the Linux setup to start MSSQL.

- name: Run SQL Server
  run: docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=1StrongPwd!!" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

Or so I thought. Unfortunately, I ran into issue when trying to connect from the .NET tests and after some fiddling around, I decided that it’s not worth to fix this for now. If anyone has any tips on how to solve the follow error, let me know or create a PR!

Error Message:
   Initialization method Jobbr.Storage.MsSql.Tests.MsSqlStorageProviderTests.SetupDatabaseInstance threw exception.
   System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - No such file or directory)
   ---> System.ComponentModel.Win32Exception: No such file or directory.
  Stack Trace:
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at ServiceStack.OrmLite.OrmLiteConnection.Open() in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteConnection.cs:line 109
   at Jobbr.Storage.MsSql.Tests.MsSqlStorageProviderTests.DropTablesIfExists() in /Users/runner/work/jobbr-storage-mssql/jobbr-storage-mssql/source/Jobbr.Storage.MsSql.Tests/MsSqlStorageProviderTests.cs:line 40
   at Jobbr.Storage.MsSql.Tests.MsSqlStorageProviderTests.SetupDatabaseInstance() in /Users/runner/work/jobbr-storage-mssql/jobbr-storage-mssql/source/Jobbr.Storage.MsSql.Tests/MsSqlStorageProviderTests.cs:line 26

I especially like the part, where a Win32Exception is thrown on macOS. 😄

Putting It All Together

You can see the final result in action on the jobbr-storage-mssql repository, but for completeness sake, I’ve also included the full GitHub Actions script below.

name: CI

on: [push, pull_request]

env:
  DOTNET_CLI_TELEMETRY_OPTOUT: 1
  IGNORE_NORMALISATION_GIT_HEAD_MOVE: 1
  GITHUBACTIONS: "True"

jobs:
  build:
    name: ${{ matrix.platform.name }} ${{ matrix.dotnet.name }}
    runs-on: ${{ matrix.platform.os }}
    strategy:
      fail-fast: false
      matrix:
        platform:
        - { name: Windows VS2022, os: windows-2022 }
        - { name: Linux, os: ubuntu-20.04 }
        - { name: MacOS, os: macos-12 }
        dotnet:
        - { name: .NET 6, version: '6.0.x' }
        - { name: .NET 7, version: '7.0.x' }

    steps:
      - uses: actions/checkout@v3
        with:
          fetch-depth: 0
          submodules: recursive
      - name: Setup .NET ${{ matrix.dotnet.version }} SDK
        id: setup-dotnet
        uses: actions/setup-dotnet@v3
        with:
          dotnet-version: ${{ matrix.dotnet.version }}
      - name: Enforce SDK Version
        run: dotnet new globaljson --sdk-version ${{ steps.setup-dotnet.outputs.dotnet-version }} --force
      - name: Verify SDK Installation
        run: dotnet --info

      - name: Install GitVersion
        uses: gittools/actions/gitversion/setup@v0
        with:
          versionSpec: '5.x'
      - name: Determine Version
        uses: gittools/actions/gitversion/execute@v0
        with:
          useConfigFile: true
          updateAssemblyInfo: true
          additionalArguments: '/l console'

      - name: Install Dependencies
        run: dotnet restore source/Jobbr.Storage.MsSql.sln
      - name: Build
        run: dotnet build --configuration Release --no-restore source/Jobbr.Storage.MsSql.sln

      - name: Install Docker on MacOS
        if: runner.os == 'macOS'
        run: |
          brew install docker
          colima start
      - name: Install SQL Server on Linux or MacOS
        if: runner.os != 'Windows'
        run: docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=1StrongPwd!!" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest
      - name: Install SQL Server on Windows
        if: runner.os == 'Windows'
        run: choco install sql-server-2019 -y --params="'/Q /SUPPRESSPRIVACYSTATEMENTNOTICE /IACCEPTSQLSERVERLICENSETERMS /ACTION=Install /SkipRules=RebootRequiredCheck /FEATURES=SQLENGINE /SECURITYMODE=SQL /TCPENABLED=1 /SAPWD=1StrongPwd!!'"
      - name: Test
        if: runner.os != 'macOS' # Can't get the tests to fully connect to the SQL Server at the moment
        run: dotnet test --no-restore --verbosity normal source/Jobbr.Storage.MsSql.sln

      - name: NuGet Pack
        run: nuget pack source/Jobbr.Storage.MsSql.nuspec -version "${{ env.GitVersion_SemVer }}" -prop "target=Release"

I hope this summary can help some other people, like the other blog posts have helped me to put this all together.