Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Database Mirroring Report


By:   |   Updated: 2015-07-13   |   Comments   |   Related: More > Database Mirroring

Problem

Once Database Mirroring has been configured and put in place, one of the things you need to do is to monitor the mirroring status to make sure things are working as planned. In this tip we will create a process to generate a report, so you can see the status.

Solution

SQL Server Database Mirroring monitoring can be accomplished a few ways:

  • Querying the "sys.database_mirroring" catalog view and creating alerts
  • Using SQL Server Event Notifications with state-change (WMI) events
  • Setting up performance threshold events
  • Setting up SQL Server Performance counters

I think it is a good idea to document the SQL Server Database Mirroring configuration along with having status reports that can be scheduled for regular monitoring.

In this tip we will look at how to implement SQL Server Database Mirroring reports. We will query the sys.database_mirroring catalog view to check the status of mirrored SQL Server databases. This view contains one row for each database in the SQL Server instance. If the database is not ONLINE or database mirroring is not enabled, the values of all columns except database_id will be NULL.

SQL Server Database Mirroring Status Report

Below is the code for report where the sys.database_mirroring and sys.databases have been joined. This will produce HTML output and email the report using Database Mail.

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( 
SELECT 
   d.name as 'td','',
   mirroring_state_desc as 'td','',
   mirroring_role_desc as 'td','',
   mirroring_partner_instance as 'td','',
   mirroring_role_sequence as 'td','',
   case (m.mirroring_witness_state_desc) when 'Unknown' then 'No Witness Server Configured'
      else m.mirroring_witness_state_desc end as 'td','' ,
   case(m.mirroring_witness_state_desc) when 'Unknown' then 'No Witness Server Configured'
      else m.mirroring_witness_name end  as  'td','',
   log_reuse_wait_desc  as 'td',''
FROM 
  sys.database_mirroring M inner join SYS.DATABASES d
  on m.database_id = d.database_id
WHERE mirroring_state_desc is not null 
ORDER BY d.name,mirroring_state_desc
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body>
<h3>Database Mirroring Report</h3>
<table border = 1> 
<tr>
<tr bgcolor="gray">
<th>Database</th> 
<th>Mirroring_State</th>
<th>Mirroring_state_desc  </th>
<th>Mirroring_partner_instance  </th>
<th>mirroring_role_sequence  </th>
<th>Mirroring_witness_state_desc </th>
<th>Mirroring_Witness_Instance</th>
<th>Log_reuse_wait_desc </th>
</tr>'    
 
SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'profile_name', -- enter Database Mail profile name
      @recipients = '[email protected]', -- list of Email recipients
      @subject = 'Database Mirroring Report',
      @body = @body,
      @body_format ='HTML';

Sample Status Report for SQL Server Database Mirroring

The output will look like this:

Data Explanation for the SQL Server Database Mirroring Status Report

After running the above code the Database Mirroring state (mirroring_role_desc) can be one of the following:

  • DISCONNECTED
  • SYNCHRONIZED
  • SYNCHRONIZING
  • PENDING_FAILOVER
  • SUSPENDED
  • UNSYNCHRONIZED
  • NULL

Note: Be sure to check the Database Mirroring state if Status is a value other than synchronized.

Mirroring_state_desc describes the current role of the local database in the database mirroring session.

  • Principal
  • Mirror
  • NULL = Database is inaccessible or is not mirrored.

Mirroring Role Sequence is used to check the number of times that mirroring partners have switched the principal and mirror roles due to a failover or forced change. This is a useful counter to track the failover and if this value is high then consider investigating. This column will have NULL values if the database is not mirrored or is inaccessible

If no witness server is configured then the column Mirroring_witness_state_desc will indicate that no witness server is configured.

If the secondary falls behind, for example because of an interrupted network connection, the log reading process will wait until it can continue to transmit transaction information. During that time, transactions in the log that have not been processed by the mirroring agent cannot be purged and the virtual log files containing these records can't be reused. SQL Server will return a log_reuse_wait_desc value of "DATABASE MIRRORING" if it runs out of virtual log files because of this. To solve this issue we have to make sure the database mirror transaction log is adequately sized to keep up with the transaction load and the network connection between the instances is stable. Make sure there is enough room for the transaction log of the source database to grow in case of a mirror delay.

Next Steps
  • Now that you have a process to generate a Database Mirroring report, setup a SQL Agent job to have this sent to you on a periodic basis.
  • Setup additional checks such as a role change to email you this report.
  • Check out these other Database Mirroring Tips/a>


Last Updated: 2015-07-13




About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools