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, as well as Azure SQL DB Managed Instance. More products will be supported in future releases.
SQL Assessment API is shipped as part of SqlServer PowerShell module (21.1.18206) and SMO NuGet Package (150.18208.0).
How to get started
Quick Start page on our GitHub repo will have you running an assessment in 2 simple steps.
You can also use Quick Start notebook with Azure Data Studio.
It is as simple as this (use Azure Data Studio, Windows PowerShell ISE, or your favorite PS tool):
# Uncomment and run Install-Module only the first time
# Install-Module -Name SqlServer -AllowClobber -Force
Import-Module -Name SqlServer
Get-SqlInstance -ServerInstance 'localhost' | Invoke-SqlAssessment
When you are ready to discover more about the API, please use the SQL Assessment API Tutorial notebook that walks you through a large set of examples including customization of rules.
Ruleset
Rules, sometimes referred to as checks, are defined in JSON formatted files. You can see the full Microsoft ruleset on our GitHub repo: config.json is the ruleset file and DefaultRuleset.csv is a more human readable rendering of the same. We will be continuously growing the ruleset with new rules going forward.
Example of a rule:
Example of a probe that grabs the data for the rule to evaluate:
Here are some major features around rules that you should be aware of.
Customization
You will find examples of how to customize existing rules and how to write new ones in the SQL Assessment API Tutorial notebook.
Rule targets
Rules are marked with a property that shows which product/version (SQL Server, Linux, …) they apply to as well as which object (server instance, database, …).
Versioning
Versioning allows users to 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).
We would love to hear your feedback. Please feel free to comment here or even better, on SQL Assessment API GitHub page.
Microsoft
