Script PowerShell pour surveiller un serveur SQL

Points à retenir

• Les scripts PowerShell permettent de surveiller un serveur SQL en toute simplicité, améliorant ainsi la précision et l’efficacité opérationnelle.
• Les paramètres personnalisables du script permettent une surveillance sur mesure des services de base de données, de l’espace disque et de la latence.
• La surveillance automatisée réduit le risque d’indisponibilité du serveur et les problèmes de performance.
• Le script vérifie l’existence de privilèges d’administrateur, indispensables pour accéder aux informations du système et effectuer certaines opérations.
• Les capacités de surveillance en temps réel du script sont essentielles pour maintenir la santé de la base de données et prévenir les pertes de données.
• Adaptable aux différentes versions de SQL Server, ce qui en fait un outil polyvalent pour différents environnements informatiques.
• La combinaison de ce script avec des plateformes de gestion informatique complètes telles que NinjaOne offre une solution de surveillance plus robuste.
• Des mises à jour régulières et une formation du personnel à PowerShell sont recommandées pour maximiser l’efficacité du script.
• La surveillance et la maintenance proactives des serveurs SQL sont essentielles pour garantir la haute disponibilité et l’intégrité des données dans les environnements professionnels modernes.

Contexte

Garantir la santé et les performances des bases de données SQL Server est essentiel pour assurer la continuité et l’efficacité des entreprises. Avec la dépendance croissante à l’égard de la prise de décision fondée sur les données, les professionnels de l’informatique sont souvent chargés de la surveillance et de la gestion de ces systèmes. L’utilisation de scripts PowerShell permet de rationaliser le processus pour surveiller un serveur SQL, en améliorant à la fois la précision et l’efficacité.

Le script PowerShell fourni représente un outil précieux pour les professionnels de l’informatique et les fournisseurs de services gérés (MSP). Il se concentre sur la surveillance de trois aspects clés de la santé du serveur SQL : l’état des services de base de données, la disponibilité de l’espace disque et la latence du disque. Cette surveillance est vitale car elle permet d’éviter les pertes de données, d’assurer une haute disponibilité et de maintenir des performances optimales du serveur SQL.

Le script :

#Requires -Version 5.1

<#
.SYNOPSIS
    Monitors the database services, database's drive free space, and database's disk latency.
.DESCRIPTION
    Monitors the database services, database's drive free space, and database's disk latency.

    Exit code of 1 means there is a problem.

    Will not detect LocalDB uses of SQL Express

.EXAMPLE
    (No Parameters)
    ## EXAMPLE OUTPUT WITHOUT PARAMS ##
    SQL Server's services are running.
    SQL Server's disk latency is below threshold.
    SQL Server's disk free space is above threshold.

PARAMETER: -RequireAgentService
    Checks if the SQL Agent service is running or not.
.EXAMPLE
    -RequireAgentService
    ## EXAMPLE OUTPUT WITH RequireAgentService ##
    SQL Server's services are running.
    SQL Server's disk latency is below threshold.
    SQL Server's disk free space is above threshold.


PARAMETER: -DiskSpaceThreshold 50
    The percentage of free space where the database is stored must have free. 0-100
.EXAMPLE
    -DiskSpaceThreshold 50
    ## EXAMPLE OUTPUT WITH DiskSpeedThreshold ##
    [MSSQLSERVER] C: is under the threshold(50%) at 20%

PARAMETER: -DiskSpeedThreshold 40
    A brief explanation of the parameter.
.EXAMPLE
    -DiskSpeedThreshold 40
    ## EXAMPLE OUTPUT WITH DiskSpeedThreshold ##
    [MSSQLSERVER] Disk Read/Write latency is over 0 ms.
    Path                                           InstanceName          CookedValue
    ----                                           ------------          -----------
    \\test01\logicaldisk(c:)\disk reads/sec      c:               42.89807648928576
    \\test01\logicaldisk(c:)\disk writes/sec     c:                49.484308202068
.OUTPUTS
    None
.NOTES
    Minimum OS Architecture Supported: Windows 10, Windows Server 2016
    Release Notes: Renamed script and added Script Variable support
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/fr/conditions-dutilisation
    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 (
    [Parameter()]
    # Expects number in percentage with out the %
    # Default is 10 %
    [int]$DiskSpaceThreshold = 10,
    [Parameter()]
    # Expects number in milliseconds(ms)
    # Default is 50 ms
    [int]$DiskSpeedThreshold = 50,
    [switch]$RequireAgentService = [System.Convert]::ToBoolean($env:requireAgentService)
)

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)
    }

    if ($env:DiskSpaceThreshold) {
        $DiskSpaceThreshold = $env:DiskSpaceThreshold
    }
    if ($env:DiskSpeedThreshold) {
        $DiskSpeedThreshold = $env:DiskSpeedThreshold
    }

    Function Get-DefaultDBLocation {
        Param ([string] $vInstance)
        # Get the registry key associated with the Instance Name
        $vRegInst = (Get-ItemProperty -Path HKLM:"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL" -ErrorAction SilentlyContinue).$vInstance
        $vRegPath = "SOFTWARE\Microsoft\Microsoft SQL Server\" + $vRegInst + "\MSSQLServer"
        # Get the Data and Log file paths if available
        $vDataPath = (Get-ItemProperty -Path HKLM:$vRegPath -ErrorAction SilentlyContinue).DefaultData
        $vLogPath = (Get-ItemProperty -Path HKLM:$vRegPath -ErrorAction SilentlyContinue).DefaultLog
        # Report the entries found
        $Locations = [PSCustomObject]@{
            Data = ""
            Log  = ""
        }
        if ($vDataPath.Length -lt 1) {
            $vRegPath = "SOFTWARE\Microsoft\Microsoft SQL Server\" + $vRegInst + "\Setup"
            $vDataPath = (Get-ItemProperty -Path HKLM:$vRegPath -ErrorAction SilentlyContinue).SQLDataRoot + "\Data\"
            $Locations.Data = $vDataPath
        }
        else {
            $Locations.Data = $vDataPath
        }
        if ($vLogPath.Length -lt 1) {
            $vRegPath = "SOFTWARE\Microsoft\Microsoft SQL Server\" + $vRegInst + "\Setup"
            $vDataPath = (Get-ItemProperty -Path HKLM:$vRegPath -ErrorAction SilentlyContinue).SQLDataRoot + "\Data\"
            $Locations.Log = $vDataPath
        }
        else {
            $Locations.Log = $vDataPath
        }
        $Locations
    }
    function Get-DiskCounters {
        param ($Drive)
        $Counters = @(
            "\LogicalDisk($Drive*)\Avg. Disk sec/Read"
            "\LogicalDisk($Drive*)\Avg. Disk sec/Write"
            "\LogicalDisk($Drive*)\Disk Reads/sec"
            "\LogicalDisk($Drive*)\Disk Writes/sec"
        )
        $CounterData = Get-Counter -Counter $Counters -MaxSamples 1 -SampleInterval 10 | Select-Object -ExpandProperty CounterSamples

        $CounterData | Where-Object { $_.CookedValue -gt $DiskSpeedThreshold } | Write-Output
    }
    function Get-DiskFreePercentage {
        param ($Drive)
        $TotalSize = Get-Partition | Where-Object { $_.DriveLetter -like $Drive } | Select-Object -ExpandProperty Size
        $Free = Get-PSDrive -Name $Drive | Select-Object -ExpandProperty Free
        try {
            $Free / $TotalSize * 100
        }
        catch {
            0
        }
    }
    $script:HasErrors = $false
}
process {
    Write-Host
    if (-not (Test-IsElevated)) {
        Write-Error -Message "Access Denied. Please run with Administrator privileges."
        exit 1
    }
    $Services = Get-Service | Where-Object { $_.DisplayName -like "SQL Server*" }
    $SqlDbServices = $Services | Where-Object { $_.DisplayName -like "SQL Server (*" } | Select-Object -ExpandProperty DisplayName
    $SqlDbNames = $SqlDbServices | ForEach-Object {
        "$_" -split '\(' -replace '\)' | Select-Object -Last 1
    }

    # Get all MS SQL Databases
    $Databases = $SqlDbNames | ForEach-Object {
        $DbName = $_
        $DbLocations = Get-DefaultDBLocation -vInstance $DbName
        [PSCustomObject]@{
            Name            = $DbName
            DatabaseService = $Services | Where-Object { $_.DisplayName -like "SQL Server ($DbName)" }
            AgentService    = $Services | Where-Object { $_.DisplayName -like "*Agent *$DbName*" }
            DataPath        = $DbLocations.Data
            LogPath         = $DbLocations.Log
        }
    }

    $Databases | ForEach-Object {
        $Database = $_
        $DatabaseService = $Database.DatabaseService
        $AgentService = $Database.AgentService
        $DatabaseName = $Database.Name
        $Drive = $Database.DataPath -split ':\\' | Select-Object -First 1

        # Check service status
        if ($DatabaseService.Status -notlike "Running") {
            Write-Host "[$DatabaseName] Database Service is not running."
            $script:HasErrors = $true
        }
        if ($AgentService.Status -notlike "Running") {
            Write-Host "[$DatabaseName] Database Agent Service is not running."
            if ($RequireAgentService) {
                $script:HasErrors = $true
            }
        }

        # Get disk free space percentage
        $FreePercent = Get-DiskFreePercentage -Drive $Drive
        if ($FreePercent -lt $DiskSpaceThreshold) {
            Write-Host "[$DatabaseName] $($Drive): is under the threshold($DiskSpaceThreshold%) at $([System.Math]::Round($FreePercent,0))%"
            $script:HasErrors = $true
        }

        # Get disk latency
        $HighCounters = Get-DiskCounters -Drive $Drive
        if ($HighCounters) {
            $HighCounters | ForEach-Object {
                Write-Host "[$DatabaseName] Disk Read/Write latency is over $DiskSpeedThreshold ms at $([System.Math]::Round($_.CookedValue,2)) for $($_.InstanceName)."
            }
            $HighCounters | Out-String | Write-Host
            $script:HasErrors = $true
        }
    }

    if ($script:HasErrors) {
        exit 1
    }
    else {
        Write-Host "SQL Server's services are running."
        Write-Host "SQL Server's disk latency is below threshold."
        Write-Host "SQL Server's disk free space is above threshold."
        exit 0
    }
    
}
end {
    
    
    
}

 

Description détaillée

Le script commence par une série de paramètres permettant une personnalisation en fonction des besoins de surveillance spécifiques, tels que les seuils d’espace disque et de vitesse. Il comprend plusieurs fonctions :

• Test-IsElevated : Vérifie si le script s’exécute avec les privilèges d’administrateur, indispensables pour accéder à certaines informations du système.
• Get-DefaultDBLocation: Détermine l’emplacement par défaut des fichiers de données et de journaux pour les instances du serveur SQL.
• Get-DiskCounters: Récupère les mesures de performance du disque.
• Get-DiskFreePercentage: Calcule le pourcentage d’espace disque libre.

Dans son processus principal, le script évalue l’état des services SQL Server, des services d’agent, de l’espace disque et de la latence du disque. Si l’un des paramètres est inférieur aux seuils définis ou si les services ne sont pas en cours d’exécution, une erreur est signalée.

Cas d’utilisation potentiels

Imaginez une entreprise MSP chargée de la maintenance de l’environnement SQL Server d’un client. Elle peut programmer l’exécution périodique de ce script, ce qui permet d’identifier et de résoudre rapidement tout problème potentiel lié aux services de base de données, à l’espace disque ou à la latence, réduisant ainsi le risque d’indisponibilité du serveur ou de dégradation des performances.

Comparaisons

Les méthodes traditionnelles pour surveiller un serveur SQL impliquent souvent des contrôles manuels ou des outils tiers. Ce script, cependant, fournit une approche plus intégrée et automatisée, permettant des alertes en temps réel et minimisant l’erreur humaine.

FAQ

Q1 : À quelle fréquence le script doit-il être exécuté ? 
A1 : Cela dépend de la criticité de l’environnement SQL Server ; pour les bases de données à fort trafic, une programmation quotidienne ou même horaire peut être appropriée.

Q2 : Le script peut-il être modifié pour différentes versions du serveur SQL ? 
A2 : Oui, il peut être adapté à différentes versions, bien que certains ajustements puissent être nécessaires en fonction des caractéristiques et des configurations spécifiques de la version.

Q3 : Est-il nécessaire d’avoir des connaissances en PowerShell pour utiliser ce script ? 
A3 : Des connaissances de base de PowerShell sont utiles, mais le script est conçu pour être facile à utiliser, avec des instructions claires sur les paramètres.

Implications

Une surveillance inefficace peut entraîner des temps d’arrêt du serveur, des réponses lentes aux requêtes, voire des pertes de données. Ce script atténue ces risques, contribuant ainsi à un environnement informatique plus sûr et plus fiable.

Recommandations

• Mettez régulièrement à jour le script pour l’adapter aux nouvelles versions du serveur SQL ou aux changements organisationnels.
• Combinez ce script avec d’autres outils de surveillance pour obtenir une vue d’ensemble de l’infrastructure informatique.
• Formez le personnel informatique à PowerShell afin de maximiser les avantages du script.

Conclusion :

Dans le domaine de la gestion des bases de données et de la surveillance de l’infrastructure informatique, des outils comme NinjaOne offrent des solutions performantes qui complètent les scripts PowerShell. NinjaOne peut améliorer les capacités des scripts de ce type en fournissant une plateforme unifiée pour la surveillance, l’alerte et l’automatisation des réponses aux problèmes de performance du serveur SQL. En intégrant des scripts à ces outils, les entreprises peuvent mettre en place une stratégie de gestion informatique plus proactive et plus efficace, en veillant à ce que leurs serveurs SQL restent performants, réactifs et fiables.