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 untuk 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.

Seperangkat aturan yang dikirim Microsoft tersedia di GitHub. Anda dapat melihat seluruh set aturan di repositori sampel.

Dalam artikel ini, kita melihat dua cara untuk menjalankan SQL Assessment API untuk SQL Server di Linux dan kontainer:

• Ekstensi Penilaian SQL untuk Azure Data Studio (Pratinjau)
• API Penilaian SQL dengan PowerShell

Ekstensi Penilaian SQL untuk Azure Data Studio (pratinjau)

Ekstensi Penilaian SQL untuk Azure Data Studio (pratinjau) menyediakan mekanisme untuk mengevaluasi konfigurasi SQL Server untuk praktik terbaik.

Dengan versi pratinjau ini, Anda dapat:

• Menilai SQL Server, database Azure SQL, atau Azure SQL Managed Instance beserta database-databasenya dengan aturan bawaan.
• Mendapatkan daftar semua aturan bawaan yang berlaku untuk instans dan databasenya
• Mengekspor hasil penilaian dan daftar aturan yang berlaku sebagai skrip untuk menyimpannya dalam tabel SQL
• Membuat laporan HTML pada hasil penilaian

Memulai Penilaian SQL

• Setelah Anda menginstal ekstensi Penilaian SQL, perluas daftar server Anda, klik kanan server atau database yang ingin Anda nilai, dan pilih Kelola.
• Kemudian, di bagian Umum, pilih Penilaian SQL. Pada tab Penilaian, pilih Panggil Penilaian untuk melakukan penilaian database SQL Server atau Azure SQL yang dipilih. Setelah hasilnya tersedia, Anda dapat menggunakan fitur pemfilteran dan pengurutan.
• Pilih Ekspor sebagai Skrip untuk mendapatkan hasil dalam format insert-into-table. Anda juga dapat memilih Buat Laporan HTML untuk menyimpan hasil penilaian sebagai file HTML. Beberapa aturan penilaian ditujukan untuk konfigurasi SQL Server tertentu dan beberapa untuk yang lain. Hal yang sama berlaku untuk aturan database. Misalnya, ada aturan yang hanya berlaku untuk SQL Server 2016 (13.x) atau tempdb database.
• Tombol Tampilkan aturan yang berlaku menampilkan aturan penilaian yang digunakan untuk melakukan penilaian server dan database Anda setelah Anda memilih Panggil Penilaian. Untuk melihat informasi tentang SQL Server dan SQL Assessment API, pilih Info. Hasil sesi penilaian dapat ditinjau pada tab Riwayat.

API Penilaian SQL dengan PowerShell

Opsi kedua adalah menggunakan PowerShell untuk menjalankan skrip SQL Assessment API.

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

Output SQL Assessment API tersedia dalam format JSON. Anda harus mengambil langkah-langkah berikut untuk mengonfigurasi SQL Assessment API sebagai berikut:

1. Dalam instans yang ingin Anda nilai, buat login untuk penilaian SQL Server menggunakan Autentikasi SQL. Anda dapat menggunakan skrip Transact-SQL (T-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>';
   
   ALTER SERVER ROLE [CONTROL SERVER] ADD MEMBER [assessmentLogin];
   GO
   

   Peran ini CONTROL SERVER berfungsi untuk sebagian besar penilaian. Namun, ada beberapa penilaian yang mungkin membutuhkan hak istimewa sysadmin . Jika Anda tidak menjalankan aturan tersebut, sebaiknya gunakan CONTROL SERVER hak akses.

2. Simpan kredensial untuk masuk pada sistem sebagai berikut, ganti lagi <password> dengan kata sandi yang Anda gunakan di 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

Berikut ini adalah contoh skrip yang memanggil SQL Assessment API, menggunakan kredensial yang dibuat pada langkah-langkah sebelumnya. Skrip menghasilkan file output dalam format JSON di lokasi ini: /var/opt/mssql/log/assessments.

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 Anda mendapatkan kesalahan jabat tangan pra-login, tambahkan flag -TrustServerCertificate dalam perintah untuk baris $serverName, $hostName, dan Get-SqlInstance dalam kode.

Jalankan penilaian

1. Pastikan skrip dimiliki dan dapat dieksekusi oleh mssql.

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

2. Buat folder log dan tetapkan mssql izin yang sesuai untuk pengguna pada folder:

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

3. Anda sekarang dapat membuat penilaian pertama Anda, tetapi pastikan Anda melakukannya sebagai mssql pengguna, sehingga penilaian berikutnya dapat dijalankan secara otomatis melalui cron atau systemd lebih aman.

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

4. Setelah perintah selesai, output dihasilkan dalam format JSON. Output ini dapat diintegrasikan dengan alat apa pun yang mendukung penguraian file JSON. Salah satu alat contoh tersebut adalah Red Hat Insights.

Konten terkait

• API Penilaian SQL
• Penilaian praktik terbaik SQL untuk SQL Server di Komputer Virtual Azure
• Penilaian kerentanan untuk SQL Server