There appears to be an issue when trying to discover certain SQL 2008 instances with SCOM. Which is related to MOF data not being registered in the WMI repository correctly when SQL is installed.
The error that you will see in your Operations Manager event log is:
DiscoverSQL2008DBEngineDiscovery.vbs : The Query ‘select * from SqlService where SQLServiceType =’1” did not return any valid instances. Please check to see if this is a valid WMI Query.. Invalid class
I’ve had success with the below solution:
From an elevated command prompt, browse to the folder “C:\Program Files (x86)\Microsoft SQL Server\100\Shared”
Run mofcomp sqlmgmproviderxpsp2up.mof
Restart your SCOM Agent on the server.
Hi – Can you elaborate on what you are doing here? Its just I am experiencing the same problem
Thanks
Hi Tom, are you getting the same error that I had in your OpsMgr event log on your SQL machine?
“DiscoverSQL2008DBEngineDiscovery.vbs : The Query ‘select * from SqlService where SQLServiceType =’1” did not return any valid instances. Please check to see if this is a valid WMI Query.. Invalid class”
In essense the cause here is that the WMI respository is missing class data that pertains to SQL. This means that the SCOM is unable to query those classes. The command in the article will register those classes for you.
This was very helpful in tracking down this issue. Seemed to only be happening on a windows 2003 cluster for me when the script was getting the virtual name of the SQL Server passed into it.
After this quick fix, all is well.
Cheers!
Do we require to reboot the server after this command is run.
Because I did run this command and restarted the agent service but no luck.
Please advise.
-Mohan
Hi Mohan,
A server restart shouldn’t be necessary, are you sure you are experiencing this specific issue?
Hello Warren,
Yes, I am using SCOM 2012 R2 and able to discover various versions of SQL DB (2005, 2008, 2012, 2014).
But on a few servers I am not able to discover 2008 version of MSSQL.
When I check the event logs I do see the below errors:
DiscoverSQL2008DBEngineDiscovery.vbs : The Query ‘select * from SqlService where SQLServiceType =’1” did not return any valid instances. Please check to see if this is a valid WMI Query.. Invalid class
The process started at 11:36:56 PM failed to create System.Discovery.Data, no errors detected in the output. The process exited with 0
Command executed: “C:\Windows\system32\cscript.exe” /nologo “DiscoverSQL2008DBEngineDiscovery.vbs” {32FBB1E4-C6D1-0517-2F47-3DDA67D46A3B} {CBFC9AB5-6F04-4FAA-9162-5ED77FA3A573} xxxx.enterprisenet.org xxxx.enterprisenet.org xxxx”Exclude:”
Working Directory: C:\Program Files\Microsoft Monitoring Agent\Agent\Health Service State\Monitoring Host Temporary Files 81\11422\
One or more workflows were affected by this.
Workflow name: Microsoft.SQLServer.2008.DBEngineDiscoveryRule.Server
Instance name: xxxxx.enterprisenet.org
Instance ID: {CBFC9AB5-6F04-4FAA-9162-5ED77FA3A573}
Management group: EnterpriseScom
Please help me fix this.
Hi Mohan ,
It definitely seems like a similar issue, if reloading the WMI inventory as discussed in this entry is not working for you then you may have deeper WMI issues. Perhaps check your application log on those server to see if other errors are prevalent. You could always attempt a WMI repair depending on what you find.
Genius
getting the same error and the command didn’t solve the problem
It’s possible that you may have deeper WMI issues.
If it’s just one one server check for other related symptoms.
Thanks boet. I’ve been scratching my head about this one for a few days now. This seemes to have resolved the issue of my last few SQL DB stragglers not discovering/monitoring correctly.
Greatly appreciated.
Awesome, glad it helped!
Great article !
I came across lots of SQL servers in our SCOM environment which suddenly fail to be monitored. It turned out that our DBAs were upgrading older SQL versions to more recent versions. During that upgrade the WMI classes were not registered. No idea, why the upgrade process is not doing that. Running above command has helped for us
Thanks
Thorsten
Thank you it worked!
Fantastic!