Problem
In a previous tip called Quickly Evaluate SQL Server Best Practices with Invoke-SqlAssessment, we saw how to evaluate SQL Server according to Microsoft best practices using PowerShell. While I can use PowerShell to run this when necessary, is there an easier way to generate a report containing the assessment results?
Solution
Running an assessment like the SQL Server Best Practices Check across many instances throughout the enterprise is a great way to be productive. But that assumes you are already comfortable with scripting like PowerShell and have done large-scale task automation. If you’re just getting started as a DBA and only familiar with GUI-based tools like SQL Server Management Studio, it might take some time before you know there are issues with your SQL Server instances and databases.
Luckily, Microsoft provided another way to run the assessment using Azure Data Studio.
Azure Data Studio for SQL Server DBAs
The target audience for Azure Data Studio is primarily data platform developers who work with data on-premises or in Azure. This means you’re not limited to working with SQL Server. You can access Azure Synapse, PostgreSQL, MongoDB, and other supported data platforms.
But just because the tool was designed for data platform developers doesn’t mean that DBAs won’t be able to take advantage of it. This is where the power of extensions comes in. Using Azure Data Studio, you can add DBA-specific extensions to perform SQL Server administrative tasks. The tip called Popular extensions available on Azure Data Studio provides a list of extensions that DBAs can use. We’re adding another one to the list – the SQL Assessment extension.
Before we get started on running the SQL Server Best Practices Check with Azure Data Studio, be sure to install it properly by following the steps outlined in Getting Started with Azure Data Studio. Also, you need to have the latest SQL Server PowerShell module in the same workstation as your Azure Data Studio installation.
Importing SQL Assessment Extension in Azure Data Studio
To import the SQL Assessment extension, click the Extensions link in the left-hand tab. In the Search Extensions in Marketplace text box, type SQL Assessment.
Once the extension appears, click Install.
Don’t worry about the extension being in Preview. You’ll still be able to run the SQL Server Best Practices Check on your SQL Server instances and databases.
Running SQL Assessment Extension in Azure Data Studio
You need to connect to the SQL Server instance before running the assessment. To connect to a SQL Server instance, click the Connections icon on the left and then click the New Connection button.
In the Connection Details dialog box, enter the connection details of the SQL Server instance like you would with SQL Server Management Studio. Click Connect.
After the connection has been established, you will see the SQL Assessment extension in the General section of the SQL Server instance.
Click on the SQL Assessment extension to display the different tasks available. To run the assessment, under the Assessment tab, click Invoke assessment.
This will run the SQL Server Best Practices Check similar to this tip but with a graphical user interface.
Exploring the Results
Unlike when running the assessment using the Invoke-SqlAssessment PowerShell cmdlet, where a separate command is used for the instance and the databases, the SQL Assessment extension will do it for both. The icon on the leftmost column will tell you whether the checks are for the instance-level or database- or object-level.
From here, you can filter based on Severity or Tags. In the example below, the assessment returned a “High: severity check”, telling me that the TestDB database is corrupted. While this could have been caught by your regular database maintenance job, the corruption may have happened after the latest consistency check. Knowing this means you can take the necessary actions immediately before the issue worsens.
Viewing Applicable Rules
Because the SQL Server Best Practices Check supports running against different versions of SQL Server from 2012 up to the latest available, SQL Server on Azure VMs, Azure SQL Managed Instances, and even on SQL Server on Linux, you can review which rules apply to your specific environment. As SQL Server evolves, some of the best practices in older versions may no longer apply to newer versions. An example of this is manually enabling instant file initialization. Starting with SQL Server 2016, this has been included as part of the setup process.
Click the View applicable rules button to learn more about the specific rules that apply to your version of SQL Server.
Saving the Results in a SQL Server Table
Similar to this tip, you can use the SQL Assessment extension to generate the T-SQL statement to create the table and insert the results. Click the Export as script button to do this.
This will open a new query tab containing the T-SQL script. You can run the script directly from this tab, making sure that you choose the appropriate database.
You can make it a part of your process to generate the T-SQL script and save the results in a table so you can share it with the other members of your team. That way, you can keep track of what issues from the check have been resolved.
Generating HTML Report
Wouldn’t it be great if there was a quick way to generate a report that you can either print out or send as an email attachment to your boss? Well, actually, there is. You can click the Create HTML Report button to do just that.
This will prompt you to save the HTML file. Take note of where you saved the file. You can store it in a shared folder or a portal to which every team member has access.
This is a great option for people who prefer a printed document that they can use to keep track of the items that they need to work on. It’s also a great way to let your boss know what you’ve been working on. And this HTML report is the proof.
Summary
Azure Data Studio allows you to run the SQL Server Best Practices Check and generate a report using the SQL Assessment extension. With just a few mouse clicks, you can run the assessment, generate a report that you can send to your boss and/or your teammates, and start working on addressing the identified issues.
Next Steps
- Read the Microsoft announcement on the SQL Assessment extension with Azure Data Studio
- Review the previous tips on Quickly Evaluate SQL Server Best Practices with Invoke-SqlAssessment
- Review the following tips to get yourself familiar with Azure Data Studio
