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.
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.
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.
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.
It quickly installs this extension.
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
-
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.
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.
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.
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.
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.
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.
Similarly, if you have a database in full recovery model but without a transaction log backup, it suggests you create a 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
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.
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
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.
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.
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.
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.
Click open, and it launches the GitHub web page with all documentation for the SQL Server Assessment API.
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.
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.
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.
Table of contents
| The SQL Server Assessment Extension for Azure Data Studio |
| Source Control using Git in Azure Data Studio (ADS) |
| Integrating Azure Data Studio with Git and GitHub |
| Working with Git components in Azure Data Studio |
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023