Sending emails in Azure SQL Managed Instance

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 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 there can be only one email profile and it must be called 'AzureManagedInstance_dbmail_profile'. Otherwise, Managed Instance will be unable to send emails using this profile.

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.