Once upon a time, in a blog post far, far away, I started talking about auditing in SQL Server.  And I told you all about how to use SQL Audit to monitor what’s going on in your databases.  Remember that?  If you do, you might also recall that I mentioned there being more than one way to audit SQL Server.  Well, it’s been a while, but I’m here to pick up that thread, and the next method I want to tell you about is what I consider to be the most under-appreciated features of SQL Server: Event Notifications.

Event Notifications were first introduced in SQL 2005, and, unlike some other features I could mention (I’m looking at you SQL Audit), it’s available in all editions.  So you don’t need to shell out beaucoup bucks to audit your instances.  You just need to do a little TSQL coding (nothing too scary, I promise).  I should mention here that Event Notifications are based on the SQL Trace architecture, and if you’ve been paying attention you’ll know that SQL Trace has been deprecated.  So the future of Event Notifications is a bit cloudy at the moment.  I really hope MS finds a way to keep it, because there’s no other feature that can take its place at this time.  So if you’re listening, Microsoft bigwigs, here’s my plea:  keep Event Notifications!!! Please?

Why I like Event Notifications

Why all the fuss?  Well, Event Notifications are kind of like SQL Trace and DDL/Logon Triggers had a baby and that kid got the best part of both parents.  Like SQL Trace, Event Notifications work asynchronously, meaning outside the scope of the transaction that caused the event.  This means that the event notification’s work doesn’t use the resources that transaction was using, and more importantly, it won’t impact that transaction if something goes horribly awry (think errors, blocking, etc.).  Unfortunately, this asynchronous-ness has its price.  Because it’s working outside the scope of the transaction, an event notification can’t roll back the firing event, like a trigger could.  (So if you’re looking for something that will prevent events from happening, Event Notifications aren’t the answer.)

Like triggers, however, Event Notifications can do more than just record an event, they can respond to it.  We’ll go more into this next time when I talk about how they work, but let’s just say that, since Event Notifications work hand in hand with Service Broker, they can be used to perform actions.  What kind of actions?  They can insert event information into a table, obviously, so no more messing with multiple trace files (whoohoo!).  But they can also do things like send an email.  Want to know the moment one of your developers modifies a stored procedure?  Event Notifications can do that.

Because they use the SQL Trace architecture, Event Notifications are very low impact.  They do incur some overhead due to their use of XML, but this is minimal and shouldn’t be noticeable.

What can Event Notifications audit?

It might be easier to ask that they can’t audit.  Really, what events you can audit will vary based on the scope of the event notification.  You can define it at the SERVER or DATABASE level, and obviously certain events only make sense at a certain scope, but other events are available at both scopes.

You can query the sys.event_notification_event_types DMV to see a full list of all events and event groups, but in a nutshell, you can use Event Notifications to audit:

• all DDL events – Things like CREATE TABLE, ALTER PROCEDURE, etc. are obvious candidates, but you can also audit CREATE STATISTICS to monitor SQL Server’s creation of auto stats, or what about linked server modifications using the ALTER_LINKED_SERVER event?
• some trace events – How about monitoring when a query is missing a join predicate or missing column stats?  What about auditing data or log file auto growth?  That might be information worth knowing about.
• security events – Monitor failed logins, or all logins, as needed.
• DML events – Not too many people know this, but you can also use Event Notifications to monitor object access with the AUDIT_SCHEMA_OBJECT_ACCESS_EVENT.  Like SQL Audit, this event is monitored at the time of the permission check, so you can audit not only successful attempts at access, but unsuccessful attempts, too.  It’s worth noting that this event is only available at the SERVER scope.  Which means it will fire for every object access event in the instance.

What can’t you audit with Event Notifications?  Temporary objects.  They won’t fire for local or global temporary tables or temporary stored procedures.  So no monitoring of TempDB usage here.

So what’s next?

That’s a basic overview of Event Notifications.  In the next post, I’ll go into how they work and creating a basic event notification. Stay tuned…