Condividi tramite

Usare l'API Valutazione SQL per SQL Server in Linux

  • Articolo

Si applica a:SQL Server - Linux

L'API Valutazione SQL offre uno strumento per valutare la configurazione dell'istanza di SQL Server in uso in base alle procedure consigliate. L'API viene fornita con un set di regole contenente le procedure consigliate dal team di SQL Server. Questo set di regole è stato migliorato con il rilascio di nuove versioni. È utile quando ci si vuole assicurare che la configurazione dell'istanza di SQL Server in uso sia conforme alle procedure consigliate.

Il set di regole Microsoft è disponibile in GitHub. È possibile visualizzare l'intero set di regole nel repository degli esempi.

Questo articolo descrive due modalità per eseguire l'API Valutazione SQL per SQL Server in Linux e i contenitori.

Estensione della Valutazione SQL per Azure Data Studio (anteprima)

L'estensione della Valutazione SQL per Azure Data Studio (anteprima) offre uno strumento per valutare la configurazione dell'istanza di SQL Server in uso in base alle procedure consigliate.

Questa versione di anteprima permette di:

  • Valutare un SQL Server, un database SQL di Azure o un Istanza gestita di SQL di Azure e i relativi database con regole predefinite
  • Ottenere un elenco di tutte le regole predefinite applicabili a un'istanza e ai relativi database
  • Esportare i risultati della valutazione e l'elenco delle regole applicabili come script per archiviarlo in una tabella SQL
  • Creare report HTML sui risultati delle valutazioni

Screenshot showing the SQL Assessment extension in Azure Data Studio.

Avviare la valutazione

  • Dopo aver installato l'estensione SQL Assessment, espandere l'elenco dei server, fare clic con il pulsante destro del mouse su un server o un database da valutare e selezionare Gestisci.
  • Quindi, nella sezione Generale selezionare Valutazione SQL. Nella scheda Valutazione selezionare Invoke Assessment (Richiama valutazione) per eseguire la valutazione del database SQL Server o SQL di Azure selezionato. Una volta che i risultati sono disponibili, è possibile usare le funzionalità di filtro e ordinamento.
  • Selezionare Export as Script (Esporta come script) per ottenere i risultati in un formato insert-into-table. È anche possibile selezionare Create HTML Report (Crea report HTML) per salvare i risultati della valutazione come file HTML. Alcune regole di valutazione sono destinate a configurazioni specifiche di SQL Server e alcune per altre. Lo stesso vale per le regole del database. Ad esempio, esistono regole applicabili solo a SQL Server 2016 (13.x) o al database tempdb.
  • Il pulsante View applicable rules (Visualizza regole applicabili) visualizza le regole di valutazione usate per eseguire la valutazione dei server e dei database dopo aver selezionato Invoke Assessment (Richiama valutazione). Per visualizzare informazioni su SQL Server e l'API Valutazione SQL, selezionare Info. I risultati della sessione di valutazione possono essere esaminati nella scheda Cronologia.

API Valutazione SQL con PowerShell

Una seconda opzione consiste nell'usare PowerShell per eseguire lo script dell'API Valutazione SQL.

Prerequisiti

  1. Verificare che sia stato installato PowerShell su Linux.

  2. Installare il modulo PowerShell SqlServer dalla PowerShell Gallery, in esecuzione come utente mssql.

    su mssql -c "/usr/bin/pwsh -Command Install-Module SqlServer"

Configurare la valutazione

L'output dell'API Valutazione SQL è disponibile in formato JSON. Per configurare l'API Valutazione SQL, è necessario seguire la procedura seguente:

  1. Nell'istanza che si desidera valutare, creare un account di accesso per le valutazioni di SQL Server usando l'autenticazione SQL. È possibile usare il seguente script Transact-SQL (T-SQL) per creare un account di accesso e una password complessa. Sostituire <*PASSWORD*> con una password complessa a scelta.

    USE [master];
GO

CREATE LOGIN [assessmentLogin] WITH PASSWORD = N'<*PASSWORD*>';
ALTER SERVER ROLE [CONTROL SERVER] ADD MEMBER [assessmentLogin];
GO

    Il ruolo CONTROL SERVER funziona per la maggior parte delle valutazioni. Esistono tuttavia alcune valutazioni che potrebbero richiedere privilegi sysadmin. Se non si eseguono queste regole, si consiglia di usare le autorizzazioni CONTROL SERVER.

  2. Archiviare le credenziali per l'accesso nel sistema come indicato di seguito, ancora una volta sostituendo <*PASSWORD*> con la password usata nel passaggio precedente.

    echo "assessmentLogin" > /var/opt/mssql/secrets/assessment
echo "<*PASSWORD*>" >> /var/opt/mssql/secrets/assessment

  3. Proteggere le nuove credenziali di valutazione assicurandosi che solo l'utente mssql possa accedere alle credenziali.

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

Scaricare lo script di valutazione

Di seguito è riportato un campione di script che chiama l'API Valutazione SQL usando le credenziali create nei passaggi precedenti. Lo script genererà un file di output in formato JSON in questo percorso: /var/opt/mssql/log/assessments.

Nota

L'API Valutazione SQL può anche generare output nei formati CSV e XML.

Questo script è disponibile per il download da GitHub.

È possibile salvare questo file come /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
    }
}

Nota

Quando si esegue lo script precedente in ambienti attendibili e viene visualizzato un errore di handshake prelogin, aggiungere il flag -TrustServerCertificate nei comandi per le righe $serverName, $hostName e Get-SqlInstance nel codice.

Eseguire la valutazione

  1. Assicurarsi che lo script sia di proprietà ed eseguibile da mssql.

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

  2. Creare una cartella di log e assegnare autorizzazioni appropriate all'utente mssql nella cartella:

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

  3. È ora possibile creare la prima valutazione, ma occorre assicurarsi di farlo come utente mssql, affinché le valutazioni successive possano essere eseguite automaticamente tramite cron o systemd in modo più sicuro.

    su mssql -c "pwsh -File /opt/mssql/bin/runassessment.ps1"

  4. Una volta completato il comando, l'output verrà generato in formato JSON. Questo output può essere integrato con qualsiasi strumento di terzi che supporta l'analisi dei file JSON. Uno di questi strumenti di esempio è Red Hat Insights.

Contenuto correlato