Database Mail and SQL Agent Mail setup by using a script
April 24th 2009 14:23
Database Mail and SQL Agent Mail setup by using a script
Problem
I was in the middle of upgrading many servers to SQL Server 2005 and SQL Server 2008. As I was setting up Database Mail and the SQL Server Agent Alert System I wanted to create a script that I could use to do this instead of having to do this manually through the GUI. In this tip I provide a script with a few parameters that need to be setup to configure and turn on both of these features.
Solution
SQL 2005 and later versions have this new option to use SMTP to send email from SQL Server and this can be setup using the GUI or by using scripts. In this tip, I go through the steps to set this up by only using scripts.
----------------------------- ----------------------------- ----------------------
The following breaks down the overall script into four sections, so I can explain what each section does. The entire script can be download from here which I encourage you to use instead of having to piece the sections together.
Section 1 - Declare and Set Values
Before you run the script, you will need to fill out some information for your environment. The four values that need to be set are:
@SMTPServer - You will need to get SMTP server that you want to use to send email out.
@AdminEmail - After setup, the script will send test email to this email address
@DomainName - This will be part of SQL Server Email Name
@replyToEmail - this is how you will see for reply to email when you get the email.
use master
DECLARE @SMTPServer VARCHAR(100)
DECLARE @AdminEmail VARCHAR(100)
DECLARE @DomainName VARCHAR(100)
DECLARE @replyToEmail VARCHAR(100)
SET @SMTPServer = 'smtp.mydomain.com'
SET @AdminEmail = 'klee@mydomain.com'
SET @DomainName = '@mydomain.com'
SET @replyToEmail = 'sqlserver@mydomain.com'
----------------------------- ----------------------------- ----------------------
Section 2 - Change Global Configuration Settings
In order to setup Database Mail and SQL Agent mail, you will need to turn on two global configuration settings.
exec sp_configure 'show advanced options', 1
exec sp_configure 'Database Mail XPs', 1
exec sp_configure 'Agent XPs',1
RECONFIGURE WITH OVERRIDE
----------------------------- ----------------------------- ----------------------
Section 3 - Database Mail Configuration
This will setup database mail. It does not require the SQL Server service to be restarted. At the end of the script, it will send a Test Mail.
The email address that will be configured for Database Mail is based on the server name. So if your server name is "ProdServer1" and your domain is "MyDomain.com" the email address that this script creates is "ProdServer1@MyDomain.com". If you want something different you will need to modify the script.
Also, this uses "anonymous authentication" for the mail server. If you want to provide other parameters take a look at these system stored procedures; sysmail_add_account_sp, sysmail_add_profile_sp, sysmail_add_profileaccount_sp and sysmail_add_principalprofile_ sp.
declare @servername varchar(100)
declare @email_address varchar(100)
declare @display_name varchar(100)
declare @testmsg varchar(100)
set @servername = replace(@@servername,'\','_')
set @email_address = @servername @DomainName
set @display_name = 'MSSQL - ' @servername
set @testmsg = 'Test from ' @servername
IF EXISTS(SELECT * from msdb.dbo.sysmail_profile)
PRINT 'DB mail already configured'
ELSE
BEGIN
--Create database mail account.
exec msdb.dbo.sysmail_add_account_ sp
@Account_name = 'SQLMail Account'
, @description = 'Mail account for use by all database users.'
, @email_address = @email_address
, @replyto_address = @replyToEmail
, @display_name = @display_name
, @mailserver_name = @SMTPServer
--Create global mail profile.
exec msdb.dbo.sysmail_add_profile_ sp
@profile_name = 'SQLMail Profile'
, @description = 'Mail profile setup for email from this SQL Server'
--Add the account to the profile.
exec msdb.dbo.sysmail_add_profilea ccount_sp
@profile_name = 'SQLMail Profile'
, @Account_name = 'SQLMail Account'
, @sequence_number=1
--grant access to the profile to all users in the msdb database
use msdb
exec msdb.dbo.sysmail_add_principa lprofile_sp
@profile_name = 'SQLMail Profile'
, @principal_name = 'public'
, @is_default = 1
END
--send a test message.
exec msdb..sp_send_dbmail
@profile_name = 'SQLMail Profile',
@recipients = @AdminEmail,
@subject = @testmsg,
@body = @testmsg
EXEC msdb.dbo.sysmail_help_profile _sp
----------------------------- ----------------------------- ----------------------
Section 4 - SQL Agent Mail Configuration
Once Database Mail is setup, you now can enable SQL Agent notifications. To take affect, this may require the SQL Agent Service to be restarted.
In order to send out email from SQL Agent you need to configure the Alert System. The script below will do this. In uses the extended stored procedure "xp_instance_regwrite" to write two values to the registry. The first setting just tells the SQL Agent Alert System to use Database Mail as the email option and the second setting tells the SQL Agent Alert System which mail profile to set. The value "SQLMail Profile" was the name that was given in Section 3.
-- Enabling SQL Agent notification
USE [msdb]
EXEC msdb.dbo.sp_set_sqlagent_prop erties
@email_save_in_sent_folder=1
EXEC master.dbo.xp_instance_regwri te
N'HKEY_LOCAL_MACHINE'
, N'SOFTWARE\Microsoft\MSSQLSer ver\SQLServerAgent'
, N'UseDatabaseMail'
, N'REG_DWORD'
, 1
EXEC master.dbo.xp_instance_regwri te
N'HKEY_LOCAL_MACHINE'
, N'SOFTWARE\Microsoft\MSSQLSer ver\SQLServerAgent'
, N'DatabaseMailProfile'
, N'REG_SZ'
, N'SQLMail Profile'
----------------------------- ----------------------------- ----------------------
There are many things that you can automate by scripting when it comes to SQL Server related tasks. This saves a lot of time if you need to do this over and over again as well as to ensure things are kept consistent.
Problem
I was in the middle of upgrading many servers to SQL Server 2005 and SQL Server 2008. As I was setting up Database Mail and the SQL Server Agent Alert System I wanted to create a script that I could use to do this instead of having to do this manually through the GUI. In this tip I provide a script with a few parameters that need to be setup to configure and turn on both of these features.
Solution
SQL 2005 and later versions have this new option to use SMTP to send email from SQL Server and this can be setup using the GUI or by using scripts. In this tip, I go through the steps to set this up by only using scripts.
----------------------------- ----------------------------- ----------------------
The following breaks down the overall script into four sections, so I can explain what each section does. The entire script can be download from here which I encourage you to use instead of having to piece the sections together.
Section 1 - Declare and Set Values
Before you run the script, you will need to fill out some information for your environment. The four values that need to be set are:
@SMTPServer - You will need to get SMTP server that you want to use to send email out.
@AdminEmail - After setup, the script will send test email to this email address
@DomainName - This will be part of SQL Server Email Name
@replyToEmail - this is how you will see for reply to email when you get the email.
use master
DECLARE @SMTPServer VARCHAR(100)
DECLARE @AdminEmail VARCHAR(100)
DECLARE @DomainName VARCHAR(100)
DECLARE @replyToEmail VARCHAR(100)
SET @SMTPServer = 'smtp.mydomain.com'
SET @AdminEmail = 'klee@mydomain.com'
SET @DomainName = '@mydomain.com'
SET @replyToEmail = 'sqlserver@mydomain.com'
----------------------------- ----------------------------- ----------------------
Section 2 - Change Global Configuration Settings
In order to setup Database Mail and SQL Agent mail, you will need to turn on two global configuration settings.
exec sp_configure 'show advanced options', 1
exec sp_configure 'Database Mail XPs', 1
exec sp_configure 'Agent XPs',1
RECONFIGURE WITH OVERRIDE
----------------------------- ----------------------------- ----------------------
Section 3 - Database Mail Configuration
This will setup database mail. It does not require the SQL Server service to be restarted. At the end of the script, it will send a Test Mail.
The email address that will be configured for Database Mail is based on the server name. So if your server name is "ProdServer1" and your domain is "MyDomain.com" the email address that this script creates is "ProdServer1@MyDomain.com". If you want something different you will need to modify the script.
Also, this uses "anonymous authentication" for the mail server. If you want to provide other parameters take a look at these system stored procedures; sysmail_add_account_sp, sysmail_add_profile_sp, sysmail_add_profileaccount_sp and sysmail_add_principalprofile_ sp.
declare @servername varchar(100)
declare @email_address varchar(100)
declare @display_name varchar(100)
declare @testmsg varchar(100)
set @servername = replace(@@servername,'\','_')
set @email_address = @servername @DomainName
set @display_name = 'MSSQL - ' @servername
set @testmsg = 'Test from ' @servername
IF EXISTS(SELECT * from msdb.dbo.sysmail_profile)
PRINT 'DB mail already configured'
ELSE
BEGIN
--Create database mail account.
exec msdb.dbo.sysmail_add_account_ sp
@Account_name = 'SQLMail Account'
, @description = 'Mail account for use by all database users.'
, @email_address = @email_address
, @replyto_address = @replyToEmail
, @display_name = @display_name
, @mailserver_name = @SMTPServer
--Create global mail profile.
exec msdb.dbo.sysmail_add_profile_ sp
@profile_name = 'SQLMail Profile'
, @description = 'Mail profile setup for email from this SQL Server'
--Add the account to the profile.
exec msdb.dbo.sysmail_add_profilea ccount_sp
@profile_name = 'SQLMail Profile'
, @Account_name = 'SQLMail Account'
, @sequence_number=1
--grant access to the profile to all users in the msdb database
use msdb
exec msdb.dbo.sysmail_add_principa lprofile_sp
@profile_name = 'SQLMail Profile'
, @principal_name = 'public'
, @is_default = 1
END
--send a test message.
exec msdb..sp_send_dbmail
@profile_name = 'SQLMail Profile',
@recipients = @AdminEmail,
@subject = @testmsg,
@body = @testmsg
EXEC msdb.dbo.sysmail_help_profile _sp
----------------------------- ----------------------------- ----------------------
Section 4 - SQL Agent Mail Configuration
Once Database Mail is setup, you now can enable SQL Agent notifications. To take affect, this may require the SQL Agent Service to be restarted.
In order to send out email from SQL Agent you need to configure the Alert System. The script below will do this. In uses the extended stored procedure "xp_instance_regwrite" to write two values to the registry. The first setting just tells the SQL Agent Alert System to use Database Mail as the email option and the second setting tells the SQL Agent Alert System which mail profile to set. The value "SQLMail Profile" was the name that was given in Section 3.
-- Enabling SQL Agent notification
USE [msdb]
EXEC msdb.dbo.sp_set_sqlagent_prop erties
@email_save_in_sent_folder=1
EXEC master.dbo.xp_instance_regwri te
N'HKEY_LOCAL_MACHINE'
, N'SOFTWARE\Microsoft\MSSQLSer ver\SQLServerAgent'
, N'UseDatabaseMail'
, N'REG_DWORD'
, 1
EXEC master.dbo.xp_instance_regwri te
N'HKEY_LOCAL_MACHINE'
, N'SOFTWARE\Microsoft\MSSQLSer ver\SQLServerAgent'
, N'DatabaseMailProfile'
, N'REG_SZ'
, N'SQLMail Profile'
----------------------------- ----------------------------- ----------------------
There are many things that you can automate by scripting when it comes to SQL Server related tasks. This saves a lot of time if you need to do this over and over again as well as to ensure things are kept consistent.
| 14 |
| Vote |
Add Comments
Read More