Microsoft Operations Manager 2000

  • Article
Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Chapter 6 - MOM Database Management

This chapter presents the operational guidelines for maintaining a secure and optimal Microsoft Operations Manager 2000 (MOM) database. Also referred to as the OnePoint database, the MOM database is the central storage facility for events, alerts, performance data, rules, and scripts within a configuration group. In defining a healthy MOM database, the following topics are covered within this chapter:

Send feedback on this chapter to MOM Operations Guide Author.

On This Page

Installation and Configuration
Maintenance
Monitoring the MOM Database
Back Up and Restore
Troubleshooting the MOM Database

Installation and Configuration

It is essential for the health and optimization of the database to follow some key guidelines when installing and configuring the MOM database. This section provides recommendations in the following areas:

  • Installation best practices

  • Optimal hardware

  • Memory configuration

  • Database sizing

Installation

The correct installation of the Microsoft SQL ServerTM database is very important. If configured incorrectly, the consequence is poor performance.

Some database installation best practices are as follows:

  • Before installation, use the database calculators provided in the "Performance and Sizing" technical paper to assist in determining what size your database will be. These calculators are also helpful in maintaining and tuning your MOM database.

  • The SQL Server database should not be installed on the same disk drive as the operating system or the MOM DCAM components.

  • Partitioning the disk drive on which the database is installed will not improve performance because the drive is still governed by the same disk controller.

  • Store the database log file on a separate disk drive for further efficiency.

MOM supports SQL Server 2000 (Standard and Enterprise) in a nonclustered environment, but does not support multiple instances of SQL Server 2000.

Note If you do not currently have SQL Server 2000 installed, the Express installation of MOM installs the Microsoft Data Engine (MSDE). MSDE supports up to 2 GB of data and has a running query connection limit of five, which limits a MOM Express installation to 10 monitored computers. This scenario, however, is not recommended for production environments.

For more information about installation, see the Microsoft Operations Manager 2000 Installation Guide at https://www.microsoft.com/mom/techinfo/productdoc/default.asp, and the "Performance and Sizing" technical paper at https://www.microsoft.com/mom/docs/perfsize.doc.

Database Redundancy

MOM does not support multiple instances of the SQL Server database. However, one third-party solution is available from Legato. Legato's CoStandbyServer allows you to replicate data to a parallel database in real time. In brief, it uses a mirrored disk solution and a virtual IP address representing a clustered server running SQL Server. For more information about how CoStandbyServer works and how to purchase it, see https://www.legato.com/.

Hardware

Because the MOM database is very active with events and alerts continuously being inserted into it, MOM requires adequate CPU power and memory to keep the server at optimum performance. Furthermore, depending on the number of servers that MOM will be monitoring, the way you configure the MOM database can directly affect the performance of your entire MOM system. This is especially critical in an enterprise network environment.

Disk System

For best performance, install the MOM database on a RAID 10 array. RAID 10 provides high I/O rates by striping RAID 1 segments. Proper disk selection is especially important for operating an optimum MOM system.

Important Each disk can process approximately 70 I/O operations per second, which is 100-percent usage. It is recommended that a disk does not exceed 85-percent usage, which translates to approximately 60 I/O operations per second.

The number of disk I/O read/write operations is directly proportional to the number of managed nodes you have. Table 6.1 shows the minimum hardware recommendations based on your configuration.

Table 6.1

Medium Configuration (Up to 250 Agents)

Large Configuration (Up to 1000 Agents)

CPU

2 x 550 MHz or higher

Memory

1 GB

RAID card

Two channels

Disks C (channel 1)

2 x 9.1 GB (or larger) disks;
RAID 1, OS, and SQL program files

Disks D (channel 2)

6 x 9.1 GB (or larger) disks;
RAID 10 for database and log file.

Disks E (channel 3)

Optional log file disk.

Network interface card

100 Mbps

Note If cost is not a concern and optimum performance is the key consideration, use RAID 1 or RAID 1+0 (RAID 10) for the transaction log drive as well as the database. Use RAID 1 or RAID 10 instead of RAID 5, where indicated in the previous table.

RAID 10 delivers better performance because it requires only one additional write I/O operation for each write request (one write to each disk in the mirror). For example, a transaction at RAID 0 generates ten reads and one write. The same transaction at RAID 1 or RAID 10 generates ten reads and two writes. In contrast, using RAID 5, the same transaction generates twelve reads and two write operations.

Memory Configuration

Configuring the database correctly for memory usage is crucial. In limited-memory situations, the server processes can consume all the memory and cause page faults. By default, the MOM OnePoint database dynamically adjusts the memory usage based on demand. This means that SQL Server uses as much RAM as it needs and finds available and then releases RAM when other resources need memory. To prevent this from happening, you can limit the SQL Server memory usage by configuring the memory to be static, rather than dynamic.

To calculate the fixed amount of memory you should use, subtract the memory required by your operating system and any other critical services running on the server and apply the remainder to the fixed memory setting.

To configure SQL Server memory

  1. Open SQL Server Enterprise Manager.

  2. Expand Microsoft SQL Servers, and then SQL Server Group.

  3. Right-click the server you want to configure, and then click Properties.

  4. Click the Memory tab, and then click Use a fixed memory size (MB).

  5. In the Minimum query memory (KB) box, specify the amount of RAM you want to allocate for the server.

Database Sizing

Database sizing depends on:

  • The number of managed servers.

  • The amount of events you plan to collect.

  • The Management Packs you plan to deploy.

These are the input factors to the database. On the other hand, data grooming determines the output factor of the database. The following calculations are provided to help you estimate the size of the database and I/O table information needed to estimate how many I/O operations will be generated by the various managed computer workloads. For additional tools and calculations to assist you in sizing your database, see the "Performance and Sizing" technical paper at https://www.microsoft.com/mom/docs/perfsize.doc.

Windows NT Event Log Size (not included security events)

(((Events/min×Event Size)Time) ×Groom Parameter)

Windows NT Security Event Log Size

((Security Events/min×Event Size)Time)×Groom Parameter)

Resolved Alerts Size

(((UnSur. Alerts/min×Alert Size)×Time)×Groom Parameter)

Sampled Numeric Data Size

(((Performance Counters×Counter Size)×Time)×Groom Parameter)

General database space consumption for alerts, events, and performance data are as follows:

  • Alerts: 4–6 KB each

  • Events: 500–2700 bytes each

  • Performance data: 195 bytes for each counter instance

Maximum Supported Database Size

MOM supports the OnePoint database up to 30 GB because a larger database can cause alert latency. Keeping your database at 30 GB or less allows alerts to show up in the MOM Administrator console within approximately two minutes.

Best Practice Database Size

While the supported limit is 30 GB, you should keep your database between 12 GB and 15 GB. The grooming jobs in MOM run hourly by default. During their execution, they lock the tables they are accessing. With a large database, this can cause poor database performance. Keeping the database small will allow it to be agile and respond quickly to transactions. Because reindexing jobs run daily, they can also impact performance if your database size is not maintained.

Elements That Can Affect Database Size

Default Event Collection for Windows NT and Windows 2000

If enabled, the Microsoft® Windows NT® and Windows® 2000 Management Packs collect every event from the Windows application, security, and system logs. If you did not clear or disable the Management Pack during setup, your database might grow more quickly than is manageable.

Too Many Management Packs Installed

You should include only the Management Packs you plan to use initially because you can always install others at a later date. This allows only the intended-use rules to be pushed to the agents, thereby minimizing superfluous data being transmitted between the agent and the Consolidator.

Global Setting for the Agent Service Uptime

The Agent Service Uptime check box, which is selected by default, can produce many internally-generated 21224 and 21225 events, consuming space in the database. The 21224 events are generated by the MOM agent to indicate that the agent is functional, while the 21225 events are generated by the MOM agent to indicate that the specified Windows NT service is functional. You might want to clear this check box if you do not need a service uptime report and rely on each specific application to monitor its own service uptime.

Note You can monitor the service uptime data by using one of three reports about service availability. To access these reports, in the Reporting tool, expand Windows NT/2000, and then expand Windows NT/2000 Operations.

Audit Log Is Enabled

Audit logging is used to track configuration and rule changes, and by default, is not enabled. If it is enabled for troubleshooting purposes, you should disable it immediately because it can be CPU intensive and consumes a large amount of space on the database. For assistance in manually clearing the auditlog table, contact Microsoft Product Support Services.

Automatic File Growth

The OnePoint database should not be configured for automatic file growth. During automatic file growth, all database operations are suspended. A database operation that needs uninterrupted access to the database (for example, insertion or a maintenance job) might attempt to write to the database during automatic file growth, but the attempt does not succeed. This condition can cause the database to keep MOM from functioning properly. Database size should be determined through proper capacity planning.

To safely disable automatic file growth

  1. Stop the OnePoint service.

  2. In SQL Server 2000 Enterprise Manager, expand SQL Server Group under Microsoft SQL Server.

  3. Click the server name, and then expand Databases.

  4. Right-click the OnePoint database, click Properties, and then click the Data Files tab.

  5. Clear the Automatically grow file check box.

Maintenance

This section provides key guidelines for database maintenance in the following areas:

  • Reindexing

  • Grooming

  • Using the SQL Server Maintenance Plan Wizard

Reindexing

MOM provides two database reindexing jobs: the OnePoint–Reindex and the OnePoint–Reindex Event Table. The OnePoint–Reindex Event Table job runs once per day, Monday through Saturday, at 12:00 midnight, by default. The OnePoint–Reindex job runs each Sunday at 10:00 P.M. by default.

These jobs lock the tables during the indexing process and prohibit any write operations to those database tables. If the database gets too large, it takes longer to run the reindexing jobs and can impact performance. It can also take longer for an event to be written to the database and for an alert to be generated. Finally, failure to run these reindexing jobs can cause data read operations to take longer in the database.

Grooming

Database grooming refers to the process of deleting data from the MOM database and automatically resolving alerts. The grooming process is a SQLSERVERAgent batch job that resolves alerts or deletes the collected events, alerts, performance counters, and other events from the database. When customizing your grooming jobs, it is important to keep the following in mind:

  • Determine how long it takes to save data because more data means longer grooming cycles.

  • Ensure sufficient lag time between grooming jobs to allow for growth and event storms.

  • Set transaction sizes for grooming jobs.

  • Monitor grooming job failures and configure an alert rule to automatically alert you.

  • Generate reports about grooming jobs to provide statistics for refining grooming.

Default Grooming Jobs

MOM provides default grooming jobs in the MOM database. These grooming jobs use the retention times and the number of records to delete specified in the Global Settings dialog box in the MOM Administrator console. The settings can be changed to meet your individual requirements. This allows you to increase or shorten the amount of time that alerts and events are actively stored in the OnePoint database. For more information, see the "Modifying Grooming Settings" section later in this chapter. Table 6.2 shows the default database grooming jobs in the MOM database:

Table 6.2

Grooming Job

Definition

OnePoint–Check Free Data Space

Scheduled to run every 2 hours, starting daily at 12:45 A.M. This job calculates the percentage of free database space and sends the information to MOM as an event.

OnePoint–Check Integrity

Scheduled to run once a week on Saturday at 10 P.M. This job validates the integrity of everything in the MOM database. For example, it checks that the index and data pages are correctly linked and that indexes are in their proper sort order.

OnePoint–Groom Alerts

Scheduled to run every hour, and runs for a maximum of 30 minutes, even if there is more data to groom. This job grooms resolved alerts from the database based on the information specified in the Global Settings dialog box.

OnePoint–Groom Events

Scheduled to run every hour. This job grooms the following events:

Windows NT event log

Windows NT security log

Other events, which are primarily Windows Management Instrumentation (WMI) events

Therefore, the maximum total time this job can run is 90 minutes (30 minutes each).

OnePoint–Groom Sampled Numeric Data

Scheduled to run every hour, and runs for a maximum of 30 minutes. This job grooms sampled numeric data from the database based on the information specified in the Global Settings dialog box.

OnePoint–Reindex

Scheduled to run once a week on Sunday at 10 P.M. This job rebuilds the MOM table indexes to improve performance. The OnePoint–Reindex job requires approximately 40 percent of free space within the database to successfully complete. For example, a 10-GB database needs 14 GB of database space to successfully rebuild the indexes. If the jobs fail due to a lack of disk space, the failure will not adversely affect your MOM implementation.

OnePoint–Reindex Event Table

Scheduled to run once a day at 12 A.M, except Sundays. This job rebuilds the event table indexes to improve performance. The OnePoint–Reindex Event Table job requires approximately 40 percent of free space within the database to successfully complete. For example, a 10-GB database needs 14 GB of database space to successfully rebuild the indexes. If the jobs fail due to a lack of disk space, the failure will not adversely affect your MOM implementation.

OnePoint–TodayStatisticsUpdateComputersAndAlerts

Scheduled to run every five minutes. This job updates the OnePoint Operations Manager Today window.

OnePoint–TodayStatisticsUpdateEvents

Scheduled to run every 30 minutes. This job updates the OnePoint Operations Manager Today window.

OnePoint–TodayStatisticsUpdatePerfmonRulesKB

Scheduled to run every hour. This job updates the OnePoint Operations Manager Today window.

OnePoint–Update Database

Scheduled to run every hour. This job grooms automatically resolved alerts based on the information specified in the Global Settings dialog box.

OnePoint–Update Statistics

Scheduled to run once a day at 1 A.M. This job updates information about key value distribution in the database to improve performance.

Default Grooming Parameters

As noted in Table 6.2, some grooming jobs have a limit to the number of events or alerts that are removed during the job execution. This is done to help keep the jobs from running too long and locking the MOM tables. Grooming jobs also have a limit on their maximum run time of 30 minutes.

Note While each grooming job has a maximum run time of 30 minutes, the OnePoint–Groom Events job consists of three jobs and therefore can total up to 90 minutes.

Table 6.3 shows the default database grooming parameters:

Table 6.3

Records to Delete/Number Deleted

Older Than

Associated SQL Agent Job

All sampled numeric data/4000

90 days

OnePoint–Groom Sampled Numeric Data

Automatically resolved error alerts/All

2 days

OnePoint–Update Database

Automatically resolved informational alerts/All

4 hours

OnePoint–Update Database

Automatically resolved success alerts/All

4 hours

OnePoint–Update Database

Automatically resolved warning alerts/All

1 day

OnePoint–Update Database

Other events/4000

7 days

OnePoint–Groom Events

Resolved alerts/4000

30 days

OnePoint–Groom Alerts

Windows NT event logs not including security/4000

7 days

OnePoint–Groom Events

Windows NT security event log/4000

30 days

OnePoint–Groom Events

Querying the MOM database

You can directly query the MOM database to determine if there are very large numbers of alerts and events waiting for grooming. This is helpful in determining whether you should adjust your grooming job frequency, as well as to ensure your database grooming jobs are being completed in an appropriate timeframe.

To query the database for grooming job time

  1. In SQL Server Query Analyzer, create a new query.

  2. In the Query window, type the following:

    3. 



SELECT jobname, timestarted, duration=datediff(s, timestarted, timeended)
FROM groomhistory GH
INNER join groomjob GJ on GH.idGroomJob=GJ.idGroomJob



    

  1. From the Query menu, select Change Database, and then select the OnePoint database from the drop-down menu.
    2. 



To query the database for alerts awaiting grooming


    

  1. In SQL Server Query Analyzer, create a new query.
    

    2. 

  2. In the Query window, type the following:
    

    3. 



SELECT COUNT(*) AS AlertsAwaitingGrooming
FROM dbo.Alert
WHERE (ResolutionState=255)



    

  1. From the Query menu, select Change Database, and then select the OnePoint database from the drop-down menu.
    2. 



To query the database for all specific alerts awaiting grooming


    

  1. In SQL Server Query Analyzer, create a new query.
    

    2. 

  2. In the Query window, type the following:
    

    3. 



SELECT Name, Description, AlertLevel, RepeatCount, ResolvedBY, TimeResolved
FROM dbo.Alert
WHERE (ResolutionState=255)



    

  1. From the Query menu, select Change Database, and then select the OnePoint database from the drop-down menu.
    2. 



To query the database for events awaiting grooming


    

  1. In SQL Server Query Analyzer, create a new query.
    

    2. 

  2. In the Query window, type the following:
    

    3. 



SELECT COUNT(*) AS EventCount
FROM dbo.Event
WHERE (ResolutionState=255)



    

  1. From the Query menu, select Change Database, and then select the OnePoint database from the drop-down menu.
    2. 



Auto Resolve


The Auto Resolve grooming task automatically sets Error, Warning, Information, and Success alerts from New to Resolved if they have not been assigned a status by an operator. The OnePoint–Update Database job is responsible for changing the resolution state. This job affects records that are older than the values defined on the Database Grooming tab in the Global Settings dialog box.


When an alert is set to the status of Resolved, either by an operator or the Auto Resolve grooming task, it will remain in the database for 30 days by default. The OnePoint–Groom Alerts job will delete alerts older than the value defined in the Global Settings dialog box. This default value of 30 days can be changed to delete resolved alerts sooner.


Alerts Stored in the Database


Some alerts that are not set to Resolved might remain in the database indefinitely. Only Error, Warning, Information, and Success alerts are automatically set to Resolved by the OnePoint–Update Database job. Unresolved alerts can consume database space over time. Acknowledged alerts will not be automatically resolved and deleted from the database. The acknowledged alerts need to be manually set to Resolved for the grooming jobs to delete them from the database.


Note Because the Security Breach and Service Unavailable alerts are not automatically resolved, you need to manually set them to a Resolved status if necessary.


GroomHistory Table


MOM tracks groomed data operations in the GroomHistory table within the OnePoint database. It is a useful tool for monitoring and tuning your grooming jobs because the resulting number of groomed rows is logged in the GroomHistory table. Therefore, the table can be used to identify which grooming jobs ran. If a grooming job did not run due to a lack of data, the row for that grooming job is not inserted in the GroomHistory table.


It is important to note that if you groom MOM data frequently, the GroomHistory table might become very large because MOM does not automatically delete the rows in the table. However, you can manually delete a range of rows from the GroomHistory table to reclaim database space by using the following Transact-SQL statement in SQL Server Query Analyzer.


Important You should back up the table before you run any queries against the table.


To manually delete rows in the GroomHistory table using SQL Server Query Analyzer


    

  1. Open SQL Server Query Analyzer.
    

    2. 

  2. On the File menu, click New, and then click Blank Query Window.
    

    3. 

  3. In the Query window, type the following (changing the date as appropriate for your environment):
    

    4. 



USE OnePoint
DELETE FROM GroomHistory
WHERE TimeEnded <'May 1, 1997'



    

  1. Press F5 to run the query.
    2. 



The query returns a result indicating the number of rows affected.


Modifying Grooming Settings


MOM provides easy access to database grooming settings in the MOM Administrator console Global Settings dialog box. On the Database Grooming tab, you can change the number of records deleted and the retention time for events, alerts performance data, and logs. You can also set the retention time for automatic resolution of alerts.


To change database grooming in the Global Settings dialog box


    

  1. Log on to the DCAM server with an account that is part of the OnePointOp ConfigAdms group.
    

    2. 

  2. From the Microsoft Operations Manager program folder, start the MOM Administrator console.
    

    3. 

  3. In the left pane, expand Microsoft Operations Manager (Default), and then expand Configuration.
    

    4. 

  4. In the left pane, click Global Settings.
    

    5. 

  5. In the right pane, click Database Grooming.
    

    6. 

  6. On the menu bar, click Action.
    

    7. 

  7. On the Action menu, click Properties.
    

    8. 

  8. Click a data type, click Edit to set its grooming parameters, and then click OK.
    

    9. 



Scheduling Database Grooming Jobs


You can change the default settings for grooming jobs using SQL Server Enterprise Manager.


Warning If you change the default scheduled times for database grooming, the new times must not interfere with other scheduled MOM tasks. For example, do not schedule a database grooming job for 2:05 A.M., because this is the default time to scan for managed computers.


To change scheduled database grooming


    

  1. Log on with a Windows 2000 Administrator account to the computer on which you installed the database.
    

    2. 

  2. From the Microsoft SQL Server folder, open Enterprise Manager.
    

    3. 

  3. Expand SQL Server Group, and then expand the server that contains the MOM database.
    

    4. 

  4. Expand Management, and then expand SQL Server Agent.
    

    5. 

  5. In the left pane, click Jobs.
    

    6. 

  6. In the right pane, right-click the job you want to reschedule, and then select Properties.
    

    7. 

  7. Click the Schedules tab.
    

    8. 

  8. In the Properties window, click Edit.
    

    9. 

  9. In the Edit Job Schedule window, click Change.
    

    10. 

  10. In the Edit Recurring Job Schedule window, enter the values you want, and then click OK.
    

    11. 

  11. In the Edit Job Schedule window, click OK.
    

    12. 

  12. In the Properties window, click OK.
    

    13. 



Entering New Grooming Jobs


MOM does not allow you to enter new grooming jobs through the MOM Administrator console. You can only set the number of records to delete per grooming job and the age of the records being removed. However, you can use SQL Server Enterprise Manager to create a custom grooming job. For more information about creating a custom grooming job, see the SQL Server 2000 documentation.


SQL Server Maintenance Plan Wizard


Regular maintenance activities can be easily accomplished using the SQL Server Maintenance Plan Wizard. The Maintenance Plan Wizard allows database administrators to quickly create plans and associated scheduled jobs for performing the most common administrative tasks for a database. A plan can be created for multiple databases or a single database.


By default, MOM has three built-in maintenance jobs:


    

  • Reindex Event Table.
    

    • 

  • Reindex, which reindexes all of the tables in the database.
    

    • 

  • Check Integrity, which checks the integrity of the database.
    

    Tip Maintain a running log of all maintenance activities. The Maintenance Plan Wizard can be set to automatically log activities.
    

    • 



Additionally, the following tasks can be accomplished:


    

  • Reorganizing the data and index pages, which removes fragmentation.
    

    • 

  • Updating statistics, which creates the most optimized plans.
    

    • 

  • Removing unused space to help manage disk space.
    

    • 

  • Performing
    

    DBCC CHECKDB
    

    • 



, which ensures allocation and structural integrity.


    

  • Backing up the database, which provides failure recovery.
    

    • 

  • Backing up the transaction log, which provides point-in-time failure recovery.
    

    • 

  • Developing a maintenance report.
    

    • 

  • Writing history information (locally or remotely), which provides auditing capability.
    

    • 



Purging Old Data from the Database


You might want to delete archived data from time to time. While it is not possible to purge the OnePoint database completely, you can configure grooming to remove old items.


To adjust the database grooming function


    

  1. In the MOM Administrator console, expand Configuration, and then click Global Settings.
    

    2. 

  2. In the right pane, right-click Database Grooming, and then click Properties.
    

    3. 

  3. Click the Database Grooming tab.
    

    4. 

  4. For each record type, select the record type, click Edit, and then change the grooming times to 24 hours.
    

    Note There are many jobs that have a run time of 24 hours. It is not advisable to permanently configure the grooming time to less than 24 hours.
    

    5. 

  5. On the DCAM server, stop the OnePoint service.
    

    6. 

  6. Start SQL Server Enterprise Manager.
    

    7. 

  7. Under (OnePoint) SQL Server, expand Management, expand SQL Server Agent, and then click Jobs.
    

    8. 

  8. Right-click OnePoint - Groom Events, and then click Start Job.
    

    9. 

  9. After this process has finished running, follow the same steps for the OnePoint - Groom Alerts and the Groom Sampled Numeric Data options.
    

    10. 

  10. Start the OnePoint service, and then adjust the database grooming settings in the MOM Administrator console.
    

    11. 



Expanding Your Database


If your MOM database needs to expand beyond the initial limits you specified for either the data file or the transaction log file, you can manually expand the database using SQL Server Enterprise Manager.


To expand your database


    

  1. Log on with a Windows 2000 Administrator account to the computer on which you installed the database.
    

    2. 

  2. From the Microsoft SQL Server folder, open Enterprise Manager.
    

    3. 

  3. Expand SQL Server Group, and then expand the server that contains the MOM database.
    

    4. 

  4. Expand Databases.
    

    5. 

  5. Right-click OnePoint, and then select Properties.
    

    6. 

  6. In the OnePoint Properties window, click the General tab.
    

    7. 

  7. Select the Space allocated column of the Database Files box.
    

    This number is the size of the database data file in megabytes.
    

    8. 

  8. In the Database Files box, type the new size of the database data file.
    

    9. 

  9. In the OnePoint Properties window, click the Transaction Log tab.
    

    10. 

  10. Click the number in the Space Allocated column of the Transaction Log Files box.
    

    This number is the size of the database transaction log file in megabytes.
    

    11. 

  11. In the Transaction Log Files box, type the new size of the database transaction log file, and then click OK.
    

    12. 



Monitoring the MOM Database


MOM can monitor your database servers, and many monitoring tools are available within the MOM Administrator console. This section covers the following topics:


    

  • Monitoring best practices
    

    • 

  • Monitoring database free space
    

    • 

  • Alert and event latency
    

    • 

  • Monitoring tools, including performance counters and built-in reports
    

    • 



Best Practices


In addition to monitoring for access issues, availability, and performance, it is important to identify job failures and other error conditions. The following guidelines are recommended:


    

  • Know your most common events by querying the database for these events. Get to know this list, because an anomaly on this list is one key for identifying an event storm.
    

    • 

  • Know your top event-generating servers by querying the database for event counts by server. Get to know this list, because an anomaly on this list can help identify troubled servers.
    

    • 

  • Set up automated notifications for important MOM events. MOM, by default, includes various processing rules to monitor itself. However, you can extend this functionality by creating an alert processing rule to process all errors or critical errors with a notification response to the MOM help,desk and administrators. You can accomplish this by modifying each of the child processing rule groups under the MOM processing rule group.
    

    • 

  • Create an additional event processing rule to monitor the SQLSERVERAgent event 208 for SQL Server jobs failures. It is important to know when SQL Server agent jobs such as grooming and reindexing have failed. Because this event will be reported for both success and failure, configure the criteria of the event processing rule so you are only alerted when the job fails. Configure alerts and notifications for all MOM SQL Server job failures, including grooming, reindexing, and status updates. Configure job failure events to alert the MOM SQL Server administrator.
    

    • 

  • Ensure that job owners have sufficient rights to run their jobs.
    

    • 

  • Set a low threshold for database free space for early notification so you can make adjustments before hitting the 40-percent mark. Groom your database aggressively.
    

    • 

  • Monitor for event 17055, which includes a SQL DBCC CHECKDB warning. This will alert you to a SQL Server database integrity issue.
    

    • 

  • Use the SQL Server Maintenance Plan Wizard to reorganize data and index of the OnePoint database and to check for database integrity.
    

    • 

  • Archive historical data to free space.
    

    • 

  • Monitor for additional errors, including the following:
    

      

    • Event 25101 — The agent failed to insert events into the database.
      

      • 

    • Event 25102 — The Consolidator failed to insert events into the database.
      

      • 

    • Event 25103 — The Consolidator failed to prepare events for insertion into the database.
      

      • 

    • Event 25105 — Data Access Servers could not be contacted.
      

      • 

    • Event 6000 — The log file is full.
      

      • 

    

    • 



Database Free Space


You should maintain 40 percent free space in your database. If you do not, certain maintenance jobs, such as reindexing, will fail. It is essential that you continually monitor your database for free space to maintain a healthy database. Database free space can be determined by directly querying the MOM database.


There are two ways to monitor for database free space. One is through the MOM Administrator console:


Monitor\Public Views\MOM\Database\MOM Database Free Space.


Note To view database free space results in the MOM Administrator console, you must have an agent installed in your MOM database server. Otherwise, this view will remain empty.


The other way to view database free space is by running a query in SQL Server Query Analyzer:


To determine database free space using SQL Server Query Analyzer


    

  1. Open SQL Server Query Analyzer.
    

    2. 

  2. On the File menu, click New, and then click Blank Query Window.
    

    3. 

  3. In the Query window, type the following:
    

    4. 



EXEC[OnePoint].[dbo].[sp_OnePointFreeSpace]



    

  1. Press F5 to run the query.
    2. 



The query returns a result indicating the amount of space available. For example:


OnePoint Free Data Space Percentage: 64.6299 



Event and Alert Latency


Event and alert latency is the interval between when an event or alert is generated on an agent computer and when the event or alert is logged in the MOM database and consequently appears in the MOM Administrator console. When communications between components is slow or unreliable, event and alert latency can become a problem. However, latency can also result when the MOM database becomes too large to record events and alerts efficiently. Additionally, MOM might report event or alert latency if the clock on a machine generating events or alerts is set a few minutes slower than the clock on the MOM database machine. There are a few ways you can monitor for latency:


    

  • Analyze individual events or alerts and compare the time in which the event or alert was raised and the time that it was received in the Administrator console.
    

    • 

  • Use the reporting component to generate latency reports that list average, maximum, and minimum time intervals on a computer-by-computer basis. The two reports are:
    

      

    • Alert Logging Latency.
      

      • 

    • Event Logging Latency.
      

      • 

    

    • 

  • Directly query the database using SQL Server Query Analyzer.
    

    • 



To query for average alert latency


    

  1. Open SQL Server Query Analyzer.
    

    2. 

  2. On the File menu, click New, and then click Blank Query Window.
    

    3. 

  3. In the Query window, type the following (changing the date as appropriate for your environment):
    

    4. 



SELECT AvgAverageAlertLatency=avg(datediff(ss, timeRaised, TimeAdded))
FROM alert
WHERE TimeAdded>DateAdd(hh, -1, getdate())



    

  1. Press F5 to run the query.
    2. 



To query for average event latency


    

  1. Open SQL Server Query Analyzer.
    

    2. 

  2. On the File menu, click New, and then click Blank Query Window.
    

    3. 

  3. In the Query window, type the following (changing the date as appropriate for your environment):
    

    4. 



SELECT AvgAverageEventLatency=avg(datediff(ss, timeGenerated, TimeStored))
FROM event
WHERE TimeStored>DateAdd(hh, -1, getdate())



    

  1. Press F5 to run the query.
    2. 



Monitoring Tools


The following performance counters are suggested for your initial monitoring baseline. You will need to test the number of counters and the frequency of collection that best suits your environment. There are several monitoring tools available to monitor SQL Server availability.


Performance Counters


Page Faults


The number of pages read from or written to disk to resolve hard page faults. Hard page faults occur when a process requires data that is not in physical memory and must be retrieved from disk.


Processor – % Processor Time


The percentage of time that the processor is executing a nonidle thread. This counter was designed as a primary indicator of processor activity.


Tip There is a built-in MOM report available for this counter. For information about this counter, see the "Built-in Views and Reports" section later in this chapter.


Memory – Pages/sec.


The number of pages read from or written to disk to resolve hard page faults. Hard page faults occur when a process requires code or data that is not in its working set or elsewhere in physical memory and must be retrieved from disk.


Network Interface – Bytes total/sec.


The number of bytes traveling over the network interface per second. If this rate begins to drop, you should investigate whether network problems are interfering with your application.


PhysicalDisk – Disk Transfers/sec.


The rate of read and write operations on the disk. You should define a counter for each physical disk on the server.


SQLServer:Databases Application Database – Percent Log Used


The percentage of space in the log that is in use.


Built-in Views and Reports


MOM has many informative built-in views and reports to assist you in maintaining a healthy database. Some of the key views are:


    

  • Free space.
    

    • 

  • Processor time.
    

    Note You must have an agent installed on the database server to see results using built-in views.
    

    • 



Some of the key reports are as follows:


    

  • Alert Logging Latency
    

    • 

  • Event Logging Latency
    

    • 

  • Most Common Events
    

    • 



For more information about reporting, see chapter 9, "MOM Reporting Management."


Back Up and Restore


Every database environment should have an established backup and restore plan for failure recovery. This plan should be thoroughly tested and documented in a simulated environment using production backups. All possible scenarios of failure should be considered and addressed. There are several factors you should consider when creating this plan, including availability requirements and cost.


Because backing up is an essential task in database maintenance, this section provides instructions on backing up and recommendations on how frequently you should back up the OnePoint, master, and msdb databases. This section also addresses several restore scenarios, complete with instructions.


Backing Up


You should perform full database backups daily. A full database backup captures the entire database, including all entries in the transaction log, and excluding any unallocated extents in the files. Pages are read directly from disk to increase the speed of the operation.


A full backup of the OnePoint database should be performed on a daily basis. The master and msdb databases should be backed up any time a change occurs that affects either database; however, you should back them up at least monthly.


Databases


There are three important databases that you need to back up regularly:


    

  • OnePoint The OnePoint database is the storage facility for MOM configuration data and data generated by the MOM systems. Specifically, the MOM database stores the rules and configuration data for the managed nodes, along with the events, alerts, and performance data generated the managed nodes.
    

    • 

  • Master The master database records all of the system-level information for a SQL Server system, including the location of the database files. It also records all logon accounts and system configuration settings.
    

    • 

  • Msdb The msdb database is used by the SQL Server agent for scheduling jobs and alerts and for recording operators.
    

    • 



Note A full database backup does not clear the transaction log. A process should be initiated to clear the log periodically to prevent the log from filling up. Any transactions that were in progress, however, during the database backup will be included in the backup.


Backup Tasks


Creating a New Backup Device


Before you perform your first backup, you need to create a new backup device in SQL Server.


To create a new backup device using SQL Server Enterprise Manager


    

  1. Open SQL Server Enterprise Manager, and then navigate to the server for which you want to create the device.
    

    2. 

  2. Expand SQL Server, and then expand Management.
    

    3. 

  3. Right-click Backup, and then select New Backup Device.
    

    4. 

  4. In the Name box, enter the name of the device, and then click OK.
    

    5. 



Backing Up Using SQL Server Enterprise Manager


To back up the MOM database using SQL Server Enterprise Manager


    

  1. Right-click your new device, and then select Backup Database.
    

    2. 

  2. In the database list, select the OnePoint database.
    

    3. 

  3. Click Add, click the backup device, and then click OK.
    

    The backup device name should be the one you created in step 1.
    

    4. 

  4. Click the Options tab, and then select the Verify backup upon completion check box.
    

    5. 

  5. Click the General tab, and then click OK to run the backup.
    

    6. 

  6. Copy the backup file to another server for storage or back it up to tape.
    

    7. 



Restoring the MOM Database


This section describes the following restoration scenarios:


    

  • Failure recovery
    

    • 

  • Restoring on the reporting database server
    

    • 



Note Note that in this case you are not actually "restoring" anything, because no data was lost; you are simply transferring the data from one database to another. This is referred to as "restoring" because the database copying procedure uses SQL Server's built-in copying functionality, which is normally used when restoring a crashed database from a backup copy.


Failure Recovery Scenario


Before you restore the MOM database in a failure recovery scenario, you first need to reinstall SQL Server 2000, and then the MOM database component.


Note When reinstalling SQL Server and MOM, be sure to keep the same installation directories that were used initially.


To restore the database on the same server


    

  1. Configure the restore options to overwrite the existing database.
    

    2. 

  2. Restore the OnePoint database from your backup device.
    

    Steps to restore from a backup device are described in the Restoring the Database on the "Reporting Database Server" section later in this chapter.
    

    Note Instead of selecting an alternative server to restore to, perform the restore on the rebuilt MOM database server.
    

    3. 

  3. Restart the OnePoint service on the DCAM.
    

    Agents should continue to properly communicate with the Consolidator.
    

    4. 



Restoring the Database on the Reporting Database Server


You should restore the database on the reporting database server to initially fill the reporting database with the zone database records.


Note In this scenario, you are not actually restoring anything, because no data was lost; you are simply transferring the data from one database to another. This is referred to as restoring because the database copying procedure uses SQL Server's built-in copying functionality, which is normally used when restoring a crashed database from a backup copy.


To restore the database on the reporting server when the data file paths are the same


    

  1. Right-click the target zone holding database to restore to, select All Tasks, and then click Restore Database.
    

    2. 

  2. Click the General tab, and then click From Device.
    

    3. 

  3. Click Select Devices, and then click Add.
    

    4. 

  4. Click Backup Device.
    

    5. 

  5. In the Restore from box, select your backup device, or New Backup Device if your device is not listed.
    

    6. 

  6. In the Choose Restore Destination box, type the name of your backup device, and then click OK.
    

    7. 

  7. In the Choose Restore Devices window, click OK.
    

    8. 

  8. Click the Options tab, and then select the Force Restore Over Existing database check box.
    

    9. 

  9. In the Restore database files as window, verify that the path name is correct in the Move to physical file name box.
    

    10. 



To restore the database on the reporting server when the data file paths are different


    

  1. Verify that the .bak file is copied to your reporting server.
    

    2. 

  2. Take target database offline.
    

    3. 

  3. Open SQL Server Query Analyzer.
    

    Note You need to know the logical and physical name of all data log files for the databases you want to restore.
    

    4. 

  4. This SQL statement will provide the logical and physical names of your data and log files:
    

    5. 



restore filelistonly from disk='e:\sqlsrv\mssql\backup\zone1.bak'



**Note** e:\\sqlsrv\\mssql\\backup\\zone1.bak represents the path of the backup device.



    

  1. The logical name for the OnePoint db is EEA_DATA.
    

    2. 

  2. The logical name for the OnePoint log file is EEA_LOG.
    

    3. 

  3. Run the following SQL script to start the restore process:
    

    4. 



restore database Holding1 from disk='e:\sqlsrv\mssql\backup\zone1.bak'
with move 'EEA_DATA' to 'e:\sqlsrv\mssql\data\Holding1_data.mdf',
move 'EEA_LOG' to 'e:\sqlsrv\mssql\data\Holding1_log.mdf',
replace



Depending on the size of the database, this script might take a while to run. The script variables are defined in the following table.











Variable


Description








Holding1


The name of the target database on the reporting server.






E:\sqlsrv\mssql\backup\zone1.bak


The backup device file path.






E:\sqlsrv\mssql\data\Holding1_data.mdf


The physical name and path of the data file for the target database.







Renaming the Server or the MOM Database


Microsoft does not support renaming the server or the OnePoint database.


Moving the MOM Database


Moving the OnePoint database is only recommended for failure recovery reasons, because it can cause serious problems and might require a reinstallation of the server. This section provides instructions for moving the database in the following scenarios:


    

  • From a server running SQL Server to another.
    

    • 

  • A MOM database that resides on a DCAM or MOM Web console server.
    

    • 



Warning This section contains information about editing the registry. Before you edit the registry, ensure that you understand how to restore it if a problem occurs. Microsoft cannot guarantee that problems that result from the incorrect use of Registry Editor can be solved. For information about how to restore the registry, see the "Restoring the Registry" Help topic in Regedit.exe, or the "Restoring a Registry Key" Help topic in Regedt32.exe.


Moving the OnePoint Database from a Server Running SQL Server to Another


Warning Using Registry Editor incorrectly can cause serious problems that might require that you reinstall your operating system. Microsoft cannot guarantee that problems that result from the incorrect use of Registry Editor can be solved. For information about how to edit the registry, see the "Changing Keys and Values" Help topic in Regedit.exe, or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. You should back up the registry before you edit it. If you are running Windows NT or Windows 2000, you should also update your emergency repair disk (ERD).


Moving the OnePoint database is not a standard maintenance procedure. However, you might need to move it for various reasons, including, but not limited to:


    

  • Volume size restrictions.
    

    • 

  • Performance improvements, such as separating the database from the logs.
    

    • 

  • Relocation of the database to a fault-tolerant volume.
    

    • 



You should only move the OnePoint database for one of these reasons. Moving the OnePoint database can cause serious problems that might require a reinstallation SQL Server.


Important The following steps are for moving the MOM database from a server running SQL Server to another server running SQL Server. If your server is also the MOM server (DCAM), you might need to make additional modifications to the MOM server registry keys and database table, which are discussed in the next section.


To move the MOM database from one server running SQL Server to another


    

  1. Install SQL Server on the destination computer.
    

    2. 

  2. Install the MOM database component by running MOM Setup.
    

    3. 

  3. Quit the OnePoint service on the current database computer, and then stop the OnePoint service on all DCAM servers.
    

    4. 

  4. Back up the current OnePoint database to a file.
    

    5. 

  5. On the destination computer, open SQL Server Enterprise Manager.
    

    6. 

  6. On the Restore menu, click From Device, and then select the backup file you created in step 4.
    

    7. 

  7. On the DCAM server, start Regedt32.exe, and then change the following registry values from the current server name to new server name:
    

      

    • HKEY_LOCAL_MACHINE\Software\Mission Critical Software\DASServer\DataSource Value
      

      • 

    • HKEY_LOCAL_MACHINE\Software\Mission Critical Software\Onepoint\Configurations\< configuration group name >\Operations\Database Value
      

      • 

    • HKEY_LOCAL_MACHINE\Software\Mission Critical Software\Onepoint\Databases
      

      • 

    

    8. 

  8. For each of these keys, delete the current SQL Server value, and then create a new SQL Server name value.
    

    MOM will recreate the DCAM keys, as needed, under this new key when the OnePoint service restarts.
    

    9. 

  9. Navigate to the following registry key:
    

      

    • HKEY_LOCAL_MACHINE\Software\Mission Critical Software\Onepoint\Configurations\Default\MCSApplications\Operations GUI\Databases\ default value
      • 

    

    10. 

  10. Change it from the old server name to the new server name.
    

    11. 

  11. Restart the OnePoint service on the DCAM servers.
    

    12. 

  12. Make the same registry changes you made in step 7 on each DCAM server in this configuration group.
    

    13. 



Moving the MOM Database That Resides on a DCAM or MOM Web Console Server


In addition to the changes discussed in the previous section, you must make changes to the configuration table in the OnePoint database if the server you are moving also hosts the MOM DCAM role or Web console role. Also note that moving the DCAM server to a computer with a different name might have an effect on the managed nodes (servers running the MOM agent) if this is the only DCAM server in the configuration group. Contact Microsoft Product Support Services if this is the case.


If you are moving all the components of the MOM server to a new hardware platform, you need to install the MOM server on the new platform.


To move the MOM database that resides on a DCAM or Web console


    

  1. Perform a custom installation of MOM on the new computer.
    

    2. 

  2. In the Configuration Group box, type the configuration group name precisely as it appears on the existing MOM server.
    

    3. 

  3. Once the MOM installation is complete, open SQL Server Enterprise Manager.
    

    4. 

  4. In the OnePoint database, right-click the Configuration table, click Open Table, and then click Return all Rows.
    

    5. 

  5. Change all entries where DataCategory = URL to DataCategory = databasename, where databasename is the name of the new database computer.
    

    6. 



Entries in the form of URLs need to remain as they were with the exception of using the name of the SQL Server computer that you moved the database to. For example, if an existing table entry looks like one of the following:


    

    • 



https://MOMServer_OLD/OnePointOperations
"PropertySheet.asp?database=MOMServer_OLD&target=%1&t=alert"



The new entry should look like one of the following:


    

    • 



https://MOMServer_NEW/OnePointOperations
"PropertySheet.asp?database=MOMServer_NEW&target=%1&t=alert"



Uninstalling the MOM Database


Completely removing MOM from your enterprise requires the removal of the MOM database from the DCAM or database server. If the setup program does not automatically remove the database, use the SQL Server administrator tools to remove it. For more information about removing the MOM database, see the SQL Server documentation.


Important Uninstall MOM from the old database server, or take the server offline if you want to keep it as a recent backup of the MOM database in case anything goes wrong with the new database server. If you leave the old database server online but do not uninstall MOM, and there still are DCAMs configured to communicate with the old MOM database, they will continue sending data there and your configuration group will be in an inconsistent state.


Troubleshooting the MOM Database


This section provides the most common troubleshooting issues that relate to the MOM database.


Slow Performance on the Database Server


Slow performance can occur because the Data Access Server does not cache SQL Server 2000 connections at any point during its normal interaction with the server that stores the OnePoint database. New server connections, sessions, and data bindings are established for each request. This added work might decrease the server's performance. The following suggestions might help resolve this issue:


    

  • Increase the number of available server connections beyond the normal requirements. Extra connections allow establishment of new sessions with the database while earlier connections are still waiting for SQL Server to close them.
    

    • 

  • Verify that enough memory is allocated to SQL Server. Each connection requires 30–50 KB of memory. For more information about the amount of memory that each connection requires, see the SQL Server 2000 documentation.
    

    • 



Rule That Is Not Valid Causes Reports of Large Numbers of Events in the Database


If a rule that is not valid is created and enabled in MOM, a large number of events (for example, event 25401 or event 25406) might be recorded in the MOM database. An rule that is not valid results when the rule contains incorrect information, such as an incorrect WMI namespace or group class. MOM produces these events and event reports to help administrators identify faulty rules. A typical event report might look like the following:


Event Type: Error 
Event Source: OnePoint Operations 
Description: The Microsoft Operations Manager 2000 WMI Numeric Provider could not 
convert instance name property Description on class win32_perfrawdata_perfproc_process to string value. 



To avoid this problem, verify that all of the enabled rules are valid.


Agent Manager Generates C++ Exceptions When It Is Unable to Read Configuration Information from the Database


You might discover that the OnePoint service logged event 21109. The information that you receive is similar to the following error message:


A C++ exception occurred while reading configuration information from the database. 
Type: McsDebugException 
Additional information: Message from file 
T:\Dev\EEM\V3\Ship\CMCom\ConfigMgr.cpp, line 918: 
CConfigMgr::ReadConfiguration failed on call to m_Config.LoadFromDb. 
File: T:\Dev\EEM\V3\Ship\CMCom\ConfigMgr.cpp 
Line: 958 
Other location: CConfigMgr::ReadConfiguration 



The event might also generate an alert. The OnePoint service might also log event 21155 and raise the following alert:


"The Agent Manager ignored a request for a scan because an error occurred while loading configuration."


This error message can appear if a database error occurs during a read from the configuration table. One known cause for these events is that the OnePoint database is full. If this is the case, increase the size of the database. For more information, see the "Expanding Your Database" section earlier in this chapter.


Custom View Is Not Saved to the Database


When you change the name and description of a custom view, it might not be saved to the MOM master database. The changes appear in the user interface but are lost when you exit the MOM Administrator console. This problem occurs because some custom views have settings in addition to name and description that must be changed before the custom view can be saved to the database. Change the other settings in the custom view before saving it to the database.