Cómo utilizar PowerShell para descubrir y documentar instancias de SQL Server

¿Quieres aprender a crear instancias de servidor y MSSQL con PowerShell? En el mundo de las TI, la gestión y el mantenimiento de las instancias de SQL Server es una tarea fundamental para los profesionales de TI y los proveedores de servicios gestionados (MSP). Saber cómo recopilar información de forma eficaz sobre las instancias de SQL Server puede mejorar significativamente las tareas administrativas y agilizar los procesos. Este post profundiza en un potente script de PowerShell diseñado para obtener una lista de instancias de servidor MSSQL, proporcionando información detallada y aplicaciones prácticas.

Contexto

Las instancias de SQL Server son el eje central de muchas aplicaciones empresariales, ya que almacenan y gestionan grandes cantidades de datos. Los profesionales de TI a menudo necesitan identificar y gestionar estas instancias para garantizar un rendimiento y una seguridad óptimos. El script PowerShell proporcionado es una herramienta muy valiosa para recuperar rápidamente información sobre las instancias de SQL Server en un sistema. Esta capacidad es crucial para tareas como auditorías, resolución de problemas y supervisión del sistema.

El script

#Requires -Version 5.1

<#
.SYNOPSIS
    Gets a list of MSSQL server instances and optionally save the results to a custom field.
.DESCRIPTION
    Gets a list of MSSQL server instances and optionally save the results to a custom field.
    The custom field can be either/both a multi-line or WYSIWYG custom field.

    SQL Server, SQL Server Developer and SQL Express are supported.

    SQL "Local" that are built into an application are not supported as they aren't an SQL Server instance.

    SQL service name that don't start with "MSSQL$" will not get detected.

    PS > Get-Service -Name "MSSQL`$*"
    Status   Name               DisplayName
    ------   ----               -----------
    Running  MSSQL$DB           SQL Server (DB)
    Running  MSSQL$DB01         SQL Server (DB01)
    Running  MSSQL$DB02         SQL Server (DB02)

.EXAMPLE
    (No Parameters)
    ## EXAMPLE OUTPUT WITHOUT PARAMS ##
     Status Name              Instance Path
     ------ ----              -------- ----
    Running SQL Server (DB01) DB01     C:\Program Files\Microsoft SQL Server\MSSQL16.DB01\MSSQL
    Running SQL Server (DB02) DB02     C:\Program Files\Microsoft SQL Server\MSSQL16.DB02\MSSQL

PARAMETER: -CustomFieldName "ReplaceMeWithAnyMultilineCustomField"
    Saves an text table to a multi-line Custom Field with a list of SQL instances.
.EXAMPLE
    -CustomFieldName "ReplaceMeWithAnyMultilineCustomField"
    ## EXAMPLE OUTPUT WITH CustomFieldName ##
     Status Name              Instance Path
     ------ ----              -------- ----
    Running SQL Server (DB01) DB01     C:\Program Files\Microsoft SQL Server\MSSQL16.DB01\MSSQL
    Running SQL Server (DB02) DB02     C:\Program Files\Microsoft SQL Server\MSSQL16.DB02\MSSQL

PARAMETER: -CustomFieldParam "ReplaceMeWithAnyWysiwygCustomField"
    Saves an html table to a Wysiwyg Custom Field with a list of SQL instances.
.EXAMPLE
    -WysiwygCustomFieldName "ReplaceMeWithAnyWysiwygCustomField"
    ## EXAMPLE OUTPUT WITH WysiwygCustomFieldName ##
     Status Name              Instance Path
     ------ ----              -------- ----
    Running SQL Server (DB01) DB01     C:\Program Files\Microsoft SQL Server\MSSQL16.DB01\MSSQL
    Running SQL Server (DB02) DB02     C:\Program Files\Microsoft SQL Server\MSSQL16.DB02\MSSQL
.OUTPUTS
    None
.NOTES
    Release Notes: Initial Release
By using this script, you indicate your acceptance of the following legal terms as well as our Terms of Use at https://www.ninjaone.com/terms-of-use.
    Ownership Rights: NinjaOne owns and will continue to own all right, title, and interest in and to the script (including the copyright). NinjaOne is giving you a limited license to use the script in accordance with these legal terms. 
    Use Limitation: You may only use the script for your legitimate personal or internal business purposes, and you may not share the script with another party. 
    Republication Prohibition: Under no circumstances are you permitted to re-publish the script in any script library or website belonging to or under the control of any other software provider. 
    Warranty Disclaimer: The script is provided “as is” and “as available”, without warranty of any kind. NinjaOne makes no promise or guarantee that the script will be free from defects or that it will meet your specific needs or expectations. 
    Assumption of Risk: Your use of the script is at your own risk. You acknowledge that there are certain inherent risks in using the script, and you understand and assume each of those risks. 
    Waiver and Release: You will not hold NinjaOne responsible for any adverse or unintended consequences resulting from your use of the script, and you waive any legal or equitable rights or remedies you may have against NinjaOne relating to your use of the script. 
    EULA: If you are a NinjaOne customer, your use of the script is subject to the End User License Agreement applicable to you (EULA).
#>

[CmdletBinding()]
param (
    [String]$CustomFieldName,
    [String]$WysiwygCustomFieldName
)

begin {
    function Test-IsElevated {
        $id = [System.Security.Principal.WindowsIdentity]::GetCurrent()
        $p = New-Object System.Security.Principal.WindowsPrincipal($id)
        $p.IsInRole([System.Security.Principal.WindowsBuiltInRole]::Administrator)
    }
    function Set-NinjaProperty {
        [CmdletBinding()]
        Param(
            [Parameter(Mandatory = $True)]
            [String]$Name,
            [Parameter()]
            [String]$Type,
            [Parameter(Mandatory = $True, ValueFromPipeline = $True)]
            $Value,
            [Parameter()]
            [String]$DocumentName
        )
    
        $Characters = $Value | Measure-Object -Character | Select-Object -ExpandProperty Characters
        if ($Characters -ge 10000) {
            throw [System.ArgumentOutOfRangeException]::New("Character limit exceeded, value is greater than 10,000 characters.")
        }
        
        # If we're requested to set the field value for a Ninja document we'll specify it here.
        $DocumentationParams = @{}
        if ($DocumentName) { $DocumentationParams["DocumentName"] = $DocumentName }
        
        # This is a list of valid fields that can be set. If no type is given, it will be assumed that the input doesn't need to be changed.
        $ValidFields = "Attachment", "Checkbox", "Date", "Date or Date Time", "Decimal", "Dropdown", "Email", "Integer", "IP Address", "MultiLine", "MultiSelect", "Phone", "Secure", "Text", "Time", "URL", "WYSIWYG"
        if ($Type -and $ValidFields -notcontains $Type) { Write-Warning "$Type is an invalid type! Please check here for valid types. https://ninjarmm.zendesk.com/hc/en-us/articles/16973443979789-Command-Line-Interface-CLI-Supported-Fields-and-Functionality" }
        
        # The field below requires additional information to be set
        $NeedsOptions = "Dropdown"
        if ($DocumentName) {
            if ($NeedsOptions -contains $Type) {
                # We'll redirect the error output to the success stream to make it easier to error out if nothing was found or something else went wrong.
                $NinjaPropertyOptions = Ninja-Property-Docs-Options -AttributeName $Name @DocumentationParams 2>&1
            }
        }
        else {
            if ($NeedsOptions -contains $Type) {
                $NinjaPropertyOptions = Ninja-Property-Options -Name $Name 2>&1
            }
        }
        
        # If an error is received it will have an exception property, the function will exit with that error information.
        if ($NinjaPropertyOptions.Exception) { throw $NinjaPropertyOptions }
        
        # The below type's require values not typically given in order to be set. The below code will convert whatever we're given into a format ninjarmm-cli supports.
        switch ($Type) {
            "Checkbox" {
                # While it's highly likely we were given a value like "True" or a boolean datatype it's better to be safe than sorry.
                $NinjaValue = [System.Convert]::ToBoolean($Value)
            }
            "Date or Date Time" {
                # Ninjarmm-cli expects the GUID of the option to be selected. Therefore, the given value will be matched with a GUID.
                $Date = (Get-Date $Value).ToUniversalTime()
                $TimeSpan = New-TimeSpan (Get-Date "1970-01-01 00:00:00") $Date
                $NinjaValue = $TimeSpan.TotalSeconds
            }
            "Dropdown" {
                # Ninjarmm-cli is expecting the guid of the option we're trying to select. So we'll match up the value we were given with a guid.
                $Options = $NinjaPropertyOptions -replace '=', ',' | ConvertFrom-Csv -Header "GUID", "Name"
                $Selection = $Options | Where-Object { $_.Name -eq $Value } | Select-Object -ExpandProperty GUID
        
                if (-not $Selection) {
                    throw [System.ArgumentOutOfRangeException]::New("Value is not present in dropdown")
                }
        
                $NinjaValue = $Selection
            }
            default {
                # All the other types shouldn't require additional work on the input.
                $NinjaValue = $Value
            }
        }
        
        # We'll need to set the field differently depending on if its a field in a Ninja Document or not.
        if ($DocumentName) {
            $CustomField = Ninja-Property-Docs-Set -AttributeName $Name -AttributeValue $NinjaValue @DocumentationParams 2>&1
        }
        else {
            $CustomField = Ninja-Property-Set -Name $Name -Value $NinjaValue 2>&1
        }
        
        if ($CustomField.Exception) {
            throw $CustomField
        }
    }
    if ($env:multilineCustomFieldName -and $env:multilineCustomFieldName -notlike "null") {
        $CustomFieldName = $env:multilineCustomFieldName
    }
    if ($env:WysiwygCustomFieldName -and $env:WysiwygCustomFieldName -notlike "null") {
        $WysiwygCustomFieldName = $env:WysiwygCustomFieldName
    }
}
process {
    if (-not (Test-IsElevated)) {
        Write-Error -Message "Access Denied. Please run with Administrator privileges."
        exit 1
    }

    try {
        $InstanceNames = $(Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\" -ErrorAction Stop).InstalledInstances
        $SqlInstances = $InstanceNames | ForEach-Object {
            $SqlPath = $(Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$_\Setup" -ErrorAction Stop).SQLPath
            $SqlServices = Get-Service -Name "MSSQL`$$_" -ErrorAction Stop
            $SqlService = $SqlServices | Where-Object { $_.Name -notlike $SqlServices.DependentServices.Name -and $_.Name -notlike "SQLTelemetry*" }
            [PSCustomObject]@{
                Status   = $SqlService.Status
                Service  = $SqlService.DisplayName
                Instance = $_
                Path     = $SqlPath
            }
        }
    }
    catch {
        Write-Host "[Error] $($_.Message)"
        Write-Host "[Info] Likely no MSSQL instance found."
        exit 1
    }

    $SqlInstances | Out-String | Write-Host

    if ($CustomFieldName) {
        Write-Host "Attempting to set Custom Field '$CustomFieldName'."
        Set-NinjaProperty -Name $CustomFieldName -Value ($SqlInstances | Out-String)
        Write-Host "Successfully set Custom Field '$CustomFieldName'!"
    }

    if ($WysiwygCustomFieldName) {
        try {
            Write-Host "Attempting to set Custom Field '$WysiwygCustomFieldName'."
            $htmlReport = New-Object System.Collections.Generic.List[String]
            $htmlReport.Add("<h1>SQL Server Instances</h1>")
            $htmlTable = $SqlInstances | ConvertTo-Html -Fragment 
            $htmlTable = $htmlTable -replace "<tr><td>Running</td>", '<tr class="success"><td>Running</td>'
            $htmlTable = $htmlTable -replace "<tr><td>StartPending</td>", '<tr class="other"><td>StartPending</td>'
            $htmlTable = $htmlTable -replace "<tr><td>ContinuePending</td>", '<tr class="other"><td>ContinuePending</td>'
            $htmlTable = $htmlTable -replace "<tr><td>Paused</td>", '<tr class="other"><td>Paused</td>'
            $htmlTable = $htmlTable -replace "<tr><td>PausePending</td>", '<tr class="other"><td>PausePending</td>'
            $htmlTable = $htmlTable -replace "<tr><td>Stopped</td>", '<tr class="danger"><td>Stopped</td>'
            $htmlTable = $htmlTable -replace "<tr><td>StopPending</td>", '<tr class="danger"><td>StopPending</td>'
            $htmlTable | ForEach-Object { $htmlReport.Add($_) }
            Set-NinjaProperty -Name $WysiwygCustomFieldName -Value ($htmlReport | Out-String)
            Write-Host "Successfully set Custom Field '$WysiwygCustomFieldName'!"
        }
        catch {
            Write-Error $_
            Write-Host "[Error] $($_.Message)"
            exit 1
        }
    }
    exit 0
}
end {
    
    
    
}

Análisis detallado

El script comienza comprobando si se ejecuta con privilegios de administrador, necesarios para acceder a ciertas propiedades del sistema. Define una función, Test-IsElevated, para comprobarlo. Si el script no se ejecuta como administrador, sale con un error.

A continuación, intenta recuperar del Registro de Windows las instancias de SQL Server instaladas. Esto se consigue mediante el cmdlet Get-ItemProperty, que accede a la ruta de registro correspondiente. A continuación, el script itera a través de cada instancia, obteniendo detalles como el nombre de la instancia, el estado del servicio y la ruta de instalación.

La información recopilada se estructura en un objeto personalizado y es visible. Si se especifica, los resultados también pueden guardarse en campos personalizados, como texto sin formato o HTML, utilizando la función Set-NinjaProperty. Esta función maneja varios tipos de datos y garantiza que los valores se ajusten a los límites de caracteres y otras restricciones.

Posibles casos de uso

Piensa en un profesional de TI que gestiona varios servidores en una organización. Utilizando este script, pueden reunir y documentar rápidamente todas las instancias de SQL Server que se ejecutan en cada servidor. Esta información es vital para tareas como actualizar el software, realizar auditorías de seguridad y planificar la capacidad. Por ejemplo, durante un ciclo de actualización, conocer todas las instancias activas ayuda a programar y minimizar el tiempo de inactividad.

Comparaciones

Este script de PowerShell ofrece una forma simplificada y automatizada de recopilar información sobre instancias de SQL Server en comparación con los métodos manuales. Tradicionalmente, los administradores podían utilizar SQL Server Management Studio (SSMS) o comprobar manualmente los servicios a través de la consola Servicios de Windows. Aunque eficaces, estos métodos requieren mucho tiempo y son propensos a errores humanos. En cambio, el script proporciona un proceso coherente y repetible que puede integrarse fácilmente en flujos de trabajo de gestión de TI más amplios.

FAQ

P: ¿Puede este script detectar instancias de SQL Server instaladas como parte de una aplicación?

R: No, el script está diseñado para detectar instancias estándar de SQL Server. No se admiten instancias incrustadas dentro de aplicaciones.

P: ¿Qué ocurre si el nombre de un campo personalizado supera el límite de caracteres?

R: El script incluye una comprobación para garantizar que los valores no superan los 10.000 caracteres. Si se supera este límite, se produce un error.

P: ¿Se puede ejecutar este script en cualquier versión de PowerShell?

R: El script requiere PowerShell versión 5.1 o superior.

Implicaciones

La ejecución de este script proporciona a los profesionales de TI información detallada sobre las implantaciones de SQL Server en su infraestructura. Esta información es fundamental para garantizar el cumplimiento de la normativa, optimizar el rendimiento y mejorar la seguridad. Al saber exactamente dónde se ejecutan las instancias de SQL Server, los administradores pueden proteger mejor los datos confidenciales y asegurarse de que los sistemas están correctamente configurados y mantenidos.

Recomendaciones

• Ejecutar como Administrador: asegúrate de que el script se ejecuta con los privilegios necesarios para acceder a la información del sistema.

• Auditorías periódicas: programa ejecuciones regulares del script para mantener un inventario actualizado de las instancias de SQL Server.

• Integración con herramientas de supervisión: considera la posibilidad de integrar la salida del script con herramientas de supervisión y documentación para agilizar la gestión del sistema.

Reflexiones finales

El script de PowerShell de la que se habla en este artículo es una potente herramienta para los profesionales de TI encargados de gestionar las instancias de SQL Server. Al automatizar el proceso de descubrimiento, ahorra tiempo y reduce el riesgo de errores, lo que lo convierte en un complemento esencial de cualquier conjunto de herramientas informáticas. Herramientas como NinjaOne pueden mejorar aún más este proceso, proporcionando una gestión robusta y capacidades de monitoreo, asegurando que tus instancias de SQL Server estén siempre bajo control y seguras.