As IT professionals and system administrators, we all are familiar with the command line tool Ping. We all use it, all the time, to see if a host is up or down or to get the IP address of a host.
Have you ever wanted a tool that can do the same with an SQL Server instance? That is, a tool that connects to a specified SQL Server instance repeatedly and lets us know if the SQL Serevr instance is up or not, and provide statistics on the connection speed?
I had faced a few situations where I needed a tool like this. Some scenarios below:
- A Windows SA is applying a Windows security patch to my SQL Server. As a DBA, I need to know as soon as SQL Server is back up and running, so I can perform SQL health checks and handover the server to the application team. I needed a tool that I can use to keep an on my SQL instance.
- A user is experiencing intermittent loss of connectivity to SQL Server. They claim that SQL Server becomes unresponsive for a few seconds, and then becomes normal again. In this case, I want to ping the SQL Server instance regularly and see if I experience the same issue or if the user’s issue is something local to him.
- As a DBA, I want to keep an eye on my most important SQL Server, to make sure its responding to connection requests in a timely fashion. I just want to run a tool that pings the SQL Server instance at specified intervals and displays the status, along with the connection speed statistics.
I did not find anything suitable that already existed – and I decided to cerate this tool myself. That’s how I came up with this nice little PowerShell script and called it SQLPing. This can be executed interactively inside the PowerShell IDE, or from the command line by specifying the necessary input parameters.
SQLPing has the below input parameters:
| SQLServer | Name of the SQL Server you want to ping. If not specified, it defaults to a local named instance called (local)\SQL2025 |
| totalPings | The number of times you want to ping your SQL Server. If a value of 1 or more is specified, it will ping the SQL Server those many times. if a value of 0 is specified, it will ping SQL Server indefinitely. You have to manually stop the script. Defaults to 4 pings, if no value is specified, or if an invalid value is specified. |
| intervalSec | Number of seconds you want the script to wait between each ping. Defaults to 1 second if no value is specified or if an invalid value is specified. |
Note: This script has been created to connect to SQL Server using integrated security – meaning, it will connect to SQL Server using the currently logged in user’s credentials. You’ll have to updae the connection string if you want to use SQL Server authentication and specify a user name and password.
SQLPing output:
I’ve coded SQLPing to mimic the output displayed the traditional Ping command. Here’s an example of what SQKPing output looks like. In this specific example, SQL Sever goes unresponsive for a brief period, before it becomes available again:
G:\PS>powershell.exe -F SQLPing.ps1 -SQLServer LDNSQLSRV\SQL2025 -totalPings 30
Pinging the SQL Server instance: LDNSQLSRV\SQL2025 with a query for 30 time(s):
Reply from LDNSQLSRV\SQL2025: time=32ms
Reply from LDNSQLSRV\SQL2025: time<1ms
Reply from LDNSQLSRV\SQL2025: time<1ms
Reply from LDNSQLSRV\SQL2025: time=2ms
Reply from LDNSQLSRV\SQL2025: time<1ms
Reply from LDNSQLSRV\SQL2025: time<1ms
Reply from LDNSQLSRV\SQL2025: time<1ms
Reply from LDNSQLSRV\SQL2025: time<1ms
Reply from LDNSQLSRV\SQL2025: time<1ms
Reply from LDNSQLSRV\SQL2025: time<1ms
Reply from LDNSQLSRV\SQL2025: time<1ms
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Reply from LDNSQLSRV\SQL2025: time=6ms
Reply from LDNSQLSRV\SQL2025: time<1ms
Reply from LDNSQLSRV\SQL2025: time<1ms
Reply from LDNSQLSRV\SQL2025: time<1ms
SQLPing statistics for LDNSQLSRV\SQL2025:
Requests: Sent = 30, Received = 15, Lost = 15 (50% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 32ms, Average = 3msHow to call SQLPing.ps1 and use its input parameters?
Suggest you save the SQLPing code to a location of your choice, and name it as SQLPing.ps1. You can then execute SQLPing as shown below. The below calls assume you are invoking the script from the same folder as the script. If not, please include the correct path to the script in your calls.
powershell.exe -F SQLPing.ps1 -SQLServer LDNSQLSRV\SQL2025powershell.exe -F SQLPing.ps1 -SQLServer LDNSQLSRV\SQL2025 -totalPings 0powershell.exe -F SQLPing.ps1 -SQLServer LDNSQLSRV\SQL2025 -totalPings 10powershell.exe -F SQLPing.ps1 -SQLServer LDNSQLSRV\SQL2025 -totalPings 10 -intervalSec 5powershell.exe -F SQLPing.ps1 -SQLServer LDNSQLSRV\SQL2025 -totalPings 0 -intervalSec 5And finally here’s the PowerShell code for SQLPing:
# SQLPing
# Created by Vyas Kondreddi
# https://dutyDBA.com
# Command line arguments
param(
[string]$SQLServer = "(local)\SQL2025",
[int]$totalPings = 4,
[int]$intervalSec = 1
)
# Variable initialisations
$Infinite = 0
$ConnectionTimeout = 5
$SqlQuery = "SELECT 1 AS SQLPing"
$ctr = 0
$rec = 0
$lost = 0
$lostPerc = 0
$mn = 1000
$mx = 0
$avg = 0
$sum = 0
$stats = ""
# If the $totalPings argument is specified as 0, the script will ping your SQL Server indefinitely
# If the $totalPings argument is specified as any valid number of 1 or more, it will ping those many times
# If no value is specified or an invalid value is spefied, it defaults to 4 pings
if($totalPings -gt 0)
{
Write-Host "`nPinging the SQL Server instance: $SQLServer with a query for $totalPings time(s):"
}
elseif($totalPings -eq 0)
{
Write-Host "`nPinging the SQL Server instance: $SQLServer with a query indefinitely:"
$Infinite = 1
}
else
{
$totalPings = 4
}
# By default the script pings your SQL Server every 1 second
# You may specify a diffrent value if you prefer - for example, ping every 5 secpnds
# If an invalid value is specified, it defaults to 1 second pings
if($intervalSec -lt 1)
{
$intervalSec = 1
}
for (;$totalPings -gt 0 -or $Infinite -eq 1;$totalPings--)
{
# Opening a connection to your SQL Server instance to run a simple query
try
{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = master; Integrated Security = True;Connection Timeout = " + $ConnectionTimeout
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$StartTime = Get-Date
$RCtr = $SqlAdapter.Fill($DataSet)
$EndTime = Get-Date
$Diff = New-TimeSpan -Start $StartTime -End $EndTime
if ($Diff.TotalMilliseconds -lt 1)
{
$ms = "<1ms"
}
else
{
$ms = "=" + [math]::round($Diff.TotalMilliseconds).toString() + "ms"
}
Write-Host "Reply from $SQLServer`: time$ms"
if($Diff.TotalMilliseconds -le $mn)
{
$mn = $Diff.TotalMilliseconds
}
if($Diff.TotalMilliseconds -ge $mx)
{
$mx = $Diff.TotalMilliseconds
}
$sum += $Diff.TotalMilliseconds
$SqlCmd.Dispose()
$SqlConnection.Dispose()
$SqlAdapter.Dispose()
$DataSet.Dispose()
$rec += 1
}
catch
{
Write-Host "Request timed out."
$lost += 1
}
# Completed the requested number of pings
# Display the statistics and exit
if($totalPings -eq 1 -and $Infinite -eq 0)
{
$ctr += 1
$lostPerc = [math]::round(($lost * 100)/$ctr)
Write-Host "`nSQLPing statistics for $SQLServer`:"
Write-Host "`tRequests: Sent = $ctr, Received = $rec, Lost = $lost ($lostPerc% loss),"
if ($rec -gt 0)
{
$avg = $sum / $rec
Write-Host "Approximate round trip times in milli-seconds:"
$stats = "`tMinimum = " + [math]::round($mn).tostring() + "ms, Maximum = " + [math]::round($mx).tostring() + "ms, Average = " + [math]::round($avg).tostring() + "ms"
Write-Host $stats
}
break
}
$ctr += 1
$Diff = 0
Start-Sleep -Seconds $intervalSec
}
Enjoy keeping an eye on your SQL Server instances, and please share if you have any enhancement ideas by leaving a comment below!
