Script PowerShell per un efficace monitoraggio di SQL server e del suo stato di integrità

Punti chiave

• Lo script PowerShell semplifica il monitoraggio di SQL Server, migliorando la precisione e l’efficienza operativa.
• I parametri personalizzabili dello script consentono un monitoraggio personalizzato dei servizi di database, dello spazio su disco e della latenza.
• Il monitoraggio automatico riduce il rischio di downtime del server e di problemi di prestazioni.
• Lo script verifica la presenza di privilegi di amministrazione, essenziali per accedere alle informazioni di sistema ed eseguire determinate operazioni.
• Le capacità di monitoraggio in tempo reale dello script sono fondamentali per mantenere l’integrità del database e prevenire la perdita di dati.
• Adattabile a varie versioni di SQL Server, è uno strumento versatile per diversi ambienti IT.
• La combinazione di questo script con piattaforme di gestione IT complete come NinjaOne offre una soluzione di monitoraggio più solida.
• Per massimizzare l’efficacia dello script per il monitoraggio di SQL server, si consiglia di effettuare aggiornamenti regolari e di formare il personale sull’uso di PowerShell.
• Il monitoraggio di SQL server e la sua manutenzione proattiva sono essenziali per garantire l’alta disponibilità e l’integrità dei dati nei moderni ambienti aziendali.

Background

Garantire la salute e le prestazioni dei database SQL Server è fondamentale per per mantenere la continuità e l’efficienza delle aziende. Con la sempre maggiore importanza di un processo decisionale basato sui dati, da cui le aziende dipendono, i professionisti dell’IT sono spesso incaricati della responsabilità critica di monitorare e gestire questi sistemi. L’utilizzo di script PowerShell a questo scopo semplifica il processo, migliorando sia l’accuratezza che l’efficienza.

Lo script PowerShell per il monitoraggio di SQL server qui fornito rappresenta uno strumento prezioso per i professionisti IT e i provider di servizi gestiti (MSP). Si concentra sul monitoraggio di tre aspetti chiave dell’integrità di SQL Server: stato dei servizi di database, disponibilità di spazio su disco e latenza del disco. Questo monitoraggio è fondamentale perché aiuta a prevenire la perdita di dati, garantisce un’elevata disponibilità e mantiene ottimali le prestazioni di SQL Server.

Lo script per il monitoraggio di SQL server:

#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/it/condizioni-utilizzo/
    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 {
    
    
    
}

 

Analisi dettagliata dello script per il monitoraggio di SQL server

Lo script per il monitoraggio di SQL server inizia con una serie di parametri che consentono la personalizzazione in base alle specifiche esigenze di monitoraggio, come le soglie di spazio su disco e di velocità. Include diverse funzioni:

• Test-IsElevated: Controlla se lo script per il monitoraggio di SQL server è in esecuzione con i privilegi di amministratore, essenziali per accedere a determinate informazioni di sistema.
• Get-DefaultDBLocation: Determina le posizioni predefinite dei file di dati e di registro per le istanze di SQL Server.
• Get-DiskCounters: Recupera le metriche delle prestazioni del disco.
• Get-DiskFreePercentage: Calcola la percentuale di spazio libero su disco.

Nel suo processo principale, lo script per il monitoraggio di SQL server valuta lo stato dei servizi di SQL Server, dei servizi dell’agente, dello spazio su disco e della latenza del disco. Se un parametro scende al di sotto delle soglie definite o se i servizi non sono in esecuzione, viene segnalato un errore.

Casi d’uso potenziali

Immagina un MSP responsabile della manutenzione dell’ambiente SQL Server di un cliente. Potrà programmare l’esecuzione periodica di questo script per il monitoraggio di SQL server, assicurandosi che qualsiasi potenziale problema con i servizi di database, lo spazio su disco o la latenza venga rapidamente identificato e risolto, e riducendo così il rischio di downtime del server o di degrado delle prestazioni.

Confronti

I metodi tradizionali di monitoraggio di SQL Server prevedono spesso controlli manuali o strumenti di terze parti. Questo script, tuttavia, fornisce un approccio più integrato e automatizzato, consentendo l’invio e la ricezione di avvisi in tempo reale e riducendo al minimo gli errori umani.

Domande frequenti

D1: Con quale frequenza deve essere eseguito lo script per il monitoraggio di SQL server? 
R1: Dipende dalla criticità dell’ambiente SQL Server; per i database ad alto traffico, può essere appropriata una pianificazione giornaliera o addirittura con frequenza oraria.

D2: È possibile modificare lo script per il monitoraggio di SQL server per versioni diverse di SQL Server? 
R2: Sì, può essere adattato a varie versioni, anche se potrebbero essere necessari alcuni aggiustamenti in base alle caratteristiche e alle configurazioni specifiche della versione.

D3: È necessario avere competenze di PowerShell per utilizzare questo script per il monitoraggio di SQL server? 
R3: Una conoscenza di base di PowerShell è utile, ma lo script per il monitoraggio di SQL server è stato progettato per essere di facile utilizzo con istruzioni chiare sui parametri.

Implicazioni

Un monitoraggio inefficiente può causare tempi di inattività del server, risposte lente alle query e persino la perdita di dati. Questo script per il monitoraggio di SQL server riduce questi rischi, contribuendo a rendere l’ambiente IT più sicuro e affidabile.

Raccomandazioni

• Aggiorna regolarmente lo script per il monitoraggio di SQL server per adattarlo alle nuove versioni di SQL Server o alle modifiche organizzative.
• Utilizza questo script per il monitoraggio di SQL server in combinazione con altri strumenti di monitoraggio per ottenere una panoramica completa dell’infrastruttura IT.
• Forma il personale IT sull’uso di PowerShell per massimizzare i vantaggi dello script per il monitoraggio di SQL server.

Considerazioni finali

Nel panorama della gestione dei database e del monitoraggio dell’infrastruttura IT, strumenti come NinjaOne offrono soluzioni solide che integrano gli script PowerShell. NinjaOne può migliorare le capacità di script come quello per il monitoraggio di SQL server, fornendo una piattaforma unificata per il monitoraggio, gli avvisi e l’automazione delle risposte ai problemi di prestazioni di SQL Server. Integrando gli script con questi strumenti, le organizzazioni possono ottenere una strategia di gestione IT più proattiva ed efficiente, garantendo che i server SQL rimangano solidi, reattivi e affidabili.