Uso de SQL Assessment API para SQL Server en Linux

  • Artículo

Se aplica a:SQL Server: Linux

SQL Assessment API ofrece un mecanismo para evaluar la configuración de SQL Server a efectos de los procedimientos recomendados. La API se entrega con un conjunto de reglas que contiene los procedimientos recomendados por el equipo de SQL Server. Este conjunto de reglas se ha mejorado con el lanzamiento de nuevas versiones. Es útil para asegurarse de que la configuración de SQL Server esté en consonancia con los procedimientos recomendados.

El conjunto de reglas de Microsoft está disponible en GitHub. Puede ver todo el conjunto de reglas en el repositorio de ejemplos.

En este artículo, se verán dos maneras de ejecutar SQL Assessment API para SQL Server en Linux y contenedores:

Extensión SQL Assessment para Azure Data Studio (versión preliminar)

La extensión SQL Assessment para Azure Data Studio (versión preliminar) proporciona un mecanismo a fin de evaluar la configuración de SQL Server según los procedimientos recomendados.

Con esta versión preliminar, puede hacer lo siguiente:

  • Evaluar una base de datos de SQL Server, Azure SQL o Azure SQL Managed Instance y sus bases de datos, con reglas integradas
  • Obtener una lista de todas las reglas integradas aplicables a una instancia y sus bases de datos
  • Exportar los resultados de la valoración y la lista de reglas aplicables como un script para almacenarlos en una tabla SQL
  • Crear informes HTML sobre los resultados de las valoraciones

Screenshot showing the SQL Assessment extension in Azure Data Studio.

Inicio de una instancia de SQL Assessment

  • Después de instalar la extensión SQL Assessment, expanda la lista de servidores, haga clic con el botón derecho en un servidor o base de datos que quiera evaluar y seleccione Administrar.
  • Después, en la sección General, seleccione SQL Assessment. En la pestaña Valoración, seleccione Invocar valoración para realizar la valoración de la instancia de SQL Server o la base de datos de Azure SQL seleccionada. Una vez que estén disponibles los resultados, puede usar las características de filtrado y ordenación.
  • Seleccione Exportar como script para obtener los resultados en un formato de insertar en tabla. También puede seleccionar Crear informe HTML para guardar los resultados de la valoración como un archivo HTML. Algunas reglas de valoración están pensadas para configuraciones de SQL Server específicas y otras para otras. Lo mismo sucede con las reglas de base de datos. Por ejemplo, hay reglas que solo son aplicables a SQL Server 2016 (13.x) o a la base de datos tempdb.
  • El botón Ver reglas aplicables muestra las reglas de valoración que se usan para realizar la valoración de los servidores y bases de datos después de seleccionar Invocar valoración. Para ver información sobre SQL Server y SQL Assessment API, seleccione Información. Los resultados de la sesión de valoración se pueden revisar en la pestaña Historial.

SQL Assessment API con PowerShell

Una segunda opción consiste en usar PowerShell para ejecutar el script de SQL Assessment API.

Requisitos previos

  1. Asegúrese de que ha instalado PowerShell en Linux.

  2. Instale el módulo de PowerShell SqlServer desde la Galería de PowerShell, como usuariomssql.

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

Configuración de la valoración

La salida de SQL Assessment API está disponible en formato JSON. Tendrá que seguir estos pasos para configurar SQL Assessment API de la siguiente manera:

  1. En la instancia que quiera evaluar, cree un inicio de sesión para valoraciones de SQL Server mediante la autenticación de SQL. Puede usar el siguiente script de Transact-SQL (T-SQL) para crear un inicio de sesión y una contraseña segura. Reemplace por <*PASSWORD*> una contraseña segura de su elección.

    USE [master];
GO

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

    El rol CONTROL SERVER funciona para la mayoría de las valoraciones. Pero es posible que algunas necesiten privilegios sysadmin. Si no ejecuta esas reglas, se recomienda usar permisos CONTROL SERVER.

  2. Almacene las credenciales de inicio de sesión en el sistema como se indica a continuación, y vuelva a reemplazar <*PASSWORD*> por la contraseña que ha usado en el paso anterior.

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

  3. Para proteger las nuevas credenciales de valoración, asegúrese de que solo el usuario mssql pueda acceder a las credenciales.

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

Descarga del script de valoración

A continuación se muestra un script de ejemplo que llama a SQL Assessment API mediante las credenciales creadas en los pasos anteriores. El script generará un archivo de salida en formato JSON en esta ubicación: /var/opt/mssql/log/assessments.

Nota

SQL Assessment API también puede generar resultados en formatos CSV y XML.

El script está disponible para su descarga desde GitHub.

Puede guardar este archivo 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
    }
}

Nota:

Al ejecutar el script anterior en entornos de confianza y obtener un error handshake de prelogin, agregue la marca -TrustServerCertificate en los comandos para las líneas $serverName, $hostName y Get-SqlInstance en el código.

Ejecución de la valoración

  1. Asegúrese de que el script sea propiedad de mssql y que lo pueda ejecutar.

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

  2. Cree una carpeta de registro y asigne los permisos adecuados al usuario mssql en la carpeta:

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

  3. Ya puede crear la primera valoración, pero asegúrese de hacerlo como usuario mssql, para que las valoraciones posteriores se puedan ejecutar automáticamente mediante cron o systemd de forma más segura.

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

  4. Una vez que se complete el comando, la salida se generará en formato JSON. Esta salida se puede integrar con cualquier herramienta de terceros que admita el análisis de archivos JSON. Una de estas herramientas de ejemplo es RedHat Insights.

Contenido relacionado