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.
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
- Build .net core app/run unit tests/package everything up for deployment
- Build database project
- Package IaC files up so its ready for provisioning/configuration
- Provision infrastructure in Azure
- Deploy Web app into Azure App Service
- 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:
And when I ran it, this is what I saw
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
Checked it in. This triggered my workflow and the result was
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
Checked it in, the output was
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.
Checking it in… the output was
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…
Wow, that’s ugly. Hmmm is it really that simple? Let’s try it
Checked it in and my output was
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
And the workflow output is
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
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
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
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…
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.
- download my build artifact which holds my IaC files
- define all the variables I need for my powershell script
- define all the secrets I need to deploy my infrastructure as secrets
- 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
The red block highlights the job and steps for provisioning and configuring my infrastructure. Triggering the workflow…
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!!!!!
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.