Monitoring Mirroring

I set up a SQL Server 2008 mirror, and – using the monitoring tool – I set thresholds, above which the system should launch warnings.

Having no real idea of the data volumes to expect between the mirrored databases, I accepted the default warnings and thresholds – warning if the unsent log exceeds at 1Kb, the unrestored log exceeds 1Kb, the oldest unsent transaction exceeds 1 minute and the commit overhead exceeds 1 millisecond.

When mirroring started, the following error messages appeared in the log:

The alert for ‘mirror commit overhead’ has been raised. The current value of ’11’ surpasses the threshold ‘1’.

The alert for ‘unrestored log’ has been raised. The current value of ’48’ surpasses the threshold ‘1’.

The alert for ‘unsent log’ has been raised. The current value of ’60’ surpasses the threshold ‘1’.

Unhelpfully, there is no indication in the log which database has exceeded the threshold. Because I had just started to configure the thresholds this was not an issue. However, in a production environment with a half-dozen mirrored databases the lack of any indication which database has exceeded the threshold could be a problem.

After a few minutes, I set thresholds for each mirrored database. To set thresholds, launch the mirroring monitor (right-click on the database, then select tasks – Launch Database Mirroring Monitor…). The far-right column (‘History) displays performance history on the mirrored databases – principal and Mirror. The columns don’t sort so I had to search through for maximum valuse manually. this is shown in fig 1 (below).

After examining the performance of the mirror on each database, I set the thresholds by selecting the warning tab on the Database Mirroring Monitor. Whenever the trhresholds are exceeded, a message is written the the SQL Server log.