SQL Server 6.0 provides the ability to set alerts on SQL Server events. For example, you could generate alerts when a database is about to become full or when a database dump is unsuccessful.
If the proper SQL Server options are set, SQL Server events are written to the Windows NT application (event) log. SQL Executive constantly monitors the Windows NT application log, waiting for events from SQL Server. When an event occurs, SQL Executive compares the event details against the alerts previously defined by the SQL Server administrator. If it finds a match, SQL Executive carries out the defined response. For each alert, the response can be either or both of these:
Paging is implemented via email. To support this, Microsoft Mail (provided with Windows NT) must be configured so that it is possible to send an email that will result in a page being sent. Email interfaces to pager services are provided by many telecommunications companies, and all that is normally required is the ability to route emails (for example, using Microsoft Mail's External Mail program) to a post office at the pager provider's site. For more information, contact your telecommunications provider.
Whether you are using email or pager-via-email notifications, you must be running Microsoft Mail on the same computer as SQL Server. For information on setting up Microsoft Mail, see your documentation for Microsoft Windows NT or the Microsoft Windows NT Resource Kit. In this documentation, see Chapter 18, Setting Up Mail.
For information on setting up SQLMail, also see the Microsoft SQL Server Transact-SQL Reference.
SQL Server events can either be processed by the local SQL Executive service or forwarded (by the local SQL Executive service) to the Windows NT application log on another computer that is running SQL Server 6.0. It is also possible to have a combination of the two: for example, you could have some events processed locally and some processed remotely. For more details on configuring event forwarding, see Setting Alert Engine Options, later in this chapter.
Microsoft SQL Enterprise Manager provides a graphical interface that allows an administrator to manage the entire alerting system. The alerts capability is supported for Microsoft SQL Server 6.0 only. You must be the System Administrator (SA) to use the alerts features of SQL Enterprise Manager.
Under the following circumstances, errors and messages generated by SQL Server and SQL Server applications are entered in the Windows NT application (event) log and can therefore cause SQL Executive to fire alerts:
Note RAISERROR WITH LOG is the recommended way to write to the Windows NT Application log from SQL Server.
Error messages with a severity lower than 19 can be logged by using RAISERROR, or by designating specific sysmessages errors as "always logged."
Also note that it is not possible to set an alert on a 4002 ("Login failed") message. However, if you turn on the option to audit failed logins, then when a login fails, the server writes a system message (18456 "Login failed - User: username" ) in the Windows NT application log. An alert can then be set on that 18456 message.
The auditing option for failed logins is a security option set by using either SQL Setup or SQL Enterprise Manager. For more information, see Chapter 9, Managing Security.