Menggunakan SQL Assessment API untuk SQL Server di Linux

Berlaku untuk:SQL Server di Linux

SQL Assessment API menyediakan mekanisme untuk mengevaluasi konfigurasi SQL Server guna mendapatkan praktik terbaik. API dikirimkan dengan seperangkat aturan yang berisi praktik terbaik yang direkomendasikan oleh tim SQL Server. Seperangkat aturan ini ditingkatkan dengan rilis versi baru. Sangat berguna untuk memastikan konfigurasi SQL Server Anda sejalan dengan praktik terbaik yang direkomendasikan.

Set aturan yang dikirimkan Microsoft tersedia di GitHub. Anda dapat melihat seluruh set aturan di repositori sampel.

Dalam artikel ini, gunakan PowerShell untuk menjalankan SQL Assessment API for SQL Server on Linux dan container.

Prasyarat

  1. Pastikan Anda menginstal PowerShell di Linux.

  2. Instal modul PowerShell dari PowerShell Gallery sambil menjalankan sebagai pengguna SqlServer.

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

Menyiapkan penilaian

SQL Assessment API menyediakan output dalam format JSON. Untuk mengonfigurasi SQL Assessment API, selesaikan langkah-langkah berikut:

  1. Buat login untuk penilaian SQL Server menggunakan Autentikasi SQL pada instance yang ingin Anda nilai. Gunakan skrip Transact-SQL berikut untuk membuat login dan kata sandi yang kuat. Kata sandi Anda harus mengikuti kebijakan kata sandi default SQL Server. Secara default, kata sandi harus panjangnya minimal delapan karakter dan berisi karakter dari tiga dari empat set berikut: huruf besar, huruf kecil, digit dasar-10, dan simbol. Panjang kata sandi bisa hingga 128 karakter. Gunakan kata sandi yang panjang dan kompleks mungkin.

    USE [master];
GO

CREATE LOGIN [assessmentLogin]
    WITH PASSWORD = N'<password>';

GRANT CONTROL SERVER TO [assessmentLogin];
GO

    Izin CONTROL SERVER ini berlaku untuk sebagian besar penilaian. Namun, beberapa penilaian mungkin memerlukan hak istimewa sysadmin . Jika Anda tidak menjalankan penilaian tersebut, gunakan izin CONTROL SERVER.

  2. Simpan kredensial untuk terhubung ke instance. Ganti <password> dengan kata sandi yang Anda gunakan pada langkah sebelumnya.

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

  3. Amankan kredensial penilaian baru dengan memastikan bahwa hanya pengguna yang mssql dapat mengakses kredensial.

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

Mengunduh skrip penilaian

Contoh skrip berikut memanggil SQL Assessment API menggunakan kredensial yang Anda buat pada langkah-langkah sebelumnya. Skrip menghasilkan output JSON di /var/opt/mssql/log/assessments direktori.

Catatan

API Penilaian SQL juga dapat menghasilkan output dalam format CSV dan XML.

Skrip ini tersedia untuk diunduh dari GitHub.

Anda dapat menyimpan file ini sebagai /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
    }
}

Catatan

Saat Anda menjalankan skrip ini di lingkungan tepercaya, dan mendapatkan kesalahan handshake prelogin, tambahkan flag -TrustServerCertificate pada perintah di baris $serverName, $hostName, dan Get-SqlInstance dalam kode.

Jalankan penilaian

  1. Pastikan mssql pengguna memiliki skrip dan dapat menjalankannya.

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

  2. Buat folder log dan berikan mssql izin kepada pengguna untuk folder tersebut.

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

  3. Sekarang Anda dapat membuat penilaian pertama Anda sebagai pengguna mssql. Menjalankan penilaian sebagai pengguna ini lebih aman, terutama jika Anda menjalankan penilaian selanjutnya secara otomatis melalui cron atau systemd.

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

  4. Ketika perintah selesai, output dibuat dalam format JSON. Anda dapat menggunakan output ini dengan alat apa pun yang mendukung parsing file JSON, termasuk Visual Studio Code.

Konten terkait

  • Terakhir diperbarui pada