Come utilizzare PowerShell per scoprire e documentare le istanze di SQL Server

Vuoi imparare a recuperare e documentare le istanze di SQL server con PowerShell? Nel mondo IT, la gestione e la manutenzione delle istanze di SQL Server è un compito fondamentale per i professionisti IT e i Managed Service Provider (MSP). Sapere come raccogliere in modo efficiente le informazioni sulle istanze di SQL Server può migliorare notevolmente le attività amministrative e snellire i processi. In questo articolo analizzeremo un potente script PowerShell progettato per ottenere un elenco di istanze del server MSSQL, e forniremo informazioni dettagliate sullo script e sue applicazioni pratiche.

Background

Le istanze di SQL Server sono la spina dorsale di molte applicazioni aziendali, in quanto memorizzano e gestiscono grandi quantità di dati. I professionisti IT devono spesso identificare e gestire queste istanze per garantire prestazioni e sicurezza ottimali. Lo script PowerShell fornito è uno strumento prezioso per recuperare rapidamente le informazioni sulle istanze di SQL Server su un sistema. Questa capacità è fondamentale per attività quali audit, risoluzione dei problemi e monitoraggio del sistema.

Lo script per documentare le istanze di SQL Server:

#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 {
    
    
    
}

 

Analisi dettagliata

Lo script per documentare le istanze di SQL Server inizia controllando se viene eseguito con i privilegi di amministratore, necessari per accedere ad alcune proprietà del sistema. Definisce una funzione, Test-IsElevated, per verificarlo. Se lo script non è eseguito come amministratore, esce con un errore.

Successivamente, lo script tenta di recuperare le istanze di SQL Server installate dal Registro di Windows. Lo fa con il cmdlet Get-ItemProperty, che accede al percorso di registro pertinente. Lo script esegue quindi l’iterazione di ogni istanza, recuperando dettagli come il nome dell’istanza, lo stato del servizio e il percorso di installazione.

Le informazioni raccolte vengono strutturate in un oggetto personalizzato e visualizzate. Se specificato, i risultati possono anche essere salvati in campi personalizzati, come plain text o HTML, utilizzando la funzione Set-NinjaProperty. Questa funzione gestisce vari tipi di dati e garantisce che i valori siano conformi ai limiti di caratteri e ad altri vincoli.

Casi d’uso potenziali

Immagina un professionista IT che gestisce più server in un’organizzazione. Utilizzando questo script per documentare le istanze di SQL Server, può raccogliere e documentare rapidamente tutte le istanze di SQL Server in esecuzione su ciascun server. Queste informazioni sono fondamentali per attività quali l’aggiornamento del software, l’esecuzione di audit di sicurezza e la pianificazione delle capacità. Per esempio, durante un ciclo di aggiornamento, conoscere tutte le istanze attive aiuta a pianificare e a ridurre al minimo i tempi di inattività.

Confronti

Questo script PowerShell offre un metodo semplificato e automatizzato, rispetto ai metodi manuali, per raccogliere informazioni e per documentare le istanze di SQL Server. Altri metodi con cui gli amministratori possono farlo sono l’utilizzo di SQL Server Management Studio (SSMS) o il controllo manualmente dei servizi tramite la console dei servizi di Windows. Pur essendo efficaci, questi metodi richiedono molto tempo e sono soggetti a errori umani. Lo script qui analizzato invece fornisce un processo coerente e ripetibile che può essere facilmente integrato in flussi di lavoro più ampi di gestione IT.

Domande frequenti

D: Questo script per documentare le istanze di SQL Server può rilevare le istanze di SQL Server installate come parte di un’applicazione?

R: No, lo script è progettato per rilevare le istanze SQL Server standard. Le istanze incorporate nelle applicazioni non sono supportate.

D: Cosa succede se il nome di un campo personalizzato supera il limite di caratteri?

R: Lo script per documentare le istanze di SQL Server include un controllo per garantire che i valori non superino i 10.000 caratteri. Se questo limite viene superato, viene lanciato un errore.

D: Questo script per documentare le istanze di SQL Server può essere eseguito su qualsiasi versione di PowerShell?

R: Lo script per documentare le istanze di SQL Server richiede PowerShell versione 5.1 o successive.

Implicazioni

L’esecuzione di questo script fornisce ai professionisti IT informazioni dettagliate sulle implementazioni di SQL Server nella loro infrastruttura. Queste informazioni sono fondamentali per garantire la conformità, ottimizzare le prestazioni e migliorare la sicurezza. Sapendo esattamente dove sono in esecuzione le istanze di SQL Server, gli amministratori possono proteggere meglio i dati sensibili e garantire che i sistemi siano configurati e manutenuti correttamente.

Raccomandazioni

• Esegui lo script per documentare le istanze di SQL Server come amministratore: Assicurati che lo script per documentare le istanze di SQL Server venga eseguito con i privilegi necessari per accedere alle informazioni del sistema.

• Effettua audit regolari: Pianifica l’esecuzione regolare dello script  per mantenere un inventario aggiornato delle istanze di SQL Server.

• Integralo con gli strumenti di monitoraggio: Prendi in considerazione la possibilità di integrare l’output dello script con strumenti di monitoraggio e documentazione per semplificare la gestione del sistema.

Considerazioni finali

Lo script PowerShell discusso in questo post è un potente strumento per i professionisti IT incaricati di gestire le istanze di SQL Server. Automatizzando il processo per rilevare e per documentare le istanze di SQL Server, si risparmia tempo e si riduce il rischio di errori, cose che rendono questo script un’aggiunta essenziale a qualsiasi kit di strumenti IT. Strumenti come NinjaOne possono migliorare ulteriormente questo processo fornendo solide funzionalità di gestione e monitoraggio, e assicurando che le istanze di SQL Server siano sempre sotto controllo e sicure.