How to Configure Database Mail in SQL Server

Database Mail is an solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on the network. For example, if an automation process like jobs, replication, and database log-shipping fails or there are alerts raised then SQL Server can use this feature to notify the administrators or operators.

There are two ways to setup Database Mail, either by using the stored procedures that are included with SQL Server or by using SQL Server Management Studio.
In this post, we will setup Database Mail by using the both the methods using GMAIL account.

Method 1: Setup Database Mail using SQL Server Management Studio

Step #1: Enable database mail
To enable Database Mail, run the following code

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


Step #2: Configure Database Mail 

To setup Database Mail, Open SSMS, connect to the SQL Server instance and expand the Management node of the tree and then right click on "Database Mail". Select "Configure Database Mail' and click "Next".


Welcome Screen will be displayed. click "Next".


In the following screen select "Set up Database Mail by performing" and click "Next"


Step #3: Create Database Mail Profile

Enter name and description for the Profile and click "Add"


Step #4: Configure Database Mail Account 

Determine the server name and port number for the Simple Mail Transfer Protocol (SMTP) server to send e-mail. The server type is always SMTP for outgoing mail. 
In this demo, we will be using GMAIL account to configure database emails. Fill the details below for mail account that will be used to send emails from SQL Server an then Click OK


Click Next to continue.


Click on the checkbox to allow this profile to be a Public then select Yes for the default profile. Click Next


Keep below shown settings to default and click Next


A summary screen will appear that shows all of the options that were selected.  click Finish


Next screen shows the status of database mail installation.  click Close


To test Database Mail, right click on Database Mail and select "Send Test E-Mail". Fill the details as shown below and click "Send Test E-Mail".




Method 2: Setup Database Mail using SQL Server Store Procedures

Execute below code from all stored procedures to configure database mail

--Enable database mail on instance level
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'PerformanceAlerts',
    @description = 'SQL server performance alerts' ;
GO

-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'PerformanceAlerts',
    @principal_name = 'public',
    @is_default = 1 ;
GO

-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'PerformanceAlerts',
    @description = 'SQL server performance alerts',
    @email_address = 'samirk26@gmail.com',
    @display_name = 'SQLAlerts',
    @mailserver_name = 'smtp.gmail.com',
    @port = 587,
    @enable_ssl = 1,
    @username = 'samirk26@gmail.com',
    @password = 'axbycz@123' ;
GO

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'PerformanceAlerts',
    @account_name = 'PerformanceAlerts',
    @sequence_number =1 ;
GO

--Test database mail configuration
EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'PerformanceAlerts',
     @recipients = 'samirk26@gmail.com',
     @body = 'This is a test SQL database mail',
     @subject = 'Automated Success Message';
GO


No comments:

Post a Comment