Week Three: Baselining Part 2 – Microsoft's Best Practices Checker for SQL Server

In Part 1 of this Week 3 dive into SQL Server baselining, I used Invoke-DbaDiagnosticQuery from dbatools to run Glenn Berry’s diagnostic scripts. It gave me a solid snapshot of current settings, performance details, and a sense of how each server is doing.

But I wanted more than just a snapshot. I wanted to check whether these servers were actually aligned with Microsoft’s best practice recommendations.

That’s where Invoke-SqlAssessment steps in.

What is Invoke-SqlAssessment?

This command is part of the SqlServer PowerShell module. It taps into SQL Assessment APIs introduced by Microsoft. The goal? Help you detect common misconfigurations or risky settings based on rules curated by Microsoft itself.

It runs two main scopes:

• Instance-level rules – checks at the server level

• Database-level rules – evaluates each database individually

You can either display results in a grid or write them directly to a SQL table. I chose the latter, so I can review, share, and archive assessment history.

The Script

Here’s the breakdown of the script I used to run Invoke-SqlAssessment across a fleet of production servers:

$dbservers = Get-Content "C:\Temp\SQLPROD.txt" #List of all SQL servers

$outputarray = @()
ForEach($server in $dbservers){
	 Write-host -nonewline "."
	#region for Named Instances
	#$SQLService = (Get-service -ComputerName $server | where {($_.displayname -like "SQL Server (*") }).Name
	#$ServerInstanceSplit = $SQLService.Split("$")
	#$InstanceName = $server + '\' + $ServerInstanceSplit[1]
	#endregion

	$StartDate = Get-Date
	Write-Host "
	##########################################
	SQL Vulnerability Assessment initiated for $server...
	Time Script Started $StartDate" -ForegroundColor Green

	#Running server scope rules
	Get-SqlInstance -ServerInstance $($server) | 
	Invoke-SqlAssessment -FlattenOutput |
	Write-SqlTableData -ServerInstance 'PROD-DB1' -DatabaseName SQLAssessment -credential (Get-Credential) -SchemaName bpc -TableName Results -Force #Change the SQL server instance for the preferred location of assessment result

	#Running database scope rules
	Get-SqlDatabase -ServerInstance $($server) | 
	Invoke-SqlAssessment -FlattenOutput |
	Write-SqlTableData -ServerInstance 'PROD-DB1' -DatabaseName SQLAssessment -credential (Get-Credential) -SchemaName bpc -TableName Results -Force #Change the SQL server instance for the preferred location of assessment result
	
	$EndDate = Get-Date
	$Time = $EndDate - $StartDate
	Write-Host "
	##########################################
	Assessment results for $server saved...
	Time Script ended at $EndDate and took
	$Time" -ForegroundColor Green
}	

A few things to point out:

• Credential Prompt: The script prompts for credentials using Get-Credential. You can adjust this to use stored credentials or secure strings if automating.

• Centralized Logging: I chose to dump all results into a dedicated database (SQLAssessment) on PROD-DB1. This makes it easier to build reports later.

• Scalable: Just add new servers to the SQLPROD.txt file. The loop will pick them up automatically.

  
  

Why This Matters

Running Invoke-SqlAssessment regularly is a smart way to catch small misconfigurations before they become bigger issues. Things like auto-shrink settings, missing backups, improper tempdb setup, max server memory, these rules cover both common and subtle best practices.

Having these results stored in a database lets you:

• Track changes or regressions over time

• Share the data with your team (or auditors)

• Tie assessments to other documentation, like Confluence

  
  

How I Handle the Results

After running the assessment, I make sure the results are centralized. Everything gets written to a dedicated table in a shared SQL Server, where I (and others) can query it anytime. I also export key results to Excel when needed and usually when preparing for a review or doing health checks before major deployments.

Wrapping Up

Getting a proper baseline isn’t about a single report or checklist, it’s about building a habit of visibility. Using tools like Invoke-SqlAssessment gives you a structured, repeatable way to surface issues before they surprise you. It also makes it easier to explain why something needs attention, especially when the recommendations come straight from Microsoft.

Baselining might not feel urgent, but once you’ve built that reference point, troubleshooting becomes clearer. And planning? Way less guesswork.

🔗 SQL Assessment API

🔗 Invoke-SqlAssessment