Where Are My SQL Servers?

At a customers site I bumped into an issue where not all SQL Servers were properly discovered. These servers had the Microsoft Monitoring Agent (MMA) installed but somehow SQL running on those servers wasn’t discovered at all.

Gladly however, SCOM reported errors on those very same undiscovered SQL servers: Operations Manager failed to start a process. And: One or more workflows were affected by this. Workflow name: Microsoft.SQLServer.2012.DBEngineDiscoveryRule.Server:

Besides this the MMA run just fine and all other components running on those same servers were neatly discovered. So somehow there was something at play here limited to the SQL server components itself, blocking SCOM to discover it properly. Time to for troubleshooting.

Check 01: Permissions
First of all a check was run whether the local system account had sufficient permissions to query the servers. And indeed, no special GPOs were in place or other security settings enforced compared to the SQL servers which were neatly discovered by SCOM.

Check 02: WMI
So perhaps WMI was at play. A basic WMI test worked out jus fine. So far so good. But still it could be that the WMI components related to SQL server weren’t okay. Time for a deeper dive.

Soon we found this thread on the TechNet Forum where other people ran into the same issue and solved it by rebuilding the WMI repository for SQL server:

So time to run those steps:

1. Open a cmd-prompt with elevated permissions;
2. Go to  folder “C:\Program Files (x86)\Microsoft SQL Server\100\Shared”;
3. Give this command: mofcomp sqlmgmproviderxpsp2up.mof;
4. Restart the MMA

And soon after it, the SQL server running on that server was neatly discovered and monitored. Awesome!