.::: Monitor Mirroring SQL Server using T-SQL SQL Server :::.

continue from http://teguhth.blogspot.com/2018/10/how-to-mirroring-microsoft-sql-server.html

1. Query monitoring fron Principal  
SELECT @@servername as ServerName,
   SERVERPROPERTY('ServerName') AS Principal,
   m.mirroring_partner_instance AS Mirror,
   DB_NAME(m.database_id) AS DatabaseName,
   SUM(f.size*8/1024) AS DatabaseSizeMB,
   CASE m.mirroring_safety_level
      WHEN 1 THEN 'HIGH PERFORMANCE'
      WHEN 2 THEN 'HIGH SAFETY'
   END AS 'OperatingMode',
   RIGHT(m.mirroring_partner_name, CHARINDEX( ':', REVERSE(m.mirroring_partner_name) + ':' ) - 1 ) AS Port
FROM sys.database_mirroring m
JOIN sys.master_files f ON m.database_id = f.database_id
WHERE m.mirroring_role_desc = 'PRINCIPAL'
GROUP BY m.mirroring_partner_instance, m.database_id, m.mirroring_safety_level, m.mirroring_partner_name

SELECT @@servername as ServerName,
   SERVERPROPERTY('ServerName') AS Principal,
   m.mirroring_partner_instance AS DR,
   DB_NAME(m.database_id) AS [Database],
   m.mirroring_state_desc AS [State],
   CASE m.mirroring_safety_level_desc WHEN 'OFF'
   THEN 'High Performance' ELSE 'High Safety' END AS [OperatingMode],
   mirroring_failover_lsn,
    mirroring_end_of_log_lsn,
   mirroring_replication_lsn,
   CAST((pc.cntr_value)/1024 AS DECIMAL(10,3)) AS unsentMB,
   CAST((pc.cntr_value)/1024/1024 AS DECIMAL(10,3)) AS unsentGB
FROM sys.database_mirroring m
JOIN sys.dm_os_performance_counters pc ON DB_NAME(m.database_id) = pc.instance_name
WHERE m.mirroring_state IS NOT NULL
--AND m.mirroring_state <> 4
AND pc.object_name LIKE '%Database Mirroring%'
AND pc.counter_name = 'Log Send Queue KB'
  

 
2. Query monitoring fron Mirroring  
 SELECT @@servername as ServerName,
   SERVERPROPERTY('ServerName') AS Principal,
   m.mirroring_partner_instance AS DR,
   DB_NAME(m.database_id) AS [Database],
   m.mirroring_state_desc AS [State],
   CASE m.mirroring_safety_level_desc WHEN 'OFF'
   THEN 'High Performance' ELSE 'High Safety' END AS [OperatingMode],
   mirroring_failover_lsn,
  mirroring_end_of_log_lsn,
  mirroring_replication_lsn,
   CAST((pc.cntr_value)/1024 AS DECIMAL(10,3)) AS unsentMB,
   CAST((pc.cntr_value)/1024/1024 AS DECIMAL(10,3)) AS unsentGB
FROM sys.database_mirroring m
JOIN sys.dm_os_performance_counters pc ON DB_NAME(m.database_id) = pc.instance_name
WHERE m.mirroring_state IS NOT NULL
  --AND m.mirroring_state <> 4
  AND pc.object_name LIKE '%Database Mirroring%'
  AND pc.counter_name = 'Log Send Queue KB'