Introducing the New SQL Assessment API For Microsoft SQL Server

Image of lines of code on a computer screen

VIAcode is happy to announce the public availability of the SQL Assessment API, a new extension of Microsoft SQL Server Management Objects (SMO) that allows SQL Server users to programmatically evaluate the configuration of SQL Server instances against a configurable set of rules.

The SQL Assessment API gives SQL Server users an easy-to-use tool for comparing the configuration of SQL Server instances with best practices and the policies of their organization. The SQL Assessment API is implemented both as an extension to SQL Server SMO and as a pair of new cmdlets in the SQL Server PowerShell module.

The SQL Assessment API supports all versions of SQL Server newer than SQL Server 2012, including both SQL on Windows and SQL on Linux. The API is available in the July 2019 release of both SQL Server Management Objects and the SQL Server PowerShell module.

Using The SQL Assessment API

The SQL Assessment API runs tests against SQL Server instances based on a set of rules or checks defined as objects in JSON files. These rules can be customized; users can add their own rules to implement custom checks, enable or disable rules and groups of rules, or adjust threshold parameters for rules that use a specific metric.

Microsoft provided a prewritten set of best-practice rules and has also made a comprehensive set of sample rules available on GitHub that can be used to guide the development of custom rulesets.

The API performs checks against SQL Server objects, including Server, Database, Filegroup, and AvailabilityGroup objects.

SQL Server users can run the tests against their SQL Server instances from PowerShell with a pair of cmdlets. ‌Get-SqlAssessmentItem provides a list of assessment checks for a specific SQL Server object. ‌Invoke-SqlAssessment runs the tests and returns the results of an assessment.

The included ruleset will test both Server and Database objects, producing a set of results and recommendations to help users conform with Microsoft’s recommended best practices. Users can schedule tasks to run periodic assessments against their SQL Server instances.

For full details of how to use the SQL Assessment API, visit Microsoft’s documentation page.

VIAcode and Microsoft SQL Server

Alex Andruschenko, software architect at VIACode, said, “Our developers are proud to work with the Microsoft SQL Server team to bring new monitoring solutions to SQL Server and other Microsoft software products. The SQL Assessment API is a valuable addition to SQL Server’s monitoring and assessment tools and will help many businesses to ensure that their database configurations conform to best practices.”

VIAcode provides a wide range of SQL Server and Microsoft Azure services, including free migration to Azure for users of SQL and Windows 2008. These platforms are reaching the end of their life, and Microsoft will stop supporting them soon.

VIAcode will migrate applications running on Windows 2008 and databases running on SQL Server 2008 to Azure for free. Fill out the form below to talk about a free initial consultation.

  • This field is for validation purposes and should be left unchanged.

VIAcode provides services for migration, optimization and management for Azure.

  • This field is for validation purposes and should be left unchanged.