Creating Email Alerts For Job Failures Demo

A very helpful tool in SQL Server 2019 is the alert notifcation system. This is important because if a full database backup fails on Sunday night and no one is notified, the company could go an entire week without a backup of their database. This can cause disasterous loss in data. Thankfully, SQL Server allows authorized individuals to receive an alert email any time a maintenance job fails. In this demo we will be going over how to set up a alert for a backup failure. To begin, we must set up Database Mail in SQL Server.

Contents:

• Setting up Database Mail Account
• Configure SQL Jobs to user DB Mail Account

Setting up Database Mail Account in SQL Server 2019

The database mail account is the account that the emails will be sent from. For example, this account name could perhaps be YourCompanyNameDB@gmail.com. It is very important to realize that this is the FROM email account. To allow SQL Server 2019 to access a company email account and send emails, there must be some work done with the emails security features. To begin this demo, we will be showing how to allow 3rd party applications to send emails through Gmail.

1. First, sign on to the company email that you would like to send SQL Server database emails FROM. Then select Manage your Google Account.

   

2. Then, select the Security tab and scroll down to the section titled "Signing in to Google". From there, enable 2 factor authentication if it is not done so already. This process is fairly straight-forward so we will not be going over it.

   

3. After 2 factor authentication is enabled, go back to that same Security tab in the Manage your Google Account window. Scroll down to where you turned 2 factor authentication on and select Create an App password. This will allow 3rd party applications to send emails from your gmail account, as they will use this password to bypass the 2 factor authentication previously set up.

4. Follow the straight-forward screens in the Create an App password option and retrieve your 16 character application password string. This password is what you will be entering into SQL Server to authenticate that you are the owner of the gmail account sending the emails. Next, we will be working in SQL Server to begin setting up the Database email system.

5. Open SQL Server 2019 and connect to the database instance. In the Object Explorer, go under the Management tab and right click Database Mail, and select Configure Database Mail. Next the Database Mail Wizard will appear.

   

6. Select the first option, "Set up Database Mail by performing the following tasks", and enable the feature.

   

7. In the next window, create a profile name and a description (optional) and then under the SMTP accounts window, select Add. This will then prompt the New Database Mail Account creation window shown below.

   

   

8. In the account creation window, input your customized account information.

   Under Account name we input the name of the new account, under Description optionally enter the account description. Under E-mail address we input the email address that we previously created an application password for. Under Display name we input the name which will be shown on email notifications sent. Under Reply e-mail, optionally we can input the email address where replies to email messages sent from this account will be sent. It can be the email of the database administrator, or it can be left blank. Under Server name we input the SMTP address for our mail server, which is most commonly in the form of smtp.XXX.com. Since we will be using Gmail, the SMTP address is smtp.gmail.com. After that we input the port number for the server under Port number. In most cases, the port number is 25, however, it is advised to consult the mail administrator for further information. For gmail in this demo, we used port number 587.

   Under SMTP authentication, we are presented with three options for the authentication. Since we are connecting to a secure email account, we must provide our authentication. Thus, we will select Basic Authentication. Under basic authentication, we will input our full email address and the application password we generated before.

   * NOTE: Do NOT input the password you normally use to log in to the email account. You must use the application password that we generated previously in this demo.

   

   

9. Once we see the new SMTP account created in the window, we can see the priority of email alerts. We can add multiple email accounts and change the priority so alerts can be sent to other emails if one fails. Select Next to continue.

   

10. On the Manage Profile Security page, there are two tabs Public Profiles and Private Profiles. In the Public Profiles tab, we configure the account that will be available to any user or role with access to mail host database (msdb) to send email notifcations using that profile. In Private Profiles tab, we select the users and which profiles they can use. For this demo we will only be selecting the profile "SRP" and "Grafton Houser under Public and Private DProfiles. Then select Next.

    

    

11. Then configure the system parameters. In this demo we will be using the default parameters.

    

12. Finally, finish the configuration and the Wizard will complete. Select close and now there is a profile for Database Mail.

    

13. To make sure that the Database email system was properly set up, right click again on Database Mail in the Object Explorer and select Send Test Email

    

14. Next, select the database mail profile that we previously created and input the email to whom you would like to send the test email to. For this demo, I sent the test email to myself. As you can see in the accompanying picture, the email was successfully sent.

    

    

    Now that we have created a Database Mail Profile, we can configure SQL Server Agent to use Database Mail

Configure SQL Server Jobs to use Database Mail Demo

1. First right click on SQL Server Agent in the Object Explorer and select Properties

   

2. Then select the Alert System tab.

   

3. Select Enable mail profile, then click the drop down menu under Mail profile and select the profile that will receive the mail alerts. Finally select OK at the bottom of the window.

   

   Now we must create an operator to assign the email notifcations to.

4. Under SQL Server Agent in the Object Explorer, right click on Operators and select New Operator.

   

5. Next, fill out the operators name and email address and select the Enabled button next to their name. Next select the Notifcations tab.

   

6. In the notifcations tab, select the Jobs button under "View notfications sent to this user by:". Then select OK.

   

   Now that an operator is created, we can now assign notifcations to that operator from the jobs themselves.

7. First locate the Jobs tab under SQL Server Agent in the Object Explorer and right click on the job that we want email notifications for and select Properties. In this demo we will be selecting the Daily Differential Backup Job.

   

8. Next, select the Notifications page in the Job Properties. Then select the email button and from the drop down select the profile that we created before. To the right we can also specify whether we would like to be notified when the job fails, when the job completes, or when the job succeeds. For this demo we will be selecting when the job succeeds so we can test it. Then select OK.

   

9. For this demo, we chose to notify the Operator when the job succeeds, just to prove that the system works. After we ran the daily differential backup that we created a job notifcation for. We received this notication showing us that it worked successfully.

   

10. For any backup job, it is wise to be notified as soon as possible if the job fails. Thus, go back to your Jobs under SQL Server Agent and customize the notifications to notify the operator whenever the backup job fails.