Database Mirroring Monitor is a graphical user interface tool that enables system administrators to view and update status and to configure warning thresholds on several key performance metrics. You can monitor a mirrored database during a mirroring session to verify whether and how well data is flowing.
There are different ways to monitor database mirroring, based on what information you want to track.
For basic information about the database mirroring state, safety level, and witness status, you can use catalog views.
Specifically, monitoring a mirrored database allow us to know:
· Verify that mirroring is functioning.
· Basic status includes knowing if the two server instances are up, that the servers are connected, and that the log is being moved from the principal to the mirror.
· Determine whether the mirror database is keeping up with the principal database.
· During high-performance mode, a principal server can develop a backlog of unsent log records that still need to be sent from the principal server to the mirror server. Furthermore, in any operating mode, the mirror server can develop a backlog of unrestored log records that have been written to the log file but still need to be restored on the mirror database.
· Determine how much data was lost when the principal server instance becomes unavailable during high-performance mode.
· You can determine data loss by looking at the amount of unsent transaction log (if any) and the time interval in which the lost transactions were committed at the principal.
· Compare current performance with past performance.
· When problems are occurring, a database administrator can view a history of the mirroring performance to help in understanding the current state. Looking at the history can allow the user to detect trends in performance, identify patterns of performance problems (such as times of day when the network is slow or the number of commands entering the log is very large).
· Troubleshoot the cause of reduced data flow between mirroring partners.
· Set warning thresholds on key performance metrics.
· If a new status row contains a value that exceeds a threshold, an informational event is sent to the Windows event log. A system administrator can then manually configure alerts based on these events. For more information, see Using Warning Thresholds and Alerts on Mirroring Performance Metrics.
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.
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.
Monitor Database Mirroring status Using TSQL Statements / Queries
We can also verify / check Database mirroring status using a system stored procedure,"sp_dbmmonitorresults".
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.
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 |
