Monitor if sqlserver is up from distant server using sqlcmd

Question

I want to monitor if my sqlserver is up from a distant machine.

I currently send a simple "select @@version" query with sqlcmd and then validate if that query returned the expected part of text :

sqlcmd -S np:\\<servername>\pipe\sql\query -i version.sql | findstr /I "expected string"

then I proceed to validate conditions based on this event.

Is there a better way to monitor db up events and db down events ?

Thanks

Answer 1

The best way (aside from buying a proper product) is to do it with PowerShell, with Invoke-SqlCmd, because it will give you more flexibility to check the response and do something with it.

I probably wouldn't confirm version because it's meaningless and will change when you upgrade or patch; I would confirm @@Servername matches what I expect in case the IP has somehow become mixed.

As for alternatives they're not really "better" but you can expand it as far as you want to:

• You can check the Event Log for failed connection attempts. Just because you can connect doesn't mean the critical application which uses an AD account which just expired when it wasn't meant to, can connect. This is actually pretty useful. If you're not tracking failed logins then you absolutely should start, whether it's the event log, an audit, extended event, or standard trace.

• Are all the databases online / sync'd? Just because you can connect to master doesn't mean one of the disks hasn't disconnected and caused the database to go into suspect mode.

• You can use WMI/CIM to determine whether the service is running. Is your Agent running? It better be because otherwise your backups and other processes are probably going to stop. You're probably going to want to use WMI/CIM to check that.

• Technically you can write a WMI handler which gets injected onto the server which can trigger an email notification when the SQL service stops/starts unexpectedly. But this is tricky and I wouldn't bother.

• You could query performance counters to pull back logins/sec to see whether it has dropped radically from the norm. But it would be so tricky to identify outliers it's probably not worth the effort. On the other hand if it increases to> 1000 per second (4 million per hour, thanks to a broken Oracle software reading from SQL!) it can indicate a problem, so maybe you do want to catch it.

• A_V: Thanks for your input, what is a good application to do such work appart from the multi-thousand dollar redgate aplications ?