PowerShell and SQL Server Events–Changes in Database Options


Today  my good friend Marcos Freecia (@sqlfreccia)  asked if there is some way to be notified if a database is taking off line.

I did not found any event to do this (if someone knows please tell me), BUUUUT every changes in the database options is logged in the SQL Server ERRORLOG and there is an event fired to a ERRORLOG.

Then was just find what error id is for the database options (5084) – Errors 5000 – 5999 and register the event :

 

$query = "select * from  ERRORLOG where Error = 5084"

Register-WMIEvent     -SourceIdentifier "ErrorLogevent" `

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

                    -Query $query `

                    -Action {    ipmo functions;    

                                Send-SMTPmail     -smtpserver 'smtp.mail.yahoo.com.br' `

                                                -from 'juniorlaerte@yahoo.com.br' `

                                                -to 'laertejuniordba@hotmail.com' `

                                                -subject 'Event Notification - Database Options' `

                                                -body     "ApplicationName  : $($Event.SourceEventArgs.NewEvent.ApplicationName) `n

                                                         Error            : $($Event.SourceEventArgs.NewEvent.Error) `n

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

                                                         SessionLoginName : $($Event.SourceEventArgs.NewEvent.SessionLoginName) `n

                                                         SPID             : $($Event.SourceEventArgs.NewEvent.SPID) `n

                                                         SQLInstance      : $($Event.SourceEventArgs.NewEvent.SQLInstance) `n 

                                                         Message          : $($Event.SourceEventArgs.NewEvent.textdata)

                                                         StartTime           : $([Management.ManagementDateTimeConverter]::ToDateTime($Event.SourceEventArgs.NewEvent.StartTime))"

 

                                                        

                            }

Notice that now I’m specifying the event identifier (SourceIndentifier parameter)  to unregister

Now, every change in the databases will be triggered when logged in the SQL Server ERRORLOG.

But what if I want every time that my server are restarted, this event is registered :

Thers is some ways. You can do it in by SQL Server Alerts, but you will depend the SQL Agent to send an email.

You can use the awesome project from Trevor Sullivan (@pcgeek85)

PowerEvents for Windows PowerShell

What is PowerEvents?

PowerEvents is a Windows PowerShell v2.0 module designed to facilitate the ease of creating, updating, and deleting WMI (Windows Management Instrumentation) permanent event registrations. PowerEvents makes it easy to create WMI event filters (define the events you want to capture) and event consumers (responders to events), and then bind them together to initiate the flow of events. By leveraging permanent event registrations, you can perform advanced monitoring functions on a workstation or server, that would otherwise require implementation of an enterprise monitoring product. Because WMI is incredibly vast in the information it provides, very detailed monitoring can be performed using almost any of the WMI objects that exist on a computer.

What are WMI Permanent Event Registrations?

A little-known capability of the WMI service, is its capability to create a permanent registration (listener) for events, and then automatically respond to those events. At a very basic level, it’s “if X happens, do Y” but in this case, it’s all built into WMI, without the need for any additional software.

In other words, you will switch from temporary consumers to a permanent consumer.

Or you can create a .ps1 with this script, put into a .bat file and excute using :

powershell.exe -noexit -windowstyle hidden  -command "c:\temp\errorlogevent.ps1"

And use the noexit and windowstyle to hidden PowerShell.exe Console Help

Then, you can put into your server startup options.

and to unregister your event :

Unregister-Event ErrorLogevent   

where ErrorEventLog is the name that I put in parameter SourceIdentifier in the Register-WMIEvent

Watch the video

The register-wmievent run a job in another runspace, then to find bugs in the code is a bit complicated. In my SQLBITS session  I will show how to use the variable $Event and how to solve these bugs.

See you in Liverpool guys, do not miss my session, I am sure that you’ll like it

#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.

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