CdG

SQL Mirror Session

Mirroring session consist in the relationships between partner servers and witness. To understand how database mirror session work and states of databases is possible to inspect data from catalog views (sys.database_mirroring for principal or mirror, sys.database_mirroring_witnesses for witness).

The following query return the description of mirror session about principal or mirror databases:
SELECT
    DB_NAME(database_id) AS 'DatabaseName'
    , mirroring_role_desc
    , mirroring_safety_level_desc
    , mirroring_state_desc
    , mirroring_safety_sequence
    , mirroring_role_sequence
    , mirroring_partner_instance
    , mirroring_witness_name
    , mirroring_witness_state_desc
    , mirroring_failover_lsn
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;

The following query return the description of mirror session about witness instance:
SELECT
    Database_name
    , safety_level_desc
    , safety_sequence_number
    , role_sequence_number
    , is_suspended
    , is_suspended_sequence_number
    , principal_server_name
    , mirror_server_name
FROM sys.database_mirroring_witnesses;

RFID Tag Memorandum

CdG