Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

Configuring database mail on SQL Server 2008 R2

Discussion in 'Installation and Administration' started by Peter Schmitz, Oct 1, 2012.

  1. Peter Schmitz

    Peter Schmitz Administrator Staff Member

    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:

    database mail 1.png
    Right-click it to bring up the Database Mail menu:

    database mail 2.png

    Click on "Configure Database Mail". This fires up the "Database Mail Configuration Wizard":

    database mail 3.png

    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):

    database mail 4.png

    Click "Next". If you are running a default installation, you will now be prompted on whether you want to enable Database Mail:

    database mail 5.png

    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.

    database mail 6.png
    Click "OK".

    database mail 7.png
    You could add additional profiles if you want. To do so, click the "Add" button on the right. Otherwise, click "Next":

    database mail 8.png

    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:

    database mail 9.png

    A brief oversight of the individual options can be found in the table below:

    OptionExplanation
    Account Retry AttemptsNumber 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 ExtensionsProhibited 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 LevelDetermines 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:

    database mail 10.png

    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:

    database mail 11.png

    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":

    database mail 12.png

    You will be prompted to fill in a recipient address, and you can configure the subject and body of the mail:

    database mail 13.png

    Click the "Send Test E-Mail" button to have the message be sent to the queue of the SMTP server:

    database mail 14.png

    If all went well, you will receive an E-mail that carries all the configured options from this tutorial:

    database mail 15.png

    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.

Share This Page

Sponsored link: