Introduction
Native alerts were valuable in SQL Server 2000, but at the same time they were unreliable in many cases due to SQL Server Mail's dependency on MAPI with Outlook. Many 'work-arounds' were created to improve the inherent functionality based on individual needs. SQL Server 2005 has alleviated this issue with the introduction of Database Mail. Thus far, this solution seems to meet industry needs for an SMTP based email system and has enabled the native SQL Server alerting capabilities to shine. In this tip we will outline the prerequisites for alerts, setup options and then highlight valuable alerts you'll want.
SQL Server 2005 alerts prerequisites
SQL Server 2005 Alerts are dependent on a few core pieces of infrastructure to operate properly. These items include:
ID
Directions
Screen Shot\Code
1
Enable the database mail stored procedures by executing the following code in a query window in SQL Server 2005 Management Studio.
2
Setup Database Mail with accounts to associate with an Operator and to support receiving alerts.
Below are the directions to start the Database Mail Configuration Wizard:
- Open the SQL Server 2005 Management Studio
- In the Object Explorer, expand the 'Management' folder
- Right click on the 'Database Mail' option
- Select the 'Configure Database Mail' option
[IMAGE]
3
Setup Operators to receive alerts.
Below are the directions to setup SQL Server Operators:
- Open the SQL Server 2005 Management Studio
- In the Object Explorer, expand the 'SQL Server Agent' option
- Right click on the 'Operators' folder
- Select the 'New Operator' option
- Complete the interface on the right for the operator
[IMAGE]
4
Configure SQL Server Agent to use the mail session as well as have a fail-safe operator.
Below are the directions to configure SQL Server Agent:
- Open the SQL Server 2005 Management Studio
- In the Object Explorer, right click on the 'SQL Server Agent' option
- Select the 'Properties' option
- Select the 'Alert System' tab
- Complete the interface on the right
[IMAGE]
SQL Server 2005 alerts setup
SQL Server has two means to setup native alerts. The first is with SQL Server 2005 Management Studio and the second is via a set of system stored procedures. Below outlines examples of each option:
ID
Option
Screen ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
shot/code
1a
SQL Server 2005 Management Studio Interface to setup alerts
General Tab – Configure the name, severity, etc on this interface
Below are the directions to configure SQL Server 2005 alerts:
- Open the SQL Server 2000 Management Studio
- In the Object Explorer, open the 'SQL Server Agent' option
- Right click on the 'Alerts' folder
- Select the 'New Alert' option
- Complete the interface on the right
[IMAGE]
1b
Response Tab – Select the operators to notify
[IMAGE]
1c
Options Tab – Indicate the options when the text will be included and the delayed response parameter
[IMAGE]
2
Two SQL Server 2005 system stored procedures are responsible for creating alerts and notifying operators.
These procedures are:
sp_add_alert
sp_add_notification
Valuable SQL Server 2005 Alerts
Below are three basic approaches to building a set of needed SQL Server 2005 alerts, since none are created by default:
ID
Approach
Sample code
1
Category
For example, -- the Severity Level is considered a Fatal Error for levels 19 to 25.
2
Keyword
For example, 'Primary Filegroup is full' when the database can no longer allocate space for the database.
3
Error Number
For example, error 9100 related to index corruption.
Duplicating the alerts
Once you have all of the alerts that you want on one specific SQL Server, they can be scripted out and created on another SQL Server. This can be accomplished by the following steps:
- Open the SQL Server 2005 Management Studio
- In the Object Explorer, open the 'SQL Server Agent' option
- Expand the 'Alerts' folder
- Select any alert that you want to script out
- Right click on the alert and select the following options
- 'Script Alert as'
- 'CREATE To'
- 'New Query Editor Window'
- Connect to the needed SQL Server, then Copy the code from the original window and paste the code into the correct SQL Server's query window
Conclusion
SQL Server 2005's alerting capabilities are expected to meet the much desired features of the SQL Server community. Based on your application needs configure your alerts to be the first line of defense in if SQL Server has an issue. Do not overlook this simple yet valuable means to obtain near real time alerts.
About the author: Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. He is the author of the Rational Guide to IT Project Management. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.
