SQL Server Mirroring can be monitored
- SQL Server Management Studio or via
- TSQL Queries
Monitor LOG Shipping status Using SSMS
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.
How to Access Database Mirroring Monitor ?
Database Mirroring Monitor can be accessed via SSMS
Open SSMS >>> SQL Server Instance >>> Databases >>> User Database, which is configured for mirroring >>> Right Click >>> Tasks >>> Select Launch Database Mirroring Monitor, as shown in following Screen Shot.
Once you Click on Launch Database Mirroring Monitor, Database Mirroring Monitor will be opened in a new window
What all information we get via Database Mirroring Monitor ?
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.
Monitor Database Mirroring status Using TSQL Statements / Queries
We can also verify / check Database mirroring status using a system stored procedure,"sp_dbmmonitorresults".
We can execute this procedure either on Principal Server or on Mirrored Server on MSDB database.
msdb..sp_dbmmonitorresults @database_name = 'Test_SQLServer_Mirroring'
This procedure will return the following information.
|role||1 – Principal and 2 – Mirror|
Synchronized = 4
|time_recorded||12/27/2010 4:19:49 PM|
|time_behind||12/27/2010 4:19:49 PM|
|local_time||12/27/2010 4:19:49 PM|
For More Information on SQL Server Database Mirroring Monitoring.