Disable ionic's sidemenu content drag to toggle menu

Monitoring Your SQL Server 2005 Database Mirror

Once you have a database mirror running, it is imperative to monitor the performance of the mirror, and in the case of high performance (asynchronous) mirrors, how far behind the mirror is compared to the principle.

With high safety (synchronous) mirroring, performance is critical, as all transactions must be committed on the mirror before they can be committed on the principle. On a high performance mirror, a delay can mean the mirror isn’t as up to date as service levels require, and the transaction log can fill up as transactions cannot be backed up until they have been applied on the mirror.

There are different tools for monitoring mirroring:

1. Database Mirroring Monitor (SQL Server Management Studio)
2. Perfmon Counters
3. Catalog Views

Database Mirroring Monitor

To launch the Database Mirroring Monitor, right click on the database in SSMS, select "Tasks", then "Launch Database Mirroring Monitor". You then need to register your mirrored database by connecting to either the principle or the mirror, and selected the mirrored database. You can then view the current statistics of the mirror, including previous snapshots of the mirror’s status.

Perfmon Counters

The primary drawback to the Database Mirroring Monitor is that you must be watching it to ensure there’s no problems. The Perfmon Counters provided by the SQL Server: Database Mirroring object provide similar information that can be found in the Database Mirroring Monitor, but with all the benefits of Perfmon counters, such as registering alerts, and having a performance baseline. The counters available are:

Name	Description
Bytes Received/sec	The number of bytes sent to the other server, per second
Bytes Sent/sec	The number of bytes received from the other server, per second
Log Bytes Received/sec	The number of bytes from the log received from the principle, per second
Log Bytes Sent/sec	The number of bytes from the log sent to the mirror, per second
Log Send Queue KB	The number of bytes in the log that has not been sent to the mirror
Pages Sent/sec	The number of transaction log pages sent per second
Receives/sec	The number of mirroring messages received per second
Redo Bytes/sec	The number of bytes of log rolled forwards per second on the mirror
Redo Queue KB	The number of bytes of transaction log that remains to be applied to the mirror to roll it forwards
Send/Receive Ack Time
Sends/sec	The number of mirroring messages sent per second
Transaction Delay	The delay while waiting for the mirror to commit a transaction

For a high safety mirror, Transaction Delay is probably the most important counter, as it monitors how long transactions are taking to be committed on the mirror. If this counter increases, then there is a performance problem somewhere in either the network or on the mirror. Meanwhile, users on the principle will notice a decrease in performance when committing transactions.

For a high performance mirror, there are two perspectives that need to be viewed - the status of the principle, and the status of the mirror. The main counter on the principle is the Log Send Queue KB, which shows how much log has not been sent over to the mirror. However, this value could increase when a lot of log traffic occurs in a short period of time, such as during an index rebuild. If this counter is combined with a drop in the number of Log Bytes Sent/sec, a problem can be identified. As long as the log is still being sent at a rapid rate, a small delay can be expected, particularly if the network cannot keep up with the amount of log traffic being generated by the principle. Note that a large Log Send Queue KB is dangerous, as this data has not been sent to the mirror, and could be lost if the principle goes down.

From the mirror’s perspective, Redo Queue KB is the premier counter. This counter shows how many KB have been received by the mirror, but not yet applied to the database. Again, this value can grow if the mirror cannot keep up with the number of transactions being sent through the network, for example, during index rebuilds. However, this number is important as it shows how far behind the mirror is to the principle. All this data is already on the mirror, but not applied, so the transactions up to this point are still safe.

Catalog Views

There are four views related to mirroring. They are:

1. sys.database_mirroring
2. sys.database_mirroring_endpoints
3. sys.database_mirroring_witnesses
4. sys.dm_db_mirroring_connections

These views show information about the mirroring setup, but not a lot about the current status of how the mirror is operating.

sys.database_mirroring contains a row for each mirror, including the current state of the mirror.

sys.dm_db_mirroring_connections has information about how much traffic the connection has experienced, and the last time that it had traffic, but viewing this is more appropriate for checking the configuration of the mirror, not constant monitoring.

Afterword

Perfmon counters are the way to go to monitor a database mirroring implementation. Having a proper benchmark of your mirroring statistics is vital to be able to set accurate thresholds for the counter alerts, and periodic review of these thresholds is recommended.