Hi there,

We have SQL servers with multiple SQL instances. When we reach the maximum of 50, we create a new server, but sometimes, we remove one freeing up a slot on a server. I would like to know if I could add a probe per machine that would tell me how many SQL instance services are running in order to know if we're capped on a specific server?

Thank you

Article Comments

Wouldn't every instance need a seperate configuration file? You could simply count the amount of configs per server and work with limits, if I got this right :)


May, 2016 - Permalink

Thanks for the reply. The problem is that sometimes, the configurations files (as well as other files) stay on the server after removing an instance, so this would lead to a miscalculation as opposed to counting the active services.


May, 2016 - Permalink

Ah, conditional as well! ;) A ssh script sensor might work as well: 

#!/bin/bash
##############
# uncomment the command available on the system
##############
#count=$(pgrep -c mysqld)
#count=$(ps -C mysqld --no-headers | wc -l)

echo "0:$count:Active SQL daemons: $count"

That should actually do the trick :)


May, 2016 - Permalink

My apologies, I did not specify the version of SQL we are using. It's SQL Express 2008 R2, not MySQL. How would you go about and modify this command? Also, I did not know I could add these sort of commands in PRTG.


May, 2016 - Permalink

Oh okay. What would be the following PowerShell script: 

param($computer)
$Count = (Get-Service -Name "MSSQL*" -ComputerName $computer).Count
Write-Host "$($Count):Active SQL services: $($Count)";

Save it to
C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\EXE\Count-MSSQL-Instances.ps1 ... and create a new EXE/Script sensor on the SQL host. Use %host as the only parameter.

Notes

  • Execute Set-ExecutionPolicy RemoteSigned in a elevated 32bit PowerShell if you don't have any custom script sensors yet.
  • Make sure the device has administrative Windows credentials configured

May, 2016 - Permalink

Thank you. We'll give this a try today or next week and let you know the results.


May, 2016 - Permalink

Update: Ya, powershell was too much of a pain to configure with security and everything. My college found a workaround by running this script with a simple WMI custom script: SELECT COUNT(*) FROM Win32_Process WHERE Name='sqlservr.exe'

Thanks anyway :)


May, 2016 - Permalink

Alright then :) Couldn't figure out how to do the COUNT(*) statement in WQL, it always error'd on me :D


May, 2016 - Permalink