SQL Server 2012 Database Mail Explained

 

 

Database mail in SQL Server 2012 allows database administrators to send emails directly from SQL Server. You can create applications that send emails to users directly. This is done natively without using any additional SMTP add ins and SQL Server 2012 database mail even supports high availability by allowing more than one mail server to be setup. Within SQL this is done by running the “sp_send_dbmail” to send an email message programmatically. You may also change the attachment size by running the sysmail_configure_sp procedure. This will also allow dbas configure allowed and disallowed file extensions. Database mail configuration for SQL Server 2012 is saved in msdb database. Also, you may use SQL agent to send mail using database mail.

 

 

Managing Database Mail accounts and Profile within SQL Server 2012

To send an email message to a SMTP server, your database mail account must have all necessary configuration data. At minimal you must have one SMTP server configured for an account. A lot environment will allow you to send emails internally without authenticating. In some case, it will require authentication if you want to relay through a SMTP server. You can use “Anonymous Authentication”, if your SMTP mail server doesn’t require any authentication. “Basic Authentication” is used for Database mail that uses a simple username / password. At last, “Windows Authentication” allows authentication via a windows account. This can be a local account or active directory account.

All the account configuration are stored in msdb as mentioned earlier. This includes:

In SQL Server 2012, Profile is essentially a group of database mail accounts. This allows database administrators and system administrators to build redundant and highly available database mail infrastructure. If one of the SMTP server is down, SQL Server will automatically failover and try to send the email to an alternate SMTP server within the database mail profile.

 

 

How to Create a Database Mail Account

Prior to creating a SQL Server 2012 database mail account, you must have the name of your SMTP server, port number and credentials. Note that this can be obtained from your system administrator, Exchange administrator or IT manager. In some cases you will not need to authenticate it the mail server. By default port number is usually 25.

To use transactSQL use below code:

EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = 'database Administrator',
@description = 'dba smtp account',
@email_address = 'dba@tinydomain.com',
@mailserver_name = 'smtp.tinydomain.com' ;

 

 

How to create a Database Mail profile

Ensure that you have a list of database mail accounts you want to use within the database mail profile.