Posted by: Colin Smith
Database Administration, DBA, Monitoring, Powershell, Windows Administration
I have a script that goes out and pings all of my SQL Servers. This script, right now, simply makes a connection to the SQL Server instance. If I am able to connect I call it good and if not I send out notifications saying that the SQL Server is down. I found that more often than not I was getting these alerts because of some issue with the OS. This could be that the network interface was down, the server was powered off, or a number of other issues that could impact connecting to the SQL Server instance. My team usually ended up looking into it and having to call one of our Windows Systems Administrators in order to get the issue corrected.
The DBA Team did not really like that we got notified on OS issues before the Windows Team. The Windows Team was happy that we were letting them know of issues but they do not really like that the DBA team sees the problem before they do either. Because of this I decided to redo the SQL Ping Servers thing that I am going to do is to ping the servers interface and make sure that the server is up and responsive. If it is not then I as a DBA will not really be able to do anything anyway so if the ping fails I will just notify the Windows team directly. To do this Powershell needs to connect to WMI and look at the win32_pingstatus like so.
This does work well but I ran into another issue. What if WMI of a server is not working for some reason? I have had this issue come up and then it may look like I cannot ping the physical interface on a server when in fact I can. I thought that I need a more effective way just to test the PING. Why not just use good old ping. So I did the following and it works great.
“*100% loss*”) -or ($pingresult
“*could not find host*”))
“Ping Failed notify Systems Team”}
This way I can test the actual ping of the server. If the hostname cannot be resolved or if my loss is 100% then I consider this a failed test and I will notify the Windows Team. If I have 0% loss then I can move to my next function and continue with my basic health check of the SQL Server.
I will but more about this PING SQL Server script out when I have completed it. I just thought this was a very important distinction to make you aware of.
Any questions please head over to http://www.sysadminsmith.com and click on the ‘Submit a Question’ link to the right.
Script and incorporate some more steps in it that will better determine what the cause of the issue is and will notify the appropriate team.