THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Scripting Database Mail Configuration with Powershell and SMO

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.

 

Published Monday, August 23, 2010 9:36 AM by Jonathan Kehayias
Filed under:

Comments

 

AaronBertrand said:

Cool script JK.  I wish .Script() would end each statement with a statement terminator, though.

August 23, 2010 10:39 AM
 

Jonathan Kehayias said:

Aaron,

See the edit above that shows how to add the statement terminators in the Powershell call.  It would be better for SMO to do that but its a work around that gets you there.

August 23, 2010 11:32 AM
 

Chad Miller said:

@AaronBertrand

In PowerShell V2 you can do this to add a terminator plus new line to each batch:

$srv.Mail.Script() -join ";`n"

August 23, 2010 11:34 AM
 

Aaron Bertrand said:

Very cool, thanks for the quick edit.  Clearly I'm a PowerShell n00b but these little tidbits really help.

August 23, 2010 11:35 AM
 

David said:

Cool script. Although it only scripts out the profiles, not the mail Accounts.  $srv.Mail.get_Accounts() shows the mail accounts, but the .Script() method doesn'nt work for that.  Any ideas?

thanks

August 30, 2010 2:37 AM
 

Jonathan Kehayias said:

David,

I am not sure what you mean it doesn't script out the accounts.  The script generated for the example in this blog post included:

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'

which creates the account before the profile.

August 30, 2010 7:26 AM
 

David said:

Hi Jonathan

Thanks for the reply.  I see that now, thanks.  The item that is missing is the SMTP server name.  At my customer we have 2 accounts per profile, each account pointing to 2 different SMTP relay servers.  This does not appear in script.

Thanks

August 30, 2010 8:00 AM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement