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.
SQL Tracing
Microsoft SQL Server provides Transact-SQL system stored procedures to create traces on an instance of the SQL Server Database Engine. These system stored procedures can be used from within our own applications to create traces manually, instead of using SQL Server Profiler. This allows us to write custom applications specific to the needs of our enterprise.
Microsoft SQL Server provides Transact-SQL system stored procedures to create traces on an instance of the SQL Server Database Engine. These system stored procedures can be used from within our own applications to create traces manually, instead of using SQL Server Profiler. This allows us to write custom applications specific to the needs of our enterprise.
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.
|
Both SQL tracing and event notifications have their own functional area. They can be access through some Server Management Objects (SMO) TraceReader Class and Server Management Objects (SMO) Event classes.
No comments:
Post a Comment