1. Auditing Events in SQL Server 2005 Jasper Smith SQL Server MVP
2. Agenda Trace Enhancements
DDL Triggers
Event Notifications
3. Trace Enhancements
4. Default Trace On by default (controlled by sp_configure 'default trace enabled)
Stored in the same folder as the SQL Server Error Log
Location can be changed by modifying the e start up parameter using the SQL Server Configuration Manager
Captures mainly audit type trace events
5. Reading a running trace The system function fn_trace_gettable has been enhanced to allow the reading of running server side traces e.g.
select * from :: fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL.1\ MSSQL\LOG\log_15.trc',-1)
order by StartTime desc
6. SMO Trace API In the Microsoft.SqlServer.Management.Trace namespace
Can read and write Trace Files and Trace Tables
Trace sources include SQL Server, Analysis Services and SSIS log tables.
Can be used to manipulate trace data programmatically
7. Trace Demo
8. DDL Triggers
9. SQL 2005 Trigger Types Instead Of Triggers (DML)
After Triggers (DML)
DDL Triggers
React to DDL
CREATE , ALTER , DROP
10. DDL Trigger Syntax CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option>]
{ FOR | AFTER } { event_type | event_group }
AS { sql_statement [ ...n ]
| EXTERNAL NAME < method specifier > }
11. DDL Trigger Scope DATABASE
Applies the scope of a DDL trigger to the current database. If specified, the trigger fires whenever event_type or event_group happens in the current database
ALL SERVER
Applies the scope of a DDL trigger to the current server. If specified, the trigger fires whenever the server level event_type or event_group happens anywhere in the current server
12. Event Groups DDL_DATABASE_LEVEL_EVENTS
DDL_TABLE_VIEW_EVENTS
DDL_TABLE_EVENTS (CREATE_TABLE,ALTER_TABLE,DROP_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_STATISTICS)
13. 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
14. eventdata() output <EVENT_INSTANCE>
<PostTime>2004-05-26T21:10:36.393</PostTime>
<SPID>55</SPID>
<EventType>CREATE_TABLE</EventType>
<ServerName>WIN2003</ServerName>
<LoginName>foo</LoginName>
<UserName>foo</UserName>
<DatabaseName>test</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>table1</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" AN...../>
<CommandText>create table foo(bar int)</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
15. 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 fire CREATE TRIGGER DDLDEMO_ROLBACK ON DATABASE FOR CREATE_TABLE AS PRINT 'Create Table is not allowed ROLLBACK
16. DDL Trigger Demo
17. Event Notifications
18. Event Notifications Objects that sends messages 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.
19. Service Broker Service Broker provides queuing and reliable messaging as part of the Database Engine
Event Notification Service is built in to all databases
20. Event Notification Syntax CREATE EVENT NOTIFICATION name
ON { SERVER | DATABASE | QUEUE }
[ WITH FAN_IN ]
FOR { event_type | event_group } [ ,...n ]
TO SERVICE broker_service
{ 'broker_instance_specifier' | 'current database' }
21. Trace Events In addition to the DDL events available in DDL Triggers, Event Notifications also allow a subset of Trace events to be captured
Audit_Login
Audit_Login_Failed
Lock_Deadlock
Data_File_Auto_Grow
Blocked_Process_Report
22. Creating Event Notifications Create a QUEUE
Create a SERVICE on a QUEUE
Create a ROUTE for the SERVICE
Create an EVENT NOTIFICATION to a SERVICE
Create a SERVICE PROGRAM to process notification events in the QUEUE
23. Event Notifications Demo
24. 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 (WQL) simplified form of SQL with WMI specific extensions
Easily accessible via the System.Management namespace in the .NET Framework
25. WQL Examples SELECT * FROM DDL_DATABASE_LEVEL_EVENTS
WHERE DatabaseName = 'AdventureWorks
SELECT * FROM ALTER_TABLE
WHERE DatabaseName = "AdventureWorks"
AND SchemaName = "Sales"
AND ObjectType="Table"
AND ObjectName = "SalesOrderDetail"
SELECT * FROM DEADLOCK_GRAPH
26. SQL Agent alerts can react to WMI events
NOT limited to SQL Server Events
27. WMI Events Demo
28. Server Trace Summary Lowest overhead
If using standard trace files there can be an increased administration overhead
Need to recreate trace after server restart
Limited notification ability
29. DDL Triggers Summary DDL Triggers are synchronous
DDL Triggers are tightly coupled to the event that caused them to fire
DDL Triggers can respond only to DDL events
DDL Triggers can issue a rollback
30. Event Notifications Summary Event Notifications are asynchronous
Target local or remote service
React to DDL and subset of Trace events
Integrate with WMI and SQL Agent alerts
Some additional processing overhead
31. Questions ? Slides will be available soon on
sqlpass.org and sqldbatips.com
Email jas@sqldbatips.com
32. Thank You! Thank you for attending this session and the
2005 PASS Community Summit in Grapevine!
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.
