We have a production DB which is in a mirror configuration. It is in
synchronous high safety with automatic failover configuration. Right now we monitor it using windows service which pings it. Is there a better way to monitor mirror state using job or script?
As stated in Monitoring Database Mirroring MSDN
You can monitor a mirrored database during a mirroring session to verify whether and how well data is flowing. To set up and manage monitoring for one or more of the mirrored databases on a server instance, you can use either Database Mirroring Monitor or the
sp_dbmmonitor system stored procedures.
A database mirroring monitoring job, Database Mirroring Monitor Job, operates in the background, independently of Database Mirroring Monitor. SQL Server Agent calls Database Mirroring Monitor Job at regular intervals, the default is once a minute, and the job calls a stored procedure that updates mirroring status. If you use SQL Server Management Studio to start a mirroring session, Database Mirroring Monitor Job is created automatically.
Also, if you want you can use the sys.database_mirroring view. You can query this Catalog View and raise an email alert for each mirrored database that we find in an abnormal state using below script as explained here
DECLARE @state VARCHAR(30) DECLARE @DbMirrored INT DECLARE @DbId INT DECLARE @String VARCHAR(100) DECLARE @databases TABLE (DBid INT, mirroring_state_desc VARCHAR(30)) -- get status for mirrored databases INSERT @databases SELECT database_id, mirroring_state_desc FROM sys.database_mirroring WHERE mirroring_role_desc IN ('PRINCIPAL','MIRROR') AND mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING') -- iterate through mirrored databases and send email alert WHILE EXISTS (SELECT TOP 1 DBid FROM @databases WHERE mirroring_state_desc IS NOT NULL) BEGIN SELECT TOP 1 @DbId = DBid, @State = mirroring_state_desc FROM @databases SET @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@mssqltips.com', @body = @string, @subject = @string DELETE FROM @databases WHERE DBid = @DbId END --also alert if there is no mirroring just in case there should be mirroring :) SELECT @DbMirrored = COUNT(*) FROM sys.database_mirroring WHERE mirroring_state IS NOT NULL IF @DbMirrored = 0 BEGIN SET @string = 'Host: '+@@servername+' - No databases are mirrored on this server - notify DBA' EXEC msdb.dbo.sp_send_dbmail 'valid_mail_profile', 'DBA@mssqltips.com', @body = @string, @subject = @string END