Rajendra Gupta
Invoke Assessment

The SQL Server Assessment Extension for Azure Data Studio

June 30, 2020 by

Azure Data Studio extensions enable adding new functionality for development and administrative tasks. It is an open-source functionality and allows Microsoft as well as third party extensions using Marketplace. We explored a few useful extensions in the Azure Data Studio category of articles.

In this article, we will acquire a knowledge of the SQL Server Assessment extension and its usage.

Pre-requisites

Install the latest version (June 2020) of Azure Data Studio

Microsoft provides monthly releases for the Azure Data Studio. You should always work with the latest version to take the benefits of all features. In this article, we will use June 2020 release version 1.19.0. You can download it from Microsoft docs on your choice of platform Window, macOS and Linux.

latest version(June 2020)

You should use the user installer (recommended) because it does not require administrative rights on your local system.

Enable Preview Features

You should also have Enable Preview Features checked in the ADS user settings. To verify it, go to View -> Command Palette ->search for Preferences: Open user settings.

It should have a tick on the Enable unreleased preview features.

Enable Preview features

Overview of the SQL Server Assessment extension for Azure Data Studio

You might be familiar with the Windows PowerShell and SQL Server PowerShell module. It has an Invoke-SqlAssessment cmdlet to assess SQL Server and returns a list of SQL Server best practices. We can use it for SQL Server 2012 onwards along with the Azure SQL Database Managed Instance.

Steps to install SQL Server Assessment Extension for Azure Data Studio

Launch ADS, and go to Marketplace as shown in the below image. Search for the SQL Server Assessment.

SQL Server Assessment extension

Click on the Install to begin installation as highlighted below. You can also see that this extension is in preview mode. You might see further changes to this extension in future ADS releases.

Click on Install

It quickly installs this extension.

Installation

Let’s explore the usage of the SQL Server Assessment extension and a quick overview of the Invoke-SqlAssessment cmdlet in Windows PowerShells.

Invoke-SqlAssessment PowerShell cmdlet

Let’s have a quick overview of this cmdlet in Windows PowerShell.

  • Install the sqlserver PowerShell module using the below command:

    > import-module sqlserver

  • Check the cmdlet using the Get-command cmdlet of PowerShell:

    > Get-Command -Module SqlServer -Name *sqlassessment*

    It returns the cmdlet information, its version and the source module. We can see that Invoke-SqlAssessment belongs to the sqlserver module

    Invoke-SqlAssessment PowerShell cmdlet

  • Run the command to execute Invoke-SqlAssessment cmdlet for the SQL instance [Kashish\SQL2019GA] and give output in a grid view

    >Get-SqlInstance -ServerInstance Kashish\SQL2019GA | Invoke-SqlAssessment | Out-GridView

    It checks your SQL instance against a pre-defined set of rules and gives you output as shown below.

    View results of cmdlet

Previously, we did not have a user interface for the Invoke-SqlAssessment cmdlet, and it might not be feasible for everyone to use PowerShell commands.

Azure Data Studio enables integration of PowerShell cmdlet for SQL Assessment API. Once you have installed the SQL Server Assessment extension, connect to a SQL instance. Right-click on the connected instance and go to manage.

It launches the server dashboard having instance details, backup status and database size. You can go through article server and Database dashboards in Azure Data Studio to learn about dashboards.

Server Dashboard

Invoke Assessment

In the General section, you can see a link for the SQL Server Assessment. It opens the homepage for the assessment extension. In the top area, you get SQL Assessment API information (number 1) and SQL Server instance details (number 2) along with the OS version.

At first, you see that it does not give any information. It asks you to click on Invoke assessment to get results.

Invoke Assessment

Once you click on Invoke assessment, it starts checks for connected SQL instance for the violated best practices. In the severity column, you can see it is an informational message or warning.

View Invoke Assessment result

Click on the tags column. You can see it explores various areas such as trace flags, memory, Performance, Deprecated features, Security, agent, statistics, jobs etc.

Filter tag

SQL Server Assessment extension does not fix the configurations for you. It highlights the things you should review and take appropriate actions. For example, in the below screenshot, we get information about an index that you should review whether the index is in use or not.

Read details

Similarly, if you have a database in full recovery model but without a transaction log backup, it suggests you create a transaction log backup.

Example of transaction log backup

You can see two rule checks below.

  • It suggests you run DBCC CHECKDB because it is not executed for more than 7 days
  • If you have a database with the AUTO_CLOSE option, it asks you to verify and set the AUTO_CLOSE option to OFF

DBCC CHECKDB

Export as script

SQL Server assessment extension for Azure Data Studio might give you a long list of configuration issues, suggestions, warnings, information messages.

You might start working on those suggestions, but how do you track these? It is difficult to track these manually. It is a good idea to import the complete output in a SQL table. You can add a column to track the status. Suppose we added a Boolean column status in the new table. For example, let’s say we fixed the AUTO_CLOSE property from a SQL database as per the warning we got in the above output. Once it is fixed, we can change the status in the SQL table with the below values.

  • Value 0 – Not Fixed
  • Value 1 – Fixed

It becomes easy to track these rules as per our requirements. But let’s take a step back and think- How to export the output of Invoke Assessment into a SQL table.

ADS gives option ‘Export As Script’ for this purpose.

Export as script

Once you click on the ‘Export As Script’, it opens a new query window with the following scripts.

  • Create table script for [dbo].[AssessmentResult]. You can change the table name if you are required to do so
  • Insert the output of the invoke assessment in the table created above

View script

Connect to the target SQL instance, database with your credentials, and it creates the table for you with an output of the invoke assessment. Later, you can view the records in the table using a SELECT statement.

Query table

View applicable rules

You must be interested to know the applicable rules, criteria for the assessment of our SQL instance. To get the details, click on the View applicable rules.

It gives you severity, rule description, tags and its checkID. You also get a hyperlink for each rule. Click on the Learn more for the respective rule, and it shows the brief description in a pop-up as shown below.

View Applicable Rules

You can check the description for the multiple rules together. You can click on the respective rule again to remove the pop-up having a description.

Get details of the messages

View all rules and learn more on GitHub

It might be difficult to explore all these rules and their description in the Azure Data Studio. You can explore these rules in the GitHub as well. Click on the View all rules and learn more on GitHub. Once you click on it, it asks your permissions to open the external website.

View All Rules And Learn More On GitHub

Click open, and it launches the GitHub web page with all documentation for the SQL Server Assessment API.

Open GitHUb page

Click on the DefaultRuleSet.csv, and it shows all the rule details in an interactive table. You can also filter rules for a specific rule.

Default Rule Set

For example, if we want to check the rule whether the SQL instance is patched with the latest CU or Service pack. It filters the rules as per your input, as shown below.

Filter rules

Conclusion

In this article, we explored the SQL Server Assessment extension for Azure Data Studio. It provides a UI interface for the PowerShell cmdlet Invoke-SqlAssessment.

It analyzes your on-premises or Azure database instance against a set of rules and best practices. All the suggestions, warnings might not be applicable for your workload. You should adequately analyze the consequences of these, test in the non-prod environment and implement on the production system if required.

Rajendra Gupta
51 Views