Delen via

  • Artikel

Using Warning Thresholds and Alerts on Mirroring Performance Metrics

After monitoring is established for a mirrored database, a system administrator can configure warning thresholds on several key performance metrics. Also, an administrator can configure alerts on these and other database mirroring events.

Note

For information about how monitoring a mirrored database works and how to configure warning thresholds, see Monitoring Mirroring Status.

Performance Metrics and Warning Thresholds

The following table lists the performance metrics for which warnings can be configured, describes the corresponding warning threshold, and lists the corresponding Database Mirroring Monitor label.

Performance metric

Warning threshold

Database Mirroring Monitor label

Unsent log

Specifies how many kilobytes (KB) of unsent log generate a warning on the principal server instance. This warning helps measure the potential for data loss in terms of KB and is especially relevant for high-performance mode. However, the warning is also relevant for high-safety mode when mirroring is paused or suspended because the partners become disconnected.

Warn if the unsent log exceeds the threshold

Unrestored log

Specifies how many KB of unrestored log generate a warning on the mirror server instance. This warning helps measure failover time. Failover time consists mainly of the time that the former mirror server requires to roll forward any log remaining in its redo queue, plus a short additional time.

NoteNote
For an automatic failover, the time for the system to notice the error is independent of the failover time.

For more information, see Estimating the Interruption of Service During Role Switching.

Warn if the unrestored log exceeds the threshold

Oldest unsent transaction

Specifies the number of minutes worth of transactions that can accumulate in the send queue before a warning is generated on the principal server instance. This warning helps measure the potential for data loss in terms of time and is especially relevant for high-performance mode. However, the warning is also relevant for high-safety mode when mirroring is paused or suspended because the partners become disconnected.

Warn if the age of the oldest unsent transaction exceeds the threshold

Mirror commit overhead

Specifies the number of milliseconds of average delay per transaction that are tolerated before a warning is generated on the principal server. This delay is the amount of overhead incurred while the principal server instance waits for the mirror server instance to write the transaction's log record into the redo queue. This value is relevant only in high-safety mode.

Warn if the mirror commit overhead exceeds the threshold

For any one of these performance metrics, a system administrator can specify a threshold on a mirrored database. For more information, see "Setting Up and Managing Warning Thresholds" later in this topic.

Setting Up and Managing Warning Thresholds

A system administrator can configure one or more warning thresholds for the key mirroring performance metrics. We recommend setting a threshold for a given warning on both partners to make sure that the warning persists if the database fails over. The appropriate threshold on each partner depends on the performance capabilities of that partner's system.

Warning thresholds can be configured and managed by using either of the following:

  • Database Mirroring Monitor

    In Database Mirroring Monitor, the administrator can view the current configuration of warnings for a selected database at both the principal and mirror server instances at the same time by selecting the Warnings tabbed page. From there, the administrator can open the Set Warning Thresholds dialog box to enable and configure one or more warning thresholds.

    For an introduction to the Database Mirroring Monitor interface, see Database Mirroring Monitor Overview. For information about launching Database Mirroring Monitor, see How to: Start Database Mirroring Monitor.

  • System stored procedures

    The following set of system stored procedures enable an administrator to set up and manage warning thresholds on mirrored databases of one partner at a time.

    Procedure

    Description

    sp_dbmmonitorchangealert (Transact-SQL)

    Adds or changes warning threshold for a specified mirroring performance metric.

    sp_dbmmonitorhelpalert (Transact-SQL)

    Returns information about warning thresholds on one or all of several key database mirroring monitor performance metrics.

    sp_dbmmonitordropalert (Transact-SQL)

    Drops the warning for a specified performance metric.

Performance-Threshold Events Sent to the Windows Event Log

If warning threshold is defined for a performance metric, when the status table is updated, the latest value is evaluated against the threshold. If the threshold has been reached, the update procedure, sp_dbmmonitorupdate, generates an informational event—a performance-threshold event— for the metric and writes the event to the Microsoft Windows event log. The following table lists the event IDs of the performance-threshold events.

Performance metric

Event ID

Unsent log

32042

Unrestored log

32043

Oldest unsent transaction

32040

Mirror commit overhead

32044

Note

An administrator can define alerts on any one or more of these events. For more information, see "Using Alerts for a Mirrored Database" later in this topic.

Using Alerts for a Mirrored Database

An important part of monitoring a mirrored database is configuring alerts on significant database mirroring events. SQL Server 2005 generates the following types of database mirroring events:

  • Performance threshold events

    For more information, see "Performance-Threshold Events Sent to the Windows Event Log" earlier in this topic.

  • State-change events

    These are Windows Management Instrumentation (WMI) events that are generated when changes occur in the internal state of a database mirroring session.

    Note

    For more information, see WMI Provider for Server Events Concepts.

A system administrator can configure alerts on these by using SQL Server Agent or other applications, such as Microsoft Operations Manager.

When you define alerts on database mirroring events, we recommend that you define warning thresholds and alerts at both partner server instances. Individual events are generated at either the principal server or the mirror server, but each partner can perform either role at any time. To make sure that an alert continues to operate after a failover, the alert must be defined at both partners.

For more information, see the white paper about alerting on database mirroring events at this SQL Server Web site. This white paper contains information about how to configure alerts using SQL Server Agent, the database mirroring WMI events, and sample scripts.

Important

For all mirroring sessions, we strongly recommend that you configure the database to send an alert on any state-change events. Unless a state change is expected as the result of a manual configuration change, something has occurred that could compromise your data. To help protect your data, identify and fix the cause of an unexpected state change.

To create an alert using SQL Server Management Studio

To script alerts