Page 113 - DCAP508_DATABASE_ADMINISTRATION

Unit 7: Automating Administrating Tasks

CREATE EVENT NOTIFICATION CreateDatabaseNotification Notes
ON SERVER
FOR CREATE_DATABASE
TO SERVICE ‘NotifyService’, ‘8140a771-3c4b-4479-8ac0-81008ab17984’ ;

!
Caution Event notifications recognize CREATE_SCHEMA events and the <schema_element>
definitions of CREATE SCHEMA statements as separate events. For example, an event
notification is created on both the CREATE_SCHEMA and CREATE_TABLE events, and you
run the following batch. CREATE SCHEMA CREATE TABLE t1 (col1 int) In this case, the
event notification is raised two times: One time when the CREATE_SCHEMA event occurs,
and again when the CREATE_TABLE event occurs. We recommend that you either avoid
creating event notifications on both the CREATE_SCHEMA events and the
<schema_element> texts of any corresponding CREATE SCHEMA definitions, or build
logic into your application to avoid capturing unwanted event data.
To create an event notification

CREATE EVENT NOTIFICATION (Transact-SQL)
To drop an event notification
DROP EVENT NOTIFICATION (Transact-SQL)

Self Assessment

State true or false:
6. To modify an event notification, you must drop and recreate the event notification.

7. Service Broker routing determines the service that receives the notification messages.
8. An event notification can occur after any Transact-SQL event that belongs to a predefined
grouping of similar events is run.

9. Event notifications are a special kind of database functions and send information about
server and database events to a Service Broker service.
10. Event notifications recognize CREATE_SCHEMA events and the <schema_element>
definitions of CREATE SCHEMA statements as same events.

7.3 SQL Server Agent

7.3.1 Meaning of SQL Server Agent

SQL Server Agent is a Microsoft Windows service that allows you to automate some
administrative tasks. SQL Server Agent runs jobs, monitors SQL Server, and processes alerts.
The SQL Server Agent service must be running before local or multiserver administrative jobs
can run automatically.
7.3.2 Configuring SQL Server Agent

You can specify some configuration options for SQL Server Agent during installation of SQL
Server. The full set of SQL Server Agent configuration options is only available within SQL

LOVELY PROFESSIONAL UNIVERSITY 107