Event Notifications - another curve

Another learning curve, after getting 98% there with Extended Events (see previous post).  For many things XE is a better tool, but for my purposes (DDL tracking over time) EN is better (again, thanks Jonathan for a great post on it: http://sqlskills.com/blogs/jonathan/post/Event-Notifications-vs-Extended-Events.aspx).  


Note that EN is NOT the same as SQL Server Notification Services, which has been dropped by MS and was for Reporting Services.

Event Notifications started with 2005, and are woefully underutilized.
Here's my annotated basic attempt at tracking all DDL changes on a server.  Next up (probably next post) is creating a routable SB to a secondary server, which would have an activated SP which would write records to a table.  As is, this is a proof of concept that writes to a queue. DO NOT JUST LEAVE IT RUNNING, as it'll fill up your queue, grow your database and you'll never figure out where the space is (since queues are sorta-hidden when it comes to space used)

TL;DR: Event Notifications tracks events, and routes them through service broker. This gives power and danger.

Code cribbed from both Jonathan Kehayias (http://www.sqlservercentral.com/articles/Event+Notifications/68831/) and Arshad Ali (http://www.mssqltips.com/sqlservertip/2121/event-notifications-in-sql-server-for-tracking-changes/).


ALTER DATABASE db_stuff SET ENABLE_BROKER;

go
USE db_stuff
go
CREATE QUEUE EventNotificationQueue   -- the reason you frequently see \\servername\queuename is because it needs to be unique
GO
create SERVICE EventNotificationService ON QUEUE EventNotificationQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
use db_stuff
go
CREATE EVENT NOTIFICATION CaptureErrorLogEvents
 ON SERVER
 WITH FAN_IN
 FOR DDL_EVENTS, ALTER_SERVER_CONFIGURATION
 TO SERVICE 'EventNotificationService', 'current database';
GO
create table blah (id int identity)
go
drop table blah
go
SELECT * FROM db_stuff..EventNotificationQueue

SELECT CAST(message_body AS XML) AS message_body_xml
FROM db_stuff.dbo.EventNotificationQueue

with MESSAGE_DATA as (SELECT queuing_order, CAST(message_body AS XML) AS message_body_xml
FROM db_stuff.dbo.EventNotificationQueue
)
SELECT
 message_body_xml.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)' ) as EventType,
 message_body_xml.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(128)') AS PostTime,
 message_body_xml.value('(/EVENT_INSTANCE/SPID)[1]', 'varchar(128)') AS SPID,
 message_body_xml.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(128)' ) AS LoginName,
 message_body_xml.value('(/EVENT_INSTANCE/UserName)[1]', 'varchar(128)' ) AS UserName,
 message_body_xml.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(128)' ) AS ServerName,
 message_body_xml.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)' ) AS DatabaseName,
 message_body_xml.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(128)' ) AS SchemaName,
 message_body_xml.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(128)' ) AS ObjectName,
 message_body_xml.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(128)' ) AS ObjectType,
 message_body_xml.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'varchar(max)' ) AS CommandText,
 *
from message_data order by queuing_order
go

DROP EVENT NOTIFICATION CaptureErrorLogEvents on server
go
DROP SERVICE EventNotificationService
GO
DROP QUEUE EventNotificationQueue

go

Annotated:

ALTER DATABASE db_stuff SET ENABLE_BROKER;

turn on service broker on the database.  It's on by default in MSDB, but let's not play god in system databases today.

USE db_stuff
CREATE QUEUE EventNotificationQueue GO
create the service broker queue in "db_stuff".  EN uses service broker.

create SERVICE EventNotificationService ON QUEUE EventNotificationQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
The notification sends to the service which sends to the queue. It needs a schema, MS has provided that (the http).

CREATE EVENT NOTIFICATION CaptureErrorLogEvents
 ON SERVER
can be server-wide, database-wide, etc.  I'm tracking all DDL changes on the server, so server it is.

 WITH FAN_IN
prevents multiple inserts from the same event into the same queue.

 FOR DDL_EVENTS, ALTER_SERVER_CONFIGURATION
the things I'm actually tracking.  you could do them individually, but there's over 1000, so I'm using "event groups". Here, I'm looking for server-level config changes, and DDL events (this includes UPDATE STATISTICS, which means it can fire a LOT)

 TO SERVICE 'EventNotificationService', 'current database';
send it to the service,  And yes "current database" is the proper format.  Do Not Like that name.

SELECT * FROM db_stuff..EventNotificationQueue
As of the CREATE EVENT NOTIFICATION, the queue is on and catching data.  Let's see what it's caught.  Not really legible, is it?  Let's change that.

SELECT CAST(message_body AS XML) AS message_body_xml
FROM db_stuff.dbo.EventNotificationQueue



So at this point, we'll use a CTE (or you could use a derived table; whatever) to provide a useful query that pulls out most of the provided fields.
with MESSAGE_DATA as (SELECT queuing_order, CAST(message_body AS XML) AS message_body_xml
FROM db_stuff.dbo.EventNotificationQueue
)
SELECT
 message_body_xml.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)' ) as EventType,
 message_body_xml.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(128)') AS PostTime,
 message_body_xml.value('(/EVENT_INSTANCE/SPID)[1]', 'varchar(128)') AS SPID,
 message_body_xml.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(128)' ) AS LoginName,
 message_body_xml.value('(/EVENT_INSTANCE/UserName)[1]', 'varchar(128)' ) AS UserName,
 message_body_xml.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(128)' ) AS ServerName,
 message_body_xml.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)' ) AS DatabaseName,
 message_body_xml.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(128)' ) AS SchemaName,
 message_body_xml.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(128)' ) AS ObjectName,
 message_body_xml.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(128)' ) AS ObjectType,
 message_body_xml.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'varchar(max)' ) AS CommandText,
 *
from message_data order by queuing_order



And finally, a list of all the events, courtesy BOL.

WITH DirectReports(name, parent_type, type, level, sort) AS
(
    SELECT CONVERT(varchar(255),type_name), parent_type, type, 1, CONVERT(varchar(255),type_name)
    FROM sys.trigger_event_types
    WHERE parent_type IS NULL
    UNION ALL
    SELECT  CONVERT(varchar(255), REPLICATE ('|   ' , level) + e.type_name),
        e.parent_type, e.type, level + 1,
    CONVERT (varchar(255), RTRIM(sort) + '|   ' + e.type_name)
    FROM sys.trigger_event_types AS e
        INNER JOIN DirectReports AS d
        ON e.parent_type = d.type
)
SELECT parent_type, type, name
FROM DirectReports
ORDER BY sort;