Configuring Database Mail using SSMS is the easiest way. Please see below the steps

Navigate to the Management folder of the appropriate server, right-click on Database Mail, and select Configure Database Mail

Click Next to pass the welcome screen

select the option Set up Database Mail by performing the following tasks and click Next

If database mail has not yet been enabled for this SQL Server instance, then you will see the screen shown. If it has been enabled, you won’t see the screen. Click Yes and proceed

A profile is a collection of one or more SMTP accounts that can be used by SQL Server to send messages. In other words, when SQL Server wants to send a message, the message is sent to the profile, and then the profile is responsible for seeing that the e-mail is actually delivered. For fault tolerance, a profile can include more than one SMTP account.

To create a new profile, you must enter a profile name, an optional description, and then add and configure one or more SMTP accounts.

Enter Profile Name and Optional Description and click Add to create and configure SMTP Account.

Database Mail Account page.

To create/configure database mail account you need to fill in the details accordingly

Account Name

Optional Description

Outgoing Mail Server (SMTP). This specifies attributes of the SMTP Server that will be sending the e-mail, including these six options:

E-mail address – the e-mail account that has been set up for use with SQL Server’s database mail (for example, dbteam@company.com).

Display name – the display name of the above e-mail address that is shown as part of an e-mail.

Reply e-mail – the e-mail address used if someone should reply to an e-mail sent from the e-mail address entered above. Database Mail can’t respond to e-mails it receives, so you can either leave this option empty, or add your own e-mail address, just in case someone should respond to an e-mail received from SQL Server.

Server name – the name of the SMTP mail server.

Port number – the port number used by your organization’s SMTP server

This server requires a secure connection (SSL) – some SMTP servers require that SSL be turned on for additional security

There are three authentication modes available. In this tutorial, we have used Anonymous authentication (This option is rarely used because it allows anyone to access the SMTP server). So, select accordingly.

Click OK to return to New Profile page

click on Next to reach the Manage Profile Security screen. I already had two profiles configured, that is why the below screen shows two more profiles.

Now you have to assign the profile as either public or private. A private profile is only usable by specific users or roles, while a public profile allows any user or role (with access to msdb) to send mail.

Click Next to move on to the Configure System Parameters screen.

This option in the Database Mail Configuration Wizard allows you to set the values of specific Database Mail parameters for the profile. Leave them to default and click Next

Review of the summary screen, click on Finish

If the Configuring… screen reports success, then Database Mail has been successfully set up

Now, In order to check whether the configured Database mail is working, right-click on the Database Mail folder in SSMS and select Send Test E-Mail,

In Database Mail Profile, select the profile just created and in To, type in your email address and click on Send Test E-Mail.

Database Mail has been configure.

Configure a SQL Server Agent Operator

When we configure a Maintenance Plan to send an e-mail, created with either the Maintenance Plan Wizard or the Maintenance Plan Designer, we can’t enter an e-mail address directly into the Maintenance Plan. Instead, we configure e-mails to be sent to an operator.

An operator is an alias for a specific person (such as yourself), or a group (such as a DBA mail group). This alias is more than just a name; it is actually a set of attributes that include the operator’s name, the operator’s contact information, and the operator’s availability schedule.

To create a new operator, open SSMS, navigate to the SQL Server instance you wish to configure, open up the SQL Server Agent folder, navigate to the Operators folder, right-click on it and select New Operator

New Operator screen has lots of options, we will focus only on the three that are most important.
Name – enter the name of the person or group who you want to receive notification e-mails.
Enabled – this option is selected by default, and you don’t want to change it, otherwise you won’t be able to receive any notices from SQL Server.
E-mail name – enter your e-mail address, or the group’s e-mail address here.

That’s it. All the other options are optional, and you can use them if you want to, we have left them to blank. click on OK

Maintaining 99.9 % Availability of database servers is one of the main goals of a DBA. To achieve this goal, an enterprise monitoring tool is required. There are various third party monitoring tools available in the market. Such as

1. Red-gate SQL Monitor

2. Idera – SQL Diagnostic Manager

3. Microsoft- SCOM

4. BMC etc.

However, all these Monitoring tools are very expensive. And most of the organizations can’t afford them. So, configuring SQL Server Event Alerts in SQL Agent is a free and easiest way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.

The script configures SQL Server Alerts for severity 16 to 25.

Severity:

— Note: You need to configure Database Mail and Operator before run this script. In this script i used the operator name DB Team. You need to change the operator name accordingly.

Follow Blog via Email

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2 other followers

Disclaimer

This is a personal weblog. The opinions expressed here are my own and not of my employer. For accuracy and official references refer to MSDN, Microsoft TechNet, Books Online. I or my employer do not endorse any of the tools / applications / books / concepts mentioned here on my blog. I have simply documented my personal experiences on this blog.