SQL Server on Linux 向けの SQL Assessment API を使用する

[アーティクル]
07/15/2024

適用対象: SQL Server - Linux

SQL Assessment API には、ベストプラクティスのために SQL Server の構成を評価するメカニズムが用意されています。この API は、SQL Server チームが推奨するベストプラクティスルールを含むルールセットと共に配布されます。このルールセットは、新しいバージョンのリリースとともに強化されます。お使いの SQL Server の構成がベストプラクティスに沿っていることを確認すると役立ちます。

Microsoft から配布されるルールセットは、GitHub で入手できます。サンプルのリポジトリでルールセット全体を確認することができます。

この記事では、SQL Server on Linux とコンテナー向けの SQL Assessment API を実行する 2 つの方法について確認します。

Azure Data Studio の SQL Assessment 拡張機能 (プレビュー)
PowerShell を使用した SQL Assessment API

Azure Data Studio の SQL Assessment 拡張機能 (プレビュー)

Azure Data Studio の SQL Assessment 拡張機能 (プレビュー) には、ベストプラクティスのために SQL Server の構成を評価するメカニズムが用意されています。

このプレビューバージョンでは、次のことができます。

組み込みのルールを使用して、SQL Server、Azure SQL データベース、または Azure SQL Managed Instance とそのデータベースを評価する
インスタンスとそのデータベースに適用できるすべての組み込みルールのリストを取得する
評価結果と適用可能なルールのリストをスクリプトとしてエクスポートし、SQL テーブルに保存する
評価結果に関する HTML レポートを作成する

SQL Assessment を開始する

SQL Assessment 拡張機能をインストールしたら、サーバーのリストを展開し、評価するサーバーまたはデータベースを右クリックして、[管理] を選択します。
次に、[全般] セクションで [SQL Assessment] を選択します。 [評価] タブで、[評価の呼び出し] を選択して、選択した SQL Server または Azure SQL データベースの評価を実行します。結果が使用できるようになったら、フィルター処理機能と並べ替え機能を使うことができます。
[スクリプトとしてエクスポート] を選択して、テーブルへの挿入形式で結果を取得します。 [HTML レポートの作成] を選択して、評価結果を HTML ファイルとして保存することもできます。評価規則の中には、特定の SQL Server 構成を対象としているものと、それ以外のものを対象としているものもあります。データベースルールについても同様です。たとえば、SQL Server 2016 (13.x) または tempdb データベースにのみ適用されるルールがあります。
[適用可能なルールの表示] ボタンには、[評価の呼び出し] を選択した後に、サーバーとデータベースの評価を行うために使用される評価ルールが表示されます。 SQL Server と SQL Assessment API に関する情報を表示するには、[情報] を選択します。評価セッションの結果は、[履歴] タブで確認できます。

PowerShell を使用した SQL Assessment API

2 つ目のオプションは、PowerShell を使用して SQL Assessment API スクリプトを実行することです。

前提条件

Linux に PowerShell がインストールされていることを確認します。
mssql ユーザーとして実行している PowerShell ギャラリーから SqlServer PowerShell モジュールをインストールします。
```
su mssql -c "/usr/bin/pwsh -Command Install-Module SqlServer"
```

評価を設定する

SQL Assessment API の出力は JSON 形式で使用できます。 SQL Assessment 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 Assessment API を呼び出すサンプルスクリプトを次に示します。このスクリプトは、JSON 形式の出力ファイルを次の場所に生成します。/var/opt/mssql/log/assessments

Note

SQL Assessment 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
    }
}

Note

信頼された環境でこのスクリプトを実行して、プリログインハンドシェイクエラーが発生する場合は、コードの $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 です。

次の方法で共有

SQL Server on Linux 向けの SQL Assessment API を使用する

Azure Data Studio の SQL Assessment 拡張機能 (プレビュー)

SQL Assessment を開始する

PowerShell を使用した SQL Assessment API

前提条件

評価を設定する

評価スクリプトをダウンロードする

評価を実行する

フィードバック

その他のリソース

次の方法で共有

SQL Server on Linux 向けの SQL Assessment API を使用する

Azure Data Studio の SQL Assessment 拡張機能 (プレビュー)

SQL Assessment を開始する

PowerShell を使用した SQL Assessment API

前提条件

評価を設定する

評価スクリプトをダウンロードする

評価を実行する

関連するコンテンツ

フィードバック

その他のリソース