Setting up SQL e-mail notifications

Note:

This guide is for skilled computer administrators with experience in working with databases. It is intended to help you set up SQL e-mail notifications. However, this guide is in no way a basis for claims in case of data loss. The customer is solely responsible for ensuring a full, correct and restorable backup of all data.

1.1. E-mail notification for SQL backup

Prerequisite for this step is that you have configured an SQL backup as explained in “Setting up an SQL backup for BMD NTCS” and that it works.

In the following example we used a local mail server and therefore the server name states “local host”. If you want to use your own (internal or external) mail server, you have to make sure that the SQL Server has permission to send e-mails to the desired recipient address via this mail server. (You can either allow relaying for the SQL Server (sending without authentication) or you can define a separate user to send the e-mails.)

1.1.1.  Configuring Database Mail

Windows 7 / server 2008 R2 or earlier versions:

Start → Programs → Microsoft SQL Server 20xx → start SQL Server Management Studio.

Windows 8 / server 2012 or later versions:

Enter ‘SQL Server Management Studio’ in the search dialogue of the Metro user interface or use the arrow buttons to search the entry and start it.

Alternatively, you can find the program in Windows Explorer under the following path:
„C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 20xx“ 

Replace “20xx” with the respective SQL version in use.

CAUTION!

If Windows User Account Control (UAC) is active, you have to start SQL Server Management Studio by right-clicking and selecting ‘Run as administrator’!

In SQL Management Studio → Management → right-click on ‘Database Mail’ → Configure Database Mail:
 

Confirm the welcome dialogue and select ‘Set up Database Mail by...’ in the next window:

Enable the database mail feature by clicking ‘Yes’:

Enter "SQL-Mail" in ‘Profile name’ and then click on ‘Add’:

Fill in the following dialogue accordingly and confirm with ‘OK’.

The following entries are mandatory:

Account name: SQL-Mail

E-mail address: e-mail address of the sender

Server name: IP address or server name of the mail server

SMTP Authentication: according to configuration of mail server
 

Continue by clicking ‘Next’:

In this window, select the name of the public profile:

Under ‘System parameters’ change the entry for ‘Account Retry Attempts’ to 3 and adjust ‘Account Retry Delay’ if necessary:

Complete the configuration wizard:

1.1.2. Test e-mail

Now you can already send a test e-mail to the desired recipient address: SQL Management Studio → Management → right-click on ‘Database Mail’ → Send Test E-Mail → in „To“ enter the desired recipient address and check whether the test e-mail has been sent and received correctly.
 

1.1.3. Enabling the mail profile

Now you have to activate mailing: Right-click on ‘SQL Server Agent’ → Properties → Alert System → Enable mail profile → SQL-Agent-Mail
 

After doing this, you have to restart SQL Server Agent. Right-click on ‘SQL Server Agent’ → Restart:

1.1.4. Creating the recipient

Now you create the recipient. Again, right-click on ‘SQL server Agent’ → New → Operator...

Please enter the e-mail address that you have already tested:

1.1.5. Defining e-mail notifications

The final step is to define the e-mail notification in both backup jobs: SQL Server Agent → Jobs → right-click on ‘Full-Backup BMD’ → Properties → Notifications → enable ‘E-mail’ and select ‘When the job completes’:

1.1.6. Transaction-log backup

For the transaction-log backup proceed in the same way: SQL Server Agent → Jobs → right-click on ‘Trans-Log-Backup BMD’ → Properties → Notifications → enable ‘E-mail’ and select ‘When the job fails’: