I recommend configuring Database Mail and Alerts while logged on as the 'sa' account or another generic account. The purpose of using this account is to ensure that your alerts continue to operate should a user account be deactivated.
Configuring Database Mail
First we need to enable Database Mail which is disabled by default:
EXEC sp_configure 'show advanced options',1
RECONFIGURE;
go
EXEC sp_configure 'Database Mail XPs',1
RECONFIGURE;
go
Under Management, right click Database Mail and select configure database mail.
Click Next and give your Database Mail Profile a name.
Click Add to add a Database Maill Account. I like to configure the name of this account as the hostname. This will make it easier to identify where your email alerts are comming from.
Click Next
Under Manage Profile Security, check off your auto created Profile. It's also recommended that you mark this as the default profile.
Click next, next, finish
You now have Database Mail configured and you can test it:
Under Management, right click Database Mail and select send test e-mail.
It is possible to create a script utilizing msdb.dbo.sysmail_add_account_sp, msdb.dbo.sysmail_add_profile_sp, and dbo.sysmail_add_profileaccount_sp for quickly deploying to multiple servers.
EXECUTE msdb.dbo.Sysmail_add_account_sp
@account_name = 'HostName',
@email_address = 'your.email@address.com',
@display_name = 'HostName',
@mailserver_name = 'yourSMTPserver'
EXECUTE msdb.dbo.Sysmail_add_profile_sp
@profile_name = dbmail;
EXECUTE msdb.dbo.Sysmail_add_profileaccount_sp
@profile_name = dbmail,
@account_name = hostname,
@sequence_number = 1;
Configure SQL Server Agent to use Database Mail
Now that we have Database Mail configured, we need to tell SQL Server Agent to use it. Right Click SQL Server Agent and click Properties
Under Alert System click Enable Mail Profile using Database Mail as the Mail System and the Profile we previously created as the Mail profile.
I've found it helpful to restart SQL Server Agent at this point.
Configure SQL Server Agent Operators
Under SQL Server Agent, right click Operators and click new operator
Enter your operator information including email name.
I like to create 2 operators 1 for serious alerts and another for not so serious alerts.
You can automate the creation of operators with msdb.dbo.sp_add_operator.
Configure SQL Server Agent Alerts
Now for the fun part. Based on SQL's Severity Levels, http://msdn.microsoft.com/en-us/library/aa937483(v=sql.80).aspx, we can configure minor alerts to notify the minor alerts operator and the more serious alerts to notify the other operator. I use severity levels 11-16 as minor alerts, and 17-24 for serious alerts.
Under SQL Server Agent, right click Alerts and click New Alert.
I like to name the alert based on the severity level. Subsequently choose the corresponding Severity Level next to the Severity radio button:
Under Response choose Notify operators and select the Minor Alerts operator for severity levels < 17.
Now it's just a matter of rinsing and repeating for each severity level. If you're so inclined you can script this out with sp_add_alert and sp_add_notification
EXECUTE msdb.dbo.Sp_add_alert
@name = N'Severity 11',
@message_id = 0,
@severity = 11,
@enabled = 1,
@delay_between_responses=300,
@notification_message =
N'A severity 11 error occured on the SQL server. Please check the database log',
@include_event_description_in = 5,
@category_name = N'[Uncategorized]'
EXECUTE msdb.dbo.Sp_add_notification
@alert_name = N'Severity 11',
@operator_name = N'DBA Minor Alerts',
@notification_method = 1
You'll notice I have @delay_between_responses=300. This setting restricts notifications to 1 every 5 minutes. I've found this helpful in order to avoid flooding yourself.
Once your done, it should look something like this:
Some more references for you:
No comments:
Post a Comment