How to Monitor the databases involved in Mirroring activity of SQL Server
Mirroring is the best disaster recovery technique in SQL Server. Monitoring these databases is an important task for a DBA in day to day task.
We can automate this task as well by scripting it in well mannered job of Agent.
/*Script to find which Databases are involved in Mirroring*/
select DB_NAME(database_id) dbname,mirroring_state_desc,mirroring_role_desc,
mirroring_safety_level_desc,mirroring_partner_name,mirroring_partner_instance,
mirroring_witness_state,mirroring_witness_state_desc
from master.sys.database_mirroring
where mirroring_state is not null
/*Detailed Script to find which Databases are involved in 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
/*Checking EndPoint Status involved in Database Mirroring */
select name,endpoint_id,protocol_desc,type_desc,state_desc,role_desc,
connection_auth_desc
from sys.database_mirroring_endpoints
/*Run on Witness Server if the same exist to know the info abot Database involved in Mirroring */
select database_name,principal_server_name,mirror_server_name,safety_level_desc,
partner_sync_state_desc,is_suspended,safety_sequence_number,role_sequence_number
from sys.database_mirroring_witnesses
/* Below Stored Procedure returns status rows for a monitored database from the status table in which database mirroring monitoring history is stored (last 2 hours) */
Exec msdb..sp_dbmmonitorresults 'database_name',1,0
Explanation of above procedure:
| Variable Value | Variable Description |
|---|---|
| 1st Variable = Databasename | Database Name involved in Mirroring |
| 2nd variable=0 | Last row is returned from the monitor history |
| 2nd variable=1 | Last two hour rows will be returned from monitor history |
| 2nd variable=2 | Last four hour rows will be returned from monitor history |
| 2nd variable=3 | Last eight hour rows will be returned from monitor history |
| 2nd variable=4 | Last day rows will be returned from monitor history |
| 2nd variable=5 | Last 2 day rows will be returned from monitor history |
| 2nd variable=6 | Last 100 days rows will be returned from monitor history |
| 2nd variable=7 | Last 500 days rows will be returned from monitor history |
| 2nd variable=8 | Last 1000 days rows will be returned from monitor history |
| 2nd variable=9 | Last 1,000,000 days rows will be returned from monitor history |
| 3rd Variable=0 | will return the status rows as in Monitor history (will not update the status of databases at current time) |
| 3rd Variable=1 | will return the status rows by loading the current time status of databases in the monitor history |
/*Below Script returns status rows for a monitored database from the status table in which database mirroring monitoring history is stored (last 2 hours) */
declare @dbmmonitor table
(database_name varchar(100),role varchar(100),
mirroring_state varchar(100),witness_status varchar(100),
log_generation_rate int,unsent_log int,send_rate int,
unrestored_log int,recovery_rate int,transaction_delay int,transactions_per_sec int,
average_delay int,time_recorded datetime,time_behind datetime,local_time datetime)
insert into @dbmmonitor
Exec msdb..sp_dbmmonitorresults 'database_name',1,0
select database_name,
case role when '1' then 'Principal' when '2' then 'Mirror' else role end role,
case mirroring_state when '0' then 'Suspended' when '1' then 'Disconnected'
when '2' then 'Synchronizing' when '3' then 'Pending Failover'
when '4' then 'Synchronized' else role end mirroring_state,
witness_status,log_generation_rate,unsent_log,send_rate,unrestored_log,recovery_rate,
transaction_delay,transactions_per_sec,average_delay,time_recorded,time_behind,
local_time
from @dbmmonitor
order by local_time desc
GO
Thanks
For More Information:
http://msdn.microsoft.com/en-us/library/ms366320.aspx
