Setting up Database Mail in SQL Server 2005 and 2008 is a common task that is performed post server setup. However, if you just took over a fairly large environment and faced having to configure Database Mail on dozens, maybe hundreds of servers, SQL Server Management Studio doesn’t offer much in the way of making this a scriptable task.
Unlike most of the other wizards and designers in SQL Server Management Studio, the Database Mail configuration wizard doesn’t have a Script button to generate the script that will be executed against SQL Server to commit the configuration settings in the server. I find this really odd because Management Studio is built on top of SMO and SMO has the necessary Script() method for the SqlMail object that can generate the scripts. Things being what they are, a quick solution to the problem of having to configure multiple servers the same way for Database Mail is to configure one server, and then generate the configuration script using SMO and Powershell.
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
#Set the server to script from
$ServerName = "ServerName";
#Get a server object which corresponds to the default instance
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $ServerName
#Script Database Mail configuration from the server
$srv.Mail.Script();
The above Powershell script will generate an output similar to the following (additional formatting has been done to prevent text wrapping in this blog post):
EXEC msdb.dbo.sysmail_configure_sp
@parameter_name=N'AccountRetryAttempts',
@parameter_value=N'1',
@description=N'Number of retry attempts for a mail server'
EXEC msdb.dbo.sysmail_configure_sp
@parameter_name=N'AccountRetryDelay',
@parameter_value=N'60',
@description=N'Delay between each retry attempt to mail server'
EXEC msdb.dbo.sysmail_configure_sp
@parameter_name=N'DatabaseMailExeMinimumLifeTime',
@parameter_value=N'600',
@description=N'Minimum process lifetime in seconds'
EXEC msdb.dbo.sysmail_configure_sp
@parameter_name=N'DefaultAttachmentEncoding',
@parameter_value=N'MIME',
@description=N'Default attachment encoding'
EXEC msdb.dbo.sysmail_configure_sp
@parameter_name=N'LoggingLevel',
@parameter_value=N'2',
@description=N'Database Mail logging level: normal - 1, extended - 2 (default), verbose - 3'
EXEC msdb.dbo.sysmail_configure_sp
@parameter_name=N'MaxFileSize',
@parameter_value=N'1000000',
@description=N'Default maximum file size'
EXEC msdb.dbo.sysmail_configure_sp
@parameter_name=N'ProhibitedExtensions',
@parameter_value=N'exe,dll,vbs,js',
@description=N'Extensions not allowed in outgoing mails'
EXEC msdb.dbo.sysmail_add_account_sp
@account_name=N'DatabaseMailAccount',
@email_address=N'SQLServers@domain.com',
@display_name=N'Database Mail Account',
@replyto_address=N'no-reply@domain.com'
EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name=N'DatabaseMailProfile'
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name=N'DatabaseMailProfile',
@account_name=N'DatabaseMailAccount',
@sequence_number=1
EXEC msdb.dbo.sysmail_add_principalprofile_sp
@principal_name=N'guest',
@profile_name=N'DatabaseMailProfile',
@is_default=0
This script can then be used to create an identical configuration of DatabaseMail on all of the SQL Server 2005/2008 servers in the environment.
EDIT:
Aaron's comment below makes a good point about the scripting, and while the SMO Script() function doesn't generate statement terminators (see Aaron's blog post Ladies and Gentlemen Start your Semi-Colons for why this matters) it is possible to account for that in the Powershell script above. The only thing required is to change the last line to:
[system.string]::join(";" + [System.Environment]::NewLine, $srv.Mail.Script())
Thanks for pointing that out Aaron.