Skip to content

Database-Mail.md

Latest commit

 

History

History
249 lines (189 loc) · 6.3 KB

Database-Mail.md

File metadata and controls

249 lines (189 loc) · 6.3 KB

Database Mail

Purpose

Document the setup and configuration of Database Mail, which is the notification infrastructure for SQL Server Agent job alerts, operator emails, and custom T-SQL notifications. Every Agent job notification and alert depends on Database Mail being configured correctly before it will work.

Prerequisites

  • Database Mail XP surface area feature enabled (disabled by default)
  • Network access from the SQL Server service account to an SMTP relay or mail server
  • An operator email address to test against
  • Appropriate firewall rules allowing outbound SMTP (default port 25, or 587 for TLS-authenticated SMTP)

Enable Database Mail XP

Database Mail is a surface area feature that must be enabled before configuration:

EXEC [sys].[sp_configure] 'Database Mail XPs', 1;
RECONFIGURE;

Verify it is enabled:

SET NOCOUNT ON;

SELECT
    [name],
    [value_in_use]
FROM [sys].[configurations]
WHERE [name] = 'Database Mail XPs';

Setup via dbatools

$ErrorActionPreference = 'Stop'

# 1 — Create a mail account (connects to the SMTP server)
$splatAccount = @{
    SqlInstance    = $instance
    Account        = 'DBA-Alerts'
    Description    = 'Primary SMTP account for SQL Server Agent notifications'
    EmailAddress   = 'sqlalerts@yourdomain.com'
    ReplyToAddress = 'sqlalerts@yourdomain.com'
    DisplayName    = 'SQL Server Alerts'
    MailServer     = 'smtp.yourdomain.com'
    EnableException = $true
}
New-DbaDbMailAccount @splatAccount

# 2 — Create a profile and associate the account
$splatProfile = @{
    SqlInstance     = $instance
    Profile         = 'DBA-Alerts'
    Description     = 'Default profile for SQL Server Agent notifications'
    Account         = 'DBA-Alerts'
    EnableException = $true
}
New-DbaDbMailProfile @splatProfile

T-SQL Fallback (Manual Setup)

Use if dbatools is unavailable or if fine-grained control is needed:

SET NOCOUNT ON;

-- Step 1: Create account
EXEC [msdb].[dbo].[sysmail_add_account_sp]
    @account_name        = N'DBA-Alerts',
    @description         = N'Primary SMTP account for SQL Server Agent notifications',
    @email_address       = N'sqlalerts@yourdomain.com',
    @replyto_address     = N'sqlalerts@yourdomain.com',
    @display_name        = N'SQL Server Alerts',
    @mailserver_name     = N'smtp.yourdomain.com';

-- Step 2: Create profile
EXEC [msdb].[dbo].[sysmail_add_profile_sp]
    @profile_name = N'DBA-Alerts',
    @description  = N'Default profile for SQL Server Agent notifications';

-- Step 3: Associate account with profile (sequence_number = priority order)
EXEC [msdb].[dbo].[sysmail_add_profileaccount_sp]
    @profile_name    = N'DBA-Alerts',
    @account_name    = N'DBA-Alerts',
    @sequence_number = 1;

-- Step 4: Grant access to the profile for the msdb public role
EXEC [msdb].[dbo].[sysmail_add_principalprofile_sp]
    @profile_name  = N'DBA-Alerts',
    @principal_name = N'public',
    @is_default    = 1;

Multi-Account Failover Profile

For environments requiring reliability, configure a second account as a fallback. Database Mail will try accounts in sequence_number order:

SET NOCOUNT ON;

-- Add a secondary SMTP account
EXEC [msdb].[dbo].[sysmail_add_account_sp]
    @account_name    = N'DBA-Alerts-Fallback',
    @description     = N'Fallback SMTP account',
    @email_address   = N'sqlalerts@yourdomain.com',
    @display_name    = N'SQL Server Alerts',
    @mailserver_name = N'smtp-backup.yourdomain.com';

-- Associate with the same profile at priority 2
EXEC [msdb].[dbo].[sysmail_add_profileaccount_sp]
    @profile_name    = N'DBA-Alerts',
    @account_name    = N'DBA-Alerts-Fallback',
    @sequence_number = 2;

SQL Server Agent Operator Configuration

Agent job notifications require an operator. Create one after the profile is configured:

$splatOp = @{
    SqlInstance     = $instance
    Operator        = 'DBA-Team'
    EmailAddress    = 'dba@yourdomain.com'
    EnableException = $true
}
New-DbaAgentOperator @splatOp

Then configure SQL Server Agent to use the Database Mail profile:

EXEC [msdb].[dbo].[sp_set_sqlagent_properties]
    @email_save_in_sent_folder = 1,
    @databasemail_profile      = N'DBA-Alerts';

-- Enable Agent mail subsystem
EXEC [master].[dbo].[xp_instance_regwrite]
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
    N'UseDatabaseMail',
    N'REG_DWORD',
    1;

Alternatively, set this in SSMS: SQL Server Agent > Properties > Alert System > Enable mail profile.

Send a Test Email

EXEC [msdb].[dbo].[sp_send_dbmail]
    @profile_name  = N'DBA-Alerts',
    @recipients    = N'dba@yourdomain.com',
    @subject       = N'Database Mail Test',
    @body          = N'Database Mail is configured and working.';
# Verify the message was queued and sent
$splatMail = @{
    SqlInstance     = $instance
    EnableException = $true
}
Get-DbaDbMailLog @splatMail |
    Select-Object -First 10 LogDate, EventType, Description

Troubleshooting

Check the mail log

SET NOCOUNT ON;

-- Recent events (errors appear here first)
SELECT TOP 50
    [log_date],
    [event_type],
    [description]
FROM [msdb].[dbo].[sysmail_event_log]
ORDER BY [log_date] DESC;

Check for failed items

SET NOCOUNT ON;

SELECT TOP 20
    [sent_date],
    [recipients],
    [subject],
    [sent_status],
    [last_mod_date]
FROM [msdb].[dbo].[sysmail_faileditems]
ORDER BY [last_mod_date] DESC;

Check the mail queue

SET NOCOUNT ON;

-- Items still in queue (not yet sent)
SELECT
    [mailitem_id],
    [sent_date],
    [recipients],
    [subject],
    [sent_status]
FROM [msdb].[dbo].[sysmail_allitems]
WHERE [sent_status] IN ('unsent', 'retrying')
ORDER BY [sent_date] DESC;

Restart the mail queue if stuck

EXEC [msdb].[dbo].[sysmail_start_sp];

If messages are stuck in unsent status and the log shows no send attempts, the Database Mail external program (DatabaseMail.exe) may have stopped. Running sysmail_start_sp restarts it without restarting SQL Server.

Related Documents