You know what my biggest regret is about sp_Blitz?

No unit tests.

Seriously. Back then, I wrote a bunch of T-SQL checks to see if a server had memory configuration errors, corruption, obscure trace flags, etc. Over time, sp_Blitz got bigger and bigger, and we started getting code contributions from strangers. Things got even more challenging when we open sourced it with the MIT License and put it on Github – it was even easier for people to throw in more code.

And we did the best job we could of testing it, but…people make mistakes, including us. That bug fix we did to fix one issue had an unforeseen side effect, and broke something else. Or even worse, silently broke checks – making it so we no longer alerted someone if their server had an issue.

When we started building SQL ConstantCare®, I didn’t wanna make that same mistake. Every check we coded with a PostgreSQL function, we needed to be able to automatically test going forward, every single time we had a commit. I have no idea how you accomplish something like that – I mean, I know the theory, but I wouldn’t even begin to know what tools to use. Fortunately, Richie does – so the way our arrangement works is that I write the query, and I supply him with test data to satisfy different edge cases that should pass or fail.

Example: the remote DAC rule

Take the remote DAC rule I blogged about last week. It sounds so simple: if it’s not enabled, you need to turn it on. Well, it’s just a little more complex than that:

• If sp_configure ‘remote admin connections’ reports 0, we want to advise you
• If sp_configure ‘show advanced options’ reports 0, then we need to have you turn that on to make the change (and possibly turn it back off afterwards if you want
• However, if any sp_configure options have value_in_use <> value, then we need to skip this advice for now, because we don’t want you running RECONFIGURE since it would put those other sp_configure changes into effect
• Except for a few sp_configure options where certain variances are normal (like min memory, which seems to jump back and forth between 0 and 16 all the time)

There’s a bunch of different test cases in there – and hey, that’s one of the cool things about having all this data up in the cloud. It’s easy for me to go spelunking through diagnostic data (looking only at the folks who gave us permission to use their data to improve their recommendations, of course – more on that in another post.) In a matter of minutes, I could find examples of:

• Someone who had remote DAC turned on (so they shouldn’t get an alert)
• Someone with remote DAC off, show-advanced on, and no pending changes
• Someone with remote DAC off, show-advanced on, but pending changes to, say, MAXDOP
• Someone with remote DAC off, show-advanced off, … you get the point

I can then run my Postgres function against these different clients, make sure it produces the right results, and then hand it over to Richie to build the automated tests. The testing is done with Javascript, and I am positive of that for two reasons: Richie told me, and the unit test files end with .js. I found them as part of writing this blog post, opened a couple of them, saw that they weren’t database queries, and did my best Grandpa Simpson.

Unit testing costs us money today,
and saves us money tomorrow.

The very first time you build something, it takes longer. As a small business, that cost us more time (and money, in terms of salary) to get to minimum viable product (MVP.) Each completely-brand-new feature (like the wait stats analysis we’re working on now) takes longer to build, too. However, when something goes out the door up to the cloud, we’re more confident that it works today, and that it’ll still be working five years from now despite tons of unrelated code changes.

Seven years ago, when we started this company, I wasn’t a believer in database unit testing. Today, I’m a raving fan.

If you wanna learn how to apply the same concepts in your SQL Server work, check out Alex Yates’ upcoming database devops class.

Richie says: In my opinion, when creating new features, that feature isn’t complete until tests are created. The tooling to write automated tests for code is fairly simple, we’ve been doing it for years, but creating tests for database code is a horse of a different color. In the SQL Server world, we have tSQLt and in the Postgres world there are similar tools, but for SQL ConstantCare, we built a small database testing engine using Node.js, Mocha, and Chai. These are the same tools that we use to test the serverless functions. We’re not doing anything super fancy like faking or mocking: we just load data, run a function, and verify the result. We even plugged these tests into our automated build system. And yes, we know that our database test are technically integration tests and not unit tests, so you don’t have to blow up the comments explaining the difference to us.

When I first joined the company, I begged Brent to let me write tests for sp_Blitz. Having tests around sp_Blitz would have been a huge undertaking and we had other things to do like build SQL ConstantCare. You might face the same situation. The decision to create unit/integration tests around your product might be more of a investment than what your company is willing to make. It’s a tough call. Just make sure to point out the risks during your testing and deployment to your stakeholders.