syntaxhelp.com Your online cheat sheet!

Checking if the database is Trustworthy

-- Database Name: AdventureWorks
SELECT 
    is_trustworthy_on
FROM sys.databases
WHERE name = 'AdventureWorks'
/*
is_trustworthy_on
-----------------
1
*/

Querying the Mirror Safety Level

SELECT 
    mirroring_safety_level_desc
FROM sys.database_mirroring
WHERE database_id = db_id('dbname')
/*
Possible Values:
FULL
OFF
UNKNOWN
NULL - Databse is offline
*/

Querying the Witness Name of a Mirroring Session

SELECT 
    mirroring_witness_name
FROM sys.database_mirroring
WHERE database_id = db_id('dbname')

Querying the State of the Witness Server

SELECT 
    mirroring_witness_state_desc
FROM sys.database_mirroring
WHERE database_id = db_id('dbname')
/*
Possible Values:
UNKNOWN
CONNECTED
DISCONNECTED
NULL
*/

Checking the Role and Mirroring State of a server

-- DatabaseName: AdventureWorks
SELECT 
    mirroring_role_desc, 
    mirroring_state_desc
FROM sys.database_mirroring
WHERE database_id = db_id('AdventureWorks')
/*
mirroring_role_desc mirroring_state_desc
------------------- --------------------
PRINCIPAL           SYNCHRONIZED
*/

Checking if the Mirroring Session has started or not

SELECT 
    role_desc, 
    state_desc 
FROM sys.database_mirroring_endpoints
/*
role_desc     state_desc  
------------- ------------
PARTNER       STARTED
*/

Querying the Mirroring Endpoints

SELECT
    name, 
    role_desc AS Role, 
    state_desc AS State, 
    connection_auth_desc AS ConnAuth, 
    encryption_algorithm_desc AS Algorithm
FROM sys.database_mirroring_endpoints;
/*
name      role    state   connAuth  Algorithm 
--------- ------- ------- --------- ----------
Mirroring PARTNER STARTED NEGOTIATE NONE
*/