Sending emails in Azure SQL Managed Instance
Published Mar 23 2019 06:45 PM 30.9K Views
Microsoft

First published on MSDN on Aug 31, 2018
Azure SQL Database Managed Instance enables you to use most of the SQL Server features in fully managed cloud environment. One of the features is database mail system that enables you to send emails directly from Managed Instance. In this post we will see how to setup mail profile and send emails in Managed Instance. 

Setup


First, you need to ensure that Managed Instance can reach your mail server. If your email server is using port 25 then you would need to open outbound NSG on port 25 to Internet. Managed Instance is placed in your VNet/subnet and you have control over the network traffic. 

 

Then you would need to setup email account information that contain address of the e-mail server that  will actually send the emails, with login/password information required to access e-mail server. In the following script set $(mailserver), $(loginEmail), and $(password) information, change titles/description shown as '...' and run it:

-- Create a Database Mail account 
EXECUTE msdb.dbo.sysmail_add_account_sp 
@account_name = '...', 
@description = '...', 
@email_address = '$(loginEmail)', 
@display_name = '...', 
@mailserver_name = '$(mailserver)' , 
@username = '$(loginEmail)' , 
@password = '$(password)' 

-- Create a Database Mail profile 
EXECUTE msdb.dbo.sysmail_add_profile_sp 
@profile_name = 'AzureManagedInstance_dbmail_profile', 
@description = '...' ; 

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


The important thing is that you can use any name for the DbMail profile (and you can have several db Mail profiles) for Db Mail procedures. However, if you want to send e-mail using SQL Agent jobs, there should be a profile that must be called 'AzureManagedInstance_dbmail_profile'. Otherwise, Managed Instance will be unable to send emails via SQL Agent. If you are using one  profile in your instance and you want to use it both for classic emails and SQL Agent, rename the profile to 'AzureManagedInstance_dbmail_profile' so it can be used on both places.

Then, you would need to enable Database email extended procedure using Database Mail XPs configuration option:

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

Now you can test the configuration by sending emails using sp_send and sp_notify_operator procedures.

Sending emails


sp_send procedure enables you to send email messages directly to the specified email address. An example of  the code that sends an email message is shown in the following listing:

DECLARE @body VARCHAR(4000) = 'The email is sent with msdb.dbo.sp_send_dbmail from ' + @@SERVERNAME; 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'AzureManagedInstance_dbmail_profile', 
@recipients = '$(email)', 
@body = @body, 
@subject = 'Azure SQL Instance - test email' ; 

If everything is fine, you will get the email on the $(email) address.

Sending email notifications to operators


You can also define the operators with assigned email addresses and send emails to them using sp_notify_operator procedure. First, you need to add an operator and specify his email address:

EXEC msdb.dbo.sp_add_operator 
@name = N'SQL DevOp', 
@enabled = 1, 
@email_address = N'$(email)', 
@weekday_pager_start_time = 080000, 
@weekday_pager_end_time = 170000, 
@pager_days = 62 ; 

Then, you can send an email notification to the operator:

DECLARE @body VARCHAR(4000) = 'The email is sent using sp_notify_operator from ' + @@SERVERNAME; 
EXEC msdb.dbo.sp_notify_operator 
@profile_name = N'AzureManagedInstance_dbmail_profile', 
@name = N'SQL DevOp', 
@subject = N'Azure SQL Instance - Test Notification', 
@body = @body; 


Again, the important thing here is that you need to specify AzureManagedInstance_dbmail_profile as the email profile that will be used to send the notifications.

Job notifications


Managed Instance enables you to notify an operator via email when a job succeeds or fails using the following script:

EXEC msdb.dbo.sp_update_job 
@job_name=N'My job name', 
@notify_level_email=2, 
@notify_level_page=2, 
@notify_email_operator_name=N'SQL DevOp' 

This script will configure SQL Agent job to notify operator if the job fails. If you run a job and it fails, the operator should get the email.

 

Important

If you experience any issue with emails - look at the article that is explaining how to troubleshoot Db Mail issues on Managed Instance.

Version history
Last update:
‎Nov 09 2020 09:43 AM
Updated by: