Saturday, April 16, 2011

How to view Mirroring database information

Using Dynamic Management Views (DMVs) to view Mirroring database information

SELECT, d.database_id, m.mirroring_role_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


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.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

No comments: