Released: SQL Assessment API (Public Preview 2)

[EbruErsan]

SQL Assessment API is a new mechanism to evaluate configuration of your SQL Server for best practices. The API methods are used by means of a SQL Server Management Object (SMO) extension and new cmdlets in SqlServer PowerShell module. API is delivered with a ruleset that is highly customizable and extensible. It can be used to assess SQL Server versions 2012 and higher, both on Windows and Linux.

SQL Assessment API is shipped as part of SqlServer PowerShell module (21.1.18179) and SMO nuget package (150.18178.0).

The first public preview of SQL Assessment API was back in July. With this new release we are introducing some new capabilities as well as expanding the ruleset.

New features

• Versioning of ruleset - User can easily tell which ruleset and version a particular rule comes from. This is useful when you are updating the ruleset overtime (different versions of the same ruleset) and using multiple rulesets (customization of rules).
• Default view of cmdlets in table format - User can easily read the results of an assessment when it is presented in table format and clearly identified as to which object they apply to.
• New rules - We continuously improve the ruleset by adding new rules.
• Support for Azure SQL DB Managed Instance - Rules have a property that shows which products it applies to. Rules that apply to Managed Instance have been tested and marked appropriately.

Run an assessment in 3 steps

If you want to quickly run an assessment against your local SQL Server, follow these simple steps:

1. Bring up Azure Data Studio, Windows PowerShell ISE, or your favorite PS tool.
2. Install-Module -Name SqlServer -RequiredVersion 21.1.18179 -AllowClobber -Force
3. Get-SqlInstance -ServerInstance 'localhost' | Invoke-SqlAssessment

Results will look similar to this:

You can find the complete ruleset and some samples at our GitHub repository. Rules, sometimes referred to as checks, are defined in JSON formatted files. You will find examples of how to customize existing rules and how to write new ones in the same location. We will be growing the repo with new rules and examples going forward.

Docs online page has more details on the API.

We would love to hear your feedback. Please feel free to comment here or even better, on SQL Assessment API GitHub page where we have the rules and sample scripts available.

[Eric SIMBOZEL]

very helpfull and easier than Assessment and planing toolkit, so

i would like to know how updating the .jon files .

best regards,

Eric Simbozel

[EbruErsan]

@Eric SIMBOZEL, we have some samples for customization of rules in json files on our GitHub page. Please feel free to comment here or on that page if you cannot find what you are looking for. We continue to improve that page with more info and examples. Thanks for the feedback.

[boniSQL]

This automatic assessment report looks cool, will give it a try!

[Eric SIMBOZEL]

Hi,

thank's for your last response, so i just would like to know how to implement a new json file, and how does your tool take into consideration this new json file

best regards and cool job 

[AlexProtsenko]

@Eric SIMBOZEL thank you. We are glad to know that the API is useful for you.

Answering your question. Just create a JSON file with your own overrides and/or rules. See examples on Github: https://github.com/microsoft/sql-server-samples/tree/master/samples/manage/sql-assessment-api (have a look at DisablingBuiltInChecks_sample.json and MakingCustomChecks_sample.json, and also the tutorial ADS notebook and json samples coming with it). Once you make your own JSON file, run the following if your customizations are for SQL Instance:

Get-SqlInstance -ServerInstance '<SQLSERVERINSTANCE>' | Invoke-SqlAssessment -Configuration '<FULL PATH TO YOUR JSON FILE>'

Or/and run the following, if your customizations are for SQL Database:

Get-SqlDatabase -ServerInstance '<SQLSERVERINSTANCE>' | Invoke-SqlAssessment -Configuration '<FULL PATH TO YOUR JSON FILE>'