Multiple Management Group Rollup Solution Accelerator

On This Page

	Executive Summary
	Introduction
	Testing Overview
	DTS Package
	MOM 2005 Data Warehouse Schema
	MOM 2005 Reports

Executive Summary

In the increasingly important and complex world of information technology (IT) operations, it is essential to implement a robust and reliable systems management infrastructure based on proven methods. Service Monitoring enables data center managers to increase operational efficiency and to achieve a higher state of availability for mission-critical applications and management of Microsoft® Windows® services. Increased levels of performance can be achieved on Windows platforms through the implementation of Service Monitoring, which incorporates best-practice guidance in planning, building, designing, and deploying Microsoft® Operations Manager (MOM) 2005 to monitor Windows applications and services.

MOM 2005 delivers open and scalable enterprise-class operational management by providing comprehensive event management, proactive monitoring and alerting, reporting and trend analysis, and knowledge and tasks that are specific to systems and applications. Companies have come to rely on MOM to provide overall visibility to the health of their complex computing environment.

MOM 2005 includes a data warehouse and a Data Transformation Services (DTS) component that transfers data periodically from a MOM database from a single management group to a data warehouse for optimized storage and analysis. This solution accelerator contains guidance on how to propagate data from multiple management groups into a centralized data warehouse using the MOM 2005 data warehouse DTS package.

This solution accelerator provides the following capabilities:

Note Using the MMGR Solution Accelerator with Active Directory Management Pack (ADMP) is not recommended. MMGR has compatibility issues with ADMP. The data from ADMP reports will be incomplete or invalid when used with MMGR.

Top of page

Introduction

Document Purpose

This test execution report describes the observations made while validating and implementing the population of a Microsoft Operations Manager (MOM) 2005 data warehouse from multiple management groups. This document is mainly based on testing the behavior of Multiple Management Group Rollup (MMGR) using the out-of-the-box MOM 2005 data warehouse Data Transformation Services (DTS) package. It provides information about the test plan, partial test results, and best practices on how to achieve MMGR using the MOM 2005 data warehouse DTS. In addition, this document includes some Microsoft SQL Server™ queries that can be used in a report to make it output data from different management groups. Full test results are contained in the Test Case Details spreadsheet for MMGR, which corresponds to this document.

Note The content of this test execution report is based on a MOM Release Candidate 2 (RC2) build.

Intended Audience

This test execution report is intended for IT professionals in data centers and in large and enterprise organizations. In particular, MOM administrators, help-desk personnel, and others involved in service monitoring and control should find this document helpful.

Since this test execution report is based on MOM 2005, the reader should have a solid understanding of it. Further information is contained in the MOM 2005 documentation and training resources.

Terminology

This document uses terminology that is current for MOM 2005. The following table lists the changes in terminology between MOM 2000 Service Pack 1 (SP1) and MOM 2005.

Table 1. Terminology Changes Between MOM 2000 SP1 and MOM 2005

Feedback

Please direct questions and feedback about this document to msmfeed@microsoft.com.

Top of page

Testing Overview

This chapter provides information about the scope of the testing that took place. In addition, it supplies physical and logical diagrams of the test environment and includes a configuration matrix of the servers used for conducting the testing.

Scope of Testing

In Scope

The following items were in scope:

Out of Scope

The following items were out of scope:

Test Setup

Network Diagram

The test platform consisted of a virtual enterprise environment that is based on Windows Server System™ Reference Architecture (WSSRA), formerly known as Microsoft Systems Architecture (MSA) 2.0. The lab diagrams (both physical and logical) of the test environment are shown in Figure 1 and Figure 2, respectively.

Figure 1. Physical diagram of the test environment
See full-sized image

Figure 2. Logical diagram of the test environment
See full-sized image

Configuration Matrix

The configuration matrix of the servers (shown in the following table) lists the servers used for the testing, along with the name of the management group, SQL instance name, domain, Data Access Server (DAS) account, MOM service account, hardware specifications (regarding the processor and memory), number of drives, and capacity.

Table 2. Configuration Matrix of Servers Used for Testing

Top of page

DTS Package

Populating a MOM 2005 data warehouse involves importing data from multiple source management groups and one destination management group. This data includes operational data (such as alerts, events, and sampled numeric data) as well as service discovery data.

As stated previously, the importing of data from the management groups to the data warehouse is achieved through the SQL Server Data Transformation Services (DTS) package, which is available out of the box in MOM 2005. The DTS is wrapped around an .exe file and is configured as a scheduled task. The user can run the executable file via the command prompt also.

The following figure illustrates the DTS data transfer from a single management group.

Figure 3. DTS data transfer from a single management group
See full-sized image

The next figure illustrates the concept of Multiple Management Group Rollup (MMGR), whereby the operational data from multiple management groups is “rolled up” to the MOM data warehouse via DTS.

Figure 4. DTS data transfer from multiple management groups
See full-sized image

By default, a single scheduled task is created that will run the .exe file. The .exe dynamically creates the DTS package and runs it. Only one instance of the DTS package can run at a time, since the DTS task does not support running multiple, simultaneous processes. This DTS package runs in the data warehouse server and pulls data from the management groups into the data warehouse.

The single scheduled task that is created by default after installing MOM Reporting will import data from only a single management group. To import data from multiple management groups, an additional scheduled task needs to be manually created and configured for every additional management group present, as shown in the following figure.

Figure 5. Creating and configuring additional scheduled tasks

The various parameters that need to be set up and configured (as depicted in the preceding figure) are as follows:

To implement MMGR, all the scheduled tasks need to be executed sequentially. One of the ways that this can be accomplished is by wrapping all the scheduled tasks under a batch file. The logic of the batch file is to execute the next scheduled task after the completion of the previous one. This enables the “rolling-up” of data from multiple management groups.

Command-Line Details

As stated, the executable file can be run via the command prompt also, in accordance with the following details:

As was true for running the executable file from the graphical user interface (GUI), after the installation of MOM Reporting, only one scheduled task is created. This enables importing of data from only one management group. To import data from multiple management groups, each separate scheduled task needs to be created individually, and the server parameters need to be changed accordingly.

Event Logs

When the DTS package completes successfully, an event is logged in Event Viewer. The following screenshot captures the details of the event type.

Figure 6. Details of the event type

As seen from the preceding figure, the event that is logged in Event Viewer does not just indicate the successful completion of the DTS package. It also includes the data statistics of each task present in the DTS package, such as the time taken for the completion of each task.

Key Findings

The following are key findings with regard to the DTS task:

Limitations and Workarounds

Open Issues

The following are open issues:

Performance

The following table lists the load rates that were used to populate the simulated data in the MOM database.

Table 3. Load Rates Used for Simulating Data in a MOM Database

The preceding load rates achieve a database size of ~ 31 gigabytes (GB) for the MOM Operational database. The DTS import of one day’s worth of data took 5.5 hours to complete.

Note Empty cells in the preceding table indicate that the parameter (such as computers or alerts) is independent of the number of computers per day, number of days, and so on.

Top of page

MOM 2005 Data Warehouse Schema

This chapter contains useful information for querying the Microsoft Operations Manager (MOM) 2005 data warehouse. The data warehouse features a dimensional model schema optimized for reporting and analysis. The following two figures show the data warehouse schema in terms of operations data and rules. They are followed by a third figure that shows the computer, MOM server, and MOM agent classes.

Figure 7. Data warehouse schema: operations data
See full-sized image

Figure 8. Data warehouse schema: rules
See full-sized image

Figure 9. Computer, MOM server, and MOM agent classes
See full-sized image

It is recommended that all queries against the data warehouse be restricted to the presentation area views documented here. These views are easily identified by their names, which follow the pattern SC_*_View except for the views with names of type SC_Rel_*_View. Broadly speaking, there are four types of presentation area views documented in this test execution report:

Property names adhere to the following conventions:

Example Number One—Alert Properties

This example shows how to query the data warehouse to return a list of alerts in which the properties of the alerts are similar to the view in the MOM Operator console. Specific to this example, a list of alerts and the following properties should be returned:

The following figure shows SC_AlertFact_View as well as some related dimension views.

Figure 10. SC_AlertFact_View and related dimension views
See full-sized image

In the preceding figure, each row in SC_AlertFact_View contains information about a single alert. Data unique to individual alerts, such as the Alert ID and Repeat Count, is represented as a column in SC_AlertFact_View. Data that can be shared between alerts, such as information about the computer on which the alert was raised, is stored in the related dimension views. The alert view contains references to these related tables. For example, the property Computer_FK contains the instance ID of the row in SC_ComputerDimension_View, which in turn contains information about the computer on which that alert was raised. Multiple alerts can refer to the same computer.

In order to query the data warehouse to return the properties, it is necessary to join SC_AlertFact_View with related views containing the properties that need to be returned. Inspecting the SC_AlertFact_View diagram yields the following information.

Table 4. Field, View, and Property

Therefore, the query needs to join SC_AlertFact_View with SC_AlertLevelDimension_View and SC_ComputerDimension_View. The following SQL query is used to do this.

Note Some parts of the following code snippet have been displayed in multiple lines only for better readability. These should be entered in a single line.

SELECT ALD.AlertLevelName AS Severity, AF.LocalDateTimeRaised AS Time, 
CD.FullComputerName AS Computer, AF.AlertName as Name, 
AF.RepeatCount AS RepeatCount
FROM SC_AlertFact_View AF INNER JOIN SC_AlertLevelDimension_View ALD ON 
AF.AlertLevel_FK = ALD.SMC_InstanceID INNER JOIN SC_ComputerDimension_View
CD ON AF.Computer_FK = CD.SMC_InstanceID

Example Number Two—Classes and Relationships

In this example, a query is authored against the operating system and the Installed Software classes to return the server name, operating system name, application name, and installation date for all software installed on a computer. Specific to this example, the data needs to be returned in the following form:

Inspecting the data warehouse schema diagram yields the information shown in the following table.

Table 5. Field, View, and Property

Figure 11. Class views
See full-sized image

Therefore, the query needed to join the three views shown in the preceding figure is as follows.

Note Some parts of the following code snippet have been displayed in multiple lines only for better readability. These should be entered in a single line.

SELECT OS.[Server Name], OS.[Operating System Name], 
ISw.[Application Name], ISw.[Installation Date]
FROM SC_Class_OS_View OS INNER JOIN [SC_Class_Rel_OS-Installed
Software_View] OSIS ON OS.ClassInstanceID = OSIS.SourceClassInstanceID
INNER JOIN [SC_Class_Installed_Software_View] ISw ON 
OSIS.TargetClassInstanceID = ISw.ClassInstanceID

All the data imported from the management groups is stored in the MOM 2005 data warehouse in the dimensional model, within the fact and dimension tables as follows:

Fact Tables

There are two kinds of fact tables in the data warehouse: periodic snapshot fact tables and additive fact tables. The classification is based on how the records for the fact tables are imported via the DTS package.

Periodic Snapshot Fact Tables

Periodic snapshot fact tables store the records that are collected from the MOM database with a time snapshot wrapped around them before they are added to the data warehouse. The time snapshot represents the time of the DTS execution during each run. This is done so that the views referencing the fact tables in the data warehouse retrieve and reflect the latest set of records that are imported from the MOM database, which directly translates to the user viewing the latest information in the reports.

Since all the records are brought into the data warehouse for every DTS import, from the corresponding source tables in the MOM database, the grooming schedule on these particular tables is set more frequently in the data warehouse.

The following are examples of a few periodic snapshot fact tables and their corresponding source tables in the MOM database.

Table 6. Periodic Snapshot Fact Tables and Source Tables

Additive Fact Tables

For additive fact tables, only the delta of the rows changed since the last DTS import is brought into the data warehouse. The delta for these tables is determined by watermarks that are defined for each of the corresponding source tables present in the MOM database. (In contrast, periodic snapshot fact tables do not have watermarks defined.) Watermarks are updated after every DTS run. This is to ensure that there is no duplication of data during multiple DTS imports from a particular management group.

There are two watermarks associated with the DTS package:

The following table provides a few examples of additive fact tables, their corresponding source tables in the MOM database, and the filter column.

Table 7. Additive Fact Tables, Source Tables, and Filter Column

*Denotes database views

Dimension Tables

All dimension tables contain updateable dimensions, and, as stated, they do not have any filtering or watermarks defined. Consequently, all rows in these tables within the MOM database are transferred during each DTS import. Existing rows are updated and new rows, if any, are added. The following is a list of a few dimension tables present in the data warehouse.

Table 8. Dimension Tables and Source Tables

Top of page

MOM 2005 Reports

Although out of scope for testing, a few of the MOM 2005 reports were informally tested to observe the effects of multiple management group rollup. This chapter discusses these observations in terms of Management Group Health, Alert Analysis, and Agent Health Detail.

There are limitations with MOM 2005 reports when displaying data from multiple management groups, since all reports do not currently support having management group as a criterion. The latter part of this chapter includes best practices that can be used in reports to display data from multiple management groups.

Operational Data Reporting: Management Group Health

Informal testing revealed that only the management group for which the first DTS import took place is displayed in the report, in spite of data being imported from multiple management groups. This is because Management Group has not been included as a criterion when displaying reports.

Figure 12. Management Group Health

Operational Health Analysis: Alert Analysis

The following observations were made during informal testing:

•	The report results are skewed, since alerts appear multiple times in the report.
•	This happens because the SQL Server query used to generate the report does not return the right results, since there is no filter based on management groups. Therefore, multiple rule groups appear, which in turn causes duplicate alerts to be displayed. Figure 13. Alert Analysis See full-sized image

Microsoft Operations Manager: Agent Health Detail

The following observations were made during informal testing:

•	The report results are skewed, since alerts are appearing multiple times in the report.
•	This happens because the SQL Server query used to generate the report does not return the right results, since there is no filter based on management groups. Therefore, there are multiple event details appearing, which in turn causes duplicate events to show up in the report. Figure 14. Agent Health Detail

Best Practices

There are several best practices that can be used in the reports to display data from multiple management groups. In order to incorporate these best practices, the reports need to be customized. Further information about customizing reports is available in the MOM 2005 product documentation.

use SystemCenterReporting
SELECT
count(*) Alerts_in_DW
FROM
dbo.SC_AlertFact_View A
WHERE
A.ConfigurationGroup_FK = ( SELECT CG.SMC_InstanceID FROM 
SC_ConfigurationGroupDimension_View CG WHERE CG.
ConfigurationGroupName = 'ZMGXX' ).

SELECT
count(*) Events_in_DW
FROM
dbo.SC_EventFact_View E
WHERE
E.ConfigurationGroup_FK = ( SELECT CG.SMC_InstanceID FROM 
SC_ConfigurationGroupDimension_View CG WHERE CG.
ConfigurationGroupName = 'ZMGXX' ).

SELECT
count(*) SND_in_DW
FROM
dbo.SC_SampledNumericDataFact_View S
WHERE
S.ConfigurationGroup_FK = ( SELECT CG.SMC_InstanceID FROM 
SC_ConfigurationGroupDimension_View CG WHERE CG.
ConfigurationGroupName = 'ZMGXX' ).

SELECT
CG.ConfigurationGroupName, 
CR.[Name] as ComputerGroups,
C.FullComputerName 
FROM
SC_ComputerToComputerRuleFact_Latest_View CCR,
SC_ComputerDimension_View C, 
SC_ComputerRuleDimension_View CR,
SC_ConfigurationGroupDimension_View CG
WHERE
CCR.Computer_FK = C.SMC_InstanceID AND
CCR.ComputerRule_FK = CR.SMC_InstanceID AND 
CCR.ConfigurationGroup_FK = CG.SMC_InstanceID AND 
CG.ConfigurationGroupName = 'ZMG01'
ORDER BY
CR.[Name]

SELECT
CG.ConfigurationGroupName, 
PR.ProcessRuleName 
FROM
SC_ConfigurationGroupDimension_View CG,
SC_ProcessRuleToConfigurationGroupDimension_View PRCG,
SC_ProcessRuleDimension_View PR
WHERE
PRCG.ConfigurationGroup_FK_PK = CG.SMC_InstanceID
AND
PRCG.ProcessRule_FK_PK = PR.SMC_InstanceID

SELECT
C.fullComputerName,
A.AlertDescription 
FROM
SC_ComputerDimension_View C
JOIN
SC_Alertfact_View A
ON
C.SMC_InstanceID = Computer_FK
AND
C.Fullcomputername = 'Domainxx\ComputerXX'

SELECT
CR.[Name] AS ComputerGroups,
C.FullComputerName, 
A.AlertDescription 
FROM
SC_ComputerToComputerRuleFact_Latest_View CCR, 
SC_ComputerDimension_Table C,
SC_ComputerRuleDimension_Table CR,
SC_Alertfact_table A
WHERE
C.SMC_InstanceID = A.Computer_FK AND 
CCR.Computer_FK = C.SMC_InstanceID AND 
CCR.ComputerRule_FK = CR.SMC_InstanceID AND 
CR.[name] = 'Microsoft Windows 2003 IIS SMTP Servers'

Top of page