How to configure database mail SQL Server? | How to Configure Database Mail on azure SQL managed instance

by

This Article guides,

How to configure database mail SQL Server? or Configure Database Mail – SQL Server.

Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine or Azure SQL Managed Instance. Your applications can send e-mail messages to users using Database Mail via an external SMTP server. The messages can contain query results and can also include files from any resource on your network.

Database mail can be used to send alert based on requirement. We can configure SQL Server alert for Backup jobs, Low disk space alert, long running queries etc. This alert can help to keep eye on production SQL server in case of any issues.

How to configure Database Mail SQL Server?

We can configure Database mail using SSMS or using T-SQL. Below step-by-step guide for How to configure Database Mail SQL Server? To Configure database mail in SQL Server we need SMTP and port.

Also need to grant access to those SMTP for SQL Server hostname or IP address.

–Change and port if required before script execution.

Step 1 : Enable database mail feature in managed instance if not enabled

USE master;
GO
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Step 2: Configure database mail profile and database mail account. Add appropriate SMTP and Port number before executing SQL script on azure SQL managed instance and SQL Server.

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBMonitoring',
@description = 'DBMonitoring'

–Add SMTP server details and Port Details and Also add username and password in below SQL script.

I have used SMTP.outlook.com and port is 587 for testing.

Watch video for step by step guide of Database mail configuration.

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBMonitoring',
@email_address = 'xyz@gmail.com',
@mailserver_name = 'SMTP server name',
@port=SMTP Port,
@enable_ssl=0,
@username='sa',
@password='password'

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBMonitoring',
@account_name = 'DBMonitoring',
@sequence_number =1 ;

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DBMonitoring',
@principal_id = 0,
@is_default = 1

Step 3: Test database email configuration by sending email on azure SQL managed instance or SQL Server.

USE msdb
GO
EXEC sp_send_dbmail @profile_name='yourprofilename',
@recipients='test@Example.com',
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.