Checking SQL Server Mirroring Health & Latency

In this blog post I’m sharing info on how to check the health and latency of your Database Mirroring in SQL Server.

Monitoring Database Mirroring latency in SQL Server is needed for maintaining database availability and ensuring quick failover readiness. As a DBA we should be monitoring this if heavily relying on the Mirroring Feature for our High Availability (HA) setup.

If Database Mirroring is lagging behind, I’ve added notes at the end to help with troubleshooting it. The intention of this post was more to help with helping identify if you have issues, check out my other post if you’re looking to jump right into the troubleshooting part.

The following is included in this article:
1. Use Database Mirroring Monitor.
2. Use T-SQL to Get Mirroring Health & Latency.
3. More Ways to Monitor Mirroring Health.
4. Further Troubleshooting Mirroring Issues.

I hope this is all useful and provide you good info on ways to check if mirroring is healthy!

How to Check SQL Server Mirroring Status

1. Use Database Mirroring Monitor

In SQL Server Management Studio (SSMS), we have an easy way to view the state and health of Mirroring.

To open Database Mirroring Monitor:
-> Right-click the Mirroring database > select Tasks > Launch Database Mirroring Monitor.

With the right permissions, you can use Database Mirroring Monitor to track the performance and data flow of mirrored databases, troubleshoot issues, and verify synchronization. You can register mirrored databases on each failover partner to cache details like the database name, partner server instances, and their last known roles (principal or mirror).

Using this dashboard, we can quickly see if the mirroring state is unhealthy. It’ll show key mirroring issues, including: Unknown (monitor disconnected, cached data only), Synchronizing (mirror lagging behind principal), Suspended (principal not sending logs), and Disconnected (partners cannot connect).

2. Use T-SQL to Get Mirroring Health & Latency

We can check Mirroring sync status and Properties by running the script below on the Principal server.

-- get health status of database mirroring
SELECT 
   SERVERPROPERTY('ServerName') AS Principal, 
   m.mirroring_partner_instance AS Mirror,
   DB_NAME(m.database_id) AS DatabaseName,
   CAST(SUM(f.size * 8 / 1024.0 / 1024) AS DECIMAL(10,1)) AS DatabaseSizeGB,
   CASE m.mirroring_safety_level
      WHEN 1 THEN 'HIGH PERFORMANCE'
      WHEN 2 THEN 'HIGH SAFETY'
   END AS OperatingMode,
   RIGHT(m.mirroring_partner_name, CHARINDEX(':', REVERSE(m.mirroring_partner_name) + ':') - 1) AS Port,
   m.mirroring_state AS MirroringState,
   CASE 
      WHEN m.mirroring_state = 4 THEN 'SYNCHRONIZED'
      WHEN m.mirroring_state = 3 THEN 'PENDING FAILOVER'
      WHEN m.mirroring_state = 2 THEN 'SYNCHRONIZING'
      WHEN m.mirroring_state = 1 THEN 'DISCONNECTED'
      WHEN m.mirroring_state = 0 THEN 'SUSPENDED'
      ELSE 'UNKNOWN'
   END AS MirroringHealth,
   m.mirroring_witness_name AS WitnessName
FROM sys.database_mirroring m
JOIN sys.master_files f ON m.database_id = f.database_id
WHERE m.mirroring_role_desc = 'PRINCIPAL'
  AND m.mirroring_state IS NOT NULL
GROUP BY 
   m.mirroring_partner_instance, 
   m.database_id, 
   m.mirroring_safety_level, 
   m.mirroring_partner_name,
   m.mirroring_state, 
   m.mirroring_witness_name;

The script provides database mirroring health metrics, including size, operating mode, state, and witness server details by querying the sys.database_mirroring system table, which tracks mirroring session information.

We could add a JOIN on this table to sys.dm_os_performance_counters, which would allow us to calculate latency, giving us the estimated time for the mirror to catch up with the Principal. Since cntr_value is cumulative, we can determine log transfer rates by sampling at intervals, calculating the difference between samples, and dividing by elapsed time to measure mirroring performance.

Instead of the above, we can also use system stored procedures, sp_dbmmonitorresults:

USE msdb;
GO
EXEC sp_dbmmonitorresults databaseName, 2, 0; 

Param 1: database name.
Param 2: range of rows to retrieve, from the last row (0) to a specific time period (e.g., last two hours: 1) or a set number of rows (e.g., last 1,000: 8).
Param3: 0: uses the last two rows without refreshing the status. 1: Updates status if not refreshed in the last 15 seconds or if the user lacks sysadmin role.

The MS Docs link above for this proc includes all the details on parameters, what’s returned, and what it means. Key metrics include role (Principal/Mirror), state (e.g., Synchronizing, Synchronized), log generation/sent/restored rates, transaction delays, and timestamps for monitoring sync status.

3. More Ways to Monitor Mirroring Health

While the methods above in this post are great for checking mirroring health and latency, there are other ways that can provide a more continuous approach to monitoring SQL Server mirroring, including:

> Extended Events and Performance Counters: You can create custom sessions to monitor specific events related to database mirroring. This will offer real-time statistics on mirroring performance, including latency, log send/receive rates, and state changes. These methods are ideal for setting up continuous monitoring and alerts.
> Third-Party Monitoring Tools: Various third-party monitoring tools can be used for more advanced monitoring setups. Tools like Redgate SQL Monitor or Idera SQL Diagnostic Manager provide easy-to-use dashboards and alerting mechanisms for mirroring latency, among other performance metrics.
> Custom / In-house Monitoring: Build your own monitor. Collect the mirroring health data on a schedule and build a dashboard and/or alert. We can use the methods described above in this post for this.
> SQL Server Profiler: Not the preferred choice, especially for continuous monitoring, but it’s there if wanted.

4. Further Troubleshooting Mirroring Issues

If you’re seeing high redo or send queue sizes, it could point to:
– Workload Spikes at Principal Mirror: Evaluate the workload on the principal server that might be overwhelming the mirror.
– Disk Performance: Ensure the mirror server can handle the redo operations efficiently.
– Network Issues: Check the bandwidth and reliability between the principal and mirror (or ask the network team).

Other troubleshooting strategies include restarting Mirroring Endpoints, or SQL Service (starting with Secondary Mirror). For more information on steps to resolve SQL Server Database Mirroring issues, check out my other post: Troubleshooting Database Mirroring Issues in SQL Server