針對 Linux 上的 SQL Server 使用 SQL 評定 API

發行項
03/11/2024

適用於：SQL Server - Linux

SQL 評定 API 會提供一種機制來評估您的 SQL Server 設定，以了解最佳作法。此 API 會搭配規則集提供，其中包含 SQL Server 小組建議的最佳作法。此規則集會隨著新版本的發行而增強。這有助於確保您的 SQL Server 設定符合建議的最佳作法。

GitHub 上會提供 Microsoft 隨附的規則集。您可以在範例存放庫中檢視整個規則集。

在本文中，我們會探討兩種方式，來針對 Linux 上的 SQL Server 和容器執行 SQL 評定 API：

適用於 Azure Data Studio 的 SQL 評定延伸模組 (預覽)
搭配 PowerShell 使用 SQL 評定 API

適用於 Azure Data Studio 的 SQL 評定延伸模組 (預覽)

適用於 Azure Data Studio 的 SQL 評定延伸模組 (預覽) 提供一種機制來評估 SQL Server 的設定是否為最佳做法。

使用此預覽版本，您可以：

使用內建規則評估 SQL Server、Azure SQL 資料庫或 Azure SQL 受控執行個體及其資料庫
取得適用於所有執行個體及其資料庫的所有內建規則清單
將評定結果和適用規則的清單匯出為指令碼，以將其儲存在 SQL 資料表中
建立評定結果的 HTML 報告

Screenshot showing the SQL Assessment extension in Azure Data Studio.

啟動 SQL 評定

安裝 SQL 評定延伸模組之後，請展開您的伺服器清單，以滑鼠右鍵按一下您要評估的伺服器或資料庫，然後選取 [管理]。
然後，在 [一般] 區段中，選取 [SQL 評定]。在 [評定] 索引標籤上，選取 [叫用評定] 以執行所選 SQL Server 或 Azure SQL 資料庫的評定。一旦結果可供使用，您就可以使用篩選和排序功能。
選取 [匯出為指令碼] 以取得「插入資料表」格式的結果。您也可以選取 [建立 HTML 報表]，將評定結果儲存為 HTML 檔案。某些評定規則適用於特定 SQL Server 設定，有些則適用於其他設定。資料庫規則也是如此。例如，某些規則僅適用於 SQL Server 2016 (13.x) 或 tempdb 資料庫。
在您選取 [叫用評定] 之後，[檢視適用規則] 按鈕會顯示用來執行伺服器和資料庫評定的規則。若要檢視 SQL Server 和 SQL 評定 API 的相關資訊，請選取 [資訊]。您可以在 [歷程記錄] 索引標籤上檢閱評定工作階段結果。

搭配 PowerShell 使用 SQL 評定 API

第二個選項是使用 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) 指令碼來建立登入和強式密碼。以您選擇的強式密碼取代 <*PASSWORD*>。
```
USE [master];
GO

CREATE LOGIN [assessmentLogin] WITH PASSWORD = N'<*PASSWORD*>';
ALTER SERVER ROLE [CONTROL SERVER] ADD MEMBER [assessmentLogin];
GO
```
CONTROL SERVER 角色適用於大部分的評定。不過，有幾個評定可能需要 sysadmin 權限。如果您未執行這些規則，建議您使用 CONTROL SERVER 權限。
將登入的認證儲存在系統上 (如下所示)，再次將 <*PASSWORD*> 取代為您在上一個步驟中使用的密碼。
```
echo "assessmentLogin" > /var/opt/mssql/secrets/assessment
echo "<*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 評定

搭配 PowerShell 使用 SQL 評定 API

必要條件

設定評定

下載評定指令碼

執行評定

意見反應

意見反應

其他資源

針對 Linux 上的 SQL Server 使用 SQL 評定 API

適用於 Azure Data Studio 的 SQL 評定延伸模組 (預覽)

啟動 SQL 評定

搭配 PowerShell 使用 SQL 評定 API

必要條件

設定評定

下載評定指令碼

執行評定

相關內容

意見反應

意見反應

其他資源