Comment utiliser PowerShell pour trouver et documenter les instances de serveur SQL

Vous souhaitez apprendre à gérer les instances de serveur et MSSQL à l’aide de PowerShell? Dans le monde de l’informatique, la gestion et la maintenance des instances SQL Server est une tâche essentielle pour les professionnels de l’informatique et les fournisseurs de services gérés (MSP). Savoir comment recueillir efficacement des informations sur les instances de serveur SQL peut considérablement améliorer les tâches administratives et rationaliser les processus. Cet article se penche sur un puissant script PowerShell conçu pour récupérer une liste d’instances de serveurs MSSQL, en fournissant des informations détaillées et des applications pratiques.

Contexte

Les instances SQL Server constituent l’épine dorsale de nombreuses applications commerciales, car elles stockent et gèrent de grandes quantités de données. Les professionnels de l’informatique doivent souvent identifier et gérer ces instances pour garantir des performances et une sécurité optimales. Le script PowerShell fourni est un outil précieux pour récupérer rapidement des informations sur les instances de serveur SQL sur un système. Cette capacité est cruciale pour des tâches telles que les audits, le dépannage et la surveillance du système.

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

 

Description détaillée

Le script commence par vérifier s’il s’exécute avec des privilèges d’administrateur, ce qui est nécessaire pour accéder à certaines propriétés du système. Il définit une fonction, Test-IsElevated, pour le vérifier. Si le script n’est pas exécuté en tant qu’administrateur, il se termine par une erreur.

Ensuite, il tente de récupérer les instances SQL Server installées dans le registre Windows. Pour ce faire, la cmdlet Get-ItemProperty permet d’accéder au chemin d’accès au registre concerné. Le script parcourt ensuite chaque instance, en récupérant des informations telles que le nom de l’instance, l’état du service et le chemin d’installation.

Les informations recueillies sont structurées dans un objet personnalisé et affichées. S’ils sont spécifiés, les résultats peuvent également être enregistrés dans des champs personnalisés, sous forme de texte brut ou de code HTML, à l’aide de la fonction Set-NinjaProperty. Cette fonction traite différents types de données et veille à ce que les valeurs soient conformes aux limites de caractères et à d’autres contraintes.

Cas d’utilisation potentiels

Prenons l’exemple d’un professionnel de l’informatique qui gère plusieurs serveurs au sein d’une entreprise. En utilisant ce script, il peut rapidement rassembler et documenter toutes les instances SQL Server fonctionnant sur chaque serveur. Ces informations sont essentielles pour des tâches telles que la mise à jour des logiciels, la réalisation d’audits de sécurité et la planification des capacités. Par exemple, lors d’un cycle de mise à niveau, le fait de connaître toutes les instances actives permet de planifier et de minimiser les temps d’arrêt.

Comparaisons

Ce script PowerShell offre un moyen rationalisé et automatisé de collecter des informations sur les instances de serveur SQL par rapport aux méthodes manuelles. Traditionnellement, les administrateurs peuvent utiliser SQL Server Management Studio (SSMS) ou vérifier manuellement les services via la console Windows Services. Bien qu’efficaces, ces méthodes prennent du temps et sont sujettes à des erreurs humaines. En revanche, le script fournit un processus cohérent et reproductible qui peut être facilement intégré dans des flux de travail plus larges de gestion des technologies de l’information.

FAQ

Q : Ce script peut-il détecter les instances SQL Server installées dans le cadre d’une application ?

R : Non, le script est conçu pour détecter les instances standard du serveur SQL. Les instances intégrées dans les applications ne sont pas prises en charge.

Q : Que se passe-t-il si le nom d’un champ personnalisé dépasse la limite de caractères ?

R : Le script comprend une vérification pour s’assurer que les valeurs ne dépassent pas 10 000 caractères. Si cette limite est dépassée, une erreur est générée.

Q : Ce script peut-il être exécuté avec n’importe quelle version de PowerShell ?

R : Le script nécessite la version 5.1 ou plus récente de PowerShell.

Implications

L’exécution de ce script permet aux professionnels de l’informatique d’obtenir des informations détaillées sur les déploiements de serveurs SQL dans leur infrastructure. Ces informations sont essentielles pour garantir la conformité, optimiser les performances et renforcer la sécurité. En sachant exactement où tournent les instances SQL Server, les administrateurs peuvent mieux protéger les données sensibles et s’assurer que les systèmes sont correctement configurés et entretenus.

Recommandations

• Exécuter en tant qu’administrateur : Assurez-vous que le script est exécuté avec les privilèges nécessaires pour accéder aux informations du système.

• Audits réguliers : Planifiez des exécutions régulières du script pour maintenir à jour l’inventaire des instances du serveur SQL.

• Intégration avec les outils de surveillance : Envisagez d’intégrer les résultats du script aux outils de surveillance et de documentation afin de rationaliser la gestion du système.

Conclusion

Le script PowerShell dont il est question dans ce billet est un outil puissant pour les professionnels de l’informatique chargés de gérer les instances du serveur SQL. En automatisant le processus de découverte, il permet de gagner du temps et de réduire le risque d’erreurs, ce qui en fait un complément essentiel à toute boîte à outils informatique. Des outils tels que NinjaOne peuvent encore améliorer ce processus en offrant de solides capacités de gestion et de surveillance, garantissant que vos instances SQL Server sont toujours sous contrôle et sécurisées.