SCOM 2016 - Always-On Discovery Script failed - SQL Always On Management Pack Monitoring

Question

Hi,

Getting alerts in SCOM at the moment relating to SQL Always On discovery scripts failing due to a few different reasons.

They all seem to relate to event 4101 in connection with "Invalid class 'SqlServiceAdvancedProperty'" but then the exact description differs.

Always On discovery script failed. Reason: Event ID: 4101. Management Group: XXXX. Script: Main Version: 6.7.31.0 : Error occurred during Always On monitoring.
Computer: XXXX
Reason: Invalid class "SqlServiceAdvancedProperty"
Position:381
Offset:22
Instance:XX

Detailed error output: Invalid class "SqlServiceAdvancedProperty"

Property XXXX does not exist at path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Modules{8401B7AE-98EC-F5EC-7CE0-36726F1F170A}\S-1-5-18\Script\SQLMPSP1\SqlHostNames.

Initially it was stating XXXX_CreationTime does not exist at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Modules{8401B7AE-98EC-F5EC-7CE0-36726F1F170A}\S-1-5-18\Script\SQLMPSP1\SqlHostNames.

True enough, there was no key/property with the "hostname" but there WAS an [FQDN]_CreationTime. So as part of testing further, I created the key with just the hostname and resolved the alert in SCOM.

30 minutes later, a new alert is created, but this time the property it is complaining about it simply [hostname]. Again, this is true, a property with JUST the hostname does NOT exist, but the FQDN does exist.

Is this a problem with the script? Should it be looking for FQDN instead? Does it actually NEED FQDN and Hostname properties for each of these properties in this location? If it does, what is responsible for creating those properties in the first place and WHY is it not creating what this is expecting to find?

...

The second alert I have doesn't have any specific "further" description and stops after reporting the detailed error output that refers to an invalid class of SqlServiceAdvancedProperty.

Just want to understand what is going on here and what should be creating these as although I can turn off the rule that alerts to event 4101, I wonder what is actually not working in the background with the discoveries.

Thank you

Andrew

Answer 1

@Andrew Perry , From the information, it seems our SQL server is with 2012 version. And we use the Microsoft.SQLServer.2012.AlwaysOn.Discovery MP to monitor. Could you confirm if our understanding is correct?

In the discovery script xml, I find both hostname and FQSN are mentioned in it. So I think both are needed. We can try to manually create it to see if it is working.


https://systemcenter.wiki/?ShowManagementPack=Microsoft.SQLServer.2012.AlwaysOn.Discovery&Version=6.7.31.0
Note: Non-Microsoft link, just for the reference.

In the xml, I notice there's function to create the registry. But they are not existing. Here, we suggest check what is the run as account of this and if it has enough permission on this agent and registry.

Hope it can help.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you for your reply @Crystal-MSFT

I am checking this with the SQL Team now.

I am suspecting though that it does have permission as the account that we have for the SQL Run As account is what I used to connect to the server and I was able to create the initial key/property yesterday.

Just to check further on this, as there are several different SQL Run As accounts... Would this be the default SQL action account, or the Discovery Account? We don't assign anything to the SQL default action account but I assume in this case it would use the default, which is the account I created with yesterday. I have noticed that for the SQL Server Discovery Account, one of the servers is not targeted here (the server that complains about the specific property) but the other server is in the targeted group (the one that doesn't specify any additional description/property). But I just want to check before I start adding objects in to these.

Thanks

Andrew

EDIT: Apologies, I also meant to confirm that it is indeed SQL 2012

EDIT 2: I have just noticed that since the last alert was generated, the alert description has been updated so now this doesn't show any property. Both servers now just report the same 4101 error relating to Invalid class "SqlServiceAdvancedProperty" but do not mention any key. In terms of timelines, I added that initial key/value at around 4PM (UK Time) last night. At 4:37PM was when the new alert was generated stating the Property was just simply the hostname. Looking in the OpsMgr event log, it seem that this event continued to log every 30 minutes but then at 6:22PM, the event description changed and no longer reported any property. Still doesn't mean much to me as there is still something it doesn't like, but it now seems ok with the properties? Hopefully this means something to someone else :-)