This will be another post about SQL Server database administration. This time I will post about Database Mail setup on SQL Server database. This task was the one I always doing as Database Administrator to give me notification when some SQL Server agent job was failed. First thing we need to prepare before configure this Database Mail, was our mail server configuration, like the user name, password, email server address, port number, etc. After everything prepared, here we go to setup Database Mail 🙂
Database Mail Setup
Open SQL Server Management Studio and connect to Database Server Engine, from Object Explorer expand instance name, expand Management, right click on Database Mail and then click on Configure Database Mail.
Database Mail configuration welcome screen will appear, click Next
On the next screen, Leave the default options Set up Database Mail by performing the following tasks and then click Next
For the first time Database mail configuration, you will get following warning popup. Click Yes and then click Next
On New Profile page, type your email profile then click Add to add an email account
On New Database Mail Account fill the correct information for all field and then click OK
Back on the previous page, click Next
On Profile Security page, check the option below Public column to make this new mail profile as Public Profile and also set this profile as Default Profile by click drop down menu below Public Profile column and select Yes and then click Next
On Configure System Parameters page, leave all default options and then click Next
Verify all action the wizard about to perform, then click Finish to execute this action
When you see all action was complete and its status was Success click OK to close this wizard.
Test Database Mail Configuration
We have to test if Database Mail was successfully configured by right click on Database Mail and click Send Test E-Mail and you will get the following page. Type email address that will used for testing and then click Send Test E-Mail.
On the screen below, click OK to process send this email
Go to your email and check if this email has arrived. If the email doesn’t arrive, check your email server to make sure that you have properly configured your Database Mail.
Setup SQL Agent Job to Send Email Notification When Job Failed
From SSMS Object Explorer, right click on SQL Server Agent and click Properties
On SQL Server Agent Properties, Click on Alert System under Select a page section, under Mail Session, check on Enable mail profile, then select the correct mail profile from Mail Profile drop down and then click OK
We have to create an operator to receive email notification when SQL Server Agent job has failed, by expand SQL Server Agent, right click on Operators and click New Operator
On New Operator page, type operator name and recipient email address on Email Name field. You can type more than one operator by type every recipient email addresses separate by semi-colon ‘;’ and then click OK
Expand Jobs folder under SQL Server Agent, right click on job name and click Properties
On Job Properties page, click Notification page under Select a page, then mark the check box beside Email and from drop down menu, select correct operator name you’ve just created. For testing purposes, you can change the option on the to When job completes and then click OK
When the job fails, you should receive an email similar to the following email.
That’s it. Database mail has configured properly. Now you don’t have to check every time to your Database Server if all job failed or not.