{"id":462,"date":"2012-11-29T14:48:00","date_gmt":"2012-11-29T14:48:00","guid":{"rendered":"\/blogs\/bobb\/post\/Reacting-to-XEvents-in-almost-real-time.aspx"},"modified":"2012-11-29T14:48:00","modified_gmt":"2012-11-29T14:48:00","slug":"reacting-to-xevents-in-almost-real-time","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/reacting-to-xevents-in-almost-real-time\/","title":{"rendered":"Reacting to XEvents in almost real-time"},"content":{"rendered":"<p>\nI&#39;ve heard it said that the main difference between SQL Server Extended Events and SQL Server Event Notifications is that you can&#39;t &quot;react to&quot; 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.\n<\/p>\n<p>\nBut there IS a way to react to Extended Events in &quot;almost realtime&quot;, that is, by capturing and reacting using the Extended Event XeReader API. It&#39;s certainly not as nice and built-in as Event Notifications, but never say never.\n<\/p>\n<p>\nMike Wachal introduced the XeReader to the world <a href=\"http:\/\/blogs.msdn.com\/b\/extended_events\/archive\/2011\/07\/20\/introducing-the-extended-events-reader.aspx\" class=\"broken_link\">in a blog post<\/a> at SQL Server 2012 CTP3. There are a variety of constructors for QueryableXEventData, you&#39;re interested in the one that uses a connection string and the name of an Extended Event session, which must be predefined (from Mike&#39;s blog):\n<\/p>\n<p>\nQueryableXEventData stream = new QueryableXEventData(<br \/>\n&nbsp; @&quot;Data Source = (local); Initial Catalog = master; Integrated Security = SSPI&quot;, <br \/>\n&nbsp; &quot;alert_me&quot;,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EventStreamSourceOptions.EventStream,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EventStreamCacheOptions.DoNotCache);\n<\/p>\n<p>\nIn this case the &quot;alert_me&quot; is the name of an existing XEvent session. Now that you&#39;re capturing the events, you can take whatever kind of action you want. See the sample in Mike&#39;s blog for where you&#39;d hook into this. You can even write the events to a service broker service, if you&#39;d like.\n<\/p>\n<p>\nIt&#39;s still not quite as good, to summarize:\n<\/p>\n<p>\nEvent Notifications are defined using DDL and the writing the broker is built-in. This method requires:<br \/>\n1. Custom .NET programming<br \/>\n2. An external program that must be running with 2 SQL connections (one to read the events, one to accomplish the actions)<br \/>\n3. Custom code to write the &quot;react to&quot; actions (although this could be templated and parameterized in the code)\n<\/p>\n<p>\nIn addition, there&#39;s a WMI event provider for &quot;Event Notification&quot;-type trace events that can be easily hooked into SQL Server Agent. You&#39;d also have to write an XeReader equivalent for XEvents. But until there&#39;s something built-in for XEvents, you have a &quot;next best thing&quot; alternative for now. Use the code in Mike&#39;s blog as a starting point\n<\/p>\n<p>\nBob\n<\/p>\n<p>\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#39;ve heard it said that the main difference between SQL Server Extended Events and SQL Server Event Notifications is that you can&#39;t &quot;react to&quot; 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 [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15,31],"tags":[],"class_list":["post-462","post","type-post","status-publish","format-standard","hentry","category-extended-events","category-sql-server-2012"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Reacting to XEvents in almost real-time - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/reacting-to-xevents-in-almost-real-time\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Reacting to XEvents in almost real-time - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"I&#039;ve heard it said that the main difference between SQL Server Extended Events and SQL Server Event Notifications is that you can&#039;t &quot;react to&quot; 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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/reacting-to-xevents-in-almost-real-time\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2012-11-29T14:48:00+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/reacting-to-xevents-in-almost-real-time\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/reacting-to-xevents-in-almost-real-time\/\",\"name\":\"Reacting to XEvents in almost real-time - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2012-11-29T14:48:00+00:00\",\"dateModified\":\"2012-11-29T14:48:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/reacting-to-xevents-in-almost-real-time\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/reacting-to-xevents-in-almost-real-time\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/reacting-to-xevents-in-almost-real-time\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Extended Events\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/extended-events\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Reacting to XEvents in almost real-time\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Reacting to XEvents in almost real-time - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/reacting-to-xevents-in-almost-real-time\/","og_locale":"en_US","og_type":"article","og_title":"Reacting to XEvents in almost real-time - Bob Beauchemin","og_description":"I&#39;ve heard it said that the main difference between SQL Server Extended Events and SQL Server Event Notifications is that you can&#39;t &quot;react to&quot; 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 [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/reacting-to-xevents-in-almost-real-time\/","og_site_name":"Bob Beauchemin","article_published_time":"2012-11-29T14:48:00+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/reacting-to-xevents-in-almost-real-time\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/reacting-to-xevents-in-almost-real-time\/","name":"Reacting to XEvents in almost real-time - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2012-11-29T14:48:00+00:00","dateModified":"2012-11-29T14:48:00+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/reacting-to-xevents-in-almost-real-time\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/reacting-to-xevents-in-almost-real-time\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/reacting-to-xevents-in-almost-real-time\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Extended Events","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/extended-events\/"},{"@type":"ListItem","position":3,"name":"Reacting to XEvents in almost real-time"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/462","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=462"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/462\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=462"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=462"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=462"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}