How to use the SQL Assessment API through Powershell

Microsoft released a SQL Assessment API module , available to Powershell . What is the SQL Assessment API?  It's a new method to assess the SQL Server configurations against a set of SQL Server best practises. The SQL Assessment API is available through the SMO and a bunch of new Powershell cmdlets. It's currently in Public Preview. 

I've installed the SQL Assessment API and started thinking about how to apply to multiple server.s The availability of the rules via Powershell is a massive plus. This raises fantastic opportunities for automation and inclusion into regular sql server health checks or certification process. One of the major benefits when working with Powershell is the power to iterate through multiple environments very quickly . 

The SQL Assessment API is available for server level and database level checks. In this post I'll take you through a server level check .

--check you have the commands required

Get-Command -Module SqlServer -Name *sqlassessment*

On my laptop , returns , which were the latest versions at the time . If you're not getting something similar to below , than you need to investigate why these powershell cmdlets , are not appearing. The main reason is normally because the latest Powershell SQLServer module is not installed or the modules are not installed in directory included as part of the PATH

CommandType Name Version Source
----------- ---- ------- ------
Cmdlet Get-SqlAssessmentItem 21.1.18179 SqlServer
Cmdlet Invoke-SqlAssessment 21.1.18179 SqlServer

--To return all available checks on a server

Get-SqlInstance -ServerInstance 'myserver\instance' | Get-SqlAssessmentItem

--To invoke a SQL assessment and print out onto the screen 

Get-SqlInstance -ServerInstance 'myserver\instance' |Invoke-SqlAssessment

--or invoke a SQL assessment using a variable for the SQL Server Instance 

$SQLInstance = “$ENV:COMPUTERNAME\MYINST”
Get-SqlInstance -ServerInstance $SQLInstance | Invoke-SqlAssessment

Sample output

Sev. Message Check ID Origin
---- ------- -------- ------
Info Consider enabling trace flag 174 to increase the plan cache TF174 Microsoft Ruleset 0.1.203
bucket count.
Info Enable trace flag 834 to use large-page allocations to improve TF834 Microsoft Ruleset 0.1.203
analytical and data warehousing workloads.

--Write the sqlassessment results to a table in a database.

If the database,schema & table doesn't exist , it will be automatically created - assuming privileges exist

$Table = "Results"
$Instance = "server\instance"
$DBname = "SQLAssessment"
$SchemaName = "Assessment"
Get-SqlInstance -ServerInstance $Instance | Invoke-SqlAssessment -FlattenOutput | Write-SqlTableData -ServerInstance $Instance -DatabaseName $DBname -SchemaName $SchemaName -TableName $Table -Force

If you want to enhance the monitoring of SQL Server vulnerabilities read How To Automate SQL Server Vulnerability Assessment