SQL Server Mirroring Monitoring

The GUI monitoring tool for SQL Server Mirroring Monitoring

While I recommend you pay attention to all properties of the GUI, a few we should take in consideration are the following:

  • An increase in the unsent log indicates that the size of the transaction log has been generated on the principal server but has not yet been sent to the mirror server.  This increase in number will indicate that during a failover, this about of data will be ‘lost’
  • The unrestored log size shows how much transaction log has not been    replayed on the mirror database.  The larger the unrestored log, the longer a failover will take

SSMS : Connect to Principle Server > Select Database > Right Click , Go to Task & Select Launch SQL Server Mirroring Monitoring

SQL Server Mirroring Monitoring

Sql scripts for monitoring and investigating database mirroring:


--SQL SCRIPTS FOR MONITORING AND INVESTIGATING  DATABASE MIRRORING:

--INFORMATION ABOUT DATABASE MIRRORING

select DB_NAME(database_id) dbname,mirroring_state_desc,mirroring_role_desc,
mirroring_safety_level_desc,mirroring_safety_sequence
mirroring_partner_name,mirroring_partner_instance,
mirroring_witness_state,mirroring_witness_state_desc,
mirroring_failover_lsn,mirroring_connection_timeout,mirroring_redo_queue,
mirroring_end_of_log_lsn,mirroring_replication_lsn,*
from master.sys.database_mirroring
where mirroring_state is not null


--INFORMATION ABOUT DATABASE MIRRORING CONNECTIONS

select state_desc,connect_time,login_time,authentication_method,principal_name,
remote_user_name,last_activity_time,is_accept,login_state_desc,
receives_posted,sends_posted,total_bytes_sent,total_bytes_received,
total_sends,total_receives,*
from sys.dm_db_mirroring_connections

--INFORMATION ABOUT DATABASE MIRRORING ENDPOINTS

select name,endpoint_id,protocol_desc,type_desc,state_desc,role_desc,
connection_auth_desc,*
from sys.database_mirroring_endpoints

--SCRIPT TO INDICATE WHICH DATABASE HAS BEEN MIRRORED:

SELECT DB_NAME(database_id) AS mirrored
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
ORDER BY DB_NAME(database_id);


--SCRIPT TO INDICATE WHICH DATABASE IS IN SYNC MODE

SELECT DB_NAME(database_id) AS synchronous_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_safety_level_desc = 'FULL'
ORDER BY DB_NAME(database_id);

--SCRIPT TO INDICATE WHICH DATABASE IS IN ASYNC MODE

SELECT DB_NAME(database_id) AS asynchronous_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_safety_level_desc = 'OFF'
ORDER BY DB_NAME(database_id);

--SCRIPT TO INDICATE WHICH DATABASE HAS FULLT SYNCHRONIZED OR NOT

SELECT 
 DB_NAME(database_id) AS fully_synchronized
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
--AND mirroring_role_desc = 'PRINCIPAL'
--AND mirroring_role_desc = 'MIRROR'
AND mirroring_state_desc = 'SYNCHRONIZED' 
ORDER BY DB_NAME(database_id);


--SETTING THE ASYNCHRONOUS MODE OFF AND ON
 

SELECT 
  'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER SAFETY OFF;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has been set to asynchronous mirroring mode.'';'
  AS command_to_set_mirrored_database_to_use_synchronous_mirroring_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_safety_level_desc = 'FULL'
ORDER BY DB_NAME(database_id);


 SELECT 
  'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER SAFETY FULL;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has been set to synchronous mirroring mode.'';'
  AS command_to_set_mirrored_database_to_use_synchronous_mirroring_mode
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_safety_level_desc = 'OFF'
ORDER BY DB_NAME(database_id);

 --TO SUSPEND THE DATABASE MIRRORING

 SELECT 
 'ALTER DATABASE [' + DB_NAME( database_id ) + '] SET PARTNER SUSPEND;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has had mirroring paused.'';' 
  AS command_to_pause_mirroring_for_the_mirrored_database 
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_state_desc <> 'SUSPENDED'
ORDER BY DB_NAME(database_id);

 --TO RESUME THE DATABASE MIRRORING 

SELECT 
 'ALTER DATABASE [' + DB_NAME( database_id ) + '] SET PARTNER RESUME;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has had mirroring resumed.'';' 
  AS command_to_resume_mirroring_for_the_mirrored_database 
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_state_desc = 'SUSPENDED'
ORDER BY DB_NAME(database_id);


--SETTING TO FAILOVER DATABASE MIRRORING
 

SELECT 
 'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER FAILOVER;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has been been manually failed over.'';' 
  AS command_to_manually_failover_the_mirrored_database
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
AND mirroring_safety_level_desc = 'FULL'
AND mirroring_state_desc = 'SYNCHRONIZED'
ORDER BY DB_NAME(database_id);


-- HISTORY OF RESULTS

Exec msdb..sp_dbmmonitorresults 'prod',1,0


--use on mirror db to stop miroring and drop db

alter database mirror set partner off
restore database mirror
drop database MIRROR

USE [master]
GO

DROP ENDPOINT [endpoint_mirror]
GO

Also See:

SQL Server 2019

Monitoring Database Mirroring