Wednesday, January 19, 2011

SCOM R2: Am I Healthy? – Part III – Are The DBs OK?

----------------------------------------------------------------------------------
Postings in the same series:
Part  I  The Introduction
Part II  – Know What You Have
Part IV – Is SCOM R2 Up-to-date or Outdated?
Part  V – Let’s Use The Community
----------------------------------------------------------------------------------

The third posting in this series is all about the SCOM R2 DBs and their health. The overall health AND performance is directly related to the SCOM R2 DBs and their health. So it is key to have an insight view into the status of them. This posting will enable you to do just that and in such a way that you don’t need to become a SQL or SCOM guru. Just like checking the oil of your car.

01 – Along came the Community
In the old days one had to run many different SQL queries against the DBs. However, thanks to the combined efforts of two fellow MVPs, Pete Zerger and Oskar Landman, this isn’t needed anymore.

Just import the SCC Health Check Management Pack Version 2 (RTFM is required since an additional data source has to be created) and you will get 27(!) new Reports, all targeted at showing the status of your SCOM R2 environment, including the Health of the DBs:
image
(The highlighted Reports are the ones related to the health of the SCOM DBs.)

And not just that. These Reports also give very good information, shown in the Report Details area when a Report is selected. Besides the explanation, some good url’s are shown as well. These url’s give you more information about Config Churn, how to identify it and how to battle it, the Known Issue with the LocalizedText table, eating away too much DB space and how to solve that one as well:
image

And:
image

02 – Let’s not forget the SCOM R2 Core MP…
On top of it all, the latest core MP for SCOM R2 does a really good job of monitoring the health of the SCOM DBs as well. The DBs are monitored in many ways so when something goes wrong you will certainly know it.

One important monitor to reckon with is the Operational Database Space Free (%) Monitor. As the name implies, it monitors the percentage of the available free space of the OpsMgr DB and raises a Warning when it falls below to 40% and creates an error when it falls below 20%:
image

When the OpsMgr DB has too less free space, many issues might happen, like being unable to import any new MP, as I blogged about earlier on. So do not alter this Monitor in any kind of way. And when an Alert comes in from this Monitor, act upon it.

Mind you, this Monitor does not monitor the size of the DB as a file on the disks, but looks inside the DB itself and monitors the available free space within that DB.  Have had some serious discussions about this some time ago.

03 – Let’s check the Default Settings for the SCOM R2 DBs
While installing SCOM R2 the DBs are created and the settings for the Recovery Model, Autogrowth and Autoshrink are configured based on best practice and shouldn’t be altered UNLESS you know what you’re doing and know its consequences. But never ever enable Autoshrink because it will automatically ‘enable’ a lot of unwanted issues as well…

Having said that it’s worthwhile checking these settings out since it wouldn’t be the first time a SQL Operator changed one or more of these settings (they seem to love the setting Auto Shrink) without knowing the consequences or communicate it to the SCOM people involved…

OperationsManager DB settings
The Recovery Model is set to Simple, Autogrowth to None and Autoshrink to False:image

And:
image 

Data Warehouse DB (OperationsManagerDW) settings
For this DB are the settings a bit different. The Recovery Model for the OperationsManager DB is set to Simple and Autoshrink to False. So far so good:
image

But the Autogrowth settings are different compared to the OpsMgr DB:
image

04 – Are the DBs properly dimensioned?
Based on my second posting you know by now how many objects are being monitored by SCOM R2. This amount has a direct relationship with the seize of the SCOM R2 DBs. So it is time to do some checking.

Do they match? Are the DBs properly sized or not? When they seem to have too much space, it is not an issue as long as there is enough disk space available and not required by other applications.

However, when the DBs are too small it is time to make them a bit bigger. Especially for the OpsMgr DB will this do some good work. But how do you know what size the DBs need to have? Good question! The answer is simple: Go here, download the sheet and use it as intended. It will explain itself.

Also something to reckon with, besides this sheet, are the recommendations from the field. Based on real life experience. To be found here. See screen dump as well:
image 

So now you have gained a good insight on your environment. But still there might be some issues at hand which need attention. And when taking a good look at those issues, those are related to the DBs as well. To name a few:

05 – The Ghost Computers. Am I being haunted?
Sometimes you might bump into an issue where you have removed some monitored Computers from the SCOM R2 environment. But they are still present in the SCOM R2 Console. No matter what you do, like clearing the cache of the Console for instance, they keep coming back, totally grayed out of course. So now what?

Good news! A much respected SCOM MVP for many years now (Maarten Goet) blogged about this issue some time ago. However, the solution is unsupported. So use it wisely and at your own risk. Since it is better to be safe then sorry, BACKUP the OpsMgr DB first before running the queries mentioned in his blog posting.

06 – Where are my new Reports?
When you import a new MP into SCOM R2, it will take a while before all the Reports do show up. They need to uploaded to the SQL Server Reporting Services (SSRS) instance. Sometimes however, they won’t appear at all or only partially. This latter might happen with the MP I mentioned in Item 01.

When does not create the required data source BEFORE importing this MP, only some pieces of the Reports contained within this MP will be uploaded to the SSRS instance. In order to remedy it go through these steps:

  1. Remove the MP,
  2. Create the data source as described in the related guide,
  3. Open SSRS in IE, remove the partially uploaded reports coming from this MP,
  4. Check out SCOM R2 Reporting. When all Reports related to this MP are gone, import the MP again and you will be just fine.

SCOM R2 will tell you when Reports are not being uploaded to the SSRS instance. However, the Alert does not tell you how to remedy it:
image

Times that I bumped into it the cause was related to altered permissions on the SCOM R2 services accounts. So check them out thoroughly. Also read this posting of mine. Resetting the accounts mentioned here will force SCOM R2 to upload the Reports contained in the MPs to the SSRS instance. So when any Reports are missing, they will be tried again. And when they go wrong, it will be logged in the OpsMgr event log of the RMS. So keep a keen eye on that log.

07 – Does the information flow into the DBs?
Sometimes a hiccup might occur and the information, collected from the monitored servers and other objects, might not flow from the Management Servers into the SCOM DBs. When that happens, SCOM R2 will tell you.

Times I bumped into this issue the cause was to be found into altered permissions. So check the SCOM R2 service accounts. Other temporary issues were network related, like an enabled firewall on the SQL server without having set the proper exceptions to be found here (under the header Operations Manager 2007 Firewall Scenarios).

08 – Does Grooming Work?
Important to know is whether grooming is still running and functional. Grooming means that closed Alerts are groomed out of the OpsMgr DB as configured in the Database Grooming settings:
image

In order to check whether grooming is doing fine and when not, how to solve it, go here.

As you can see much of the Health of SCOM is directly related to the DBs. It goes without saying that the underlying Server OS and hardware (whether P or V) must be up to specs as well. So check it out your self and regain control over SCOM R2. Next posting in this series will be all about updates and the lot. So stay tuned and see you all next time.

No comments: