Create a Monitoring Server for SQL Server with PowerShell

At some point, you are going to need a notification system for a range of events that occur in your servers, even if it is only a warning of low disk space. Laerte shows how you can even set up temporary or permanent alerts for any WMI events to give you a system that fits your server environment perfectly.

Notification Systems

I’ll be showing you how to set up an effective notification system for SQL Server via scripting, using WMI and PowerShell. We will set up a Monitoring Server. It will use SQL Server’s WMI Provider for Server Events. I’ll show you how you can set up both temporary and permanent alerts in this way.

Notification systems of the type that I’ll be describing are everywhere. Your car, if it is a recent design, warns you when something is going wrong, your bank informs you when you make a transaction via SMS; your body will indicate a problem with an infection. In information systems, notifications are just like these. Notification systems save you from constantly having to check everything. They warn you when something needs attention. In the meantime, you can get on with other things.

In our world, the Database Administration world, notification systems are essential for the timely management of the systems in our care. Once we’ve implemented an effective notification system, we can stop having to rely on such repetitive polling tasks as checking whether the jobs ran, whether there is enough disc capacity or if any policy was broken; and allows us to focus our time and energy on the management of the server environment. Essentially, we are saving time in our everyday routine work

Monitoring and control

When I set up a notification system, whether I build it, or use some third-party software, I usually develop it in different ways depending on whether it just monitors my SQL Server environment, or actually controls it

“Unless you are a control freak, you’ll

use a third-party monitoring system

to do the main monitoring tasks, and

use scripting like I’ll be showing you

for those events that are unique to

your SQL Server environment.”

  1. Controlling: When I Control the quality of a product or service I am providing activities that monitor its quality by measurement, I will judge whether action needs to be taken to ensure that the requirements of the service I’m monitoring are being met, and that business objectives are being achieved
  2. Monitoring: When I Monitor SQL server, then I script the actions that need to be taken to reduce defects in the product or service and warn me when thresholds have been exceeded. I can then I can diagnose any potential problem.

If, for example, I create an alert in order to notify me if disk space is almost full, but we’ve not yet actually run out of space, I am monitoring my environment, but I take an action in consequence of an event happening, such as when SQL Server Service stops, I am controlling my environment.

Unless you are a control freak, you’ll use a third-party monitoring system to do the main monitoring tasks, and use scripting like I’ll be showing you for those events that are unique to your SQL Server environment.

Control is better served by scripting than use of a tool, because the DBA’s Server-environments are too varied to allow for generalized cures for common problems: The DBA needs to be able to exercise judgment, and a knowledge of the local circumstances and server topology, in configuring the control system and scripting appropriate fixes.

WMI Provider for Servers Events

Server Events were introduced in SQL Server 2005 to allow the administrator to use Windows Management Instrumentation (WMI) to monitor events in SQL Server. WMI has been, for ten years, the standard way of ‘Instrumenting’ MS Windows-based services. PowerShell has a very efficient and simple way of accessing WMI, and Simple-Talk has already published simple example scripts that demonstrate the power of scripting WMI via PowerShell.

Every service, such as Exchange, IIS, or SQL Server has a ‘provider’ that is accessible to scripting. There is a WMI provider, the WMI Provider for Server Events that you can use to create and manage events in instances of SQL Server. SeeUnderstanding the WMI Provider for Server Events

As with any WMI provider, you can use WQL to query these events. WQL is based on SQL, so that, for the DBA and Database developer, it is a very intuitive way of querying events. The SQL Dialect has been changed slightly to make it more effective for the process, but it is still recognizable. With PowerShell you can protect yourself from using it directly but we’d prefer to use it directly because it makes sense to those of us who are already familiar to its declarative syntax.

In the WMI namespace, each SQL Server Instance has its own namespace and the provider manages them separately, using the format:

\\.\root\Microsoft\SqlServer\ServerEvents\instance_name

This means that, that if you have two instances in the same server, The default and INST1, the format for each on will be …

\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER (Instance DEFAULT)

\\.\root\Microsoft\SqlServer\ServerEvents\INST1 (Instance INST1)

There is a whole bunch of classes that you can work with; I suggest that the best approach is to look at the WMI Server Events in BOL (Books On Line) for complete information about each individual class, their properties and their methods.

I have to confess that, when I started studying the subject of event notification in SQL Server in more detail using PowerShell and WMI Server Events, I became very excited by the potential it gave for saving me routine work, and so I started writing some posts on my blog. This article sums up my journey of exploration, which started with creating a monitoring and alerting system for Database Mirroring.

Monitoring Database Mirroring.

If you work with Database Mirroring, you’ll definitely want to know of any changes in the mirroring state, such as when a Manual Failover occurs, or when a ‘Suspended’ or ‘Lost Connection’ event happens. You’d want a notification that you could receive on your phone, or on your desktop. Here is how you can do it, using ‘temporary WMI event consumers’ which are designed to monitor events only when the ‘event consumer’ script is running.

The Database Mirroring State Change Event Class informs you of all the changes in the state of the Database Mirroring, in both the Principal and Mirroring Server.

There are several Properties that you can retrieve when the event is triggered, such as : DatabaseID , DatabaseName or Session login name . You can find these on BOL, but one of the most important is the value State. This property will give you the new state of the mirroring and 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

With just this information, we can start to play, first writing our WQL query to the principal and mirror server :

$query = “select * from DATABASE_MIRRORING_STATE_CHANGE where state = 5 or state = 6 or state = 7 or state = 8 or state =9”

As you can see, I only query the states 5,6,7,8,9 but you can monitor and query all the properties that have Filterable = Yes on the BOL page .

Now we need to register this event in both the principal server and the mirror server, using the Register-WMIEvent
function; and yes, I can do it remotely. The Register-WMIEvent function has a parameter ComputerName to which we assign the name of the Principal Server

And in the mirror

We just changed the ComputerName parameter to the Mirror Server, and changed, if it is applicable, the runspace ( -namespace parameter) to the correct SQL Server Instance

Some interested things that we need to be aware in this code :

  • $event.SourceEventArgs.NewEvent

    When an event is triggered, all the information ( properties) about this event are stored in the $event variable. Then, if I need to retrieve a property called MyProperty, it will be $event.SourceEventArgs.NewEvent .MyProperty

  • Action Parameter

    I put ipmo functions ; ` Send-SMTPmail this is because I am using a send-smtpMail function to send the email, and ipmo is a alias to the Import-Module cmdlet, then this is means that the send-smtpMail function is within the functions module. Because the Register-WMIEvent cmdlet creates a job, and every PowerShell Job runs in another runspace, you need to explicitly import the modules that you will use.

  • [Management.ManagementDateTimeConverter]::
    ToDateTime($event.SourceEventArgs.NewEvent.StartTime)

    One of the properties that I want to display in my notification email is the date and time that the event was triggered. The Native WMI Date format is rather complicated to understand, especially because WMI dates have a couple of different formats. You can imagine the complexity of your code if you have to manage both of these formats. To simplify this process, we use the .NET class Management.ManagementDateTimeConverter

Because the PowerShell -Action Job is created and run in another runspace, it is an interesting challenge to work out how to track down bugs in the action parameter code.. This means that, if your code has some bug, it will not display. You will only know what has happened when you type Get-Job to see the state of your job, and it will be either ‘Running’ or ‘Failed’ if you do it before the event is triggered.

Let’s say that we want to put a log entry in the local event viewer when the SQL Server Service stops. You could use this Action parameter.

But there is a problem here, because we do not have a Source and LogName called Poshevents and MonitorSQL in the Local Event Viewer. if you run this in the write-eventlog cmdlet’s -Action Parameter, then nothing will display. You would know if it works, of course, because a message would appear in the event viewer. You would also see if the task failed or succeeded if you typed Get-Job .

In this case, it is simple to check to see what is wrong. I can run this code outside the Register-WMIEvent cmdlet, and it will display the error

Write-EventLog : The source name “Poshevents” does not exist on computer “Vader” .

It isn’t entirely easy to find out the intermediate results of the script when it is running in a different runspace. However, you can use global variables to inspect values in the script. Imagine that you are running a function in the action parameter that has, as its parameter $event.SourceEventArgs.NewEvent . In what part of the code was the bug?

Register-WMIEvent ….

-Action {

Get-MyFunction $event .SourceEventArgs.NewEvent

}

Simple, you would just need to change the command to $global:MyEvent=$event

Register-WMIEvent ….

-Action {

$global:MyEvent = $event

}

Then the event Information will be stored at the global variable MyEvent and you can inspect the information, and also debug the function, with this variable as its parameter.

Get-MyFunction $MyEvent .SourceEventArgs.NewEvent

And you will find the bug.

You can find out more about monitoring with PowerShell and WMI on my blog, as I demonstrate various techniques with a small video and some good rock music

  1. PowerShell and SQL Server Events – Monitoring SP Recompilations
  2. PowerShell and SQL Server Events-Monitoring Schema Changed
  3. PowerShell and SQL Server Events-Monitoring DATABASE SUSPECT DATA PAGE
  4. PowerShell and SQL Server Events-Monitoring Changes in Database Mirroring
  5. PowerShell and SQL Server Events-Monitoring DataFile Grow
  6. PowerShell and SQL Server Events-BLOCKED PROCESS REPORT and SP_WHOISACTIVE
  7. PowerShell and SQL Server Events-Changes in Database Options

Creating Your Own Monitoring Server

When I started my idea, I was looking for a way to centralize all the events that I want to monitor in one server, which would then send out the email-alerts, rather than to have all the servers sending email, so I came up with the following command.

1374-img61.jpg

I will have one monitoring Server: All the monitored servers will write in the Event Viewer of this server, and when a new Event Viewer Entry (to a specified source and logname) happens, an event is triggered with the email. This way I have all messages centralized in one place and only this Server will send emails.

First let’s create the logname and source of the event log at the monitoring server (ObiWan):

New-EventLog MonitorSQL -source PoshEvents

Then we need to test to see if it works :

Write-EventLog -LogName MonitorSQL -EventId 01 -Message TestSQLServerServiceStops -Source Poshevents -EntryType Warning -Category 1

Write-EventLog -LogName MonitorSQL -EventId 02 -Message TestDatabaseMirroring -Source Poshevents -EntryType Warning -Category 1

And then we look in the event log to see if the entries are properly logged.

1374-img64.jpg

..and having checked that it is working, we clear the event log

Clear-EventLog MonitorSQL

I chose the a value of 2 for the event id in the log for all our Database Mirroring event monitoring entries, so I can then display just these entries by filtering on the event_ID of 2 in the event viewer.

Then, let´s register the events. You will see in the code that I am registering the event in the three servers, ObiWan, Chewie and Yoda, that are involved in the mirroring, and which I want to monitor: The Monitor is ObiWan (The ‘Monitoring Server’ on the left, in the diagram), the principal being Chewie (Server 1 in the diagram) and the mirror being Yoda (Server2 in the diagram). The write-event log in all the servers is switched to my Monitoring Server as you will notice when you look at the ComputerName parameter for the write-eventlog cmdlet on Chewie.

Now we need to register the event that will be triggered to each entry in the Monitoring Server Event Viewer (ObiWan). But filtering for LogFile to “MonitorSQL” only, I do not want all entry’s triggering the event.

Then all the events from the monitored servers will be stored in ObiWan’s Event Viewer and it will send the emails.

I did a video to show this implementation. In this video I am just using 2 servers and one is The monitor (obiwan) but the idea is the same:

  1. Creating Your Own Monitoring Server with PowerShell and WMI/Server Events

Now comes the big question. If I restart the monitoring server, what will happen?

All of these examples so far are using temporary WMI event consumers, which are volatile. In other words, you will need to have a PowerShell Windows open and, if the server running the script is restarted, then all events are lost. This may be something you’d want to avoid in a production system.

You have two alternative solutions to get round this difficulty:

  1. Create a .ps1 with the cmdlets to register the events and a command batch script in each server that will be monitored and put into your startup menu with this command in the .bat file.:

    powershell.exe -noexit -windowstyle hidden -command “c:\temp\MyEvents.ps1”

    The NoExit will be holding the session and windowstyle. Hidden will hide the PowerShell Session from the user, so it is unlikely to be terminated by accident..

  2. you can use Permanent Event Consumers: This is what we’ll describe next.

Keeping my Events alive

A permanent event consumer* is a COM object that can receive a WMI event at all times. A permanent event consumer uses a set of persistent objects and filters to capture a WMI event. In the same way as with a temporary event consumer, you set up a series of WMI objects and filters that capture a WMI event. When an event occurs that matches a filter, WMI loads the permanent event consumer and notifies it about the event. Because a permanent consumer is implemented in the WMI repository and is an executable file that is registered in WMI, the permanent event consumer operates and receives events after it is created and even after a reboot of the operating system as long as WMI is running.

* Permanent Event Consumers

The permanent event consumer have this preinstalled classes :

Class

Description

ActiveScriptEventConsumer

Executes a predefined script in an arbitrary scripting language when an event is delivered to it. This consumer is available on Windows XP and Windows 2000.

Example: Running a Script Based on an Event

CommandLineEventConsumer

Launches an arbitrary process in the local system context when an event is delivered to it. This consumer is available on Windows XP.

Example: Running a Program from the Command Line Based on an Event

LogFileEventConsumer

Writes customized strings to a text log file when events are delivered to it. This consumer is available on Windows XP.

Example: Writing to a Log File Based on an Event

NTEventLogEventConsumer

Logs a specific message to the Windows NT event log when an event is delivered to it. This consumer is available on Windows XP.

Example: Logging to NT Event Log Based on an Event

ScriptingStandardConsumerSetting

Provides registration data common to all instances of the ActiveScriptEventConsumer class.

SMTPEventConsumer

Sends an email message using SMTP each time an event is delivered to it. This consumer is available on Windows XP and Windows 2000.

Example: Sending Email Based on an Event

* Standard Consumer Classes

In our case, we will be interested in SMTPEventConsumer and NTEventLogEventConsumer

Well, as we do not want to reinvent the wheel, we will use PowerEvents Module. PowerEvents is a PowerShell module created by Trevor Sullivan (blog | twitter) in order to facilitate this process of setting up permanent Event Consumers.

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

PowerEvents has the following Advanced Functions to support creation of permanent event consumers.

  • Get-WmiEventConsumer
  • Get-WmiEventFilter
  • New-WmiEventConsumer
  • New-WmiEventFilter
  • New-WmiFilterToConsumerBinding

To create a permanent event consumer with PowerEvents model , you need to :

  • Create WMI event filter using WQL.
  • Create an event consumer (response to the event occurrence).
  • Create a WMI binding between the event filter and the event consumer

1374-img67.jpg

I will cover each step with my examples, but you can find it detailed the references at the end of the article (Monitor and Respond to Windows Power Events with PowerShell)

Sending Email directly from the Monitored Server

Unlike the above model that we have made using temporary event Consumers, if we are using permanent event consumers, it is the server that is being monitored that sends the mail itself.

First let’s create a filter, and we will use the ‘stop the SQL Server service’ event.

Now, we have to create the consumer service. In the list above is the SMTPEventConsumer class which allows us to send an email message using SMTP. You must follow some rules to work with this :

  • An SMTP server must exist on the network.
  • The email message cannot have an attachment.
  • The email message must be encoded in US-ASCII.

Now, we just need to bind the Filter and the Consumer :

New-WmiFilterToConsumerBinding -Filter $Filter -Consumer $Consumer -Verbose

To test this out, just stop the SQL Server Service and you will see the magic.

Because these events are permanent ones, they have to be dropped explicitly now, In order to do this, you can use a tool that comes with the PowerEvents , called WmiEventHelper.exe .

1374-img6C.jpg

Conclusions

These event monitoring techniques are no substitute for a third-party monitoring tool, but it is a good supplement for those more specialized events that are required for the less common server configurations that require alerts.

That is it Guys! I hope you liked it .

References :

  1. Receiving a WMI Event
  2. Understanding the WMI Provider for Server Events
  3. Ravikanth Chaganti´s Blog
  4. Monitor and Respond to Windows Power Events with PowerShell
  5. Trevor Sullivan´s Blog
  6. SQL Server Alerts: Soup to Nuts
Tags: , , , ,

  • 31772 views

  • Rate
    [Total: 0    Average: 0/5]