Skip to Content

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

image

Right click in Database mail and then select configure Database mail

image

Click on Next to continue

image 

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

image

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.

image 

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

Click on Add to add an account for sending mails

image 

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

image

After completing this click on Ok and proceed

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

image

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.

image

Click On Next to continue

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

image

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.

image 

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

image

 

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.

image 

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

image

Go to Alert System

image

 Check on Enable mail profile and then click on Ok

image 

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

image

The notifications has been configured successfully in case of job failures

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

    1. Anonymous
      Hello Lars,

      well CCMS can be configured for jobs that are scheduled through SM37 I suppose but what about the jobs that are scheduled through SQL server.
      Also suppose our SAP is down but database is still up,then in that case jobs scheduled through SQL server will still run and then in that case CCMS wont work but still we will get auto-notifications from Database if job failures…
      how about it,we are using this in our setup and its working fine without any extra load on database server

      (0) 
    2. João Dimas
      Hi Lars how are you?

      I was looking this explanation and I look your question and the funny of this is that I want to configure that alert functionality of CCMS… can you explain how I can setup that?
      I´m newest in CCMS monitoring, so I ask you to explain me from the beginning when you´ll try to explain this functionality, OK?

      Thanks in advance
      Best regards,
      Joao Dimas

      (0) 

Leave a Reply