PSBlitz 6.1.0 is out and it brings new backup and security checks, improved Excel handling, support for the ImportExcel PowerShell module, as well as other improvements.
What is PSBlitz?
For anyone not familiar with PSBlitz: it’s a PowerShell-based tool that outputs SQL Server health and performance diagnostics data to either Excel or HTML, and saves execution plans and deadlock graphs as .sqlplan and .xdl files.
If you’re familiar with Oracle, this is pretty much my attempt of a SQL Server equivalent to Oracle’s AWR report.
Feel free to check out the blog post announcing PSBlitz 4 years ago, as well as the 3 year anniversary post.
Major updates in PSBlitz v6.1.0
New features
New check: Database Backup Information
Powered by a custom non-sp version of Brent Ozar’s sp_BlitzBackups.
This page shows information about your backup history, RPO and RTO estimates, as well as warnings about backup-related issues. Warning include: recovery models being switched between backups, backing up to the NUL device, etc.
While on the topic of backups: don’t forget to test yours.
New check: Instance Security
Powered by a custom script I’ve written specifically for PSBlitz.
The checks aren’t exhaustive, and I still consider the underlying .sql script as being in a beta stage since I plan on adding other checks and refining the output a bit more, plus adding some more support pages for some of the findings.
But it’s a good starting point for an initial triage of common SQL Server security issues.
This step checks for things such as:
- Weak passwords – common passwords based on the top 20 most common, as well as blank and password same as login name.
- Configurations that increase attack surface.
- Configurations that allow for privilege escalation.
- Linked server connection security.
- Excessive privileges.
You can find the complete list of checks in the repo’s PSBlitz-Security-Check-Findings.md file.
Support for the ImportExcel PowerShell module
ImportExcel brings some additional benefits like no Office license or subscription being required, and better overall writing performance over the Excel app.
This has been a recurring request since PSBlitz’s inception, and I’ve delayed adding it because I did not want to force people to install additional PowerShell modules.
But now it’s officially supported, and it takes precedence over the Excel app.
As of 6.1.0, when running PSBlitz without specifying an output format, the decision flow will look like this:
- If ImportExcel is installed, then PSBlitz will use the module.
- If ImportExcel is not installed, but the Excel app is, then PSBlitz will use the Excel app.
- If neither ImportExcel nor the Excel app are found, then HTML will be used.
New switch: -ForceExcelApp – this overwrites the logic described above and will force PSBlitz to use the Excel application even if ImportExcel is installed.
I’ve added this more for my testing and debugging purposes, but I’ve also exposed it in the GUI.
Improvements
Excel COM app performance improvements
While working on supporting the ImportExcel PowerShell module, I’ve also overhauled the way data is written to Excel using the Excel app.
The result is now an up to 70% reduction in the time needed to write data to Excel.
Even more so for sheets that tend to have lots of data (e.g.: Index Usage)
Retry logic for transient errors
This is specifically helpful when working with an unstable network connection or in a busy Azure SQL DB environment.
For the time being, the retry count (3) and seconds between retry (5) are hard-coded, but I’m ok with them getting their own parameters if needed.
Just note that query timeouts are not part of the default retry logic… which brings me to my next point.
New switch: -RetryOnTimeout this tells PSBlitz to also include query timeouts in the retry logic.
Bug fixes
- Small CSS corrections in the HTML report.
- Small cleanup in the Excel report template.
- Fixed an issue in the README.md documentation in the Parameters table.
Any breaking changes this time?
No.
If you’ve updated your automations when 6.0.0 was released, then updating to 6.1.0 just requires replacing the contents of your PSBlitz folder.
Screenshots
Conclusion
Just a brief overview of PSBlitz 6.1.0 and its new features like the database backup and security checks.