Setting up Database Mail to receive alerts

Here are the steps I used to set up SQL Server to email alerts to me if our development ETL process ever fails.

Before starting, have an email account set up that can be used to send the alerts.

A. Set up Database mail
We have to enable DB Mail and set up a profile that will be used to send the alerts.

1) In the SSMS Object Explorer, Go to Management => Database Mail. Double click to start configuration wizard.
2) Select ‘Set up Database Mail …’.
3) You will get a dialog saying ‘The Database Mail feature is not available. Would you like to enable this feature?’. Click ‘Yes’.
4) Fill in the ‘Profile Name’.
5) In ‘SMTP Accounts’ click ‘Add’.
6) On the Mail Account dialog, fill in an ‘Account Name’. Under ‘Outgoing Mail Server’ fill in ‘E-mail address’, ‘Display Name’ with the account that will be used to send out the alerts. Under ‘Server Name’ fill in the name of your e-mail server.
7) For ‘SMTP Authentication’, select ‘Windows Authentication’. Click ‘OK’ to close dialog, and ‘Next’ to move from the ‘New Profile’ screen.
8) On ‘Manage Profile Security’, the ‘Public Profiles’ tab, select the profile just created as a public profile and select ‘Yes’ to make it the default public profile. Click ‘Next’.
9) Review (and change if necessary) the System Parameters and click ‘Next’ and ‘Finish’ to close out the wizard.

B. Set up an operator

1) In the SSMS Object Explorer, Under ‘SQL Server Agent’ right click ‘Operators’ and select ‘New Operator’.
2) On the ‘New Operator’ dialog, fill in ‘Name’ with your name and make sure ‘Enabled’ is checked.
3) Click ‘OK’ to close the dialog.

C. Select the jobs that you want to be altered on.

1) In the SSMS Object Explorer, Under ‘SQL Server Agent’ select the job to set up alerts for and double-click.
2) Under ‘Select a page’ select the ‘Notifications’ page.
3) Check ‘e-mail’, select the operator created in step B, and select ‘When the job fails’.
4) Click ‘OK’ to close the dialog.

D. Configure SQL Server Agent

1) In the SSMS Object Explorer, right click on ‘SQL Server Agent’ and select ‘Properties’.
2) On ‘Select a page’ select ‘Alert System’.
3) Under ‘Mail session’, check ‘Enable Mail profile’. For ‘Mail profile’ select the profile set up in step A.
4) Click ‘OK’ to close the dialog.
5) Right-click on ‘SQL Server Agent’ again and select ‘Restart’ to restart the agent

Once you have completed these steps, you can right-click on ‘Database Mail’ and select ‘Send Test E-mail’ to send a test message.