SCOM issue discovering SQL Instances / Databases

 

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.

Loading

15 thoughts on “SCOM issue discovering SQL Instances / Databases

  1. Warren Kahn Post author

    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.

    Reply
    1. S. Kusen

      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!

      Reply
  2. Mohan Sekar

    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

    Reply
  3. Mohan Sekar

    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.

    Reply
  4. Warren Kahn Post author

    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.

    Reply
  5. Victor

    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.

    Reply
  6. Thorsten Schneider

    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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.