Github Actions 2.0–Let’s do something a little more involved

Table Of Content

This is a part 2 in a 3 part blog series about GitHub Actions.

Part 1: Github Actions 2.0 Is Here!!! – A first look and simple walkthrough of GitHub Actions 2.0 for CI/CD

Part 2: Git Actions 2.0-Let’s do something a little more involved – A deeper dive using GitHub Actions 2.0 for a more involved CI/CD pipeline including provisioining infrastructure using IaC and deploy schema changes in a SqlServer Database using a dacpac.

Part 3: Writing My First Custom Github Action – Walkthrough writing a custom GitHub Action.

Introduction

In my last blog article, I used github actions to build a .net core web app AND deploy the web app to Azure. It turned out to be super simple. So this time, I figured I’d do something a little more complex.

My .net core app has a sql server backend. The database schema is encapsulated in a Sql Server Data Tools (SSDT) project. I also have IaC for provisioning and configuring the azure app service and azure sql. A “real world-ish” pipeline would be

  1. Build .net core app/run unit tests/package everything up for deployment
  2. Build database project
  3. Package IaC files up so its ready for provisioning/configuration
  4. Provision infrastructure in Azure
  5. Deploy Web app into Azure App Service
  6. Deploy new DB Schema

I already have a workflow that builds/tests/packages up my .net core app using an ubuntu vm. And then, using another ubuntu vm, it deploys the webapp into Azure app service.  To do all the other steps, I would probably have to build some Actions like, deploying a database schema using a dacpac. Or maybe even an action that would build my database project that’s part of a visual studio solution.

Building Database Project

The first thing I needed to do was build my database project. Since a database project is a full blown Visual Studio Enterprise project type, I knew I would need a windows vm. Looking at the software installed on a github windows vm (https://help.github.com/en/articles/software-in-virtual-environments-for-github-actions#windows-server-2019), I saw that VS Enterprise is already installed. Perfect. All I would need to do is figure out how to do a Visual Studio build of a .sln file. A quick google search showed me I can easily build a .sln file by calling MSBuild.exe and passing in the path to the solution file.

Cool. Now… is MSBuild.exe installed? Surely it is. If Visual Studio 2019 Enterprise is installed on there, I bet MSBuild is part of it. But where is it installed?

First I tried running MSBuild.exe from the command line so I created a job that did this:

jobs:
  testStuffOut:
    runs-on: windows-latest
    steps:
    - name: see if msbuild is installed
      run: MSBuild.exe
workflow.yml

And when I ran it, this is what I saw

image

Hmmm… ok. MSBuild.exe was not just part of the path. But… surely it’s installed with Visual Studio Enterprise right? I have VSE installed on my desktop so I browsed to where it is installed on my desktop: C:\Program Files (x86)\Microsoft Visual Studio searched for MSBuild.exe and found it here: C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\Bin\MSBuild.exe

Ok… does that exist on the build vm? I knew from the documentation that VSE is installed here on the github windows vm: C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise

How could I see if that MSBuild exists on the github VM? I know!!! I’ll just write my step to do a ls of that directory!

Tweaked my build step a bit

jobs:
  testStuffOut:
    runs-on: windows-latest
    steps:
    - name: see if msbuild is installed
      run: |
        ls "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\\MSBuild\Current\Bin
workflow.yml

Checked it in. This triggered my workflow and the result was

image

Bam! There it is. MSBuild.exe. Cool. So I know it exists on the github windows vm. Next, I created steps that downloads my source repo, and then calls msbuild.exe on my solution (I now know the path to MSBuild.exe on the github windows vm). I tweaked my workflow yml so now my build database job looked like this

  # build database schema, build artifact is the dacpac
  buildDatabase:
    runs-on: windows-latest
    steps:
    # checkout code from repo
    - name: checkout repo
      uses: actions/checkout@v1
    # use msbuild to build VS solution which has the SSDT project
    - name: build solution
      run: |
        echo "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\bin\MSBuild.exe MercuryHealthCore.sln"
        "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\Bin\MSBuild.exe" MercuryHealthCore.sln
    

Checked it in, the output was

image

Holy moly. Wait, did that actually work? It looks like it. And looking closer, it looks like the build created my dacpac for me and stuck it at D:\a\MercuryHealthCore\MercuryHealthCore\MercuryHealthDB\bin\Debug\MercuryHealthDG.dacpac.

Cool, just to make sure that my dacpac was really created and sitting in that directory, I added another step to ls that directory. I decided to be extra and instead of hardcoding the path, I used the default environment variable.

  # build database schema, build artifact is the dacpac
  buildDatabase:
    runs-on: windows-latest
    steps:
    # checkout code from repo
    - name: checkout repo
      uses: actions/checkout@v1
    # use msbuild to build VS solution which has the SSDT project
    - name: build solution
      run: |
        echo "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\bin\MSBuild.exe MercuryHealthCore.sln"
        "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\Bin\MSBuild.exe" MercuryHealthCore.sln
    # look for dacpac
    - name: find dacpac
      run: |
        echo "ls d:\a\MercuryHealthCore\MercuryHealthCore\MercuryHealthDB\bin\Debug\"
        ls "${GITHUB_WORKSPACE}\MercuryHealthDB\bin\Debug"
workflow.yml

Checking it in… the output was

image

What the freak????? I swear that was the way you’re supposed to call default environmental variables. Looking at the documentation a second time verifies that my syntax is correct?!?!?!?! So weird… after a little bit of thought, it dawned on me. Wait… i’m on a windows vm. Not a linux flavored vm. How do you access windows environment variables from the command line? A quick google search and…

%ENVIRONMENT_NAME%

Wow, that’s ugly. Hmmm is it really that simple? Let’s try it

  # build database schema, build artifact is the dacpac
  buildDatabase:
    runs-on: windows-latest
    steps:
    # checkout code from repo
    - name: checkout repo
      uses: actions/checkout@v1
    # use msbuild to build VS solution which has the SSDT project
    - name: build solution
      run: |
        echo "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\bin\MSBuild.exe MercuryHealthCore.sln"
        "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\Bin\MSBuild.exe" MercuryHealthCore.sln
    # look for dacpac
    - name: find dacpac
      run: |
        echo "ls d:\a\MercuryHealthCore\MercuryHealthCore\MercuryHealthDB\bin\Debug\"
        ls "%GITHUB_WORKSPACE%\MercuryHealthDB\bin\Debug"
workflow.yml

Checked it in and my output was

image

Sweet!!! There is my dacpac. Cool, now all I needed to do is upload that as a build artifact. And I know how to do that! I did that last time already. So I added a step to upload the build artifacts of my database project

  # build database schema, build artifact is the dacpac
  buildDatabase:
    runs-on: windows-latest
    steps:
    # checkout code from repo
    - name: checkout repo
      uses: actions/checkout@v1
    # use msbuild to build VS solution which has the SSDT project
    - name: build solution
      run: |
        echo "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\bin\MSBuild.exe MercuryHealthCore.sln"
        "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\Bin\MSBuild.exe" MercuryHealthCore.sln
    # look for dacpac
    - name: find dacpac
      run: |
        echo "ls d:\a\MercuryHealthCore\MercuryHealthCore\MercuryHealthDB\bin\Debug\"
        ls "%GITHUB_WORKSPACE%\MercuryHealthDB\bin\Debug"
    # publish build artifact (dacpac) back to github
    - name: publish build artifacts back to GitHub
      uses: actions/upload-artifact@master
      with:
        name: db
        path: MercuryHealthDB\bin\Debug
workflow.yml

And the workflow output is

image

Bam! I just built a SSDT database project using github actions and a github windows vm and then uploaded the dacpac as a build artifact and this all took maybe 10 minutes? Crazy! It can’t be that easy! But…. it is.

Deploying a DB Schema Using a dacpac

Next, I needed to take that dacpac we just created and use that to deploy my db schema. I knew from previous experience you can do a dacpac deploy by calling sqlpackage.exe. Again, I figured I would need to build an Action but thought, hey, why not see if sqlpackage.exe is already installed. Looking through the list of installed software, I did not see sqlpackage.exe. However, is it part of VSE? Searching on my local desktop under the VSE folder, I see that it’s actually there at C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150

Whoa. If this is installed on the windows vm as well, this makes things super easy. To see if sqlpackage.exe exists on the github windows vm, I added a job to deploy my database schema and for the first step, I ls the directory C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150

# deploy new database schema using dacpac
deployDB:
  needs: buildDatabase
  runs-on: windows-latest
  steps:
    - name: see if sqlpackage.exe exists
      run: ls "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150"
workflow.yml

I wanted to make sure the database project was built first so I made the deployDB job be dependent on the buildDatabase job by adding in line 134. Checking this in, the output was

image

Wow. There it is. sqlpackage.exe is on the github vms and I know where it is. This is gonnabe so easy. All I need to do is have a step that will download the db artifact down, then another step that will call sqlpackage.exe, pass it the path to the dacpac and then a bunch of other command line arguments including the connection string to the database. So I added the connection string to the database as a secret in this project named DATABASE_CONNECTION_STRING. Tweaked my yaml so the deployDB job looked like

  # deploy new database schema using dacpac
  deployDB:
      needs: [buildDatabase, provisionInfra]
      runs-on: windows-latest
      steps:
      # download build artifacts
      - name: download build artifacts
        uses: actions/download-artifact@master
        with: 
          name: db
      # find where the dacpac is downloaded, make sure it's where i think it is
      - name: find dacpac
        run: |
          cd db
          ls
      # call sqlpackage.exe to deploy my db schema using my dacpac
      - name: update database schema using dacpac
        run: >
          "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\sqlpackage.exe" 
          /Action:Publish 
          /SourceFile:"%GITHUB_WORKSPACE%\db\MercuryHealthDB.dacpac" 
          /TargetConnectionString:"${{ secrets.DATABASE_CONNECTION_STRING }}" 
          /p:BlockOnPossibleDataLoss=False
workflow.yml

On line 138, I use a github built action to download my db artifact. Line 144, I have a step that lists my db folder to see if it really downloaded my dacpac. And finally on line 150, I call sqlpackage.exe, pass in a reference to my dacpac as well as other parameters that I need. Checking this in to trigger my workflow…

image

Whoa… for real… that totally worked. And I didn’t even need to write a custom Action!

Deploy infrastructure using IaC

Things have gone a little too smooth. So far, everything just worked the way I assumed they would work. And I’m a little weirded out by all of this. Since everything else has gone so smoothly, I might as well try to deploy my infrastructure using my IaC scripts. For this project, my IaC is a powershell script that calls the Azure CLI to provision and configure an azure app service and also an azure sql db. The configuration consists of setting up application insight as well as putting in the connection string to the db as an app service variable.

The provision infrastructure script does take in a bunch of input paramaters including things like my sql server database password so I need to remember to add those in as secrets. But the rest of the steps seemed pretty straight forward.

  1. download my build artifact which holds my IaC files
  2. define all the variables I need for my powershell script
  3. define all the secrets I need to deploy my infrastructure as secrets
  4. call the powershell script and pass in all the variables I need

The only iffy part is if the Azure CLI is already installed or do I need to install it? I figured I might as well just try it and see what happens right? I mean… what could go wrong? So I tweaked my yaml and the full yaml that does everything now looks like this

name: Mercury Health Core CI/CD
on: [push]
jobs:
  # build .net core web app
  buildWeb:
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v1
    - name: Setup .NET Core
      uses: actions/setup-dotnet@v1
      with:
        dotnet-version: 2.2.108
    - name: Build with dotnet
      run: |
        echo ls
        ls
        dotnet build Web/MercuryHealthCore.csproj --configuration Release
    - name: Test with dotnet
      run: dotnet test Web/MercuryHealthCore.csproj --configuration Release
    - name: Package everything up with dotnet
      run: dotnet publish Web/MercuryHealthCore.csproj --configuration Release
    - name: publish build artifacts back to GitHub
      uses: actions/upload-artifact@master
      with:
        name: webapp
        path: Web/bin/Release/netcoreapp2.2/publish
  # build database schema, build artifact is the dacpac
  buildDatabase:
    runs-on: windows-latest
    steps:
    # checkout code from repo
    - name: checkout repo
      uses: actions/checkout@v1
    # use msbuild to build VS solution which has the SSDT project
    - name: build solution
      run: |
        echo "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\bin\MSBuild.exe MercuryHealthCore.sln"
        "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\Bin\MSBuild.exe" MercuryHealthCore.sln
    # look for dacpac
    - name: find dacpac
      run: |
        echo "ls d:\a\MercuryHealthCore\MercuryHealthCore\MercuryHealthDB\bin\Debug\"
        ls "%GITHUB_WORKSPACE%\MercuryHealthDB\bin\Debug"
    # publish build artifact (dacpac) back to github
    - name: publish build artifacts back to GitHub
      uses: actions/upload-artifact@master
      with:
        name: db
        path: MercuryHealthDB\bin\Debug
  # publish IaC as build artifact
  uploadIaC:
    runs-on: windows-latest
    steps:
    # checkout code from repo
    - name: checkout repo
      uses: actions/checkout@v1
    # upload IaC folder
    - name: upload IaC
      uses: actions/upload-artifact@master
      with:
        name: IaC
        path: IaC
  # provision infrastructure in Azure
  provisionInfra:
    needs: uploadIaC
    runs-on: windows-latest
    steps:
    # download IaC artifact
    - name: download IaC
      uses: actions/download-artifact@master
      with:
        name: IaC
    - name: look for ps1 file
      run: |
        ls '%GITHUB_WORKSPACE%\IaC\AzCLI'
    - name: provision webapp and db infrastructure in azure
      env:
        SERVICE_PRINCIPAL: http://AbelDeployPrincipal
        SERVICE_PRINCIPAL_TENANT: 72f988bf-86f1-41af-91ab-2d7cd011db47
        AZURE_SUBSCRIPTION_NAME: ca-abewan-demo-test
        RESOURCE_GROUP: mercuryhealthcore-rg
        RESOURCE_GROUP_REGION: southcentralus
        SERVER_NAME: abelmercuryhealthcoredbserverbeta
        DB_LOCATION: southcentralus
        ADMIN_LOGIN: abel
        START_IP: 0.0.0.0
        END_IP: 0.0.0.0
        DB_NAME: abelmercuryhealthcoredbbeta
        WEB_APP_NAME: abelmercuryhealthcore-beta
        ENVIRONMENT: "[beta]"
      run: >
        powershell -command "& '%GITHUB_WORKSPACE%\IaC\AzCLI\provisionWebAndDB.ps1'" 
        -servicePrincipal %SERVICE_PRINCIPAL% 
        -servicePrincipalSecret ${{ secrets.SERVICE_PRINCIPAL_SECRET }} 
        -servicePrincipalTenantId %SERVICE_PRINCIPAL_TENANT% 
        -azureSubscriptionName %AZURE_SUBSCRIPTION_NAME% 
        -resourceGroupName %RESOURCE_GROUP% 
        -resourceGroupNameRegion %RESOURCE_GROUP_REGION% 
        -serverName %SERVER_NAME% 
        -dbLocation %DB_LOCATION% 
        -adminLogin %ADMIN_LOGIN% 
        -adminPassword ${{ secrets.DB_PASSWORD }} 
        -startip %START_IP% 
        -endip %END_IP% 
        -dbName %DB_NAME% 
        -webAppName %WEB_APP_NAME% 
        -environment %ENVIRONMENT%
  # deploy web app to azure app service
  deployWeb:
      needs: [buildWeb, provisionInfra]
      runs-on: windows-latest
      steps:
      # download build artifacts
      - name: download build artifacts
        uses: actions/download-artifact@master
        with: 
          name: webapp
      # Deploy build artifact to Azure App Service
      - name: Publish website to Azure App Service
        uses: azure/appservice-actions/webapp@master
        with:
          app-name: abelmercurywebcore  # Replace with your app name
          package: webapp  # Specify the folder or file to deploy
          publish-profile: ${{ secrets.PUBLISH_PROFILE_2 }}  # Replace with the name of your publish profile
  # deploy new database schema using dacpac
  deployDB:
      needs: [buildDatabase, provisionInfra]
      runs-on: windows-latest
      steps:
      # download build artifacts
      - name: download build artifacts
        uses: actions/download-artifact@master
        with: 
          name: db
      # find where the dacpac is downloaded, make sure it's where i think it is
      - name: find dacpac
        run: |
          cd db
          ls
      # call sqlpackage.exe to deploy my db schema using my dacpac
      - name: update database schema using dacpac
        run: >
          "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\sqlpackage.exe" 
          /Action:Publish 
          /SourceFile:"%GITHUB_WORKSPACE%\db\MercuryHealthDB.dacpac" 
          /TargetConnectionString:"${{ secrets.DATABASE_CONNECTION_STRING }}" 
          /p:BlockOnPossibleDataLoss=False
          
workflow.yml

image

The red block highlights the job and steps for provisioning and configuring my infrastructure. Triggering the workflow…

image

Whaaaaaaat? Did that really work? Ok, it seemed like everything is working but just to be sure, I went into my azure subscription, deleted everything and triggered my workflow. And…..

BAM! Everything just worked. My infrastructure was deployed, my web app and db projects were built. And after all that was done, the workflow deployed my web app and db schema changes to the infrastructure it just provisioned!!!!

AND I DID ALL THIS WITHOUT BUILDING A CUSTOM ACTION!!!!!

Final Thoughts

This exercise was seriously way too easy. Everything just worked the way I assumed it would. I created a full ci/cd pipeline including database devops and IaC in about 30 minutes using both the github hosted windows and linux vm’s. Now granted, I build a lot of pipelines so I’m super familiar with a lot of the concepts. But even taking that into account, I did not expect to finish everything this quickly!!!

I still need to create a custom Action. Maybe next time, I’ll try wrapping up the dacpac deploy task in an action so it’s a little easier to use. I mean… really, I just need an excuse to write an action to see what that’s like.

2 Comments

    • abel

      Azure DevOps Services and Server are still here and going strong. In fact, the office team at microsoft just moved everything over to Azure DevOps Services.

      GitHub is a premium offering from Microsoft. They are cloud agnostic and Actions only work with GitHub. Open source? Code already in GitHub? Makes perfect sense to use Actions (specially when Actions gets more fully fleshed out in terms of features). You’re an enterprise whose code is not in GitHub? Azure DevOps Services work GREAT. And you are now left with a choice. Do you want to pay the premium for GitHub? Is the extra chrome you get with GitHub worth it to you?

Leave a Reply

Your email address will not be published. Required fields are marked *