Support SQL Broker Service to be a target of Extended Events - by Vladimir Moldovanenko

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


69
1
Sign in
to vote
ID 782941 Comments
Status Resolved Workarounds
Type Suggestion Repros 2
Opened 4/3/2013 3:27:11 PM
Access Restriction Public

Description

Event Notifications is a wonderful system that allows event processing automation. Events are sent to SQL Broker Service, therefore queue where they can be read, logged and processed by activated procedure or external program. Unfortunately it is being deprecated in SQL 2012, with no announced replacement. 

Sign in to post a comment.
Posted by Dave Mason SQL on 1/14/2017 at 8:09 PM
Resolved as Fixed?
Ok...What else can you tell us?

Which version of SQL Server?
How's it work?
What's the TSQL syntax?
Posted by Vladimir Moldovanenko on 3/4/2016 at 11:22 AM
What version is it supported in? it would be nice to know. thanks
Posted by Vladimir Moldovanenko on 11/10/2015 at 9:25 AM
NO WAY! it's 'Active' now! WOW!
If this is not a mistake, you, Microsoft, have made my day!
I (and many others) are very pleased that you have reconsidered this issue and we look forward to delivery of this enhancement.
Posted by Cody Konior on 7/22/2015 at 11:21 PM
I love how it's closed with no comment as to why, despite no workarounds.
Posted by Martin Catherall on 3/25/2014 at 1:20 PM
While extended events are a great addition to the product, the one thing they lack is the ability to write to a service broker queue. As SQL Trace is deprecated - we need the ability to have a replacement for event notifications which is able to write to a service broker queue. As Vladimir suggests processing events such as the DEADLOCK_GRAPH and immediately sending a notification (usually via email from a service broker activation stored procedure) is extremely useful. Developers and DBAs appreciate immediate feedback that something has gone wrong.
This is not possible with extended events - although it could be mimicked by writing an Agent job that constantly polled the target. This seems untidy and there is the potentially that messages could get lost.
There are a wide range of use cases for extended events writing to a Service Broker Queue and I think the addition of such a feature would be welcomed by developers and DBA's.
I wrote a blog post about the challenges this has given here - http://www.sqlservercentral.com/blogs/martin_catherall/2014/03/19/the-sql-elephant-in-my-room/


Posted by Vladimir Moldovanenko on 4/17/2013 at 8:50 AM
To Greg Low. For me it is sad that MS can just throw away very useful functionality that users rely on and replace it with nothing. I use Event Notifications to automate processing of several events, most useful of which is DEADLOCK_GRAPH. It helped me to iron many deadlocks in my ERP system. Somehow target file on a drive and SQL Agent polling log directory looking for a file is presented as "solution" as well as would be "faster" than Service Broker queue. It will not be! SQL Server Broker is very fast; I know it as I use it a lot. And by the time a file is filled to max , then picked up and processed, deadlock situation is gone completely!

Extended Events is a good sub-system for SQL Server too, just needs a small enhancement to have it linked to Service Broker. Microsoft, how hard can this be?
Posted by Greg Low - Australia on 4/11/2013 at 5:09 PM
I raised a suggestion on this back when extended events were introduced in 2008 (https://connect.microsoft.com/SQLServer/feedback/details/611166/service-broker-target-for-audit) and it was closed as "won't fix". At the time, I was keen to have a way to easily centralize SQL Audit events, by sending them on a Service Broker queue. I still think that an SB queue would be a good target for extended events, even more so now with much more useful extended events.