SQL Server Database Mail Configuration Step by Step

SQL Server Database DB Mail Configuration Step by Step:

This Article will help Configuring SQL Server Database mail in SQL Server for Alerts and Notifications.

Database Mail is very Useful and powerful ability in SQL Server. Send email in SQL Server is one of the best ability for monitoring SQL Server

One can configure SQL Server Database Mail so that it sends you important notifications and alerts for example

1. Alerts for any high severity errors, corruptions etc..
2. Notifications Agent jobs Success or failure
3. You can also use Database Mail to email query results and reports

Configuring Database Mail and Enabling it on the SQL Server Agent for Alerts can be done by following below steps

Step 1. Get the needed information handy for setting up database mail like below

SMTP Server Information. We will fully qualified name, port information, and authentication information for your smtp server. Get this from your IT Team.

It will be like mailrelay.yourcompanydomain.com and port will be like 25 and authentication.

We will need to enable Database Mail in SQL server which is not enabled default by below code

Below will tell us if its already enabled

SELECT * FROM sys.configurations WHERE name = 'Database Mail XPs'
GO


Below TSql commands will enable the Database mail option on SQL Server

EXEC sp_configure 'show advanced options', '1';
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE
GO


Normally by default Service Broker is enabled but we can check this as its needed

Below will tell us if its already enabled

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
GO
Step 2. Configuring Database Mail Using the Wizard

a. In Object Explorer, expand Management and right click Database Mail:

Click ‘Next’, then click the first option to set up Database Mail.

b. Create a Profile

Create a profile for Database mail. We can have multiple.

c. Create An Account

We will need to create a Account for Database Mail which is used by above Profile for Database Mail to work.

Email address: Say like DBA@yourCompanydomain.com
Display name: We can use SQL Server’s name from where emails are sent.
Reply email: We can use DONOTREPLY@yourCompanydomain.com or sometimes DBA Email address
Server name: Ask your IT Team for the details of your Exchange server. This smtp server we will use. Select the Port and your authentication options as given by IT Team.

d. Send a Test Email

Right click on “Database Mail” in Object Explorer and click on “Send a Test Email.” and its pretty simple to send a test email to yourself

If it doesn’t work, there’s a problem in your setup. Right click “Database Mail” again and select “View Database Mail Log” for errors needed for troubleshooting

Step 3. We will need to enable Database Mail on the SQL Server Agent for notifications.

Configuring SQL Agent to use Database Email.

Right click on the SQL Server Agent and select properties, like this:

SQL Server Agent Properties

Now Click on the Alert System Tab

Now here is where we configure SQL Server Agent to tell which database mail profile to use. Enable the mail profile, then select your mail profile. we can also set failsafe operator here.

Restart the SQL Server Agent Service to Make That Take Effect