Using Dynamic Management Views (DMVs) to view Mirroring database information
SELECT d.name, d.database_id, m.mirroring_role_desc,
m.mirroring_state_desc,m.mirroring_safety_level_desc,
m.mirroring_partner_name, m.mirroring_partner_instance,
m.mirroring_witness_name, m.mirroring_witness_state_desc
FROM sys.database_mirroring m JOIN sys.databases d
ON m.database_id = d.database_id
WHERE mirroring_state_desc IS NOT NULL
Witness Server Information
The sys.database_mirroring_witnesses catalog view provides information on the witnesses server
SELECT principal_server_name, mirror_server_name,
database_name, safety_level_desc
FROM sys.database_mirroring_witnesses
Endpoints
Query the catalog view sys.database_mirroring_endpoints to find useful information regarding the endpoints, such as the status of the endpoint, encryption settings, etc.
SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc, t.port, e.is_encryption_enabled, e.encryption_algorithm_desc, e.connection_auth_desc
FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
ON e.endpoint_id = t.endpoint_id
