SQL - Event Notifications

This is also a very beautiful feature of SQL Server which was introduced with the release of SQL Server 2005. Event Notifications mechanism is used for tracking all events or changes happening at the database or instance level.

Event Notifications convey the very same data as DDL triggers and occur on the very same events, but they are asynchronous and loosely coupled as SQL Trace.

Event Notifications work asynchronously like as SQL Trace does outside the scope of the transaction that caused the event and never used the transaction resources. The Most important this is that it won’t impact those transactions if something goes horribly awry (think errors, blocking, etc.). The event notification can’t be rolled back if the firing transaction rolls back due to working outside the scope of the transaction.

Event Notifications can do more than just record an event, they can respond to it like as DDL Triggers do and they can insert event information into a table. They can be used inside a database application to respond to events without using any resources defined by the immediate transaction.

Event data can be used by applications that are running together with SQL Server to track progress and make decisions. Event notifications can be used to do the following:

• Log and review changes or activity occurring on the database.
• Perform an action in response to an event in an asynchronous instead of synchronous manner.

In simple words, we can easily refer Event notifications as they can offer a programming alternative to DDL triggers and SQL Trace.

When an event notification is created, one or more Service Broker conversations between an instance of SQL Server and the target service you specify are started and these conversations typically remain open as long as the event notification exists as an object on the server instance. But in some error cases the conversations can close before the event notification is dropped. These conversations are never shared between event notifications that mean every event notification will have its own exclusive conversations. Ending a conversation explicitly prevents the target service from receiving more messages, and the conversation will not reopen the next time the event notification fires. Event information is delivered to the Service Broker as a variable of type xml that provides information about when an event occurs, about the database object affected, the Transact-SQL batch statement involved, and other information. Event data can be used by applications that are running together with SQL Server to track progress and make decisions.

Permissions for Event Notifications

Based on the requirements for the various targets/objects (Databases, servers etc.), they need to specific permissions on these targets/objects-

1. To create an event notification which is scoped to the database (ON DATABASE), requires CREATE DATABASE DDL EVENT NOTIFICATION permission in the current database.
2. To create an event notification on a DDL statement that is scoped to the server (ON SERVER), requires CREATE DDL EVENT NOTIFICATION permission in the server.
3. To create an event notification on a trace event, requires CREATE TRACE EVENT NOTIFICATION permission in the server.
4. To create an event notification that is scoped to a queue, requires ALTER permission on the queue.

How to create an Event Notification in a database

This will be notified you whenever a new table is created or altered in your database.

Use AdventureWorks2012 Go -- Create Event Notification which will be fired -- Whenever a new table is created CREATE EVENT NOTIFICATION CreateTableNotification ON DataBase FOR CREATE_TABLE TO SERVICE 'EventNotificationService', 'Current Database'; -- Create Event Notification which will be fired -- Whenever an existing table is altered CREATE EVENT NOTIFICATION AlteredTableNotification ON DataBase FOR ALTER_TABLE TO SERVICE 'EventNotificationService', 'Current Database'; -- check event nofification SELECT * FROM sys.event_notifications WHERE name IN ('CreateTableNotification', 'AlteredTableNotification')

Event notifications can be created directly in the SQL Server Database Engine or by using the WMI Provider for Server Events. You can also create a notification event group; for example you can create a single notification with DDL_TABLE_EVENTS (CREATE_TABLE, ALTER_TABLE and DROP_TABLE) to track all events of a table like creation, alteration and drop.   

Use AdventureWorks2012 Go -- Create Event Notification which will be fired -- Whenever a new table is created, altered table or dropped table CREATE EVENT NOTIFICATION DDLTableEventNotification ON DataBase FOR DDL_TABLE_EVENTS TO SERVICE 'EventNotificationService', 'Current Database'; -- Create table to store table monitoring CREATE TABLE TableMonitoring ( PostTime DATETIME, LoginName NVARCHAR(100), EventType NVARCHAR(100), TSQLCommand NVARCHAR(2000) NULL , ObjectName NVARCHAR(100) NULL, ObjectType NVARCHAR(100) NULL, SchemaName NVARCHAR(100) NULL, ) GO --Create the DDL trigger CREATE TRIGGER TrTableMonitoring ON DATABASE FOR DDL_TABLE_EVENTS AS DECLARE @Data XML SET @Data = EventData() -- Insert Log Values INSERT TableMonitoring ( PostTime, LoginName, EventType, TSQLCommand, ObjectType,ObjectName,SchemaName ) VALUES (GETDATE(), CONVERT(NVARCHAR(100), CURRENT_USER),   @Data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),  @Data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'), @Data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)'),  @Data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)'), @Data.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') ) ; GO

Server Scoped Event Notification

The following example creates the required objects to set up a target service using Service Broker. The target service references the message type and contract of the initiating service specifically for event notifications. Then an event notification is created on that target service that sends a notification whenever an Object_Created trace event happens on the instance of SQL Server.

Use AdventureWorks2012 Go --Create a queue to receive messages. CREATE QUEUE NotifyQueue ; GO --Create a service on the queue that references --the event notifications contract. CREATE SERVICE NotifyService ON QUEUE NotifyQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]); GO --Create a route on the service to define the address --to which Service Broker sends messages for the service. CREATE ROUTE NotifyRoute WITH SERVICE_NAME = 'NotifyService', ADDRESS = 'LOCAL'; GO --Create the event notification. CREATE EVENT NOTIFICATION Logs_DDL ON SERVER FOR Object_Created TO SERVICE 'NotifyService', '8140a771-3c4b-4479-8ac0-81008ab17984';

Event Notifications vs. Triggers :

The following table compares and contrasts triggers and event notifications.

Triggers	Event Notifications
DML triggers respond to data manipulation language (DML) events. DDL triggers respond to data definition language (DDL) events.	Event notifications respond to DDL events and a subset of SQL trace events.
Triggers can run T-SQL or common language runtime (CLR) managed code.	Event notifications do not run code. Instead, they send xml messages to a Service Broker service.
Triggers are processed synchronously, within the scope of the transactions that cause them to fire.	Event notifications may be processed asynchronously and do not run in the scope of the transactions that cause them to fire.
The consumer of a trigger is tightly coupled with the event that causes it to fire.	The consumer of an event notification is decoupled from the event that causes it to fire.
Triggers must be processed on the local server.	Event notifications can be processed on a remote server.
Triggers can be rolled back.	Event notifications cannot be rolled back.
DML trigger names are schema-scoped. DDL trigger names are database-scoped or server-scoped.	Event notification names are scoped by the server or database. Event notifications on a QUEUE_ACTIVATION event are scoped to a specific queue.
DML triggers are owned by the same owner as the tables on which they are applied.	The owner of an event notification on a queue may have a different owner than the object on which it is applied.
Triggers support the EXECUTE AS clause.	Event notifications do not support the EXECUTE AS clause.
DDL trigger event information can be captured using the EVENTDATA function, which returns an xml data type.	Event notifications send xml event information to a Service Broker service. The information is formatted to the same schema as that of the EVENTDATA function.
Metadata about triggers is found in the sys.triggers and sys.server_triggers catalog views.	Metadata about event notifications is found in the sys.event_notifications and sys.server_event_notifications catalog views.

Event Notifications vs. SQL Trace

The following table compares and contrasts using event notifications and SQL Trace for monitoring server events.

SQL Trace	Event Notifications
SQL Trace generates no performance overhead associated with transactions. Packaging of data is efficient.	There is performance overhead associated with creating the XML-formatted event data and sending the event notification.
SQL Trace can monitor any trace event class.	Event notifications can monitor a subset of trace event classes and also all data definition language (DDL) events.
You can customize which data columns to generate in a trace event.	The schema of the XML-formatted event data returned by event notifications is fixed.
Trace events generated by DDL are always generated, regardless of whether the DDL statement is rolled back.	Event notifications do not fire if the event in the corresponding DDL statement is rolled back.
Managing the intermediate flow of trace event data involves populating and managing trace files or trace tables.	Intermediate management of event notification data is accomplished automatically through Service Broker queues.
Traces must be restarted every time the server restarts.	After being registered, event notifications persist across server cycles and are transacted.
After being initiated, the firing of traces cannot be controlled. Stop times and filter times can be used to specify when they initiate. Traces are accessed by polling the corresponding trace file.	Event notifications can be controlled by using the WAITFOR statement against the queue that receives the message generated by the event notification. They can be accessed by polling the queue.
ALTER TRACE is the least permission that is required to create a trace. Permission is also required to create a trace file on the corresponding computer.	Least permission depends on the type of event notification being created. RECEIVE permission is also needed on the corresponding queue.
Traces can be received remotely.	Event notifications can be received remotely.
Trace events are implemented by using system stored procedures.	Event notifications are implemented by using a combination of Database Engine and Service Broker Transact-SQL statements.
Trace event data can be accessed programmatically by querying the corresponding trace table, parsing the trace file, or using the SQL Server Management Objects (SMO) TraceReader Class.	Event data is accessed programmatically by issuing XQuery against the XML-formatted event data, or by using the SMO Event classes.

Summary

Event Notifications are asynchronous and they can target local or remote services. They allow scale out of auditing and can easily respond to DDL and Trace events. They are easily integrated WMI and SQL Agent. They allow custom, scalable, performance enterprise auditing out of the box.

References:

https://msdn.microsoft.com/en-us/library/ms186376.aspx
https://msdn.microsoft.com/en-us/library/ms189453(v=sql.110).aspx