SQL 评估 APISQL Assessment API

SQL 评估 API 提供了一种评估 SQL Server 配置以获得最佳做法的机制。SQL Assessment API provides a mechanism to evaluate the configuration of your SQL Server for best practices. 该 API 附带一个规则集,其中包含 SQL Server 团队建议的最佳做法规则。The API is delivered with a ruleset containing best practice rules suggested by SQL Server Team. 此规则集将随新版本的发布而进行强化,与此同时,构建此 API 的目的是提供高度可自定义和可扩展的解决方案。This ruleset is enhanced with the release of new versions but at the same time, the API is built with the intent to give a highly customizable and extensible solution. 这样用户便可以优化默认规则并创建自己的规则。So, users can tune the default rules and create their own.

在确保 SQL Server 配置符合建议的最佳做法时,SQL 评估 API 非常有用。SQL Assessment API is useful when you want to make sure your SQL Server configuration is in line with recommended best practices. 初步评估后,可以通过定期安排评估来跟踪配置稳定性。After an initial assessment, configuration stability can be tracked by regularly scheduled assessments.

该 API 可用于评估 Azure SQL 托管实例和 SQL Server 2012 及更高版本。The API can be used to assess Azure SQL Managed Instance and SQL Server versions 2012 and higher. 支持 Linux 上的 SQL。SQL on Linux is supported.

规则Rules

规则(有时称为“检查”)在 JSON 格式的文件中进行定义。Rules sometimes referred to as checks, are defined in JSON formatted files. 规则集格式要求指定规则集名称和版本。Ruleset format requires a ruleset name and version to be specified. 因此,当使用自定义规则集时,可以很容易地知道哪些建议来自哪些规则集。So when you use custom rulesets, you can easily know which recommendations from what ruleset come.

GitHub 上提供了 Microsoft 附带的规则集。Microsoft's shipped ruleset is available on GitHub. 可以访问示例存储库了解更多详细信息。You can visit the samples repository for more details.

SQL 评估 cmdlet 和 SMO 扩展SQL Assessment cmdlets and SMO extension

SQL Server 管理对象 (SMO) 2019 年 7 月发行版和更高版本以及 SQL Server PowerShell 模块 2019 年 7 月发行版和更高版本中均包含 SQL 评估 API。SQL Assessment API is part of the SQL Server Management Objects (SMO) July 2019 release version and higher and the SQL Server PowerShell module July 2019 release version and higher.

SqlServer 模块提供两个新的 cmdlet 来与 SQL 评估 API 搭配使用:SqlServer module is getting two new cmdlets to work with SQL Assessment API:

  • Get-SqlAssessmentItem - 提供 SQL Server 对象的可用评估检查列表 Get-SqlAssessmentItem – Provides a list of available assessment checks for a SQL Server object

  • Invoke-SqlAssessment - 提供评估结果 Invoke-SqlAssessment – Provides results of an assessment

SQL 评估 API 扩展对 SMO Framework 进行补充,该扩展提供以下方法:SMO Framework is supplemented by the SQL Assessment API extension that provides the following methods:

  • GetAssessmentItems - 返回特定 SQL 对象的可用检查 (IEnumerable<…>)  GetAssessmentItems – Returns available checks for a particular SQL object (IEnumerable<…>)

  • GetAssessmentResults - 对评估进行同步评估并返回结果和错误(如果有)(IEnumerable<…>)  GetAssessmentResults – Synchronously evaluates assessment and returns results and errors if any (IEnumerable<…>)

  • GetAssessmentResultsList - 对评估进行异步评估并返回结果和错误(如果有)(Task<…>) GetAssessmentResultsList – Asynchronously evaluates assessment and returns results and errors if any (Task<…>)

开始使用 SQL 评估 cmdletGet started using SQL Assessment cmdlets

针对所选的 SQL Server 对象执行评估。An assessment is performed against a chosen SQL Server object. 默认规则集中仅提供针对两种对象的检查:Server 和 Database(除此之外,该 API 还支持另外两种对象:Filegroup 和 AvailabilityGroup)。In the default ruleset, there are checks for two kinds of objects only: Server and Database (in addition to them, the API supports two more kinds: Filegroup and AvailabilityGroup). 如果要评估 SQL 实例及其所有数据库,应分别为每个对象运行 SQL 评估 cmdlet。If you want to assess a SQL instance and all its databases, you should run the SQL Assessment cmdlets for each object separately. 或者,也可以将要评估的对象通过变量或管道传递给 SQL 评估 cmdlet。Or you can pass objects for assessment to the SQL Assessment cmdlets in a variable or the pipeline.

SqlServer 和 RegisteredServer 对象是可交换的,因此可以将任何对象传递给 SQL 评估 cmdlet。SqlServer and RegisteredServer objects are interchangeable, so you can pass any to the SQL Assessment cmdlets.

浏览下面的示例以开始使用。Go through the examples below to get started.

  1. 获取本地默认实例的可用检查列表,以便熟悉这些检查。Get a list of available checks for a local default instance to familiarize yourself with the checks. 在此示例中,我们通过管道将 Get-SqlInstance cmdlet 的输出传递到 Get-SqlAssessmentItem cmdlet,以将实例对象传递给它。In this example, we're piping the output of the Get-SqlInstance cmdlet to the Get-SqlAssessmentItem cmdlet to pass the instance object to it.

    Get-SqlInstance -ServerInstance 'localhost' | Get-SqlAssessmentItem
    
  2. 获取实例的所有数据库的可用检查列表。Get a list of available checks for all databases of the instance. 此处,我们使用 Get-Item cmdlet 和 Windows PowerShell SQL Server 提供程序实现的路径来获取数据库列表,然后将其传送到 Get-SqlDatabase cmdlet。Here, we're using the Get-Item cmdlet and a path implemented with the Windows PowerShell SQL Server provider to get a list of the databases, and then piping it to the Get-SqlDatabase cmdlet.

    Get-Item SQLSERVER:\SQL\localhost\default | Get-SqlAssessmentItem
    

    此外,还可以使用 Get-SqlDatabase cmdlet 来执行相同的操作。Also, you can use the Get-SqlDatabase cmdlet to do the same.

    Get-SqlDatabase -ServerInstance 'localhost' | Get-SqlAssessmentItem
    
  3. 获取实例的所有数据库的可用检查列表。Get a list of available checks for all databases of the instance. 此处,我们使用 Get-Item cmdlet 和 Windows PowerShell SQL Server 提供程序实现的路径来获取数据库列表,然后将其传送到 Get-SqlDatabase cmdlet。Here, we're using the Get-Item cmdlet and a path implemented with the Windows PowerShell SQL Server provider to get a list of the databases, and then piping it to the Get-SqlDatabase cmdlet.

    Get-Item SQLSERVER:\SQL\localhost\default | Get-SqlAssessmentItem
    

    此外,还可以使用 Get-SqlDatabase cmdlet 来执行相同的操作。Also, you can use the Get-SqlDatabase cmdlet to do the same.

    Get-SqlDatabase -ServerInstance 'localhost' | Get-SqlAssessmentItem
    
  4. 调用实例的评估,并将结果保存到 SQL 表。Invoke assessment for the instance and save the results to a SQL table. 在此示例中,我们通过管道将 Get-SqlInstance cmdlet 的输出传递到 Invoke-SqlAssessment cmdlet,并通过管道将其结果传递给 Write-SqlTableData cmdlet。In this example, we're piping the output of the Get-SqlInstance cmdlet to the Invoke-SqlAssessment cmdlet, which results are piped to the Write-SqlTableData cmdlet. 在此示例中,Invoke-Assessment cmdlet 与 -FlattenOutput 参数一起运行。The Invoke-Assessment cmdlet is run with the -FlattenOutput parameter in this example. 此参数使输出适用于 Write-SqlTableData cmdlet。This parameter makes the output suitable for the Write-SqlTableData cmdlet. 如果不使用该参数,后者将引发错误。The latter raises an error if you omit the parameter.

    Get-SqlInstance -ServerInstance 'localhost' |
    Invoke-SqlAssessment -FlattenOutput |
    Write-SqlTableData -ServerInstance 'localhost' -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force
    

    现在,我们来调用实例的所有数据库的评估,并将结果添加到同一个表中。Now let's invoke an assessment for all databases of the instance and add the results to the same table.

    Get-SqlDatabase -ServerInstance 'localhost' |
    Invoke-SqlAssessment -FlattenOutput |
    Write-SqlTableData -ServerInstance 'localhost' -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force
    
  5. 请按照表中的说明和链接来进一步了解建议。Follow descriptions and links in the table to further understand the recommendations.

  6. 根据环境和组织要求自定义规则(见下文)。Customize the rules based on your environment and organizational requirements (see below).

  7. 安排任务或作业以定期或按需运行评估从而衡量进度。Schedule a task or a job to run the assessment regularly or on-demand to measure progress.

自定义规则Customizing rules

规则是可自定义和可扩展的。Rules are designed to be customizable and extensible. Microsoft 的规则集旨在适用于大多数环境。Microsoft's ruleset is designed to work for most environments. 但是,一个规则集不可能适用于所有环境。However, it's impossible to have one ruleset that works for every single environment. 用户可以编写自己的 JSON 文件并自定义现有规则或添加新规则。Users can write their own JSON files and customize existing rules or add new ones. 示例存储库中提供了自定义示例和完整的 Microsoft 发布的规则集。Examples of customization and complete Microsoft released ruleset are available in the samples repository. 有关如何使用自定义 JSON 文件运行 SQL 评估 cmdlet 的更多详细信息,请使用 Get-Help cmdlet。For more details on how to run the SQL Assessment cmdlets with custom JSON files, use the Get-Help cmdlet.

规则自定义功能提供的选项Options available with rule customization feature

启用/禁用某些规则或规则组(使用标签)Enabling/disabling certain rules or groups of rules (using tags)

当特定规则不适用于环境时,或在完成计划工作以解决问题之后,可以使这些规则处于静默状态。You can silence specific rules when they aren't applied to your environment or until scheduled work is done to rectify the issue.

更改阈值参数Changing threshold parameters

特定规则会将自身阈值与当前的指标值进行比较,以发现问题。Specific rules have thresholds that are compared against the current value of a metric to find out an issue. 如果默认阈值不合适,可以对其进行更改。If the default thresholds don't fit, you can change them.

添加更多由所在组织或第三方编写的规则Adding more rules written by you or third parties

通过将一个或多个 JSON 文件作为参数添加到 SQL 评估 API 调用中,可以将多个规则集组合在一起。You can string together rulesets by adding one or more JSON files as parameters to your SQL Assessment API call. 组织可能会编写这些文件,或从第三方获取这些文件。Your organization might write those files or obtain them from a third party. 例如,可以让你的 JSON 文件禁用 Microsoft 规则集中的特定规则,让行业专家提供的另一个 JSON 文件包含你认为对环境有用的规则,然后再让另一个 JSON 文件更改该 JSON 文件中的某些阈值。For example, you can have your JSON file that disables specific rules from the Microsoft ruleset, and another JSON file by an industry expert that include rules you find useful for your environment, followed by another JSON file that changes some threshold values in that JSON file.

重要

我们强烈建议不要使用来自不受信任源的规则集,除非对其全面检查以确保它们安全。We urge you not to use rulesets that come from untrusted sources until you thoroughly review them to make sure they are safe.

后续步骤Next steps