How to Monitor Database Mirroring, SQL Server?

Leave a comment (0) Go to comments

SQL Server Mirroring can be monitored

  1. SQL Server Management Studio or via
  2. 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.

 How to Monitor Database Mirroring, SQL Server? SQL Server Monitor Database Mirroring sql server Database Mirroring Monitor sp dbmmonitorresults Monitor Database Mirroring how to monitor sql server mirroring using tsql queries Database Mirroring Monitor

Once you Click on Launch Database Mirroring Monitor, Database Mirroring Monitor will be opened in a new window

How to Monitor Database Mirroring, SQL Server? SQL Server Monitor Database Mirroring sql server Database Mirroring Monitor sp dbmmonitorresults Monitor Database Mirroring how to monitor sql server mirroring using tsql queries Database Mirroring Monitor


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'

OUTPUT

How to Monitor Database Mirroring, SQL Server? SQL Server Monitor Database Mirroring sql server Database Mirroring Monitor sp dbmmonitorresults Monitor Database Mirroring how to monitor sql server mirroring using tsql queries Database Mirroring Monitor

This procedure will return the following information.

database_name Test_SQLServer_Mirroring
role 1 – Principal and 2 – Mirror
mirroring_state

Unknown

Synchronizing

Synchronized = 4

Suspended

Disconnected

witness_status

Unknown

1 = Connected

Disconnected

log_generation_rate 0
unsent_log 0
send_rate 0
unrestored_log 0
recovery_rate 0
transaction_delay 0
transactions_per_sec 0
average_delay 0
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.

EOF - How to Monitor Database Mirroring, SQL Server?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.