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:
- Bring up Azure Data Studio, Windows PowerShell ISE, or your favorite PS tool.
- Install-Module -Name SqlServer -RequiredVersion 21.1.18179 -AllowClobber -Force
- 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.