Sending emails in Azure SQL Managed Instance

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.