cmatskas

Configure Database Mail on SQL Server Azure VM using T-SQL

Christos Matskas -

Emails are an integral part for any SQL Administrator who wishes to stay on top of thins. Emails can be used to send alerts on job failures, run and report on diagnostics or return a query result set that your users may be interested in.

Today we will look on how to configure SQL Server to send emails from an Azure VM. However, there is little to no difference between this configuration and an on premise SQL Server. However, Azure doesn't offer smtp servers by default so you have to use a third party provider. For this example, we will use the excellent and free SendGrid

First we need to enable the email feature on SQL Server which is disabled by default.

USE master;
EXECUTE sp_configure 'show advanced options',1;
RECONFIGURE WITH OVERRIDE;
sp_configure 'Database Mail XPs',1;
RECONFIGURE;

Next, we will create a new email profile

-- Create a new mail profile:
USE msdb;
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'EmailAdmin',
@description = 'Profile for sending Automated DBA Notifications';

The next step will create the email account that will be used to send the emails. You will need to change the following as appropriate for your environment:

  • email address
  • smtp server
  • smtp credentials
  • smtp port
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQLAlerts',
@description = 'Account for Automated DBA Notifications',
@email_address = 'Your email addresss',
@display_name = 'SQL Alerts',
@mailserver_name = 'smtp.sendgrid.net',
@username = 'Your Username',
@password = 'Your Password',
@port = 25

The next code snippent will add the account to the email profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'EmailAdmin',
@account_name = 'SQLAlerts',
@sequence_number = 1

If configuring emails is all you need, then you could stop here and have a successful email profile on your SQL Server. However, if you want to use allow SQL Agent jobs to send out emails, then there are a couple more steps that we need to add. But we are nearly there, so stay with me.

The step below enables the SQL Agent Service to send email. Ensure that the databasemail_profile you use is the one you configured earlier.

USE [msdb];
EXEC msdb.dbo.sp_set_sqlagent_properties @databasemail_profile=N'EmailAdmin';

At this point, you will need to restart the SQL Agent Service for the changes to take effect.

Finally, you will need to create a SQL Agent Operator which wil used as the destination email address. This is where you want your emails to be delivered.

EXEC msdb.dbo.sp_add_operator @name=N'EmailOperator',
	@enabled=1, 
	@pager_days=0, 
	@email_address=N'Your destination email address'

And this is all you need for email to work on your SQL Server.

I hope this helps you a little bit.