Part of the DBA-Tools Project.
Still Running Database Mirroring? Here’s What to Check
Database Mirroring has been deprecated since SQL Server 2012, Always On Availability Groups are the supported replacement for any new deployment. But deprecated doesn’t mean gone: plenty of instances still run it, migrations to AG take time, and a mirrored database left running still needs the same two questions answered as any HA feature. Can the two sides actually talk to each other, and is the mirror actually 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
STARTEDbreaks mirroring communication entirely, and it’s the first thing to check before troubleshooting anything else about a “disconnected” mirror mirroring_statedistinguishesSynchronized(caught up) fromSynchronizing(catching up) fromSuspended(manually paused, not failing over) fromDisconnected(actually broken), four very different situations that all look similar from a distance- High Safety mode with a witness enables automatic failover, High Performance mode does not, knowing which one is configured changes what “the mirror is behind” actually 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
*/
-- Blog: https://sqldba.blog/dba-scripts-get-mirroring-endpoint-health-and-status/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
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;
An empty result here means no mirroring endpoint exists at all on this instance, mirroring has never been configured, distinct from an endpoint that exists but isn’t STARTED.
Get-MirroringStatus — Is the Mirror Actually 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
*/
-- Blog: https://sqldba.blog/dba-scripts-get-mirroring-endpoint-health-and-status/
-- 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 on the principal, mirroring_role_desc = 'PRINCIPAL' filters to that side deliberately, so the output doesn’t double up if run against the mirror as well.
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.sqlsql/high-availability/mirroring/Get-MirroringStatus.sql
Example Output
Both scripts return an empty result set on this lab instance, not staged: no mirroring endpoint has ever been configured on HPAI01, and no databases show a non-null mirroring_guid. That’s the honest, correct result for an instance that has never used Database Mirroring. On an instance with mirroring configured, Get-MirroringStatus returns a row like this:
| principal_server | mirror_server | database_name | operating_mode | mirroring_state | witness_server | log_send_queue_kb | redo_queue_kb |
|---|---|---|---|---|---|---|---|
| SQLPROD01 | SQLPROD02 | OrdersDB | High Safety | Synchronized | SQLWITNESS01 | 0 | 0 |
Understanding the Results
- endpoint_state not STARTED — mirroring cannot communicate at all until this is fixed; check this before investigating anything else about a disconnected mirror
- mirroring_state = Disconnected — the endpoint is up but the partners aren’t communicating right now, usually a network issue or the partner instance being down
- mirroring_state = Suspended — mirroring was deliberately paused (often before a maintenance operation); this is not the same as a failure and won’t auto-resume until manually resumed
- operating_mode = High Safety with no witness — synchronous, but without automatic failover; a witness is required for the cluster to fail over automatically, without one this is a manual-failover configuration despite the safety level
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
Suspendedmeans 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_kbon aSynchronizingmirror the same as a growing AG redo queue, a real, worsening lag, not a stable steady-state
Related Scripts
You may also find these scripts useful:
- High Availability (hub)
- AG Replica Role and Synchronization State
- AG Failover Readiness and Readable Secondary Usage
Frequently Asked Questions
Is Database Mirroring still supported?
It’s deprecated since SQL Server 2012 but still functional in current versions. Microsoft’s guidance is to migrate to Always On Availability Groups for any new deployment; mirroring remains for environments that haven’t migrated yet.
Why does Get-MirroringStatus filter to the PRINCIPAL role only?
Running it on the mirror side would show the same pairing from the other direction. Filtering to PRINCIPAL keeps the output to one row per mirrored database regardless of which server you run it from, as long as it’s run on the current principal.
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.
Leave a Reply