A word of caution about your Data Warehouse health monitor.


This isn’t a question but more a tale of caution about issues you may or may not have when it comes to the inbuilt monitoring of your SCOM Data Warehouse.

I’d noted a number of times in recent months that when I go to the Operations Manager perspective in Squared Up the Data Warehouse Database state would show in grey. It was odd to me but as someone who was relatively new to SCOM I wasn’t quite sure what was going on. The system was working so it was an annoyance but not one that I felt warranted too much time; at least not based on other jobs that I also had on my list.

Anyway, today I finally looked into the issue and I was pretty surprised by what I found.

The root cause of my problem was actually a failing discovery. This meant that an instance of the ‘Operations Manager Data Warehouse Watcher’ was not being created.

So what was wrong with the discovery?

Well, the discovery is based around a vbScript which includes a function that tries to determine the SQL Provider that should be used as part of the connection string. This function looks for the provider to use in two specific places.

First it looks for the key named SQLOLEDB under HKEY_LOCAL_MACHINE\CLSID{5A23DE84-1D7B-4A16-8DED-B29C09CB648D}. If the key is found then it’s value will be returned.

If that value can’t be located then it enumerates the registry keys under HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI to see if it can locate a suitable provider. And here’s where the problem kicks in.

It contains this code fragment:

intValue = InStr(oSubkey, "SQL Server Native Client")
if intValue <> 0 Then
  strValueName = "Driver" 

So it will only consider the provider to be valid if the key name contains ‘SQL Server Native Client’. Neither of our SCOM Servers had this key so the function returns a blank. This results in an invalid connection string meaning the discovery then fails.

I’m rather stunned to not find any mention of this issue anywhere online because the discovery mentioned above is one of the base discoveries so it must be present in all SCOM installations. I don’t know if our SCOM config is especially unusual or if there are people out there who just haven’t realised that they’re missing a class instance required for monitoring the data warehouse.

The solution? In our case we installed the SQL Server Native Client 11 and that solved the issue. It’s not an ideal solution as that particular client has been deprecated, but later clients install with a different value being used for the registry key so they wouldn’t address the issue.

If anyone has any questions then please let me know. Our current SCOM Environment is SCOM 2016 running with one MP on Server 2012 R2 and the other on 2016. This issue came to light as we’re running some checks prior to an upgrade which should bring the OS into parity before we upgrade to SCOM 2019.