Brent says: It’s really funny how the world works. I wrote what you see below in December 2023 and scheduled it for publication on January 16th (today), hoping to get help from the community to get this process started. On January 9, before the post went live, David Wiseman submitted a pull request with the first steps! More on that at the end of the post. I’m leaving the post as-written in Dec 2023 because it’s still useful.

It’s a new year. You tell me if the state of the art has changed.

The biggest challenge in maintaining the First Responder Kit is testing people’s code. Different people submit code changes all the time, and I need to quickly assess:

• If their code will work on all current versions of SQL Server, Amazon RDS, and Azure SQL DB Managed Instances
• If their code works on case sensitive instances
• If their code works when some databases aren’t in an expected health state (offline, inaccessible for security reasons, restoring, etc)
• If their changes still have the intended good effects of the original code – for example, if we’re testing for databases with auto-shrink on, are we still catching that?

It’s especially hard with the First Responder Kit because the queries use SQL Server’s own system objects as a source.

Writing queries for multiple SQL Server versions is hard.

Here’s a very simplified example from a recently failed pull request:

That pull request Worked On My Machine™, but it failed on SQL Server 2016 because the version_generated_inrow and version_generated_offrow columns didn’t exist back then.

Sure, you could check that if you read the sys.dm_db_index_operational_stats documentation, which shows which columns are available in which version. On that page, do a control-F and search for “version”, which will take you to the part of the page that shows those two columns, and the version of SQL Server that introduced them. Go ahead, I’ll wait.

What’s that you say?

Those columns aren’t listed in the documentation, and many aren’t?

Ah well you see, that’s because Microsoft’s documentation is open source now. It’s up to kind folks like you and I to make pull requests with updates to the documentation, explaining to Microsoft what columns in their DMVs have changed, and what the contents of those columns are. We can’t expect Microsoft to do that for us, you know. They only made $151,597,000,000 of profit last year. That’s not revenue, that’s profit, after they paid all their expenses. I know that seems like a big number to you, and that with so much leftover change, they’d be able to keep their documentation up to date, but as the Microsoft folks say, that’s a big ask.

Just to put that in perspective, that’s $17,305,593 per hour. $4,807 per second.

So forgive me for not making that pull request to fix the documentation. I’ll leave that to you, dear reader. While you work on that, I’ll get back to the problem at hand.

Testing that multiple versions is even harder.

To test this simplified T-SQL:

We would need to somehow:

• Fake the contents of a read-only system DMV (because you can’t delete/update/insert data in that DMV.)
• Fake the contents of different versions of SQL Server, which have different columns, and aren’t even documented.
• Keep those tests up to date, which is monstrously challenging given the cloud versions like AWS RDS and Azure SQL DB MI, which can and do change over time, including system stored procedures and their RDS equivalents.

Realistically, that’s not going to happen. So instead, it would make more sense to do automated testing. Each time a pull request comes in:

• Spin up instances of SQL Server 2014, 2016, 2017, 2019, and 2022.
• Run the First Responder Kit’s creation scripts (the new pull request’s version), ensuring that the scripts are at least created without error.
• Run a series of predefined tests of sp_Blitz & friends, ensuring that the scripts run without error and produce the expected result sets.
• And then perhaps the hardest one, repeat those tests on AWS RDS and Azure SQL DB MI. Those are tougher because the spin-up times are much, much longer, and we get charged while they’re up.

That’s hard – but let’s take the first step.

Several years ago, I approached community members & consultants who were well-known for SQL Server CI/CD work. I explained this challenge, and I asked, “How much would you charge me to build a solution for this? You can open source the whole thing, or whatever parts are useful to you.”

Back then, no one would even give me a number to solve that problem – the effort in designing the solution alone was too high. Given that years have passed, and the state of the art might be different now, you tell me in the comments: what would the first steps look like? How long would they take? If it’s doable today, I’d definitely be interested in funding it.

Because manual testing blows, which means we don’t do it as well as we should, and we rely on community testers. Anything I can do to lessen the work of the community on stuff like this, I’m down!

Update 2024-01-09: David Wiseman took the first step!

Back to that first foreshadowing paragraph of the post: I’m thrilled to see that David Wiseman actually took the first step. He used Github Actions so that when someone opens a pull request:

• A GitHub runner deploys a clean instance of SQL Server 2017 in a Linux Docker container
• The First Responder Kit scripts are deployed
• Basic automated tests are run with PowerShell/Pester

To see how it’s done, check out the changed files in pull request 3419.

I love this as a first step because like David wrote in his Github issue, in just 2 minutes, this verifies that:

• The scripts deploy & run without errors on a case-sensitive 2017 Linux instance – which would actually fail more than you might think, just because a lot of folks don’t use case sensitive instances or Linux
• The expected number of row sets are returned with the expected number of columns – this actually would have caught pull requests in the past where folks left in debug results