通过

使用 Azure Monitor 中的 SQL Server 运行状况检查解决方案优化 SQL 环境

SQL 运行状况检查符号

可以使用 SQL 运行状况检查解决方案定期评估服务器环境的风险和运行状况。 本文将帮助你安装解决方案，以便你可以针对潜在问题采取纠正措施。

此解决方案提供特定于已部署服务器基础结构的建议的优先列表。 这些建议分为六个重点领域，可帮助你快速了解风险并采取纠正措施。

这些建议基于来自数千次客户访问的Microsoft工程师获得的知识和经验。 每个建议都提供有关问题为何可能对你很重要以及如何实施建议的更改的指导。

可以选择对组织最重要的重点领域，并跟踪运行无风险和健康环境的进度。

添加解决方案并完成评估后，焦点区域的摘要信息将显示在环境中基础结构的 SQL 运行状况检查 仪表板上。 以下部分介绍如何使用 SQL 运行状况检查 仪表板上的信息，在其中您可以查看并执行 SQL Server 基础结构的建议操作。

SQL 健康检查磁贴图像

SQL 运行状况检查仪表板的图像

先决条件

  • SQL 运行状况检查解决方案要求在安装了Microsoft监视代理（MMA）的每台计算机上安装受支持的 .NET Framework 4.6.2 版本。 MMA 代理被 System Center 2016 - Operations Manager、Operations Manager 2012 R2 和 Azure Monitor 使用。

  • 该解决方案支持 SQL Server 版本 2012、2014、2016、2017 和 2019。

  • 一个 Log Analytics 工作区，用于从 Azure 门户中的 Azure 市场添加 SQL 运行状况检查解决方案。 若要安装解决方案，你必须是 Azure 订阅中的管理员或参与者。

    注释

    添加解决方案后，AdvisorAssessment.exe 文件将添加到具有代理的服务器。 将读取配置数据，然后将其发送到云中的 Azure Monitor 进行处理。 逻辑应用于接收的数据，云服务会记录数据。

若要针对 SQL Server 服务器执行运行状况检查，需要使用以下支持的方法之一来代理并连接到 Azure Monitor：

  1. 如果 System Center 2016 - Operations Manager 或 Operations Manager 2012 R2 尚未监视服务器，请安装 Microsoft监视代理（MMA ）。
  2. 如果使用 System Center 2016 - Operations Manager 或 Operations Manager 2012 R2 进行监视，并且管理组未与 Azure Monitor 集成，则可以使用 Log Analytics 多宿主服务器收集数据并转发到服务，并且仍由 Operations Manager 监视。
  3. 否则，如果 Operations Manager 管理组与该服务集成，则在工作区中启用解决方案后，需要按照添加代理管理的计算机下的步骤，将域控制器添加到该服务的数据收集中。

SQL Server 上的代理向 Operations Manager 管理组报告、收集数据、转发到其分配的管理服务器，然后直接从管理服务器发送到 Azure Monitor。 数据不会写入 Operations Manager 数据库。

如果 SQL Server 由 Operations Manager 监视，则需要配置 Operations Manager 运行方式帐户。 有关详细信息，请参阅下面的 Azure Monitor 的 Operations Manager 运行方式帐户 。

SQL 运行状况检查数据收集详细信息

SQL 健康检查使用您已启用的代理从以下源收集数据：

  • Windows Management Instrumentation （WMI）
  • 注册表
  • 性能计数器
  • SQL Server 动态管理视图结果

数据在 SQL Server 上收集，每七天转发到 Log Analytics。

用于 Log Analytics 的 Operations Manager 运行账户

Log Analytics 使用 Operations Manager 代理和管理组收集数据并将其发送到 Log Analytics 服务。 Log Analytics 基于工作负载管理包来提供增值服务。 每个工作负荷都需要特定于工作负荷的权限才能在不同的安全上下文（例如域用户帐户）中运行管理包。 需要通过配置 Operations Manager 运行方式帐户来提供凭据信息。

使用以下信息设置 SQL 运行状况检查的 Operations Manager 运行方式帐户。

为 SQL 运行状况检查设置运行方式帐户

如果已在使用 SQL Server 管理包，则应使用该运行方式配置。

在操作控制台中配置 SQL 运行方式账户

注释

默认情况下，管理包中的工作流在本地系统帐户的安全上下文中运行。 如果使用Microsoft监视代理直接连接到服务，而不是直接向 Operations Manager 管理组报告，请跳过下面的步骤 1-5 并运行 T-SQL 或 PowerShell 示例，并将 NT AUTHORITY\SYSTEM 指定为用户名。

  1. 在 Operations Manager 中，打开作控制台，然后单击“ 管理”。

  2. 在 “运行方式配置”下，单击“ 配置文件”，然后打开 “SQL 评估运行方式配置文件”。

  3. 在“运行方式账户”页面上，单击“添加”。

  4. 选择包含 SQL Server 所需的凭据的 Windows 运行方式帐户，或单击“ 新建 ”以创建一个。

    注释

    运行方式帐户类型必须是 Windows。 运行方式帐户还必须是托管 SQL Server 实例的所有 Windows Server 上的本地管理员组的成员。

  5. 单击“ 保存”。

  6. 在每个 SQL Server 实例上修改并执行以下 T-SQL 示例，以授予运行方式帐户执行运行状况检查所需的最低权限。 但是，如果运行方式帐户已是 SQL Server 实例上的 sysadmin 服务器角色的一部分，则无需执行此作。

    ---
    -- Replace <UserName> with the actual user name being used as Run As Account.
    USE master

    -- Create login for the user, comment this line if login is already created.
    CREATE LOGIN [<UserName>] FROM WINDOWS

    -- Grant permissions to user.
    GRANT VIEW SERVER STATE TO [<UserName>]
    GRANT VIEW ANY DEFINITION TO [<UserName>]
    GRANT VIEW ANY DATABASE TO [<UserName>]

    -- Add database user for all the databases on SQL Server Instance, this is required for connecting to individual databases.
    -- NOTE: This command must be run anytime new databases are added to SQL Server instances.
    EXEC sp_msforeachdb N'USE [?]; CREATE USER [<UserName>] FOR LOGIN [<UserName>];'

使用 Windows PowerShell 配置 SQL 运行方式帐户

打开 PowerShell 窗口，用您的信息更新脚本后，运行以下脚本:

    import-module OperationsManager
    New-SCOMManagementGroupConnection "<your management group name>"

    $profile = Get-SCOMRunAsProfile -DisplayName "SQL Assessment Run As Profile"
    $account = Get-SCOMrunAsAccount | Where-Object {$_.Name -eq "<your run as account name>"}
    Set-SCOMRunAsProfile -Action "Add" -Profile $Profile -Account $Account

了解如何确定建议的优先级

每个建议都会获得一个权重值，该值标识建议的相对重要性。 仅显示十项最重要的建议。

如何计算权重

权重是基于三个关键因素的聚合值：

  • 所识别问题的 概率 会导致问题。 更高的概率相当于建议的总体分数越大。
  • 如果这个问题确实导致问题，将对您的组织产生影响。 更高的影响相当于建议的总体分数更大。
  • 实施建议所需的 工作量 。 工作量越大，建议的总体评分就越低。

每个建议的权重表示为每个焦点区域可用的总分数的百分比。 例如，如果“安全性和符合性”重点区域中的建议的分数为 5%，则实施该建议会将总体安全性和合规性分数提高 5%。

重点区域

安全性和符合性 - 此重点区域显示了有关潜在安全威胁和违规、公司策略和技术、法律和法规合规性要求的建议。

可用性和业务连续性 - 此重点区域显示了有关服务可用性、基础结构复原能力和业务保护的建议。

性能和可伸缩性 - 此重点区域显示有助于组织 IT 基础结构增长的建议，确保 IT 环境满足当前的性能要求，并能够响应不断变化的基础结构需求。

升级、迁移和部署 - 此重点区域显示有助于将 SQL Server 升级、迁移和部署到现有基础结构的建议。

作和监视 - 此重点区域显示有助于简化 IT作、实施预防性维护以及最大化性能的建议。

更改和配置管理 - 此重点区域显示有助于保护日常作的建议，确保更改不会对基础结构产生负面影响，建立变更控制过程，以及跟踪和审核系统配置。

你应该打算在每个重点领域得分 100% 吗？

不一定。 这些建议基于Microsoft工程师在数千次客户访问中获得的知识和经验。 但是，没有两个服务器基础结构是相同的，并且特定建议可能与你相关或多或少。 例如，如果虚拟机未向 Internet 公开，则某些安全建议可能不太相关。 对于提供低优先级即席数据收集和报告的服务，某些可用性建议可能不太相关。 对成熟企业很重要的问题对初创企业来说可能不太重要。 你可能想要确定哪些重点领域是你的优先级，然后看看分数随时间的变化。

每个建议都包含有关为何很重要的指导。 鉴于 IT 服务和组织的业务需求的性质，应使用本指南来评估是否适合你实施建议。

使用运行状况检查重点区域建议

必须先安装解决方案，然后才能在 Azure Monitor 中使用评估解决方案。 安装后，可以使用 Azure 门户中 Azure Monitor 的“概述 ”页上的“SQL 运行状况检查”磁贴查看建议摘要。

查看基础设施的合规性评估汇总，然后深入查看建议。

查看关注领域的建议并采取纠正措施

  1. 通过 https://portal.azure.com 登录到 Azure 门户。
  2. 在 Azure 门户中，单击左下角的“ 更多服务 ”。 在资源列表中，键入 Monitor。 当您开始输入时，列表会根据您的输入进行筛选。 选择“显示器”。
  3. 在菜单的 “见解 ”部分中，选择“ 更多”。
  4. 在 “概述 ”页上，单击 “SQL 运行状况检查 ”磁贴。
  5. 在运行状况检查页上，查看某个重点区域的摘要信息，然后单击某个查看该重点区域的建议。
  6. 在任何重点区域页面上，可以查看针对环境提出的优先建议。 单击 “受影响的对象 ”下的某个建议，查看有关为何发出建议的详细信息。

    SQL 运行状况检查建议的图像
  7. 可以采取建议的措施中的纠正措施。 当项目得到解决时，以后的评估将记录已采取建议的作，并且合规性分数将增加。 更正的项显示为 传递的对象。

忽略建议

如果有要忽略的建议，可以创建一个文本文件，Azure Monitor 将使用该文本文件来防止建议出现在评估结果中。

确定那些您将忽略的建议

  1. 在 Azure Monitor 菜单中，单击“ 日志”。

  2. 使用以下查询列出环境中那些未在计算机上成功执行的建议。

    SQLAssessmentRecommendation | where RecommendationResult == "Failed" | sort by Computer asc | project Computer, RecommendationId, Recommendation

    下面是显示日志查询的屏幕截图：

    失败的建议

  3. 选择要忽略的建议。 在接下来的步骤中，将使用推荐 ID 的值。

如何创建和使用 IgnoreRecommendations.txt 文本文件

  1. 创建名为 IgnoreRecommendations.txt的文件。
  2. 请将您希望 Azure Monitor 忽略的每个建议的 RecommendationId 粘贴或键入在单独的每一行中，然后保存并关闭文件。
  3. 将文件放在希望 Azure Monitor 忽略建议的每台计算机的以下文件夹中。
    • 在具有 Microsoft Monitoring Agent（直接或通过 Operations Manager 连接）的计算机上：SystemDrive：\Program Files\Microsoft Monitoring Agent\Agent
    • 在 Operations Manager 管理服务器上 - SystemDrive：\Program Files\Microsoft System Center 2012 R2\Operations Manager\Server
    • 在 Operations Manager 2016 管理服务器上 - SystemDrive：\Program Files\Microsoft System Center 2016\Operations Manager\Server

验证是否忽略建议

  1. 下一次计划评估运行后，默认情况下每隔 7 天，指定的建议将标记为“忽略”，不会显示在评估仪表板上。

  2. 可以使用以下日志搜索查询列出所有忽略的建议。

    SQLAssessmentRecommendation | where RecommendationResult == "Ignored" | sort by Computer asc | project Computer, RecommendationId, Recommendation

  3. 如果稍后决定要查看被忽略的建议，请删除任何 IgnoreRecommendations.txt 文件，或者从这些文件中删除建议 ID。

常见问题

SQL 评估解决方案执行了哪些检查？

  • 以下查询显示当前所执行的所有检查的描述：
SQLAssessmentRecommendation
| distinct RecommendationId, FocusArea, ActionArea, Recommendation, Description
| sort by FocusArea,ActionArea, Recommendation

然后，可以将结果导出到 Excel，以便进一步查看。

健康检查的运行频率是多长时间一次？

  • 检测每七天运行一次。

是否有办法配置检查运行的频率？

  • 目前不是。

如果在添加 SQL 运行状况检查解决方案后发现另一台服务器，是否会对其进行检查？

  • 是的，一旦发现它，就会每隔七天检查一次。

如果服务器已停用，何时会将其从运行状况检查中删除？

  • 如果服务器未提交数据 3 周，则会将其删除。

数据收集过程的名称是什么？

  • AdvisorAssessment.exe

收集数据需要多长时间？

  • 服务器上的实际数据收集大约需要 1 小时。 在具有大量 SQL 实例或数据库的服务器上，可能需要更长的时间。

收集哪些类型的数据？

  • 收集以下类型的数据：
    • WMI
    • 注册表
    • 性能计数器
    • SQL 动态管理视图（DMV）。

是否有一种方法可以配置何时收集数据？

  • 目前不是。

为何需要配置“以特定身份运行”帐户？

  • 对于 SQL Server，运行少量 SQL 查询。 若要运行这些帐户，必须使用对 SQL 具有 VIEW SERVER STATE 权限的运行方式帐户。 此外，为了查询 WMI，需要本地管理员凭据。

为什么只显示前 10 条建议？

  • 建议先专注于解决优先建议，而不是提供详尽的任务列表。 在处理完这些建议后，更多建议将会出现。 如果想要查看详细列表，可以使用 Log Analytics 日志搜索查看所有建议。

是否有办法忽略建议？

后续步骤

  • 记录查询 ，了解如何分析详细的 SQL 运行状况检查数据和建议。