I've set up a service, queue, event notification and activation procedure to record various interesting events such as
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?: 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
asked Jul 26 '11 at 03:40 AM in Default
The best place to start would be the [MSDN entry for Event Notification] (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.: http://msdn.microsoft.com/en-us/library/ms182602(v=SQL.100).aspx
answered Jul 26 '11 at 04:14 AM