Today’s blog post is part of T-SQL Tuesday. T-SQL Tuesday is the brainchild of Adam Machanic. It is a blog party on the second Tuesday of each month. Everyone is welcomed to participate.
This month’s T-SQL Tuesday, hosted by Arun Sirpal ([b]|[t]), is all about overcoming technical challenges.
The challenge
I always try to solve my technical issues with natively available (aka Microsoft) solutions. It’s one of the many reasons I love PowerShell.
When it comes to monitoring, I know there are some really wonderful solutions out there. SentryOne and SolarWinds come to mind but I don’t always need deep query level monitoring or I may not be able to pay licencing costs for all of my SQL Instances. But I do need to easily know if they are all set up correctly.
Ever since I was a baby DBA, I’ve wanted to create a centralized solution that I could roll in with and get going with after finding all of my servers. Over the years, I’ve tried to put together some ASP/VBScript solutions, then when I learned PowerShell, tried PowerShell and SQL Agent, but I just wasn’t feeling it. I could never figure out how to make the checks modular and universal. That is, until I learned about Pester in-depth from Rob Sewell.
The solution
The solution I’ve been looking for turns out to be a mix of dbatools and Pester. Pester is the first Open Source community project formally included in a Windows release! Windows 10 and Windows Server 2016 include Pester by default.
Pester makes it easy to standardize tests or “checks” because it can output its results in a number of standardized formats, including NunitXml and JSON. It’s all PowerShell, after all.
dbatools makes it easy to perform these checks without installing additional software or stored procedures on each of my servers. I have one central workstation that needs to be updated and checking hundreds of servers is as easy as checking just one.
So after poring over Rob’s prodigious GitHub Presentation Repository, I found a bunch of his tests, and added them to a newly created module. I then created an index of the checks to make them easy to access. Once I got the proof of concept down, I invited a few people from the community who I know are involved in Pester and asked them to help.
The result has been a dream come true!
Index
Index of checks. You can run them all, run by group or only run the specific tests that matter to your environment.
Power BI Universal Dashboard
Courtesy of Cláudio Silva. Just hit refresh and you’re all set!
Included Parser & Mailer
Also from Rob’s repo!
We are still working on the checks, but these screenshots should give you an idea about the new SQL PowerShell module that Rob and I will be premiering at our SQLBits PowerShell Precon, Reliable, Repeatable, & Automated: PowerShell for DBAs, in London on Thursday, February 22 2018.
Interested in finding out more? Find us at SQLBits!
This looks awesome, Chrissy! A real contender for replacing policy-based management for best practices.
Hello, where can I find the Power BI dashboard “SQL Server Infrastructure Validation Dashboard”?.
Is that available online?
Hey Franceso,
We will be debuting the Power BI dashboard at SQL Bits in February. It will become available on GitHub then.
Looks great guys. I’m sure there will be lots who want to contribute tests towards it too!
Glad you like! We’re really excited about it as well
Pingback: Using the AST in Pester for dbachecks | SQL DBA with A Beard
Pingback: Pester 4.2.0 has a Because…… because :-) | SQL DBA with A Beard
This looks fantastic! I can’t wait to get my hands on it.
I’m so glad I picked your precon day for sqlbits 🙂
We’re glad too 🤗