Has anyone attempted to do this? My DBAs would like a “dashboard” showing the status (green = running, red = stopped) of the “SQL Server Windows Service Name” and the “SQL Server Agent Windows Service Name” for each SQL server.
For the life of me I cannot come up with a solution.
Any help would be GREATLY appreciated. Been stuck on tihs.
I would advise to create a Visio plugin https://squaredup.com/resources/videos/ for this. You will need to locate the SCOM Object ID for each of the services. Find the SQL server in Squared Up and drill-down the SQL Serve DB Engine. Then click on the “Show all monitors and rules” link. Click on the SQL Server Windows Service and get the Object ID from the URL.
You will also need to update the Visio manually as new SQL servers are added.
It will take some trial and error to get Visio to work correctly.
Hope this helps.
We have the same, setup on a custom dashboard, simply a status view. Pointing to a SCOM group of servers… being our SQL servers. with a class of “SQL SERVICE” which is a custom windows service monitor configured in SCOM.
works like a dream. tried using the native MSSQL Service from the SQL MP and didnt work so well, had to create custom for each named instance, but ours luckily 99% are default instances being MSSQLSERVER.
Hope this helps…
[Moderation: moved answer by scottbanyas 24 Jun, 2016 to comment]
That is part of the problem trying to utilize the Microsoft SQL Management Pack, the service (either sql server or agent) is not exposed under the “Show all monitors and rules” link.
Thank you Warren. I suspected we may have to go down this route but was really hoping we didn’t. Question: after implementing your own custom service monitors did you disable the native MP monitors?
No we didnt disable the native MP monitors, as there may be hiddeen instances lying somewhere as we have a very large environment. that alerting stays as is, but alerting disabled on the custom monitor…