Sql-server – Propper way to monitor SQL Server mirror status from service or job

mirroringsql-server-2008

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?

Best Answer

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
Related Question