Monitoring SQL Server database exception mirroring status sending alert messages

Tag: Database Mirroring SP

Monitoring SQL Server database exception mirroring status sending alert messages

After database mirroring has been deployed, we need to monitor the status of the primary and mirrored databases participating in the mirror, and if the status is abnormal, send an alert message. Then this script needs to be run on both the primary and the mirror servers.

catalog view sys.database_mirroring contains one row for each database on the instance of SQL Server (both the system database and the database that does not have the mirror configured) and, of course, the status information for all the mirrored databases. We can query the catalog view to trigger an alert message for each of the mirror databases of the exception state. The author's environment is configured with asynchronous mirroring and relies on manual failover.

Prerequisites:

1. Configure the Database Mail with the correct profile.

2. A valid login with permission to send a message needs to be a member of the DatabaseMailUserRole role in the msdb database.

3. A pair of mirrored databases for monitoring.

Declare@state varchar (Declare@dbmirrored intdeclare@dbid intdeclare@string varchar) (100) declare@databases table  (Dbid int, mirroring_state_desc varchar ())-- get  Status for mirrored databasesinsert@databasesselectdatabase_id, mirroring_state_ descfromsys.database_mirroringwheremirroring_role_desc in  (' PRINCIPAL ', ' MIRROR ') andmirroring_state_ desc not in  (' SYNCHRONIZED ', ' SYNCHRONIZING ')-- iterate through mirrored  databases and send email alertwhileexists  (Select top 1 dbid from   @databases  where mirroring_state_desc is not null) beginselect top 1   @DbId  = DBid,  @State  = mirroring_state_descFROM  @databasesSET   @string  =  ' host:  ' [email protected] @servername + '. ' +cast (db_name (@DbId)  as varchar) +  '  - db mirroring is  ' [email protected] + '  - notify dba ' EXEC msdb.dbo.sp_send_ dbmail  ' valid_mail_profile ',  ' [email protected] ',  @body  =  @string,  @subject  =  @stringDELETE  FROM  @databases  WHERE DBid =  @DbIdEND--also  Alert if there is no mirroring just in case there should  be mirroring :) select@dbmirrored = count (*) fromsys.database_mirroringwheremirroring_ state is not nullif@dbmirrored = 0beginset  @string  =  ' host:  ' [ Email protected] @servername + '  - no databases are mirrored on this  server - notify dba ' exec msdb.dbo.sp_send_dbmail  ' valid_mail_profile ',  ' [ Email protected] ',  @body  =  @string,  @subject  =  @stringEND

relies on manual failover. Place the above script in a job on the primary server and the mirror server every 5 minutes.

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1906242

Monitoring SQL Server database exception mirroring status sending alert messages