Configuring Auto-Notifications for SAP jobs scheduled though SQL server agent in SQL Server 2005

For configuring email alerts for Backup Jobs first we will need to configure either SQL Mail or Database Mail in SQL Server 2005.

Database mail is newly introduced concept in SQL Server 2005 and it is replacement of SQLMail of SQL Server earlier version. Database Mail has many enhancement over SQLMail. Database Mail is based on SMTP (Simple Mail Transfer Protocol) and also very fast and reliable where as SQLMail is based on MAPI (Messaging Application Programming Interface). Additionally, for MAPI to be enabled for SQLMail it will require Outlook to be installed. All this leads to potential security threat to database server.So we will be using Database mail

There are two ways that we can setup Database Mail, either by using the stored procedures that are included with SQL Server 2005 or by using SQL Server Management Studio. I have configured the Database Mail by using the GUI

Log on to SQL server Management studio and expand the Management node of the tree

Right click in Database mail and then select configure Database mail

Click on Next to continue

By default the option is set for “Set up Database mail by performing the following tasks”.We will use this

We will be setting up a profile and account for database alerts

Click on Next to continue

Here you will be asked to enable Database mail feature if it is not already enabled.

You have two options here: either click on yes to continue or there is another option through a SQL statement.

I clicked on yes to continue. This will enable the database mail feature.

Give the name of the profile and the description for that.

Click on Add to add an account for sending mails

Give the account name and description.

Under Outgoing Mail server, you have to give

E-mail address- the email id from which the mails will come

Display name – the mail will come in your mailbox with this name

Reply e-mail – we will not be using this

Server Name – It is the mail server which will be used to send mails, by default the port will be 25

Under SMTP Authentication I have used Basic authentication, the password is for the user sidadm

After completing this click on Ok and proceed

The following screen will appear.This shows that the Account has been added to the profile.

Click on Next to continue.

Here we can configure the following system parameters of Database Mail system for sending mails.

I have used the default ones.

Click On Next to continue

The following screen will appear.Click on Finish to perform the action mentioned here.

When you click “‘Finish” the next screen will appear that shows you the status of installing Database Mail. When this has finished just click “Close” to close this screen.Make sure everything is in green.

Now we will be configuring the operator to whom the mail is send when a job fails.Expand the tree SQL server agent; right click on operators and then New Operator

In this give the name of the operator.

I have used IBM Basis(the operator for our purpose).In email-name give the email id of the operator who you want to be notified; I have given the email id of our group.Click on Ok to complete the task.

Thus in case of any job failure a mail notification will be send out to the operator at the mail id specified above

Now we have to configure SQL server agent to send the auto notifications mail.Right click on SQL server agent and then go to properties

Go to Alert System

 Check on Enable mail profile and then click on Ok

Now we have configured the Database mail to send us auto notifications. 

Now we have to configure the background job to send auto alerts.

Go to Notifications Tab.Right click on the job and then properties

Check on E-Mail box and select the operator to whom we should send mails.

In our case it is IBM Basis. Also choose this for notification form when the job fails.We can also configure this when the job completes successfully

The notifications has been configured successfully in case of job failures