Today I was modifying the configuration of database mail on one of my company’s SQL Servers, so I decided to configure the Database mail on another server where it wasn’t yet configured. Being something made once every five years or more, I decided to publish the steps to do it, this way I can repeat the operation on my customers servers to be sure someone is alerted if something on the SQL Server goes wrong.
My server is a SQL 2008 server, but the configuration is identical for SQL 2005.
Open the Server Management folder on SQL Management Studio and select Database Mail.
If it is the first configuration, leave the option as selected, elsewhere select the proper option and click NEXT.
Give the profile a proper name and description, Database Mail can be configured for other tasks then the simple Job notification, and you can have more than a single profile for the different tasks.
Click on the ADD button to create a mail account that has to be used to send the e-mails, pay attention, if you already have an account the Window above does not appear automatically, it appears first a window to select an existing account with a button that allows you to create a new one that opens this window.
Fill in the account data with a name and a description, set the e-mail addresses and the SMTP server name and then fill the authentication credentials if necessary or leave the anonymous authentication (for example if the company SMTP server is configured to allow so).
Then you need to set the security data for the profile, to be able to send e-mails from the SQL Server Agent, it’s better to create a public profile.
After clicking the NEXT button, you’ll see two verification and execution confirm windows, the latter shown above.
Now select the Operators Folder inside the SQL Server Agent Objects folder and create a new Operator.
We just need to set the operator name and the e-mail where to send notifications, unless you want to set also Net Send and Pager for the operator.
In Every backup, Maintenance or Script Execution Job set inside the Agent, you need to set up the Operator, Check the E-Mail notification action and set wether to notify Job Completion or Just Job Error or Job Success.
Now we need to right click the SQL Server Agent icon and select the Alert System, activating the Database mail profile we generated. If you don’t do this operation, the Agent ignores E-Mail notifications and does nothing, it does not give any error or warning message.
After the configuration of the Alert System you must Restart SQL Server Agent to activate the E-mail notification system for Jobs and Alerts.