question

AndrewPerry-6656 avatar image
0 Votes"
AndrewPerry-6656 asked Crystal-MSFT commented

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

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

msc-operations-manager
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndrewPerry-6656 avatar image
0 Votes"
AndrewPerry-6656 answered Crystal-MSFT commented

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 :-)

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@AndrewPerry-6656, Firstly, I would like your help to confirm if the MP we imported is this one:
https://www.microsoft.com/en-us/download/details.aspx?id=56203

If yes, according to the document in this MP, I find it will use Always On Discovery Account for script-based discovery of Always On objects. We check the permission for this.
88074-image.png
In your update information, I notice the alert only with Invalid class "SqlServiceAdvancedProperty". Research and find it maybe that the class is missing. we can contact SQL team to repair the SQL server to see if it is working.
https://www.sqlservercentral.com/forums/topic/get-wmiobject-invalid-class-for-sqlserviceadvancedproperty
Note: non-Microsoft link, just for the reference.

Hope it can help.


1 Vote 1 ·
image.png (54.6 KiB)

Hi,

I just wanted to update on this...

Whilst I am still unable to validate on the key, I can confirm that the alerts that didn't mention a specific property have both now been resolved by a repair (MOF command).

The SQL Team performed this last week and I was waiting, before replying, to see if it came back. And so far it hasn't.

I came here today to update, but just noticed that you had actually suggested the same :-) So hopefully the repair has resolved this now.

Thanks for your help @Crystal-MSFT

0 Votes 0 ·

@AndrewPerry-6656, Thanks for the update. As our SQL team has performed the repair and need some time waiting, We will monitor on it. If there's any further update, feel free to let us know.

Hope the repair can fix our issue. Have a nice day!

0 Votes 0 ·
Crystal-MSFT avatar image
0 Votes"
Crystal-MSFT answered Crystal-MSFT edited

@AndrewPerry-6656, 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.
87480-image.png
87498-image.png
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.



image.png (46.4 KiB)
image.png (49.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.