PowerShell Script for Effective SQL Server Health Monitoring

Key takeaways

• PowerShell scripts streamline SQL Server monitoring, enhancing accuracy and operational efficiency.
• Customizable parameters in the script allow for tailored monitoring of database services, disk space, and latency.
• Automated monitoring reduces the risk of server downtime and performance issues.
• The script checks for admin privileges, essential for accessing system information and performing certain operations.
• Real-time monitoring capabilities of the script are crucial for maintaining database health and preventing data loss.
• Adaptable to various SQL Server versions, making it a versatile tool for different IT environments.
• Combining this script with comprehensive IT management platforms like NinjaOne offers a more robust monitoring solution.
• Regular updates and staff training in PowerShell are recommended to maximize the script’s effectiveness.
• Proactive monitoring and maintenance of SQL Servers are essential for ensuring high availability and data integrity in modern business environments.

Background

Ensuring the health and performance of SQL Server databases is crucial for businesses to maintain continuity and efficiency. With increasing reliance on data-driven decision-making, IT professionals are often tasked with the critical responsibility of monitoring and managing these systems. Leveraging PowerShell scripts for this purpose streamlines the process, enhancing both accuracy and efficiency.

The provided PowerShell script represents a valuable tool for IT professionals and Managed Service Providers (MSPs). It focuses on monitoring three key aspects of SQL Server health: database services status, disk space availability, and disk latency. Such monitoring is vital as it helps prevent data loss, ensures high availability, and maintains optimal performance of the SQL Server.

The 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/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 (
    [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 {
    
    
    
}

 

Detailed breakdown

The script begins with a series of parameters allowing customization according to specific monitoring needs, such as disk space and speed thresholds. It includes several functions:

• Test-IsElevated: Checks if the script is running with Administrator privileges, essential for accessing certain system information.
• Get-DefaultDBLocation: Determines the default data and log file locations for SQL Server instances.
• Get-DiskCounters: Retrieves disk performance metrics.
• Get-DiskFreePercentage: Calculates the percentage of free disk space.

In its main process, the script evaluates the status of SQL Server services, agent services, disk space, and disk latency. If any parameter falls below the defined thresholds or services are not running, it flags an error.

Potential use cases

Imagine an MSP responsible for maintaining a client’s SQL Server environment. They can schedule this script to run periodically, ensuring that any potential issues with database services, disk space, or latency are quickly identified and addressed, thereby reducing the risk of server downtime or performance degradation.

Comparisons

Traditional methods of SQL Server monitoring often involve manual checks or third-party tools. This script, however, provides a more integrated and automated approach, enabling real-time alerts and minimizing human error.

FAQs

Q1: How often should the script be run? 
A1: It depends on the criticality of the SQL Server environment; for high-traffic databases, a daily or even hourly schedule may be appropriate.

Q2: Can the script be modified for different SQL Server versions? 
A2: Yes, it can be adapted for various versions, though some adjustments may be necessary based on specific version features and configurations.

Q3: Is it necessary to have PowerShell expertise to use this script? 
A3: Basic understanding of PowerShell is beneficial, but the script is designed to be user-friendly with clear parameter instructions.

Implications

Inefficient monitoring can lead to server downtimes, slow query responses, and even data loss. This script mitigates such risks, contributing to a more secure and reliable IT environment.

Recommendations

• Regularly update the script to adapt to new SQL Server versions or organizational changes.
• Combine this script with other monitoring tools for a comprehensive overview of IT infrastructure.
• Train IT staff in PowerShell to maximize the script’s benefits.

Final thoughts

In the landscape of database management and IT infrastructure monitoring, tools like NinjaOne offer robust solutions that complement PowerShell scripts. NinjaOne can enhance the capabilities of scripts like this by providing a unified platform for monitoring, alerting, and automating responses to SQL Server performance issues. By integrating scripts with such tools, organizations can achieve a more proactive and efficient IT management strategy, ensuring their SQL Servers remain robust, responsive, and reliable.