HOWTO: Enable and Configure Database Mail, Add SQL Agent Operator

Why
If you are a DBA and want to receive ANY kind of alert/notification email from the SQL Server, you need to setup the Database Mail first, just like you have to configure Outlook to connect with Exchange before receiving any email.

Who
DBAs who want to get notified for any pre-configured alerts or job notifications

How
Prerequisites

• External SMTP mail server, or Microsoft exchange server

Steps

• Creates a Database Mail Profile
• Creates a Database Mail Account
• Adds SQL Agent Operator
• Enables SQL Agent to use the Database Mail Profile (needs to manually restart SQL Agent)

When
Run this setup only for initial SQL Server configuration.
Normally there is no need to re-run afterwards

Where
SQL Server 2005/2008

Reference: this code was taken and modified from Jonathan's "Configuring SQL Server 2005/2008 Database Mail"

What

USE [master]
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE 
GO
----------------------------------------------------------------------------------------------------------------
-- Create a New Mail Profile for Notifications
EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'DBA_Notifications',
    @description = 'Profile for sending Automated DBA Notifications'
GO
-- Set the New Profile as the Default
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'DBA_Notifications',
    @principal_name = 'public', @is_default = 1 ;
GO
-- Create an Account for the Notifications
EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'SQLMonitor',
    @description = 'Account for Automated DBA Notifications',
    @email_address = 'sqlnotify@domain.com',  -- ************ Change This ************
    @display_name = 'SQL Monitor',
    @mailserver_name = 'exchange.domain.com'-- ************ Change This ************  
GO
-- Add the Account to the Profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'DBA_Notifications',
    @account_name = 'SQLMonitor', @sequence_number = 1
GO

----------------------------------------------------------------------------------------------------------------
--Adds Operator for Job Notification
USE [msdb]
GO

EXEC msdb.dbo.sp_add_operator @name = N'SQLDBAs', @enabled = 1,
    @weekday_pager_start_time = 90000, @weekday_pager_end_time = 180000,
    @saturday_pager_start_time = 90000, @saturday_pager_end_time = 180000,
    @sunday_pager_start_time = 90000, @sunday_pager_end_time = 180000,
    @pager_days = 0, @email_address = N'sqlnotify@domain.com',-- ************ Change This ************
    @category_name = N'[Uncategorized]'
GO

----------------------------------------------------------------------------------------------------------------
-- Enable SQL Server Agent to use Database Mail profile (in Alert System tab)
-- restart SQL Agent after
USE [msdb]
GO
EXEC MASTER.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile',
    N'REG_SZ', N'DBA_Notifications'
GO