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.

Azure Data Studio扩展允许添加用于开发和管理任务的新功能。 它是一种开源功能，允许Microsoft以及使用Marketplace的第三方扩展。 我们在文章的Azure Data Studio类别中探索了一些有用的扩展。

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

在本文中，我们将获得有关SQL Server评估扩展及其用法的知识。

先决条件 (Pre-requisites)

安装最新版本（2020年6月）的Azure Data Studio (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.

Microsoft提供Azure Data Studio的每月版本。 您应该始终使用最新版本以充分利用所有功能。 在本文中，我们将使用2020年6月发行版1.19.0。 您可以选择Windows平台，macOS和Linux平台从Microsoft文档下载它。

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.

您还应该在ADS用户设置中选中“ 启用预览功能” 。 要进行验证，请转到查看->命令面板->搜索首选项：打开用户设置。

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

它应该在“ 启用未发布的预览功能”上打勾。

适用于Azure Data StudioSQL Server评估扩展概述 (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.

您可能熟悉Windows PowerShell和SQL Server PowerShell模块。 它具有一个Invoke-SqlAssessment cmdlet可以评估SQL Server并返回SQL Server最佳实践的列表。 我们可以将其与 Azure SQL数据库托管实例一起用于SQL Server 2012及更高版本 。

为Azure Data Studio安装SQL Server评估扩展的步骤 (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.

启动ADS，然后转到市场，如下图所示。 搜索“ SQL Server评估”。

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.

单击“ 安装”以开始安装，如下所示。 您还可以看到此扩展程序处于预览模式。 您可能会在以后的ADS版本中看到对该扩展的进一步更改。

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.

让我们探索SQL Server评估扩展的用法，以及Windows PowerShell中Invoke-SqlAssessment cmdlet的快速概述。

Invoke-SqlAssessment PowerShell cmdlet (Invoke-SqlAssessment PowerShell cmdlet)

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

让我们快速概览一下Windows PowerShell中的此cmdlet。

  • Install the sqlserver PowerShell module using the below command:

    使用以下命令安装sqlserver PowerShell模块：

    > import-module sqlserver

    >导入模块sqlserver

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

    使用PowerShell的Get-command cmdlet检查cmdlet：

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

    >获取命令-模块SqlServer-名称* sqlassessment *

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

    它返回cmdlet信息，其版本和源模块。 我们可以看到Invoke-SqlAssessment属于sqlserver模块

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

    运行命令以为 SQL实例[Kashish \ SQL2019GA]执行Invoke-SqlAssessment cmdlet，并在网格视图中提供输出

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

    > Get-SqlInstance -ServerInstance Kashish \ SQL2019GA | 调用SqlAssessment | 网格外视图

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

    它根据预定义的规则集检查SQL实例，并为您提供输出，如下所示。

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.

以前，我们没有Invoke-SqlAssessment cmdlet的用户界面，并且每个人都无法使用PowerShell命令。

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.

Azure Data Studio启用了用于SQL评估API的PowerShell cmdlet集成。 安装SQL Server评估扩展后，连接到SQL实例。 右键单击连接的实例，然后进行管理。

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.

它启动具有实例详细信息，备份状态和数据库大小的服务器仪表板。 您可以浏览Azure Data Studio中的文章服务器和数据库仪表板以了解仪表板。

调用评估 (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.

在常规部分，您可以看到SQL Server评估的链接。 它将打开评估扩展的主页。 在顶部区域，您将获得SQL评估API信息（编号1）和SQL Server实例详细信息（编号2）以及操作系统版本。

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.

单击“调用评估”后，它将开始检查所连接SQL实例是否违反了最佳实践。 在“严重性”列中，您可以看到它是参考消息或警告。

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.

SQL Server评估扩展不能为您修复配置。 它突出显示了您应检查并采取适当措施的事项。 例如，在下面的屏幕截图中，我们获取有关索引的信息，您应该查看该索引是否正在使用中。

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

    建议您运行DBCC CHECKDB，因为它执行的时间不超过7天
  • If you have a database with the AUTO_CLOSE option, it asks you to verify and set the AUTO_CLOSE option to OFF

    如果您的数据库具有AUTO_CLOSE选项，它将要求您验证并将AUTO_CLOSE选项设置为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.

用于Azure Data StudioSQL Server评估扩展可能会为您提供一长串的配置问题，建议，警告和信息消息。

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.

您可能会开始处理这些建议，但是如何跟踪这些建议？ 手动跟踪它们很困难。 最好将完整的输出导入到SQL表中。 您可以添加一列来跟踪状态。 假设我们在新表中添加了布尔列状态。 例如，假设我们根据上述输出中的警告修复了SQL数据库中的AUTO_CLOSE属性。 修复后，我们可以使用以下值更改SQL表中的状态。

  • Value 0 – Not Fixed

    值0 –不固定
  • Value 1 – Fixed

    值1 –固定

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.

根据我们的要求轻松跟踪这些规则。 但是，让我们退后一步思考：如何将Invoke Assessment的输出导出到SQL表中。

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

为此，ADS提供了“导出为脚本”选项。

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

    为[dbo]。[AssessmentResult]创建表脚本。 如果需要，可以更改表名
  • 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.

使用您的凭据连接到目标SQL实例，数据库，然后使用调用评估的输出为您创建表。 以后，您可以使用SELECT语句查看表中的记录。

查看适用规则 (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.

您必须对了解适用的规则，评估我们SQL实例的标准感兴趣。 要获取详细信息，请点击查看适用规则 。

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.

它为您提供严重性，规则描述，标签及其checkID。 您还会为每个规则获得一个超链接。 单击了解有关相应规则的更多信息，它将在弹出窗口中显示简要说明，如下所示。

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.

您可以一起查看多个规则的描述。 您可以再次单击相应的规则以删除具有说明的弹出窗口。

查看所有规则并在GitHub上了解更多信息 (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.

在Azure Data Studio中探索所有这些规则及其描述可能很困难。 您也可以在GitHub中探索这些规则。 单击查看所有规则，并在GitHub上了解更多信息。 单击它后，它会询问您打开外部网站的权限。

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

单击“打开”，它会启动GitHub网页，其中包含SQL Server评估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.

单击DefaultRuleSet.csv ，它将在交互式表格中显示所有规则详细信息。 您还可以过滤特定规则的规则。

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.

例如，如果我们要检查规则是否用最新的CU或Service Pack修补了SQL实例。 它将根据您的输入过滤规则，如下所示。

结论 (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.

在本文中，我们探讨了适用于Azure Data StudioSQL Server评估扩展。 它为PowerShell cmdlet Invoke-SqlAssessment提供了UI界面。

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.

它根据一组规则和最佳实践来分析本地或Azure数据库实例。 所有建议，警告可能不适用于您的工作负载。 您应充分分析这些后果，在非生产环境中进行测试，并在需要时在生产系统上实施。

目录 (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
适用于Azure Data StudioSQL Server评估扩展
在Azure Data Studio（ADS）中使用Git进行源代码控制
将Azure Data Studio与Git和GitHub集成
在Azure Data Studio中使用Git组件

翻译自: https://www.sqlshack/sql-server-assessment-extension-for-azure-data-studio/

更多推荐

适用于Azure Data StudioSQL Server评估扩展