By: Rajendra Gupta | Comments (1) | Related: > 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>
About the author
Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
