Enabling alerts and notifications in SQL Server can be very useful, especially when you use the SQL Agent jobs functionality. By enabling alerts and notifications, you can be notified if your SQL job fails or each time is completes.
Here are the steps for enabling SQL Server Alerts and Notifications.
Configure Database Mail
1. Using the SQL Server Management Studio, expand the Management section, right-click on Database Mail and choose Configure Database Mail. This will launch the Database Mail Configuration Wizard. Choose Next.
2. Select Configuration Task: If this is your first time configuring the Database Mail, select the Set up Database Mail by performing the following tasks option. Choose Next.
3. Confirmation: If you are prompted to enable the Database Mail feature, choose Yes.
4. New Profile: Enter a Profile Name and click the Add button to add an SMTP account. (You will need to add at least one SMTP account in order to choose Next.)
5. New Database Mail Account: If you are a developer, you may need assistance from your IT department in order to be able to configure the following properties. Once you are done, you can choose OK to add the profile and then choose Next to continue through the wizard.
- Email address: You may want to use a generic email address or distribution list, such as dba@mycompany.com. Your IT department may need to set that up for you.
- Server Name: You will need to know the name of your SMTP server and whether it will accept anonymous authentication
6. Manage Profile Security: Check Public profile and choose Next.
7. Configure System Parameters: You can choose to modify each of the system parameters here, but I will keep the defaults for this example for the sake of simplicity. Choose Next.
9. Complete the Wizard: Go ahead and choose Finish in order to configure for Database Mail.
10. Once it has finished, you can right-click on Database Mail and choose Send Test E-Mail to make sure it has been properly configured.
Enable SQL Server Agent Mail Profile *** probably the singlemost overlooked step (You will need to restart SQL Server Agent when this step has completed)
Once you have successfully configured and test your Database Mail, then you need to enable it for your SQL Server Agent.
1. Using SQL Server Management Studio, right-click on SQL Server Agent and choose Properties.
2. Click Enable mail profile.
3. Mail system should be set to Database Mail.
4. Mail profile should be set to the Profile you just created, in my case SQLAlerts.
5. Press OK.
Create Operator
Once you have configured Database Mail, you can create an Operator in order to specify a user you can select for specific database alerts and notifications.
1. Using SQL Server Management Studio, expand SQL Server Agent.
2. Right-click on Operators and choose New Operator.
3. Type in the Name and Email name for your operator. Choose OK.
Add Notification to Job
Now that you have enabled Database Mail and have created an Operator, you can specify a notification. For this example, I am going to update my SQL Server Agent Job to notify me when the job has completed.
1. Using SQL Server Management Studio, expand SQL Server Agent.
2. Locate and double-click my SQL Server Agent job to modify properties.
3. On the left Select a page navigation, select Notifications.
4. Click E-mail, select the Operator I just created, and specify When the job completes.
