Example: Monitoring DB mirroring

Aug 4, 2011 at 10:55 PM

You wanted to know how people were using this so...

We have 4 database servers with about 100 DBs between them in two mirrored pairs. We wanted a solution to monitor the mirror states, specifically when a DB goes into 'disconnected' state, meaning it lost it's partner. The SQL Agent solution was OK but I'm not much of a DBA and would rather have a monitoring method 'outside' of SQL server itself anyway.

So, as soon as I found this project (4 days ago) I worked up the following event filters and bound them to SMTPEvent consumers to send me an email. The solution works great aside from the fact we get spammed with 100 emails whenever a server disappears (which has only happened so far when testing against staging *knocks on wood*)

Filters:

$MirrorLostFilter = New-WmiEventFilter -Name 'Mirror Lost' -EventNamespace 'root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER' -Query 'select * from DATABASE_MIRRORING_STATE_CHANGE where State = 6'

$PrincipalLostFilter = New-WmiEventFilter -Name 'Principal Lost' -EventNamespace 'root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER' -Query 'select * from DATABASE_MIRRORING_STATE_CHANGE where State = 5'

Consumers (note, the headerfields are a custom addition, maybe they'll show up in future releases. ;):

$MirrorLostEmail = New-WmiEventConsumer -Name 'Email Mirror Lost' -ConsumerType SMTP -SMTPServer "smtp_relay" `
    -ToLine 'tired_ops_people@mycompany.com' `
    -Subject '%DatabaseName% has lost connection to the mirror' `
    -Message '%DatabaseName% has lost connection to the mirror' `
    -HeaderFields "X-Auto-Response-Suppress: DR,OOF,AutoReply"

$PrincipalLostEmail = New-WmiEventConsumer -Name 'Email Principal Lost' -ConsumerType SMTP -SMTPServer "smtp_relay" `
    -ToLine 'tired_ops_people@mycompany.com' `
    -Subject '%DatabaseName% has lost connection to the principal' `
    -Message '%DatabaseName% has lost connection to the principal' `
    -HeaderFields "Importance: High","X-Auto-Response-Suppress: DR,OOF,AutoReply"