Reacting to XEvents in almost real-time

I've heard it said that the main difference between SQL Server Extended Events and SQL Server Event Notifications is that you can't "react to" Extended Events. Event Notifications are written to the service broker service of your choice, and multiple Event Notifications can even be routed to the same service, even on different machines.

But there IS a way to react to Extended Events in "almost realtime", that is, by capturing and reacting using the Extended Event XeReader API. It's certainly not as nice and built-in as Event Notifications, but never say never.

Mike Wachal introduced the XeReader to the world in a blog post at SQL Server 2012 CTP3. There are a variety of constructors for QueryableXEventData, you're interested in the one that uses a connection string and the name of an Extended Event session, which must be predefined (from Mike's blog):

QueryableXEventData stream = new QueryableXEventData(
  @"Data Source = (local); Initial Catalog = master; Integrated Security = SSPI",
  "alert_me",      EventStreamSourceOptions.EventStream,      EventStreamCacheOptions.DoNotCache);

In this case the "alert_me" is the name of an existing XEvent session. Now that you're capturing the events, you can take whatever kind of action you want. See the sample in Mike's blog for where you'd hook into this. You can even write the events to a service broker service, if you'd like.

It's still not quite as good, to summarize:

Event Notifications are defined using DDL and the writing the broker is built-in. This method requires:
1. Custom .NET programming
2. An external program that must be running with 2 SQL connections (one to read the events, one to accomplish the actions)
3. Custom code to write the "react to" actions (although this could be templated and parameterized in the code)

In addition, there's a WMI event provider for "Event Notification"-type trace events that can be easily hooked into SQL Server Agent. You'd also have to write an XeReader equivalent for XEvents. But until there's something built-in for XEvents, you have a "next best thing" alternative for now. Use the code in Mike's blog as a starting point

Bob

@bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.