The Database Mirroring view allows you to monitor the mirrored databases set up on monitored SQL Server instances. You can view the mirrored databases by clicking Mirroring on the Databases tab.
If you have enabled database mirroring on your SQL Server, the SQL Diagnostic Manager Database Mirror Monitoring view is enabled by Default.
Database mirroring is a process available in SQL Server 2005 and 2008 that moves database transactions from one SQL Server database to another SQL Server database on a different instance of SQL Server.
Database mirroring allows you to configure a duplicate of your important databases for availability and redundancy.
Access the Mirroring view
You can open the Mirroring view of the SQL Diagnostic Manager Databases tab by selecting the appropriate SQL Server instance, and then clicking Databases > Mirroring.
Database mirroring statistics
In this view, you can see all the mirrored databases on all the monitored SQL Server instances, their current status, and history. The database table provides the following information:
Database Name
The name of the database.
Server Instance
The SQL Server instance hosting the mirrored database.
Current Role
Describes whether the database is the principal or the mirrored database.
Partner Instance
The name of the SQL Server instance that is the mirror partner.
Mirroring State
The status of the database mirror, color-coded based on the configured alert level.
Witness Connection
The status of the connection, color-coded based on the configured alert level.
Operational State
Describes whether the database mirroring session is operating in a preferred configuration based on how the user established which SQL Server instance hosts the principal and which SQL Server instance hosts the mirrored database.
Operating Mode (Hidden)
Shows the current operating mode: High availability with automatic fail-over (has a witness, synchronous); High availability without automatic fail-over (no witness, synchronous); High performance (no witness, asynchronous).
Available details for a specific session
Select a session, and then click the Status tab to view the following details. You can also view historical information for a selected session using the History tab. This information includes the history of the metrics over time for both the local and remote partner.
Unsent log (in kilobytes)
Size of the log waiting in the send queue on the principal, displayed in kilobytes.
Oldest unsent transaction (dd.hh:mm)
Date and time of the oldest unsent transaction in the send queue on the principal.
Time to send log (estimated)
Estimated number of minutes required by the principal to send the log waiting in the send queue to the mirror based on the current send rate. Note that this rate is affected by the rate of incoming transactions.
Send rate (in kilobytes per second)
Rate at which the principal is sending transactions to the mirror, displayed in kilobytes per second.
Rate of new transactions (in kilobytes per second)
Rate at which transactions are added to the unsent queue on the principal.
Unrestored log (in kilobytes)
Size of the log waiting in the redo queue on the mirror, in kilobytes.
Time to restore log (estimated)
Estimated number of minutes required to apply the log in the redo queue to the mirror database.
Current restore rate (in kilobytes per second)
Rate at which transactions in the redo queue are applied to the mirror database, in kilobytes per second.
Mirror commit overhead
Size of the log waiting in the redo queue on the mirror, in kilobytes. This delay is due to mirror operations and is the first indication that the traffic exceeds the capabilities of the hardware.
Time to send and restore all current log (estimated)
Estimated number of minutes required to process all outstanding transactions on both the principal and mirror.
Operating mode
Displays one of the following operating modes for the current database mirroring session: High availability with automatic fail-over (has a witness, synchronous), High availability without automatic fail-over (no witness, synchronous), or High performance (no witness, asynchronous).
Witness Address
Network address of the witness SQL Server instance.
Mirror Address
Network address of the mirror SQL Server instance.
Principal Address
Network address of the principal SQL Server instance.
Available actions in the Database Mirror Monitoring view
Each row of the mirrored databases table provides a menu of actions you can perform:
Failover
Select Fail over to partner to fail over the principal role between the two partner databases in the mirroring session.
The duration of a mirroring failover operation varies according to the amount of log in the redo queue on the mirror. All transactions in the redo queue must roll forward before the mirror can assume the role of Principal. After the failover is initiated, the Principal changes to PENDING_FAILOVER while the mirror synchronizes. Once complete, there is a brief period where user connections are terminated and while roles are switching the mirroring status is undetermined. If a refresh occurs at this time, your mirrored database does not show in the Mirrored Databases grid. This lasts for a moment and your next refresh reflects the switched roles.
For more information about the failover function, see SQL Server Books Online.
Suspend or Resume Monitoring
Use Suspend\resume session to pause all mirroring operations. While mirroring is paused in "High Availability" mode, all transactions opened against the principal are queued for transmission to the mirror partner once the session resumes.
Set mirror relationship as "Failed over" or "Normal"
Select Mark session as "failed over" or Mark session as "normal" operational status to define the status of a mirror relationship. Once you have set a preferred operational state, SQL Diagnostic Manager alerts you if the configuration differs from your selection.
Clear the role preference for this mirroring session
Use Clear preferred role for this session to clear the preferred role for the session and stop receiving alerts for differences between the current configuration and the Normal status.
Available alerts for mirrored databases
You can configure the following alerts for mirrored databases .
Mirror Commit Overhead
Average delay (in milliseconds) on the principal server instance for each transaction due to database mirroring.
Mirrored Server Role Change
A change in role occurred on a mirrored database on the monitored server.
Mirroring Oldest Unsent Transactions
The age of the oldest unsent transaction in minutes on the principal. This is only meaningful on the principal server instance.
Mirroring Preferred Configuration
A change occurred in terms of the preferred operational status.
Mirror Status
Status of the mirrored database such as synchronized, suspended or disconnected.
Mirroring Unrestored Log
Size of the redo queue on the mirror in kilobytes. The redo queue is the size in kilobytes of the outstanding transactions on the mirror that are not yet applied.
Mirroring Unsent Log
Size of the unsent log in the send queue on the principal in kilobytes.
Mirroring Witness Connection
If a witness is configured, this alert displays whether the witness is connected.