Pester v5 migration

[wsmelton]

Start of a thread from Slack that @niphlod and I started discussing...basic purpose is workout bullet points for our current test framework requirements, wishes, desires and dreams 🎐 😉 ...

Pester v5 is a need as v4.10.0 (which we are not even using) isn't going to be updated any longer; the last v4 release is from 2020...time to move on and up 🙃 💣 .

TL;DR from slack thread:

1. Moving to Pester v5 all on one large PR or break this up and slowly convert

• I would be up for small change PRs by moving test files into a folder structure such as tests/private and tests/public then adjust our AppVeyor to ignore those folders
• This would allow new framework structure to only pull tests from those new folders and be easier to troubleshoot what failures are occurring, and piece-meal things over to Pester v5.

3. Pattern to use for things as we know Pester v5 requires our test are going to be rewritten
4. In that rewrite cover more of the commands that don't have much in testing (e.g., AG commands)

• [Unit testing while some don't prefer it serves a purpose for this so that we can at least show SMO is going to do what we expect...integration testing comes from user base and releasing beta versions if we want to make big changes).

5. Do we want to move any test groups into GitHub Actions or keep with Appveyor?

[1d](https://sqlcommunity.slack.com/archives/C3EJ852JD/p1716842011734479)
niphlod
  
I wouldn't know how going about a "big bang" PR that replaces every single test of our suite in one round from v4 to v5 (edited) 
[2h](https://sqlcommunity.slack.com/archives/C3EJ852JD/p1716927323086919?thread_ts=1716842011.734479&cid=C3EJ852JD)
Shawn
  I've contemplated doing it in pieces but that seems like wasted effort to rewrite our current AppVeyor setup to handle multiple versions. Unless we are okay with folder organization being used (Public/Private)?
Meaning having a tests/public and tests/private and tests in those new folders would be v5, whatever is in the root tests folder stays at v4. That maybe easier.
I know for Connect-DbaInstance I had to rewrite more than I thought I would. I'm not fond of it and need to stare at it a bit more to see what I can make cleaner as a pattern. :joy:
[2h](https://sqlcommunity.slack.com/archives/C3EJ852JD/p1716927541929119?thread_ts=1716842011.734479&cid=C3EJ852JD)
niphlod
  given coverage may be a good metric while rewriting, are pester v5 tests going to target any live instance ?
if so, we need a "runner" that identifies, in some way, tests that need v4 and v5. And a way to run v4 and v5 with "both" pester modules installed, I think there's a way to do it, because if not, each environment needs to be replicated, and there'll be runs with v4 and runs with v5
[2h](https://sqlcommunity.slack.com/archives/C3EJ852JD/p1716927624835809?thread_ts=1716842011.734479&cid=C3EJ852JD)
Shawn
  I kinda (a big "kinda") want to see if we could move things to GitHub Actions (they run faster and give us less spin up requirements). At least those things that run on a single or two instances to just check functions work against a give version of SQL. The AG and cluster type commands I'm comfortable with throwing in unit tests to "fake" the Windows side but will take me a bit to figure out what is required for all that. (edited) 
[2h](https://sqlcommunity.slack.com/archives/C3EJ852JD/p1716927770464399?thread_ts=1716842011.734479&cid=C3EJ852JD)
Shawn
  One thing that is bothering me on AppVeyor is some of our tests fail locally for me on Linux (think Jess had one that failed for her on Windows too) but they don't fail in AppVeyor. I don't know if that is the images we are using have something funny on them or what, but would like to use something more known and I know more about how the images we'd use on Github Actions are built :stuck_out_tongue_winking_eye:
[2h](https://sqlcommunity.slack.com/archives/C3EJ852JD/p1716927837656379?thread_ts=1716842011.734479&cid=C3EJ852JD)
Shawn
  For ref: all of the latest images used by GH and Azure DevOps MS hosted agents come from this repo https://github.com/actions/runner (I use this at work to build our self-hosted agents).
Only thing on GitHub is if we can cover the tests running fast enough with the "free" compute...not sure what we could get as an open-source project and all right off.
[2h](https://sqlcommunity.slack.com/archives/C3EJ852JD/p1716927898182739?thread_ts=1716842011.734479&cid=C3EJ852JD)
niphlod
  well, those funky tests we can absolutely review, as for running the entire test suite on GH Actions ... I'd say Appveyor is not that slow. Also, appveyor runs are sponsored, I think, so we can run 8-10ish in parallel, GH Actions won't have that capacity, unless "sponsorship" is redirected there.
[2h](https://sqlcommunity.slack.com/archives/C3EJ852JD/p1716927978307549?thread_ts=1716842011.734479&cid=C3EJ852JD)
niphlod
  appveyor images have instances installed (ahem, 2008R2, too) and ready. GH Actions, unless we build our own, either need to target a container or reinstall two instances before each run !?
2h
niphlod
  that being said, one approach may be to have tests running v4 on appveyor and v5 on gh actions. But then coverage won't be a "merged" result, so, even here, I think we need to figure out a valid approach before starting any steps
[2h](https://sqlcommunity.slack.com/archives/C3EJ852JD/p1716928415308049?thread_ts=1716842011.734479&cid=C3EJ852JD)
Shawn
  Any version that mcr.microsoft.com has available for SQL Server can be used in GitHub Actions, you can deploy multiple to interact with them all in one test or have it run on each; isn't much different than what is done now they are all just Windows servers in AppVeyor and GH lets us use Windows + Linux to run the tests against.
I'm not really sure why we care much about 2008R2 anymore because it is officially dead and only supported method to run it is in Azure (and only in Azure). SMO version we are on as well I don't think truly supports it, meaning if someone finds something broke with the SMO version we are on they (being MS) aren't going to fix it now.
Maybe we should start with that 
@cl
 nailing down what versions of SQL Server do we want to target supporting going forward and then we can determine from that what we run the Pester v5 tests against. This would all let us do any restructuring to our tests groups too.
[2h](https://sqlcommunity.slack.com/archives/C3EJ852JD/p1716929195334429?thread_ts=1716842011.734479&cid=C3EJ852JD)
niphlod
  agreed. BUT ... the registry lists linux containers, which is cool, but hardly a representative percentage of the target dbatools has, which are "plain old sql instances on windows" . the win image on GH Actions or Devops doesn't have sql installed so .... how do you run a test against a SQL 2016 on a windows box ? (edited) 
2h
niphlod
  (as anticipated, I reeeally don't know GH Actions)
2h
niphlod
  IMHO (as I just use devops) windows images are on par with appveyor "free", and a lot slower compared to appveyor "sponsored" we have. linux runners are definitely snappier.
7m
Shawn
  Up to the greater vote I guess but every machine I use it on is Linux based, not Windows...but I also never run PowerShell scripts on a SQL Server itself which I know isn't the cool thing in most DBA teams :nicmoji_laugh:
1m
Shawn
  I forgot I had even created this thread https://github.com/dataplat/dbatools/discussions/7634