PowerShell and SQL Server Events–Monitoring Changes in Database Mirroring


This is cool !!!!!  and without any database background engine such as SQL Agent or Service Broker

This is a bit that I will show in SQLBITS Alegre

Watch The Video

Firts the Event Class that we will use is DATABASE_MIRRORING_STATE_CHANGE and the States can be  :

0 = Null Notification
1 = Synchronized Principal with Witness
2 = Synchronized Principal without Witness
3 = Synchronized Mirror with Witness
4 = Synchronized Mirror without Witness
5 = Connection with Principal Lost
6 = Connection with Mirror Lost
7 = Manual Failover
8 = Automatic Failover
9 = Mirroring Suspended
10 = No Quorum
11 = Synchronizing Mirror
12 = Principal Running Exposed

Then we need to use the correct WMI namespace . I have 2 instances, the default and INST1. Default Is the principal and INST1 the Mirror.

WMI NameSpace To Default Instace

Register-WMIEvent -Namespace 'root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'  `

 

WMI NameSpace to INST1

Register-WMIEvent -Namespace 'root\Microsoft\SqlServer\ServerEvents\INST1'  

Then it is just create the event notification Alegre

 

#PRINCIPAL

$query = "select * from DATABASE_MIRRORING_STATE_CHANGE where state = 6 or state = 7  or state = 8 or state =9"

Register-WMIEvent -Namespace 'root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'  `

-Query $query  -Action {ipmo sqlevents ;ipmo functions;Send-SMTPmail -smtpserver 'smtp.mail.yahoo.com.br' -from 'juniorlaerte@yahoo.com.br' -to 'laertejuniordba@hotmail.com' -subject 'Event Notification - Database Mirroing' `

-body " Details : `n`n

        ServerName  : $($event.SourceEventArgs.NewEvent.ServerName) `n 

        Database ID : $($event.SourceEventArgs.NewEvent.Databaseid) `n  

        Database Name  : $($event.SourceEventArgs.NewEvent.DatabaseName ) `n   

        SPID :  $($event.SourceEventArgs.NewEvent.spid) `n 

        Start Time : $([Management.ManagementDateTimeConverter]::ToDateTime($event.SourceEventArgs.NewEvent.StartTime)) `n  

        Message   : $(     switch ($event.SourceEventArgs.NewEvent.State) 

                                {   

                                    6 {'Connection with Mirror Lost'}

                                    7 {'Manual Failover'}

                                    8 {'Automatic Failover'}

                                    9 {'Mirroring Suspended'}

                                }  

                    )  

" }

 

#MIRROR

$query = "select * from DATABASE_MIRRORING_STATE_CHANGE where state = 5 or state = 7  or state = 8 or state =9"

Register-WMIEvent -Namespace 'root\Microsoft\SqlServer\ServerEvents\INST1'  `

-Query $query  -Action {ipmo sqlevents ;ipmo functions;Send-SMTPmail -smtpserver 'smtp.mail.yahoo.com.br' -from 'juniorlaerte@yahoo.com.br' -to 'laertejuniordba@hotmail.com' -subject 'Event Notification - Database Mirroing' `

-body " Details : `n`n

        ServerName  : $($event.SourceEventArgs.NewEvent.ServerName) `n 

        Database ID : $($event.SourceEventArgs.NewEvent.Databaseid) `n  

        Database Name  : $($event.SourceEventArgs.NewEvent.DatabaseName ) `n   

        SPID :  $($event.SourceEventArgs.NewEvent.spid) `n 

        Start Time : $([Management.ManagementDateTimeConverter]::ToDateTime($event.SourceEventArgs.NewEvent.StartTime)) `n  

        Message   : $(     switch ($event.SourceEventArgs.NewEvent.State) 

                                {   

                                    5 {'Connection with Principal Lost'}

                                    7 {'Manual Failover'}

                                    8 {'Automatic Failover'}

                                    9 {'Mirroring Suspended'}

                                }  

                    )  

" }

 

 

and I do not need any background engine 🙂  PowerShellLifestyle 🙂

Dowload (Mirroring.ps1)
 

#PowerShellLifeStyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

3 Responses to PowerShell and SQL Server Events–Monitoring Changes in Database Mirroring

  1. Laerte,

    Cara parabéns, muito legal, show de bola…..

  2. Perl says:

    Alternative is to use the Get-CsDatabaseMirrorState PowerShell commandlet, which coudl find the current status of the SQL mirror
    http://technet.microsoft.com/en-us/library/jj204845.aspx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s