Shulou(Shulou.com)06/01 Report--

Monitor the abnormal mirror status of SQL Server database and send alarm email

After deploying database mirroring, we need to monitor the status of the master database and mirror database participating in the mirroring, and send an alarm email if the status is abnormal. Then this script needs to be run on both the primary and mirror servers.

The catalog view sys.database_mirroring contains a row for each database on the SQL Server instance (including system databases and unmirrored databases) and, of course, state information for all mirrored databases. We can query the catalog view and trigger an alarm message for each mirrored database in an abnormal state. The author's environment is configured with asynchronous mirroring, which relies on manual failover.

Prerequisites:

1. Configure database mail and have the correct Profile.

two。 A valid Login that has permission to send mail needs to be a member of the DatabaseMailUserRole role in the msdb database.

3. A pair of mirrored databases for monitoring.

DECLARE@state VARCHAR (30) DECLARE@DbMirrored INTDECLARE@DbId INTDECLARE@String VARCHAR (100) DECLARE@databases TABLE (DBid INT, mirroring_state_desc VARCHAR (30))-- 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 WHEREmirroring_ state_desc IS NOT NULL) BEGINSELECT TOP 1 @ DbId = DBid @ State = mirroring_state_descFROM @ databasesSET @ string = 'Host:' + @ @ servername+'.'+CAST (DB_NAME (@ DbId) AS VARCHAR) +'- DB Mirroring is'+ @ state +'- notify DBA'EXEC msdb.dbo.sp_send_dbmail 'valid_mail_profile',' DBA@xxx.com', @ 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:' + @ @ servername+'-No databases are mirrored on this server-notify DBA'EXEC msdb.dbo.sp_send_dbmail 'valid_mail_profile',' DBA@xxx.com', @ body = @ string, @ subject = @ stringEND

Rely on manual failover. Put the above script into the job on the primary server and mirror server and execute it every 5 minutes.

The effect of receiving the email is as follows:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.