Home
HA & DR
Mirroring

Mirroring:Important Scripts/DMV'S For Mirroring:

Anurag Dobade

Following are some important Scripts/DMV'S For monitoring Mirroring:

Check which databases have mirroring configured



SELECT DB_NAME(database_id) AS these_databases_have_mirroring_configured
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL--if IS NULL
--then databases do not have mirroring --configured
ORDER BY DB_NAME(database_id);

Synchronous and Asynchronous Modes:


Check which mirrored databases are in synchronous mode


 SELECT DB_NAME(database_id) AS these_databases_are_in_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);
Check which mirrored databases are not in synchronous mode


SELECT DB_NAME(database_id) AS these_databases_are_not_in_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);
Check which mirrored databases are in asynchronous mode

SELECT DB_NAME(database_id) AS these_mirrored_databases_are_in_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);
Check which mirrored databases are not in asynchronous mode

selECT DB_NAME(database_id) AS these_mirrored_databases_are_not_in_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);
Change mirrored databases to asynchronous mode

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);
Change mirrored databases to synchronous mode

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); 

Check Synchronized or Not Synchronized:

  Check which mirrored databases are synchronized


SELECT 
 DB_NAME(database_id) AS these_databases_are_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);
Check which mirrored databases are not synchronized


SELECT 
 DB_NAME(database_id) AS these_databases_are_not_fully_synchronized
 ,mirroring_state_desc 
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);

Ping timeout:

Check what the mirroring ping timeout value is for the mirrored databases


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

Change the mirroring ping timeout value for the mirrored databases


SELECT 
  'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER TIMEOUT 90;'
+ ' PRINT ''The mirroring ping timeout value for [' +  DB_NAME(database_id) + '] has been changed.'';'
  AS command_to_change_the_mirroring_ping_timeout_value_for_the_mirrored_database
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
AND mirroring_role_desc = 'PRINCIPAL'
ORDER BY DB_NAME(database_id);

 Pause & Resume Mirroring:

Check which mirrored databases have mirroring paused


SELECT DB_NAME( database_id ) AS these_databases_have_mirroring_paused
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 = 'SUSPENDED'
ORDER BY DB_NAME( database_id );
Check which mirrored databases do not have mirroring paused


 SELECT DB_NAME( database_id ) AS these_databases_do_not_have_mirroring_paused
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 <> 'SUSPENDED'
ORDER BY DB_NAME( database_id );

 Pause mirroring for mirrored databases


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);
Resume mirroring for mirrored databases


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);

Manually failover mirrored databases


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);

Remove database mirroring from mirrored databases


SELECT 
 'ALTER DATABASE [' + DB_NAME( database_id ) + '] SET PARTNER OFF;'
+ ' PRINT ''[' +  DB_NAME(database_id) + '] has had mirroring removed.'';' 
  AS command_to_remove_mirroring_from_the_mirrored_database 
FROM master.sys.database_mirroring 
WHERE 1=1
AND mirroring_guid IS NOT NULL
ORDER BY DB_NAME(database_id);

Post a Comment