Database mail can be a very useful tool in case you administrate one or more SQL Servers, as you could configure the server to automatically send out mails upon completion (either succesful or failure) of jobs, to notify you of alerts you configure, etc.
To configure Database mail, log onto the server using SQL Server Management Studio (SSMS), and then expand the server and the "Management" folder below it:
Right-click it to bring up the Database Mail menu:
Click on "Configure Database Mail". This fires up the "Database Mail Configuration Wizard":
Click "Next". You will now be prompted to select a setup option. In our case, we will set up Database Mail from scratch, so we use the first option (default):
Click "Next". If you are running a default installation, you will now be prompted on whether you want to enable Database Mail:
Click "Yes". In the "New Database Mail Account" screen, you will can specify a name and description for the for the mail account configuration, and you will need to fill in the E-mail address and SMTP you want SQL Server to send the mails from. When in doubt, your network administrator should be able to provide you with those. You can also provide the security settings.
In the screenshot, the "Anonymous Authentication" option is used. I would not recommend this in a production environment setting.
Click "OK".
You could add additional profiles if you want. To do so, click the "Add" button on the right. Otherwise, click "Next":
You now need to decide whether you want to configure the mail account as public or private. Books Online (BOL) has the following to say about public or private profiles:
You can specifically state a public or private profile, or simply click "Next" to accept the profile to become public. Now you will be able to add some additional configuration settings if you want to:
A brief oversight of the individual options can be found in the table below:
When you are satisfied with the changes you made, click "Next". The wizard will show you an oversight of the choices you made:
Click "Finish" to implement your Database Mail according to your specifications. When it is done, you will receive a screen showing you whether the setup was successful:
Click "Close".
To test the new Database Mail, right-click "Database Mail" again, similar to step 2 from before. This time, select the option to "Send Test Email":
You will be prompted to fill in a recipient address, and you can configure the subject and body of the mail:
Click the "Send Test E-Mail" button to have the message be sent to the queue of the SMTP server:
If all went well, you will receive an E-mail that carries all the configured options from this tutorial:
In summary, setting up Database Mail is a quick and easy process. In future articles we will put it to good use when we configure operators and use Database Mail to send mails to these operators in case of successful or failed operations.
To configure Database mail, log onto the server using SQL Server Management Studio (SSMS), and then expand the server and the "Management" folder below it:
Click on "Configure Database Mail". This fires up the "Database Mail Configuration Wizard":
Click "Next". You will now be prompted to select a setup option. In our case, we will set up Database Mail from scratch, so we use the first option (default):
Click "Next". If you are running a default installation, you will now be prompted on whether you want to enable Database Mail:
Click "Yes". In the "New Database Mail Account" screen, you will can specify a name and description for the for the mail account configuration, and you will need to fill in the E-mail address and SMTP you want SQL Server to send the mails from. When in doubt, your network administrator should be able to provide you with those. You can also provide the security settings.
In the screenshot, the "Anonymous Authentication" option is used. I would not recommend this in a production environment setting.
You now need to decide whether you want to configure the mail account as public or private. Books Online (BOL) has the following to say about public or private profiles:
You can specifically state a public or private profile, or simply click "Next" to accept the profile to become public. Now you will be able to add some additional configuration settings if you want to:
A brief oversight of the individual options can be found in the table below:
| Option | Explanation |
|---|---|
| Account Retry Attempts | Number of retry attempts for a mail server to send e-mail. |
| Account Retry Delay (seconds) | Delay between attempts for a mail server to send e-mail in seconds. |
| Maximum File Size (Bytes) | Maximum file size in Bytes for an attachment for a mail serer to send e-mail. |
| Prohibited Attachment File Extensions | Prohibited file extensions for a mail server to send e-mail. |
| Database Mail Executable Minimum Lifetime (seconds) | Minimum lifetime for Database Mail executable in seconds. |
| Logging Level | Determines which events are written to the Database Mail event log. |
When you are satisfied with the changes you made, click "Next". The wizard will show you an oversight of the choices you made:
Click "Finish" to implement your Database Mail according to your specifications. When it is done, you will receive a screen showing you whether the setup was successful:
Click "Close".
To test the new Database Mail, right-click "Database Mail" again, similar to step 2 from before. This time, select the option to "Send Test Email":
You will be prompted to fill in a recipient address, and you can configure the subject and body of the mail:
Click the "Send Test E-Mail" button to have the message be sent to the queue of the SMTP server:
If all went well, you will receive an E-mail that carries all the configured options from this tutorial:
In summary, setting up Database Mail is a quick and easy process. In future articles we will put it to good use when we configure operators and use Database Mail to send mails to these operators in case of successful or failed operations.