Database Fundamentals

(1)

Database

Fundamentals

A article about database maintenance in Microsoft Operations Manager 2005

(2)

Introduction

The OnePoint database is the heart of every Microsoft Operations Manager 2005 management group. When an agent generates an alert it will be sent to the operation database through the MOM server DAS component. An operator will see all alerts in the database based on the current settings. If there is a problem with your database agents might not be able to insert new alerts and operators will not be able to see them.

Alerts generated and inserted into the OnePoint database

(4)

All screenshots and instructions will be based on MS SQL 2005 SP1 and a healthy MOM management group.

Microsoft Operations Manager 2005 databases

When a new alert is generated it will be sent from the agent to the management server. At management server the DAS component will insert it into the OnePoint database. Later when the alert is solved and marked as resolved it will be groomed out of the Onepoint database. If MOM reporting service are installed the alert will be transferred to reporting database, SystemCenterReporting. There is a schedule d task on the reporting database server that will handle the transfer. Default time is every day at 01am. The database is actually only two files, one database file, eeadata.mdf, and one transaction logfile, eealog.ldf. All interactions are first written to the transaction log. These transactions are then

committed to the tables by the database engine itself. If these processes were allowed to read and read directly to the database and for some reason was interrupted during the operation, the record that was process was working with would be corrupt. Instead this way provides buffers for communication between the computer working with the database and the database itself.

(5)

Overview of Microsoft Operations Manager 2005 Architecture

(6)

OnePoint

MOM uses the OnePoint database to store the following types of data:

• Global configuration and management pack defined configuration data that specifies what data is to be collected and disseminated by this management group. Thus, with a backup of the OnePoint database, you can recover your base MOM installation (minus reporting). However, restoring a full database backup is not always desirable, such as for rolling back unwanted management pack changes. • Operational data from the agents, including all events, performance data, alerts, and discovery data collected from MOM discovery and monitoring processes

SQL jobs

When you install MOM and the OnePoint database is created you will get nine SQL maintenance jobs.

SQL job Definition

OnePointReindex Every Sunday at 3 am (default) SQL builds indexes for the

tables in the OnePoint database. These indexes will speed up searching. If this job fails OnePoint response time will increase. When the response time increases it will take more time to write an alert to the database and also to see the alert in the Operator Console. Runs every Sunday at 3am.

Database grooming Grooming is the process when resolved alerts are deleted

from the database and auto resolve alerts. When an alert is set to the resolved status, either by an operator or by the Auto Resolve grooming task, it will remain in the database for 4 days by default. There is a 30 GB max size of the OnePoint database. If you use reporting service it’s important that the DTS job to transfer data to the

(7)

Check Integrity This job validates the integrity of everything in the MOM database Runs every Saturday at 10pm.

Computer Maintenance This job checks for computers that should return from maintenance mode. Please read my article about

maintenance mode for more information. Runs every five minutes.

TodayStatisticsUpdateComputersAndAlerts This job updates the OnePoint Operations Manager Today window. Runs every five minutes.

TodayStatisticsUpdateEvents This job updates the OnePoint Operations Manager Today window. Runs every 30 minutes.

TodayStatisticsUpdatePerfmonRulesKB This job updates the performance, rules and

Management Pack knowledgebase. Runs every hour.

Update Database This job grooms automatically resolved alerts based on

the information specified in the Global Settings dialog box. Runs every hour.

Update Statistics This job updates information about key value distribution

(8)

Reporting databases

MOM reporting consists of two databases, SystemCenterReporting and ReportServer. The

SystemCenterReporting data warehouse is the database where alerts, performance and event data reside for long-term storage (default is 395 days). ReportServer database stores all report definitions, metadata and any cached reports. If you look in your SQL manager you will see a third database, ReportServerTempDB. This database is used during the reporting process but does not store any important data.

SQL jobs

SQL job Definition

SCDWGroomJob This job runs at 3am nightly, grooming event, alert

and performance data from the SCDW data warehouse that is older than the desired retention interval.

Change number of days to store in SystemCenterReporting

By default all data is stored in the SystemCenterReporting database for 395 days. You can set different number of days on different data type, for example if you like to save your performance data longer then the rest. To change this you can run the following query in SQL Server Management Studio: Exec p_updategroomdays ‘tablename’, 300

There are six fact tables in SystemCenterReporting database and you will have to update them all. The tables are:

• SC_AlertFact_Table • SC_AlertHistoryFact_Table • SC_AlertToEventFact_Table • SC_EventFact_Table • SC_EventParameterFact_Table • SC_SampleNumericDataFact_Table

To display current settings (good to do both before and after changing settings) SELECT cs.CS_TableName, wcs.WCS_GroomDays

(9)

WHERE cs.CS_TableName = 'SC_AlertFact_Table' OR cs.CS_TableName = 'SC_AlertHistoryFact_Table' OR cs.CS_TableName = 'SC_AlertToEventFact_Table' OR cs.CS_TableName = 'SC_EventFact_Table' OR

cs.CS_TableName = 'SC_EventParameterFact_Table' OR cs.CS_TableName = 'SC_SampledNumericDataFact_Table' AND wcs.WCS_MustBeGroomed = 1

Fact tables contain the primary data of the Reporting database, and tend to be very large. Example data includes alerts, events, and sampled numeric data (performance counters).

To run a query

1. Start SQL Server Management Studio

2. Expand you databases and right-click SystemCenterReporting and choose New Query

3. In right part of SQL Server Management Studio you can now write the query, for example Exec p_updategroomdays ‘SC_AlertFact_Table’, 300

This will change the number of days in the database to 300 days. 4. Choose Execute in the Query menu or press F5 to run the query

Database maintenance

This script reindexes the SystemCenterReporting data warehouse to optimize MOM Reporting Server performance. Complete explanation and instructions for use are in the accompanying readme.txt file. http://www.momresources.org/momscripts/scdw_reindex.zip

How MOM databases work together

(10)

SystemCenterDTSPackageTask is a scheduled task on your reporting database server. You will find the DTS task here:

1. Start menu choose All Programs 2. Choose Accessories

3. Choose System Tools 4. Choose Scheduled Task

5. You will see SystemCenterDTSPackadeTask in the scheduled Tasks window

Backup of databases

All IT Pros knows that’s important to have a backup. So what should you backup in MOM? • Server OS installation documentation.

Create a server build document detailing all configuration tasks in the initial setup process, for example patches, local accounts, settings, drivers and application paths.

• OnePoint database

All MOM configuration settings and management packs are in the OnePoint database. You can use SQL included backup tool to make this backup. You should do a full backup every day. • SQL system databases master and msdb

These databases store all settings about your SQL server. You should do a full backup every day. • Management Packs

It´s probable that you sometimes need to restore just one management pack. For that reason its good to have a separate backup of your management packs.

• Report definitions

If you make any changes to the reports or create your own reports you will have to backup them too.

• ManualMC.txt files

ManualMC is a text file that administrators can use to control which computers are included, or excluded, in the computer discovery and agent installation processes

• Customized Operator consoles and custom administrator consoles If you use MOM reporting services you should backup that database too.

In general you should avoid run backup during times when the database is especially busy or when a process puts a lock on a database table such as when grooming SQL jobs run. You can read about all default schedule tasks under “SQL jobs”. The best time for the database backup to start is around 8 pm. To avoid high disk latency you should write backup to a different disk than the ones the database and logs are on.

(11)

You can create seven schedule SQL backup jobs, one for every day of the week. The Sunday SQL backup job on should include both OnePoint, master and msdb databases. You can configure your SQL backup jobs to overwrite the existing file (previous week), this means that there should never be more than seven SQL backup files in the destination folder. It will be easy for you to find the right file if you need to do a restore of a backup.

Don’t forget to test your backups sometime. Try to restore your MOM installation in a lab. It’s a great way to prepare to do a disaster recovery of your real installation.

Backup Best Practices

Below there us a recommended backup schedule for regular backups of your MOM installation. Backup Item Backup type and frequency

MOM Database (OnePoint) Full backup every night MOM Reporting Database

(SystemCeneterReporting)

Full backup every month, incremental every week

SQL Reporting Database (ReportServer)

Full backup every month, incremental every week. You should also do a backup immediately following report customization.

Master Database (Master) Make a backup once MOM database is installed and after larger changes to logons and other security changes.

Management Packs and Reports (.akm and .xml files)

You should do it after changes to management packs.

File Transfer files Depends on how often these files are changed and files are added/removed.

Notification Workflow Solution Accelerator, an add-on to MOM 2005 which provides a more flexible self-service e-mail notification framework includes three databases. If you install MOM Notification Workflow you should make backup of those databases too. NotificationWorkflowNSMain,

(12)

How to setup backup in SQL 2005

This is a very general backup setup. You can use it as a general guide and adjust it to your organization requirements.

When you install SQL 2005 and MOM database the database is in simple recovery mode. The simple recovery mode is designed for databases that need to be recovered to the point of last backup. You can change your database recovery mode to full recovery mode. Full recovery mode is designed for

databases that need to be recovered to the point of failure or to a specified time. Please remember that changing recovery mode is not a supported configuration.

Make backup of OnePoint database once

2. Expand the menu and right-click the OnePoint database, choose Tasks and then Back up… 3. Back Up Database – OnePoint: Verify that selected database is OnePoint and backup type is

Full.

4. Back Up Database – OnePoint: Enter a suitable name under Backup set, for example OnePoint Full Database Backup

5. Back Up Database – OnePoint: Enter a suitable description under Backup set, for example MOM configuration database

6. Back Up Database – OnePoint: Click Add under Destination and choose suitable destination of your backup files. You can remove the default Destination.

7. Back Up Database – OnePoint: Now select the Options page from the page pane

8. Back Up Database – OnePoint: If you are planning to overwrite the database you should select it. You should also select to verify the backup when finish if it´s a requirement in your

organization.

9. Back Up Database – OnePoint: Click ok

Backup OnePoint database on regular basis

2. Expand Management and right-click Maintenance Plans and choose Maintenance Plan Wizard 3. SQL Server Maintenance Plan Wizard: Click Next

4. Maintenance Plan Wizard: Enter a suitable name and description, for example OnePoint Full database backup as name and MOM Configuration database backup as description. Verify that Use Windows Authentication is selected and that your server is selected as Server. Click Next 5. Maintenance Plan Wizard: Select Back Up Database (Full) and then click Next

(13)

7. Maintenance Plan Wizard: Select OnePoint as Database. Verify that create a backup file for every database is selected and create a sub directory for each database is selected. Choose a folder where you want to store your backup. Click Next

8. Maintenance Plan Wizard: Click Change to start making a schedule for the new maintenance plan.

9. New Job Schedule: Enter a suitable name, for example OnePoint full backup daily. Schedule type: Recurring

Occurs: Daily Recurs every: 1 day Occurs one at: 21:00:00

Start date: Choose the present date Click OK

10. Maintenance Plan Wizard: Click Next

11. Maintenance Plan Wizard: Select if you would like a report and/or a e-mail about the maintenance plan actions. By default an event will be logged for job dailure, and MOM will generate an alert.

12. Maintenance Plan Wizard: Click Finish, after a short while is your new maintenance plan created and you can click Close to close the wizard.

If you have change recovery mode to full you should create additional maintenance plan to backup your transaction logs too. It’s the same maintenance plan wizard, on part 5 you choose Back Up Database (Transaction Log).

You can restore management packs changes by restore the OnePoint database, but it’s not the best way to do it. Instead you should do regular management pack backups. You can export your management packs from the administrator console manually. You can also use the script below and run it as a

scheduled task on your MOM management server. This script will first query your OnePoint database for top-level rule groups, then create a folder named with date and time of the job, then perform the backup and then it will delete any older backups then the number of days you specify in the script. MP Backup Script with Archive Mgmt:

http://www.momresources.org/momscripts/MPBackups.txt

Monitor your MOM Databases

(14)

SQL Queries

To query the database for alerts awaiting grooming

2. Expand you databases and right-click OnePoint and choose New Query 3. In the query window, type the following:

SELECT COUNT(*) AS AlertsAwaitingGrooming FROM dbo.Alert WHERE (ResolutionState=255) 4. Choose Execute in the Query menu or press F5 to run the query

To verify the number of days to retain data in the SystemCenterReporting

database

2. Expand you databases and right-click SystemCenterReporting and choose New Query 3. In the query window, type the following

SELECT cs.cs_tablename 'Table Name', wcs.wcs_groomdays 'Groom Days' from warehouseclassschema wcs Join classschemas cs On cs.cs_classID = wcs.wcs_classID Where cs.cs_tablename =

'SC_SampledNumericDataFact_Table' And wcs.wcs_mustbegroomed = 1

Change “Table Name” to one of the tables in the SystemCenterReporting database. The tables are: • SC_AlertFact_Table • SC_AlertHistoryFact_Table • SC_AlertToEventFact_Table • SC_EventFact_Table • SC_EventParameterFact_Table • SC_SampleNumericDataFact_Table

Feedback

I hope you find this article helpful. Your feedback is always welcome and appreciated at

Database Fundamentals

Database

Fundamentals

Table of Contents

Introduction

Microsoft Operations Manager 2005 databases

OnePoint

SQL jobs

Reporting databases

SQL jobs

Change number of days to store in SystemCenterReporting

Database maintenance

How MOM databases work together

Backup of databases

Backup Best Practices

How to setup backup in SQL 2005

Make backup of OnePoint database once

Backup OnePoint database on regular basis

Monitor your MOM Databases

SQL Queries

To query the database for alerts awaiting grooming

To verify the number of days to retain data in the SystemCenterReporting

database

Feedback