Event Name

1
Examining DDL Triggers and Event Notifications in
Microsoft SQL Server 2005
Jasper SmithSQL Server MVP
2
Agenda

• How to use DDL Triggers for auditing and change
  tracking
• How to use DDL Triggers as a safety mechanism
• Introducing Event Notifications
• Scaling out Event Notifications
• Combining WMI, SQL Agent and Event Notifications

3
SQL 2000

• Instead Of Triggers
• After Triggers
• React only to DML
• INSERT , UPDATE , DELETE

4
SQL 2005

• Instead Of Triggers
• After Triggers
• DDL Triggers
• React to DDL
• CREATE , ALTER , DROP

5
DDL Trigger Syntax

• CREATE TRIGGER trigger_name
• ON ALL SERVER DATABASE
• WITH ltddl_trigger_optiongt ,n
• FOR AFTER event_type event_group
  ,...n
• AS sql_statement ...n
• EXTERNAL NAME lt method specifier gt
• ltmethod_specifiergt
• assembly_name.class_name.method_name

6
Event Groups

• DDL_DATABASE_LEVEL_EVENTS
• DDL_TABLE_VIEW_EVENTS
• DDL_TABLE_EVENTS (CREATE_TABLE,ALTER_TABLE,DR
  OP_TABLE)
• DDL_VIEW_EVENTS
• (CREATE_VIEW,ALTER_VIEW,DROP_VIEW)
• DDL_INDEX_EVENTS
• (CREATE_INDEX,ALTER_INDEX,DROP_INDEX)
• DDL_STATISTICS_EVENTS
• 
  (CREATE_STATISTICS,ALTER_STATISTICS,DROP_STATISTIC
  S)

7
eventdata() Function

• eventdata() returns a value of type xml
• The base XML schema returned by the eventdata()
  function depends on the scope and event type

TSQL CREATE TRIGGER DDL_TESTON DATABASEFOR
CREATE_TABLEASPRINT CONVERT(nvarchar(max),eventd
ata())) VB.NET Public Shared Sub DMLTrigger()
Dim sqlP As SqlPipe SqlContext.GetPipe()
sqlP.Send(SqlContext.GetTriggerContext.EventData.V
alue) End Sub
8
eventdata() output

• ltEVENT_INSTANCEgt
• ltPostTimegt2004-05-26T211036.393lt/PostTimegt
• ltSPIDgt55lt/SPIDgt
• ltEventTypegtCREATE_TABLElt/EventTypegt
• ltServerNamegtWIN2003lt/ServerNamegt
• ltLoginNamegtfoolt/LoginNamegt
• ltUserNamegtfoolt/UserNamegt
• ltDatabaseNamegttestlt/DatabaseNamegt
• ltSchemaNamegtdbolt/SchemaNamegt
• ltObjectNamegttable1lt/ObjectNamegt
• ltObjectTypegtTABLElt/ObjectTypegt
• ltTSQLCommandgt
• ltSetOptions ANSI_NULLS"ON" AN...../gt
• ltCommandTextgtcreate table foo(bar
  int)lt/CommandTextgt
• lt/TSQLCommandgt
• lt/EVENT_INSTANCEgt

9
Quick look at XQuery

• value() method of the XML data type
• select eventdata().value
• (
• '(/EVENT_INSTANCE/ObjectName)1','sysname
• )
• ltEVENT_INSTANCEgt
• ltObjectNamegttable1lt/ObjectNamegt
• lt/EVENT_INSTANCEgt

10
DDL Trigger Audit Demo
11
Rollbacks and DDL Triggers

• If a DDL action is rolled back, so is the logging
  of the event in an audit table BEGIN TRAN
• CREATE TABLE TEST(a int)
• ROLLBACK TRAN
• Within a DDL trigger you can rollback the DDL
  that caused it to fireCREATE TRIGGER
  DDLDEMO_ROLBACK ON DATABASE FOR CREATE_TABLE AS
  PRINT 'Create Table is not allowed ROLLBACK

12
DDL Trigger Rollback Demo
13
DDL Triggers Summary

• React to CREATE,ALTER,DROP
• Eventdata() function
• Used to audit DDL at database and server scopes
• Used as a control mechanism to prevent accidental
  DDL

14
Event Notifications

• Objects that sends information about a database
  or server event to a service broker service
• To create an event notification, you must
  complete the following steps
• Create a target service to receive event
  notifications.
• Create the event notification.

15
Service Broker

• Service Broker provides queuing and reliable
  messaging as part of the Database Engine
• Event Notification Service is built in to all
  databases

16
Creating Event Notifications

• Create a QUEUE
• Create a SERVICE on a QUEUE specifying a CONTRACT
• Create an EVENT NOTIFICATION to a SERVICE
• Create a SERVICE PROGRAM to process notification
  events as they arrive on the queue

17
Event Notification Syntax

• CREATE EVENT
• NOTIFICATION event_notification_name
• ON SERVER DATABASE ASSEMBLY assembly_name
  object_specifier object_name
• WITH FAN_IN notification_identifier
• FOR event_type event_group ,...n
• TO SERVICE broker_service , 'broker_instance_spe
  cifier'

18
Trace Events

• In addition to the DDL events available in DDL
  Triggers, Event Notifications also allow Trace
  events to be captured e.g.
• Audit_Login
• Audit_Login_Failed
• Lock_Deadlock
• Data_File_Auto_Grow
• Audit_DBCC_Event

19
Event Notification Demo
20
Scaling out Auditing

• Can specify a Remote Service when creating an
  Event Notification
• Need to create a ROUTE to the remote service so
  SQL Server knows where to send the message

CREATE ROUTE RemoteAuditRoute WITH SERVICE_NAME
'//AuditDemo.com/RemoteAuditService', BROKER_INSTA
NCE 'F5E4242A-D37E-4BBA-A5C5-D4ACA7EF6BB2', ADDR
ESS 'TCP//remoteserver4023'
21
WMI Integration

• WMI Provider for Server Events (SQLWEP)
• SQL 2005 is a managed WMI object
• Consume events based on an Event Notification
  Query
• WMI Query Language simplified form of SQL with
  WMI specific extensions
• Easily accessible via the System.Management
  namespace in .NET Framework

22
WMI and SQL Agent

• SQL Agent can consume WMI events
• NOT limited to SQL Events

23
WMI Demo
24
Summary

• Event Notifications are asynchronous
• Can target local or remote service
• Allow scale out of auditing
• Can respond to DDL and Trace events
• Integrate WMI and SQL Agent
• Allow custom, scalable, performant enterprise
  auditing out of the box

25
Questions ?

• Slides will be available soon on
• sqlpass.org and sqldbatips.com
• Email jas_at_sqldbatips.com

26
Thank you!

• Thank you for attending this session and the
• 2004 PASS Community Summit in Orlando!
• Please help us improve the quality of our
  conference by completing your session evaluation
  form.
• Completed evaluation forms may be given
• to the room monitor as you exit or to staff
• at the registration desk.