Add Automated Testing with GitHub Actions

[DavidWiseman]

Is your feature request related to a problem? Please describe.

GitHub actions are a great way to run some automated tests. These can be ran as code is pushed to GitHub and can identify issues before someone creates a PR. It might not replace your existing checks and balances but it can help catch issues early and save time in reviewing code that is broken.

Describe the solution you'd like

When code is pushed to GitHub, you can have a runner deploy a clean instance of SQL Server in a docker container, deploy the FRK scripts and run some automated tests using PowerShell/Pester. This is all done using a .yml file. Some .ps1 files are also added to the repository to perform the testing.

Describe alternatives you've considered

N/A. Many projects use GitHub actions and I think it will be a good fit for First Responder Kit. It's free automated testing that will save you time and improve code quality. If you don't agree though, feel free to close this PR.

Are you ready to build the code for the feature?

Yes. I will submit a PR soon...

Scope: The initial scope of the tests will be limited but it will provide some scaffolding that the community can build on. The tests should still provide some real value though and be able to catch some bugs as code is pushed to GitHub - possibly before a user submits a PR. Saving you time reviewing broken PRs!

e.g.

• Does it deploy and run without errors?
• Do we get results returned?
• Does it return the expected number of columns?
  Note: This means updating the test if you want to deliberately change the output, or this check can be removed if preferred
• Does it work on a case sensitive instance? A good test as many people will validate on case insensitive instances
• Does it work on SQL 2017 (checking backward compatibility to this point) Note: We can run 2017-2022 in Docker. Additional workflow files could be created to test against multiple versions
• Does it work on Linux? Also a good test as most users won't test if it works on Linux.

What could be improved: The tests are quite basic - some more complex validation could be performed. Also testing different parameter combinations etc. The tests can be improved by the community over time to catch more issues.

integration-tests.yml

This is the workflow file. It creates a SQL instance, deploys the FRK and runs run-tests.ps1. When you push code to GitHub, the action will be triggered. This will pass/fail based on the results of the tests and you can drill down to see the details of each run.

run-tests.ps1

This runs all the .tests.ps1 files in the tests folder with Invoke-Pester*. Sets up some parameter defaults for the tests.

*.tests.ps1 files

A file for each stored proc to run a basic test.

Example

The above shows a successful workflow run followed by a failure. The failure in Workflow-BreakSomething branch is just down to this commit.

This causes a few of the tests to fail as we are using a case sensitive instance so any references to sp_BlitzWho will now fail as we changed it to sp_blitzwho (just for this demo - not in the PR!). You can drill down and see why it failed.

[BrentOzar]

@DavidWiseman that is so funny because I literally have a blog post scheduled for next Tuesday, asking for help from the community to do this, HAHAHA:

I don't know anything about how these work, so I'm going to do the obvious, natural thing: Leeeeeroy Jennnkinnnnns! I'm going to merge the pull request into the dev branch and then start banging on it today, tweaking it to get it to work the way I want.

Thanks so much for doing this! I really appreciate it. It'll be in the next release with credit to you in the release notes.

[DavidWiseman]

Good timing then - great! I might have ruined your blog post though. 🤣 I'd still call on help from the community to help improve the Pester tests I created.

If you are going to be working on it yourself I'd recommend using VS Code with the GitHub Actions extension for working with the *.yml files. White space is very important in yml files - VS Code helps make it a bit easier to work with. VS Code is also a good for editing PowerShell with the PowerShell extension.

You might also be able to make use of GitHub actions for automating the release process. You could have an action configured to run on workflow_dispatch (Just allows you to run the workflow manually when you want to create a release). This could zip up whatever files you want to include the in release and add a tag with the current date as the version number. I'm not sure what your release process looks like exactly, but you can probably automate it with an action. I have mine configured to publish the release in draft mode for review.

[SeanKilleen]

I dropped in with #3422 after reading said blog posts. Adds matrices so you can specify multiple SQL versions and collations and run the tests against all of the combinations.

[DavidWiseman]

I dropped in with #3422 after reading said blog posts. Adds matrices so you can specify multiple SQL versions and collations and run the tests against all of the combinations.

@SeanKilleen Nice! I think 2022 is also an option based on the release notes, but looks like the docs haven't been updated.

For the collations, I initially went with a binary collation but it seemed to have some issues with sqlcmd. Just a heads up. I think any case sensitive collation is likely to pick up most issues but makes sense to make it easier to test with other collations if required in future. 👍

[bluckholl]

Very good work!

At work I'm using tsqlt unit tests framework, very convenient to write unit tests as stored procedure, with built-in assert mechanism. I'm raising it as an option, as it probably be easier for DBAs to create test sp than powershell tests.

https://tsqlt.org/

[BrentOzar]

@DavidWiseman @SeanKilleen thanks for the work! I'm closing this for now since the basics are in place, but if anybody wants to add more testing for things, we can open new tickets for those.