Script Your Database Mail Setup

Whenever I set up a new server, I make sure that SQL Server's Database Mail feature is set up so I can use it to email me notifications when critical alerts fire or when jobs fail. If you've walked through the Configure Database Mail wizard, you'll know that it's easy to miss a step and misconfigure mail, and you wonder why you don't get the email you expect. For this reason I have a PowerShell script I use to set it up.

Related: PowerShell the SQL Server Way

Once the SQLPS environment is loaded, I connect to the server by creating a new SMO Server object, pointing to the new server.

$inst = 'WS12SQL'$svr = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst

In the event that the Database Mail configuration option hasn't been enabled, I enable it. (It's an advanced option, so I need to enable that as well to be able to set the Mail option.)

# Enable Database Mail$svr.Configuration.ShowAdvancedOptions.ConfigValue = 1$svr.Configuration.DatabaseMailEnabled.ConfigValue = 1$svr.Configuration.Alter()

Then, assign the servers Mail object to a variable that I'll use a couple of times later in the script.

$mail = $svr.Mail

To configure Database Mail, I first need to set up a MailAccount object. The object requires a reference to the server's Mail object and the name of the account as parameters. I assign that new object to a variable called $acct, and then assign the account properties their proper values. Once that's done, I use the Create() method to create the account.

$acct = new-object ('Microsoft.SqlServer.Management.Smo.Mail.MailAccount') ($mail, 'sqldba')$acct.Description = 'Database Administrator Email'$acct.DisplayName = 'Database Administrator'$acct.EmailAddress = '[email protected]'$acct.ReplyToAddress = '[email protected]'$acct.Create()

The MailAccount object has a collection called MailServers, but there's no way to create a new MailServer object. The process of creating a MailAccount object creates a MailServer object, though, with default values. To reset that MailServer object properties to be useful, I set a variable to the first member of the MailServers collection (using an index value of 0), and then use the MailServer object Rename() method to set the name of the SMTP server I need to use. I also set the EnableSsl property to False, and set the UserName property to an empty string, which causes the SMTP connection to use Anonymous Authentication. Once the properties are set, I use the Alter() method for both the MailServer object and the MailAccount object to set those values.

$mlsrv = $acct.MailServers$mls = $mlsrv.Item(0)$mls.Rename('smtpsrv.example.com')$mls.EnableSsl = 'False'$mls.UserName = ''$mls.Alter()$acct.Alter()

The last thing I need to do is to set the profile, and I do that by creating a MailProfile object. That requires a reference to the server's Mail object, the name of the profile, and a description of the profile. I use the Create() method to create the profile, then use the AddAccount() method to add the account I'd created earlier. The AddAccount() method needs the name of the account and the sequence number of the account as parameters. Once I've done that I use the Alter() method on the profile to set it.

$mlp = new-object ('Microsoft.SqlServer.Management.Smo.Mail.MailProfile') ($mail, 'DBAMail', 'Database Administrator Mail Profile')$mlp.Create()$mlp.AddAccount('sqldba', 1)$mlp.Alter()

By running this script, I quickly set up Database Mail on a server, and now I can use the DBAMail profile in SQL Server agent to send email notifications when things go wrong.

Related: Gather SQL Server Instance Information with PowerShell