DBA Scripts: Get Mirroring Endpoint Health and Status

Still Running Database Mirroring? Here’s What to Check

Database Mirroring has been deprecated since SQL Server 2012, and Always On Availability Groups are the replacement for any new deployment. Deprecated doesn’t mean gone though. Plenty of instances still run it, migrations to AG take time, and a mirror that is still running needs the same two questions answered as any HA feature: can the two sides talk to each other, and is the mirror caught up?

Mirroring Endpoint Health checks the first question: the endpoint’s state, port, and authentication configuration, since if the endpoint isn’t STARTED, nothing else here matters. Mirroring Status checks the second: operating mode, mirroring state, and point-in-time send/redo queue latency for every mirrored database.

Why Mirroring Endpoint Health and Status Matter

• An endpoint that isn’t STARTED breaks mirroring communication entirely, and it’s the first thing to check before troubleshooting anything else about a “disconnected” mirror
• mirroring_state separates a mirror that is caught up, one that is catching up, one that was paused on purpose and one that is broken, four situations that look much the same from a distance
• High Safety with a witness gives automatic failover and High Performance does not, so knowing which one is configured changes what “the mirror is behind” means for risk
• If you’re planning a migration away from mirroring to Availability Groups, knowing exactly what’s configured now is the starting point

When to Run These Scripts

• Any environment still running Database Mirroring, as a routine health check
• Troubleshooting a mirror reported as Disconnected, start with endpoint health before assuming a data problem
• Before planning a migration off mirroring to Availability Groups, to document the current configuration accurately
• After any network change between the principal and mirror servers, to confirm the endpoint still communicates

The Scripts

Get-MirroringEndpointHealth: Can the Two Sides Talk?

/*
Script Name : Get-MirroringEndpointHealth
Category    : high-availability
Purpose     : Returns the state, port, role, and authentication configuration of the database
              mirroring endpoint. If the endpoint is not STARTED, mirroring cannot communicate.
              Run on both the principal and mirror server during troubleshooting.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-mirroring-endpoint-health-and-status/)
Requires    : VIEW SERVER STATE
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM sys.database_mirroring_endpoints)
BEGIN
    SELECT 'No database mirroring/AG endpoint exists on this instance.' AS status;
END
ELSE
BEGIN
    SELECT
        e.name AS endpoint_name,
        e.state_desc AS endpoint_state,
        d.role_desc AS endpoint_role,
        tcp.port AS port_number,
        d.connection_auth_desc AS connection_auth,
        d.encryption_algorithm_desc AS encryption_algorithm
    FROM sys.endpoints e
    JOIN sys.database_mirroring_endpoints d ON d.endpoint_id = e.endpoint_id
    JOIN sys.tcp_endpoints tcp ON tcp.endpoint_id = e.endpoint_id;
END

If no endpoint exists at all, the script returns one status row saying so. That is a different finding from an endpoint that exists but isn’t STARTED: the first means mirroring was never set up here, the second means it was and the endpoint is down.

Get-MirroringStatus: Is the Mirror Caught Up?

/*
Script Name : Get-MirroringStatus
Category    : high-availability
Purpose     : Shows health, state, and point-in-time latency for all mirrored databases on this
              instance. Run on the principal server. Reports operating mode, mirroring state,
              database size, log send queue, and redo queue.
              Note: Database Mirroring has been deprecated since SQL Server 2012. Always On
              Availability Groups are the supported replacement for new deployments.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-mirroring-endpoint-health-and-status/)
Requires    : VIEW SERVER STATE, VIEW DATABASE STATE
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)) AS principal_server,
    m.mirroring_partner_instance AS mirror_server,
    DB_NAME(m.database_id) AS database_name,
    CAST(SUM(f.size * 8 / 1024.0 / 1024) AS DECIMAL(10,1)) AS database_size_gb,
    CASE m.mirroring_safety_level
        WHEN 1 THEN 'High Performance'
        WHEN 2 THEN 'High Safety'
        ELSE 'Unknown'
    END AS operating_mode,
    CASE m.mirroring_state
        WHEN 0 THEN 'Suspended'
        WHEN 1 THEN 'Disconnected'
        WHEN 2 THEN 'Synchronizing'
        WHEN 3 THEN 'Pending Failover'
        WHEN 4 THEN 'Synchronized'
        ELSE 'Unknown'
    END AS mirroring_state,
    m.mirroring_role_desc AS role,
    m.mirroring_witness_name AS witness_server,
    perf.log_send_queue_kb,
    perf.redo_queue_kb,
    RIGHT(
        m.mirroring_partner_name,
        CHARINDEX(':', REVERSE(m.mirroring_partner_name) + ':') - 1
    ) AS endpoint_port
FROM sys.database_mirroring m
JOIN sys.master_files f ON f.database_id = m.database_id
OUTER APPLY (
    SELECT
        MAX(CASE WHEN counter_name = N'Log Send Queue KB' THEN cntr_value END) AS log_send_queue_kb,
        MAX(CASE WHEN counter_name = N'Redo Queue KB' THEN cntr_value END) AS redo_queue_kb
    FROM sys.dm_os_performance_counters
    WHERE object_name LIKE N'%Database Mirroring%'
      AND instance_name = DB_NAME(m.database_id)
) perf
WHERE m.mirroring_guid IS NOT NULL
  AND m.mirroring_role_desc = N'PRINCIPAL'
GROUP BY
    m.database_id,
    m.mirroring_partner_instance,
    m.mirroring_safety_level,
    m.mirroring_state,
    m.mirroring_role_desc,
    m.mirroring_partner_name,
    m.mirroring_witness_name,
    perf.log_send_queue_kb,
    perf.redo_queue_kb
ORDER BY DB_NAME(m.database_id);

Run it on the principal. The mirroring_role_desc = 'PRINCIPAL' filter is deliberate, so a database shows once and running it on the mirror returns no rows.

How To Run From The Repo

Clone DBA Tools, initialize and run either script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Mirroring endpoint state, port, and authentication:
.\run.ps1 Get-MirroringEndpointHealth

# Mirroring state and queue latency, run on the principal:
.\run.ps1 Get-MirroringStatus

# To run against a remote sql server:
.\run.ps1 Get-MirroringStatus -ServerInstance SQLSERVER01

These scripts live in the repo at:

• sql/high-availability/mirroring/Get-MirroringEndpointHealth.sql
• sql/high-availability/mirroring/Get-MirroringStatus.sql

Example Output

My lab has never had mirroring configured, so Get-MirroringEndpointHealth returns its status row, No database mirroring/AG endpoint exists on this instance., and Get-MirroringStatus returns no rows because no database has a mirroring_guid. That is the correct answer for an instance that has never used Database Mirroring. On an instance with mirroring configured, Get-MirroringStatus returns a row like this (key columns shown):

Understanding the Results

Get-MirroringEndpointHealth

One row per database mirroring endpoint, which on most instances means one row.

Get-MirroringStatus

One row per mirrored database where this server is the principal.

Best Practices

• Check endpoint health first when a mirror looks disconnected, a communication problem and a data problem need completely different fixes
• Don’t assume Suspended means broken, it’s a deliberate, reversible pause, resume it explicitly once the reason for suspending has passed
• If planning a migration to Availability Groups, capture this output as your documented starting configuration before making changes
• Treat any growing redo_queue_kb on a Synchronizing mirror the same as a growing AG redo queue, a real, worsening lag, not a stable steady-state

Microsoft Learn: sys.database_mirroring · sys.database_mirroring_endpoints · Database Mirroring performance counters

Frequently Asked Questions

Summary

A deprecated feature still in production needs the same two questions answered as any HA technology: can the two sides communicate, and are they actually in sync? These two scripts answer both, endpoint health first since nothing else matters if the endpoint isn’t started, then mirroring state and queue latency for whatever’s actually configured.

Run both as a routine check on any instance still using Database Mirroring, and treat a growing queue or a disconnected endpoint with the same urgency you’d give the equivalent AG finding.

Related