Monitor SQL Server on Windows

What you'll know

• The port takes connections from where your applications connect. A TCP check on 1433, sent from another machine in the same network, follows the same path your applications use.
• SQL Server answers a query, and every database is online. A short PowerShell script on the server signs in with the agent's own Windows account and asks sys.databases about each database. A database that is suspect, pending recovery or in emergency mode is a problem. One still recovering after a restart is a warning.

The port check catches a network or firewall change between your applications and the server. The script catches a server that accepts connections but can't serve them, and a single database in trouble while the rest are fine.

What this can't tell you: whether queries are fast, whether anything is blocked, or whether last night's backup ran. For backups, see Check SQL Server backups on Windows.

Before you start

• The SQL Server machine, added as a host in Gryphon, with the Gryphon agent installed on it and connected: the host's page says Connected.
• An administrator's PowerShell on that machine, and a login with the sysadmin or securityadmin role in SQL Server.
• For the port check, an agent on another machine in the same network, such as one of your application servers.

Steps

1Watch the port

On the host of an application server that runs the agent, open Manage Services, choose Add service, and pick TCP port (agent). Its Host is the SQL Server as that machine reaches it: a private address, or a name your internal DNS answers.

A refused connection, or none within 10 seconds, is a problem. A default instance listens on 1433. A named instance usually has a port of its own, shown in SQL Server Configuration Manager under Protocols → TCP/IP → IP Addresses.

If SQL Server is meant to be reached from the internet, TCP port, with no agent, checks it from Gryphon's own servers at the host's public address. Most SQL Servers shouldn't be, and the agent's version is the one to use.

2Give the agent a login

On Windows, the agent runs as the service account NT SERVICE\GryphonAgent. In SQL Server Management Studio, connected as an administrator, give that account a login:

CREATE LOGIN [NT SERVICE\GryphonAgent] FROM WINDOWS;

That's all it needs. A login can connect, and every login can see the list of databases and their states, so there's no database to add it to and no role to grant. It has no password, because it's a Windows login. Install the agent first: Windows only knows the account once its service exists.

3Turn on script checks

Script checks are off until the agent is told which folder to run them from. In an administrator's PowerShell, open C:\ProgramData\Gryphon\agent.env in Notepad, uncomment the GWC_SCRIPTS_DIR line, and restart the agent:

GWC_SCRIPTS_DIR=C:\ProgramData\Gryphon\scripts

Restart-Service GryphonAgent

4Add the script

Create the script in that folder from the same administrator's PowerShell, paste the text below, and save:

notepad C:\ProgramData\Gryphon\scripts\sql-ping.ps1

# Does SQL Server answer a query, and is every database online?
# Exit 0 healthy, 1 warning, 2 problem: the first line printed is the message.
$Server = 'localhost'   # a named instance is localhost\NAME

$ErrorActionPreference = 'Stop'
$down = @()
$recovering = @()
try {
    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Server=$Server;Database=master;Integrated Security=SSPI;Connect Timeout=5;Application Name=Gryphon"
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandTimeout = 5
    $cmd.CommandText = "SELECT name, state_desc FROM sys.databases WHERE state_desc IN ('RECOVERING', 'RECOVERY_PENDING', 'SUSPECT', 'EMERGENCY')"
    $reader = $cmd.ExecuteReader()
    while ($reader.Read()) {
        if ($reader['state_desc'] -eq 'RECOVERING') {
            $recovering += $reader['name']
        } else {
            $down += '{0} ({1})' -f $reader['name'], $reader['state_desc']
        }
    }
    $conn.Close()
} catch {
    $e = $_.Exception
    if ($e.InnerException) { $e = $e.InnerException }
    Write-Output ('Cannot query {0}: {1}' -f $Server, ($e.Message -split '\r?\n')[0])
    exit 2
}

if ($down.Count -gt 0) {
    Write-Output ('Not online: ' + ($down -join ', '))
    exit 2
}
if ($recovering.Count -gt 0) {
    Write-Output ('Still recovering: ' + ($recovering -join ', '))
    exit 1
}
Write-Output "$Server answers, and every database is online."
exit 0

For a named instance, change $Server to localhost\NAME. Create the file in the folder rather than copying it in. A new file takes the folder's permissions, which only administrators can change. A file moved in from your Downloads keeps its old ones, and the agent refuses to run it, with a message naming the icacls command that fixes it.

5Add the script check

On the SQL Server's own host, add Script (agent):

The script's first line of output becomes the check's message. Exit code 0 is healthy, 1 a warning and 2 a problem. It has 10 seconds to finish, and a connection gives up after 5.

Test it

1. Use the check's Check now button. It runs the script as the agent's account, so it proves the login works. The message should read localhost answers, and every database is online. If it says Login failed for user 'NT SERVICE\GryphonAgent', go back to step 2.
2. Put a database in trouble on purpose, in SQL Server Management Studio: CREATE DATABASE GryphonTest; ALTER DATABASE GryphonTest SET EMERGENCY; On its next run, the check reads Not online: GryphonTest (EMERGENCY). Gryphon then checks every minute, and alerts once three results in a row agree. Warn whoever gets the alerts first.
3. Drop the test database with DROP DATABASE GryphonTest;. Three healthy results in a row bring the check back, and the recovery is announced.

Variations

Is SQL Server Agent running?

If SQL Server Agent stops, the server keeps answering, but no scheduled job runs, backups included. Watch its service with Check that a Windows service is running. It's SQLSERVERAGENT for a default instance and SQLAgent$NAME for a named one.

A SQL Server on another machine

The script can run on any machine with the agent that can reach the server: change $Server to its name. Over the network, the agent's account signs in as the machine it runs on, so in a domain the login is that computer's account, for example CREATE LOGIN [CORP\APP01$] FROM WINDOWS;. Outside a domain, a script would need a SQL Server login and its password, so install the agent on the SQL Server itself instead.

Disk space for the data and log drives

A full log drive stops a database. The agent's Disk Space check takes a Windows drive as its Path to monitor, such as D:\. Add one for each drive that holds data or log files, with your own Warning at and Problem at levels in its settings.