How to use the SQL Assessment API through Powershell

15 October,2019 by Jack Vamvas

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 

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

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



Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to use the SQL Assessment API through Powershell


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer