Automated SQL Service Monitoring using PowerShell

Introduction

PowerShell is a powerful scripting language designed for task automation and configuration management. If you're new to PowerShell and want to learn how to monitor SQL services on your server, you're in the right place. This article will walk you through a simple script that checks if SQL services are running and starts them if they're not. It also sends email notifications when services are started. Let's break down the script step by step.

The PowerShell Script

Here's the complete script for reference.

# PowerShell Script to check if SQL Service is running or not
# Schedule it on task scheduler daily every 5 minutes
# SMTP server configuration
$smtpServer = "your_smtp_server"
$smtpPort = 443 # Change the port if necessary
$smtpUsername = "your_smtp_username"
$smtpPassword = "your_smtp_password"
# Sender and recipient email addresses
$senderEmail = "[email protected]"
$recipientEmail = "[email protected]"
# Define the list of SQL Server service names
$sqlServices = @(
    "MSSQLSERVER",     # SQL Server (default instance)
    "MSSQL$SQLEXPRESS", # SQL Server (named instance)
    "SQLWriter",
    "MSSQLServerOLAPService",
    "SSASTELEMETRY",
    "SQLTELEMETRY",
    "MsDtsServer160",
    "SSISTELEMETRY160"
    # Add more SQL Server service names if needed
)

# Loop through each SQL Server service name
foreach ($service in $sqlServices) {
    # Check if the service is running
    $serviceStatus = Get-Service -Name $service -ErrorAction SilentlyContinue
    if ($serviceStatus -eq $null) {
        Write-Host "Service $service not found. Please check if SQL Server is installed." -ForegroundColor Yellow
    }
    elseif ($serviceStatus.Status -ne "Running") {
        # Service is not running, attempt to start it
        Write-Host "Starting service $service..."
        Start-Service -Name $service
        # Check if the service started successfully
        if ((Get-Service -Name $service).Status -eq "Running") {
            Write-Host "Service $service started successfully." -ForegroundColor Green
            # Send email notification
            $emailBody = "Service $service has been started by the script."
            Send-MailMessage -SmtpServer $smtpServer -Port $smtpPort -UseSsl -Credential (New-Object System.Management.Automation.PSCredential ($smtpUsername, (ConvertTo-SecureString $smtpPassword -AsPlainText -Force))) -From $senderEmail -To $recipientEmail -Subject "SQL Server Service Started" -Body $emailBody
        } else {
            Write-Host "Failed to start service $service. Please check the service manually." -ForegroundColor Red
        }
    } else {
        Write-Host "Service $service is already running." -ForegroundColor Green
    }
}

Understanding the Script

1. SMTP Server Configuration

The script starts by setting up the SMTP server configuration for sending email notifications.

$smtpServer = "your_smtp_server"
$smtpPort = 443 # Change the port if necessary
$smtpUsername = "your_smtp_username"
$smtpPassword = "your_smtp_password"

$smtpServer: Specifies the SMTP server address.
$smtpPort: Specifies the port used by the SMTP server.
$smtpUsername: The username for authenticating with the SMTP server.
$smtpPassword: The password for authenticating with the SMTP server.

2. Sender and Recipient Email Addresses

Next, we define the sender and recipient email addresses.

$senderEmail = "[email protected]"
$recipientEmail = "[email protected]"

$senderEmail: The email address from which the notifications will be sent.
$recipientEmail: The email address that will receive the notifications.

3. Define SQL Server Services

We then define an array of SQL Server service names that we want to monitor.

$sqlServices = @(
    "MSSQLSERVER",     # SQL Server (default instance)
    "MSSQL$SQLEXPRESS", # SQL Server (named instance)
    "SQLWriter",
    "MSSQLServerOLAPService",
    "SSASTELEMETRY",
    "SQLTELEMETRY",
    "MsDtsServer160",
    "SSISTELEMETRY160"
    # Add more SQL Server service names if needed
)

4. Loop Through Each SQL Service

The script loops through each SQL Server service name to check its status.

foreach ($service in $sqlServices) {
    # Check if the service is running
    $serviceStatus = Get-Service -Name $service -ErrorAction SilentlyContinue

foreach ($service in $sqlServices): Iterates over each service name in the $sqlServices array.
Get-Service -Name $service -ErrorAction SilentlyContinue: Retrieves the status of the specified service. The -ErrorAction SilentlyContinue parameter suppresses errors if the service is not found.

5. Check Service Status

The script checks if the service is running and takes appropriate actions.

    if ($serviceStatus -eq $null) {
        Write-Host "Service $service not found. Please check if SQL Server is installed." -ForegroundColor Yellow
    }
    elseif ($serviceStatus.Status -ne "Running") {
        # Service is not running, attempt to start it
        Write-Host "Starting service $service..."
        Start-Service -Name $service
        # Check if the service started successfully
        if ((Get-Service -Name $service).Status -eq "Running") {
            Write-Host "Service $service started successfully." -ForegroundColor Green
            # Send email notification
            $emailBody = "Service $service has been started by the script."
            Send-MailMessage -SmtpServer $smtpServer -Port $smtpPort -UseSsl -Credential (New-Object System.Management.Automation.PSCredential ($smtpUsername, (ConvertTo-SecureString $smtpPassword -AsPlainText -Force))) -From $senderEmail -To $recipientEmail -Subject "SQL Server Service Started" -Body $emailBody
        } else {
            Write-Host "Failed to start service $service. Please check the service manually." -ForegroundColor Red
        }
    } else {
        Write-Host "Service $service is already running." -ForegroundColor Green
    }
}

If the service is not found ($serviceStatus -eq $null), a message is displayed.
If the service is not running ($serviceStatus.Status -ne "Running"), the script attempts to start it using Start-Service -Name $service.
After attempting to start the service, the script checks if it is successful. If so, it sends an email notification using Send-MailMessage.

6. Sending Email Notifications

The Send-MailMessage cmdlet is used to send email notifications.

Send-MailMessage -SmtpServer $smtpServer -Port $smtpPort -UseSsl -Credential (New-Object System.Management.Automation.PSCredential ($smtpUsername, (ConvertTo-SecureString $smtpPassword -AsPlainText -Force))) -From $senderEmail -To $recipientEmail -Subject "SQL Server Service Started" -Body $emailBody

SmtpServer $smtpServer: Specifies the SMTP server.
Port $smtpPort: Specifies the port for the SMTP server.
UseSsl: Indicates that SSL should be used.
Credential: Provides the credentials for the SMTP server.
From $senderEmail: Specifies the sender's email address.
To $recipientEmail: Specifies the recipient's email address.
Subject: Sets the subject of the email.
Body: Sets the body of the email.

Scheduling the Script

To automate this script, you can schedule it to run at regular intervals using Task Scheduler in Windows. Here are the steps:

Open Task Scheduler and create a new task.
Set the trigger to run the script every 5 minutes.
Set the action to start a program, and specify powershell.exe as the program.
Add the path to your script as an argument.

Conclusion

This PowerShell script provides a simple yet effective way to monitor SQL services and ensure they are running. By scheduling it to run at regular intervals, you can automate the monitoring process and receive email notifications whenever a service is started. PowerShell's powerful cmdlets and scripting capabilities make it an excellent tool for system administration tasks.