x

Event Notifications architecture?

I've set up a service, queue, event notification and activation procedure to record various interesting events such as DATA_FILE_AUTO_GROW and LOG_FILE_AUTO_GROW based largely around the code [in this article][1].

It appears to work fine but could someone please explain the architecture to me? How does the event get fired? Is it synchronous or asynchronous for example? If asynchronous what latency should I expect and what happens if multiple events fire almost simultaneously?

[1]: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/04/06/event-notifications-monitoring-blocked-processes-and-other-events-end-to-end-how-to-set-it-up-and-make-it-work.aspx
more ▼

asked Jul 26, 2011 at 03:40 AM in Default

Martin 1 gravatar image

Martin 1
203 8 9 11

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

The best place to start would be the [MSDN entry for Event Notification][1] (change the version of SQL Server as necessary).

Event notification takes place outside the scope of transactions and runs asynchronously. It allows you to do all sorts of things when events occur and is built upon the very robust Service Broker architecture. As far as I am aware the service itself will be perfectly happy to process multiple events firing, you just need to be aware of the overhead of processing those events (similar to how running heavy-handed traces on a production system can cause problems).

You setup a notification and this monitors for the events it should register. When an event happens, you then get a load of XML data with information on that event, which you can then process using Service Broker (to then send an email, audit the data to a table etc.).

I have only touched on it a little on my test-bed, so don't have a great deal more to say on system load/ease of use, but it seems similar to tracing but with the extra of Service Broker being bolted on.

[1]: http://msdn.microsoft.com/en-us/library/ms182602(v=SQL.100).aspx
more ▼

answered Jul 26, 2011 at 04:14 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

+1 @WilliamD, the Service Broker and Event Notification is a great feature of SQL Server when it is used in right way.
Jul 26, 2011 at 05:52 AM Pavel Pawlowski
+1 Thanks, That is useful, though it doesn't cover issues such as the greatest possible latency that might ensue between an event occurring and my activation procedure being fired. It would be interesting to find a description of exactly what happens between an event occurring and the procedure being fired if anyone has seen such a thing anywhere...
Jul 26, 2011 at 06:35 AM Martin 1
@Martin - good luck on that front! :) If you find out, let us all know. I think it will be in the region of milliseconds, as it is that deep down. Maybe if you manage to talk to someone from the SQL Engine team. Have you tried asking this on twitter with the hashtag #sqlhelp - there are so many people on there, there is bound to be someone who knows, or knows someone who knows (ad nauseum!)
Jul 26, 2011 at 07:11 AM WilliamD

In my previous work we had and application build which has notification base on the Service Broker. It was for treasury transactions requests processing. There were multiple clients running. When the client changed state of the transaction and moved it to other state in which it should be processed by other person, it generated an event which was consumed by a service which then took case about notifying appropriate persons the they have a new transaction for processing. The latency for the notification is minimal and notification is fully asynchronous.

Wen the data from the queue are not processed, they are en-queued in right order and then once the consumer of the queue connects the messages are de-queued.
Jul 26, 2011 at 07:34 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x14
x6

asked: Jul 26, 2011 at 03:40 AM

Seen: 1370 times

Last Updated: Jul 26, 2011 at 03:40 AM