Usar a API de Avaliação do SQL para SQL Server em Linux

  • Artigo

Aplica-se a: SQL Server – Linux

A API de Avaliação do SQL fornece um mecanismo a fim de avaliar a configuração do SQL Server para obter as práticas recomendadas. A API é fornecida com um conjunto de regras que contém as melhores práticas recomendadas pela equipe do SQL Server. Esse conjunto de regras é aprimorado com o lançamento de novas versões. Certifique-se de que a configuração do SQL Server esteja alinhada com as melhores práticas recomendadas.

O conjunto de regras enviado pela Microsoft está disponível no GitHub. Veja o conjunto regras inteiro no repositório de exemplos.

Neste artigo, examinamos duas maneiras de executar a API de Avaliação do SQL para SQL Server em Linux e contêineres:

Extensão de Avaliação do SQL para o Azure Data Studio (versão prévia)

A extensão de Avaliação do SQL para o Azure Data Studio (versão prévia) fornece um mecanismo para avaliar a configuração do SQL Server para práticas recomendadas.

Com esta versão prévia, você pode:

  • Avaliar um banco de dados do SQL Server, SQL do Azure ou Instância Gerenciada de SQL do Azure e os respectivos bancos de dados, com regras internas
  • Obter uma lista de todas as regras internas aplicáveis a uma instância e os respectivos bancos de dados
  • Exportar resultados de avaliação e a lista de regras aplicáveis como um script para armazená-los em uma tabela SQL
  • Criar relatórios HTML nos resultados das avaliações

Captura de tela mostrando a extensão de Avaliação do SQL no Azure Data Studio.

Iniciar uma Avaliação do SQL

  • Depois de instalar a extensão de Avaliação do SQL, expanda sua lista de servidores, clique com o botão direito do mouse em um servidor ou banco de dados que deseja avaliar e selecione Gerenciar.
  • Em seguida, na seção Geral, selecione Avaliação do SQL. Na guia Avaliação, selecione Invocar Avaliação para executar a avaliação do banco de dados do SQL Server ou SQL do Azure selecionado. Depois que os resultados estiverem disponíveis, você poderá usar os recursos de filtragem e classificação.
  • Selecione Exportar como Script para obter os resultados em um formato de inserção na tabela. Você também pode selecionar Criar Relatório HTML para salvar os resultados da avaliação como um arquivo HTML. Algumas regras de avaliação destinam-se a configurações específicas do SQL Server, enquanto outras regras destinam-se a outras configurações. O mesmo vale para regras de banco de dados. Por exemplo, há regras aplicáveis somente ao SQL Server 2016 (13.x) ou ao banco de dados tempdb.
  • O botão Exibir regras aplicáveis exibe as regras de avaliação usadas para executar a avaliação de seus servidores e bancos de dados depois de selecionar Invocar Avaliação. Para exibir informações sobre o SQL Server e a API de Avaliação do SQL, selecione Informações. Os resultados da sessão de avaliação podem ser revisados na guia Histórico.

API de Avaliação do SQL com o PowerShell

Uma segunda opção é usar o PowerShell para executar o script da API de Avaliação do SQL.

Pré-requisitos

  1. Certifique-se de instalar o PowerShell no Linux.

  2. Instale o módulo SqlServer do PowerShell do Galeria do PowerShell, em execução como o usuário mssql.

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

Configurar a avaliação

A saída da API de Avaliação do SQL está disponível no formato JSON. Você precisará executar as seguintes etapas para configurar a API de Avaliação do SQL:

  1. Na instância que você deseja avaliar, crie um logon para avaliações de SQL Server usando a Autenticação do SQL. Você pode usar o script T-SQL (Transact-SQL) a seguir para criar um logon e uma senha forte. Substitua <secure_password> por uma senha forte de sua escolha.

    USE [master];
GO

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

    A função CONTROL SERVER funciona para a maioria das avaliações. No entanto, há algumas avaliações que podem precisar de privilégios sysadmin. Se você não estiver executando essas regras, recomendamos usar permissões CONTROL SERVER.

  2. Armazene as credenciais para logon no sistema da seguinte maneira, substituindo <secure_password> novamente pela senha usada na etapa anterior.

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

  3. Proteja as novas credenciais de avaliação garantindo que somente o usuário mssql possa acessar as credenciais.

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

Baixar o script de avaliação

Veja a seguir um script de exemplo que chama a API de Avaliação do SQL usando as credenciais criadas nas etapas anteriores. O script gerará um arquivo de saída no formato JSON neste local: /var/opt/mssql/log/assessments.

Observação

A API de Avaliação do SQL também pode gerar saída nos formatos CSV e XML.

O script está disponível para download no GitHub.

Você pode salvar esse arquivo como /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
    }
}

Observação

Quando você executar esse script em ambientes confiáveis e receber um erro de handshake de pré-logon, adicione o sinalizador -TrustServerCertificate nos comandos para as linhas $serverName,$hostName e Get-SqlInstance no código.

Executar a avaliação

  1. Verifique se o script é de propriedade e executável por mssql.

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

  2. Crie uma pasta de log e atribua permissões apropriadas ao usuário mssql na pasta :

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

  3. Agora você pode criar sua primeira avaliação, mas certifique-se de fazer isso como o usuário mssql, para que as avaliações subsequentes possam ser executadas automaticamente por meio de cron ou systemd com mais segurança.

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

  4. Depois que o comando for concluído, a saída será gerada no formato JSON. Essa saída pode ser integrada a qualquer ferramenta de terceiros que dê suporte à análise de arquivos JSON. Um exemplo dessas ferramentas é o RedHat Insights.

Conteúdo relacionado