使用适用于 Linux 上的 SQL Server 的 SQL 评估 API

项目
07/15/2024

适用于：SQL Server - Linux

SQL 评估 API 提供了一种机制来评估 SQL Server 的配置，以获得最佳做法。该 API 附带一个规则集，其中包含 SQL Server 团队建议的最佳做法。随着新版本的发布，此规则集得到增强。对于确保 SQL Server 配置符合建议的最佳做法，它非常有用。

Microsoft 发布的规则集可以在 GitHub 上找到。可以在示例存储库中查看完整规则集。

在本文中，我们将了解用于运行适用于 Linux 上的 SQL Server 和容器的 SQL 评估 API 的两种方式：

Azure Data Studio 的 SQL 评估扩展（预览版）
将 SQL 评估 API 与 PowerShell 配合使用

Azure Data Studio 的 SQL 评估扩展（预览版）

Azure Data Studio 的 SQL 评估扩展（预览版）提供了一种机制来评估 SQL Server 的配置，以获得最佳做法。

使用此预览版，可以：

使用内置规则评估 SQL Server、Azure SQL 数据库或 Azure SQL 托管实例及其数据库
获取适用于实例及其数据库的所有内置规则的列表
将评估结果和适用规则列表导出为脚本，以将其存储在 SQL 表中
创建有关评估结果的 HTML 报告

启动 SQL 评估

安装 SQL 评估扩展后，展开服务器列表，右键单击要评估的服务器或数据库，然后选择“管理”。
然后，在“常规”部分中，选择“SQL 评估”。在“评估”选项卡上，选择“调用评估”，对所选 SQL Server 或 Azure SQL 数据库执行评估。结果可用后，可以使用筛选和排序功能。
选择“导出为脚本”，以插入到表的格式获取结果。还可以选择“创建 HTML 报告”，将评估结果另存为 HTML 文件。有些评估规则专用于特定的 SQL Server 配置，而有些则用于其他配置。数据库规则也是如此。例如，有些规则仅适用于 SQL Server 2016 (13.x) 或 tempdb 数据库。
“查看适用规则”按钮显示评估规则，这些规则用于在你选择“调用评估”后对服务器和数据库执行评估。若要查看有关 SQL Server 和 SQL 评估 API 的信息，请选择“信息”。可以在“历史记录”选项卡上查看评估会话结果。

将 SQL 评估 API 与 PowerShell 配合使用

第二个选项是使用 PowerShell 运行 SQL 评估 API 脚本。

先决条件

请确保在 Linux 上安装 PowerShell。
安装 PowerShell 库中的 SqlServer PowerShell 模块，并以 mssql 用户身份运行。
```
su mssql -c "/usr/bin/pwsh -Command Install-Module SqlServer"
```

设置评估

SQL 评估 API 输出以 JSON 格式提供。保修执行以下步骤来配置 SQL 评估 API，步骤如下：

在要评估的实例中，使用 SQL 身份验证创建用于 SQL Server 评估的登录名。可以使用以下 Transact-SQL (T-SQL) 脚本创建登录名和强密码。将 <secure_password> 替换为所选的强密码。
```
USE [master];
GO

CREATE LOGIN [assessmentLogin] WITH PASSWORD = N'<secure_password>';
ALTER SERVER ROLE [CONTROL SERVER] ADD MEMBER [assessmentLogin];
GO
```
CONTROL SERVER 角色适用于大多数评估。但是，有一些评估可能需要 sysadmin 特权。如果未运行这些规则，建议使用 CONTROL SERVER 权限。

按如下方式存储用于登录系统的凭据，再次将 <secure_password> 替换为上一步中使用的密码。

echo "assessmentLogin" > /var/opt/mssql/secrets/assessment
echo "<secure_password>" >> /var/opt/mssql/secrets/assessment

确保仅 mssql 用户可以访问新的评估凭据，从而保护这些凭据。

chmod 600 /var/opt/mssql/secrets/assessment
chown mssql:mssql /var/opt/mssql/secrets/assessment

下载评估脚本

下面是使用前面步骤中创建的凭据调用 SQL 评估 API 的示例脚本。该脚本会在此位置生成 JSON 格式的输出文件：/var/opt/mssql/log/assessments。

注意

SQL 评估 API 还可以生成 CSV 和 XML 格式的输出。

可从 GitHub 下载该脚本。

可以将此文件另存为 /opt/mssql/bin/runassessment.ps1。

[CmdletBinding()] param ()

$Error.Clear()

# Create output directory if not exists

$outDir = '/var/opt/mssql/log/assessments'
if (-not ( Test-Path $outDir )) { mkdir $outDir }
$outPath = Join-Path $outDir 'assessment-latest'

$errorPath = Join-Path $outDir 'assessment-latest-errors'
if ( Test-Path $errorPath ) { remove-item $errorPath }

function ConvertTo-LogOutput {
    [CmdletBinding()]
    param (
        [Parameter(ValueFromPipeline = $true)]
        $input
    )
    process {
        switch ($input) {
            { $_ -is [System.Management.Automation.WarningRecord] } {
                $result = @{
                    'TimeStamp' = $(Get-Date).ToString("O");
                    'Warning'   = $_.Message
                }
            }
            default {
                $result = @{
                    'TimeStamp'      = $input.TimeStamp;
                    'Severity'       = $input.Severity;
                    'TargetType'     = $input.TargetType;
                    'ServerName'     = $serverName;
                    'HostName'       = $hostName;
                    'TargetName'     = $input.TargetObject.Name;
                    'TargetPath'     = $input.TargetPath;
                    'CheckId'        = $input.Check.Id;
                    'CheckName'      = $input.Check.DisplayName;
                    'Message'        = $input.Message;
                    'RulesetName'    = $input.Check.OriginName;
                    'RulesetVersion' = $input.Check.OriginVersion.ToString();
                    'HelpLink'       = $input.HelpLink
                }

                if ( $input.TargetType -eq 'Database') {
                    $result['AvailabilityGroup'] = $input.TargetObject.AvailabilityGroupName
                }
            }
        }

        $result
    }
}

function Get-TargetsRecursive {

    [CmdletBinding()]
    Param (
        [Parameter(ValueFromPipeline = $true)]
        [Microsoft.SqlServer.Management.Smo.Server] $server
    )

    $server
    $server.Databases
}

function Get-ConfSetting {
    [CmdletBinding()]
    param (
        $confFile,
        $section,
        $name,
        $defaultValue = $null
    )

    $inSection = $false

    switch -regex -file $confFile {
        "^\s*\[\s*(.+?)\s*\]" {
            $inSection = $matches[1] -eq $section
        }
        "^\s*$($name)\s*=\s*(.+?)\s*$" {
            if ($inSection) {
                return $matches[1]
            }
        }
    }

    return $defaultValue
}

try {
    Write-Verbose "Acquiring credentials"

    $login, $pwd = Get-Content '/var/opt/mssql/secrets/assessment' -Encoding UTF8NoBOM -TotalCount 2
    $securePassword = ConvertTo-SecureString $pwd -AsPlainText -Force
    $credential = New-Object System.Management.Automation.PSCredential ($login, $securePassword)
    $securePassword.MakeReadOnly()

    Write-Verbose "Acquired credentials"

    $serverInstance = '.'

    if (Test-Path /var/opt/mssql/mssql.conf) {
        $port = Get-ConfSetting /var/opt/mssql/mssql.conf network tcpport

        if (-not [string]::IsNullOrWhiteSpace($port)) {
            Write-Verbose "Using port $($port)"
            $serverInstance = "$($serverInstance),$($port)"
        }
    }

    # IMPORTANT: If the script is run in trusted environments and there is a prelogin handshake error,
    # add -TrustServerCertificate flag in the commands for $serverName, $hostName and Get-SqlInstance lines below.
    $serverName = (Invoke-SqlCmd -ServerInstance $serverInstance -Credential $credential -Query "SELECT @@SERVERNAME")[0]
    $hostName = (Invoke-SqlCmd -ServerInstance $serverInstance -Credential $credential -Query "SELECT HOST_NAME()")[0]

    # Invoke assessment and store results.
    # Replace 'ConvertTo-Json' with 'ConvertTo-Csv' to change output format.
    # Available output formats: JSON, CSV, XML.
    # Encoding parameter is optional.

    Get-SqlInstance -ServerInstance $serverInstance -Credential $credential -ErrorAction Stop
    | Get-TargetsRecursive
    | ForEach-Object { Write-Verbose "Invoke assessment on $($_.Urn)"; $_ }
    | Invoke-SqlAssessment 3>&1
    | ConvertTo-LogOutput
    | ConvertTo-Json -AsArray
    | Set-Content $outPath -Encoding UTF8NoBOM
}
finally {
    Write-Verbose "Error count: $($Error.Count)"

    if ($Error) {
        $Error
        | ForEach-Object { @{ 'TimeStamp' = $(Get-Date).ToString("O"); 'Message' = $_.ToString() } }
        | ConvertTo-Json -AsArray
        | Set-Content $errorPath -Encoding UTF8NoBOM
    }
}

注意

在受信任的环境中运行此脚本并收到预登录握手错误时，在代码的 $serverName、$hostName 和 Get-SqlInstance 代码行添加 -TrustServerCertificate 标志。

运行评估

确保脚本的所有者和执行者为 mssql。

chown mssql:mssql /opt/mssql/bin/runassessment.ps1
chmod 700 /opt/mssql/bin/runassessment.ps1

创建日志文件夹，并向 mssql 用户分配对文件夹的适当权限：

mkdir /var/opt/mssql/log/assessments/
chown mssql:mssql /var/opt/mssql/log/assessments/
chmod 0700 /var/opt/mssql/log/assessments/

现在可以创建你的第一个评估，但请确保以 mssql 用户身份创建，以便可以通过 cron 或 systemd 以更安全的方式自动运行后续评估。
```
su mssql -c "pwsh -File /opt/mssql/bin/runassessment.ps1"
```
命令完成后，将生成 JSON 格式的输出。此输出可以与支持分析 JSON 文件的任何工具集成。其中一种示例工具是 Red Hat Insights。

通过

使用适用于 Linux 上的 SQL Server 的 SQL 评估 API

Azure Data Studio 的 SQL 评估扩展（预览版）

启动 SQL 评估

将 SQL 评估 API 与 PowerShell 配合使用

先决条件

设置评估

下载评估脚本

运行评估

反馈

其他资源

通过

使用适用于 Linux 上的 SQL Server 的 SQL 评估 API

Azure Data Studio 的 SQL 评估扩展（预览版）

启动 SQL 评估

将 SQL 评估 API 与 PowerShell 配合使用

先决条件

设置评估

下载评估脚本

运行评估

相关内容

反馈

其他资源