OpsMgr 2012 Data Warehouse Health Check Report - TYANG

Report Date: 2015-Jun-04

Management Group Information

Data Warehouse SQL Server Information

Note:

Please compare your Operations Manager configuration with Microsoft's supported configuration:

System Requirements for System Center Operations Manager 2012 (7.0.8560.0)

System Requirements for System Center Operations Manager 2012 SP1 (Version 7.1.9538.0)

System Requirements for System Center Operations Manager 2012 R2 (Version 7.1.10226.0)

Data Warehouse SQL Database Information

Recommendations:

It is recommended the OpsMgr Data Warehouse DB is configured as the following:

• Recovery Mode: leave it as default (SIMPLE)
• Make sure it is been backed up on a schedule
• Other than CHECKDB, do not configure any other maintenance tasks against this database
• Use the OpsMgr 2012 Sizing Helper Tool to estimate the space required for the Data Warehouse Database.
• If Auto-Growth is enabled on the Data Warehouse database, please make sure the auto increase amount is configured to increase a reasonable amount. This will ensure the auto grow will not take place too frequently, and help reduce the data base file fragmentation.
• Make sure Auto-Shrink is disabled.

Please refer to Kevin Holman's article What SQL maintenance should I perform on my OpsMgr databases? for more information.

Data Warehouse SQL Server TempDB Configuration

Recommendations:

Please create separate equally sized files for the tempdb and size them appropriately. For better performance, please place them on faster disks.

Please refer to Page 19 of the SQL Server guide for System Center 2012 for detailed tempdb configuration recommendation.

Data Warehouse SQL Service Account Configuration

Recommendations:

It is recommended that the SQL Service Account has 'Performance Volume maintenance tasks' and 'Lock Pages in Memory' privilege on the SQL server.

Please refer to Page 16 of the SQL Server guide for System Center 2012 for details.

Note:

This health check script only checks direct privilege assignments. It does not check if the service account is a member of each groups that have been assigned to the privileges listed above. If this report indicates the service account does not have the privilege, you may manually check if the service account is a member of any groups listed in the local policy(gpedit.msc), under 'Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignments'

Data Warehouse Data Sets Information

Recommendations:

• Use dwdatarp.exe to configure data retention according to your business needs. reduce the retention peroids from default if the business does not require longer retention periods (for reporting).
• Use overrides to disable the performance and event collection rules that you do not wish to run reports against.
• Wherever you can, use overrides to increase the interval on how often the performance and event collection rules. This would help to reduce the amount of raw data stored in the Data Warehouse DB.

Additional Information:

• Understanding and modifying Data Warehouse retention and grooming By Kevin Holman
• Everything you wanted to know about OpsMgr Data Warehouse Grooming but were afraid to ask By Stefan Stranger

Data Warehouse Data Sets Aggregation Backlog

Additional Information:

• SCOM DWH aggregations data loose Tip and Tricks By Michel Kamp
• Get a grip on the DWH aggregations By Michel Kamp
• Dude where is my Availability Report data from the SCOM DWH ?? By Michel Kamp
• SCOM Standard Dataset maintenance workflow By Oleg Kapustin

Data Warehouse Database Staging Tables Row Count

Additional Information:

• Case of the fast growing SCOM datawarehouse db and logs By Bob Cornelissen

Key SQL Performance Counters

Key Operating System Performance Counters

Additional Information:

Please refer to Page 32 of the SQL Server guide for System Center 2012 for detailed explaination of the key SQL and OS performance counters.

Data Warehouse Related Events From Management Servers

Event ID: 2115, 8000 and 31550-31559

Additional Troubleshooting Resources:

• Event ID 2115: Event ID 2115 A Bind Data Source in Management Group
• Event ID 2115, 31552: Troubleshooting Blank Reports in System Center Operations Manager
• Event ID 31552: FIX: Failed to store data in the Data Warehouse due to a Exception ‘SqlException': Timeout expired.
• Event ID 31552: The 31552 event, or why is my data warehouse server consuming so much CPU?
• Event ID 31552: [HOWTO] Failed to store data in the Data Warehouse : Arithmetic overflow error converting expression to data type float.
• Event ID 31553: Standard Dataset Maintenance troubleshooter for System Center Operations Manager 2007