Back in Pre SQL Server 2005 editions of SQL Server there was nice feature called SQL Mail which basically allowed users to send email from within database server. But that system has many dependencies and major one was requirement of Microsoft Exchange… thus usage was severely limited to enterprise customers who has resources to setup such a system. When MSFT released SQL Server 2005 they introduced new feature called Database Mail which replaced SQL Mail … and they removed all kind of external dependencies. Now all you had to do was to provide just SMTP server and you are all set !!
I always thought that I have blogged about process to setup Database mail but it appears that I never did so today I am going to write about step-by-step process to setup / configure database mail. Since we have moved on from 2005 to 2008 to 2008R2 to 2012 …. I am going to use SQL Server 2012 edition but process remains same for previous editions too.
Database Mail can be found under “Management” group in SQL Server Management Studio. To configure Database Mail right click on it and select “Configure Database Mail” which will start configuration wizard. It will show bunch of options like “Set up Database Mail”, “Manage”, “Change system parameters”. Since we have not setup this before we are going to select first option, “Setup Database Mail by performing following tasks”. And with that selected click next.
 |
 |
 |
By default Database Mail option is not enabled, and if that is the case you will be prompted to enable it. Which essentially means running
sp_configure 'Database Mail XPs', 1; GO RECONFIGURE GO
Once that is done, you will be presented with Profile creation page where you will have to create new profile by providing profile name and description (optional). Next step is to add Database mail account for that profile. I am using GMail for this demo and great thing is it works just fine !! Just provide all required information and in SMTP Authentication part select basic authentication and fill up your email address as user name and email login password as password. Now if you have SMTP server then you need to know all required items for that server like server address, port, if ssl is being used or not and authentication methods.
Once that is added, you will see that in your mail profile you have new SMTP account. As a side note, you can have more than one SMTP account for one profile, and it is particularly helpful if you see frequent delays or timeouts in sending emails.
Next step is to select profile security, you can either select public or private profile. Different between them two is, Public profile is accessible to anyone who has access to MSDB database. Where as Private profile can be accessed by certain users and roles. You can also make any profile as default profile. This default profile will be used to send any mail if no profile is specified. But you could run into issue if your default profile is private profile and the user who is trying to access that profile doesn’t have permission !! So better double check your settings (plus refer to better documentation) if you are trying this in production.
 |
 |
 |
Once that is done click NEXT and you will taken to system parameter setup page where you can setup options like retry attempts, max file size, prohibited file extensions, logging level etc. Once you select proper settings (or just go with default ones) click next. And you should see mini summary of what you wanted to do…. this step will actually create everything that you have configured till now 
… click next.
 |
 |
 |
 |
 |
 |
If everything runs green then you have database mail setup with one default profile which you can use to send email from database itself. To test the setup, you can send test mail from SQL Server. For that right click on “Database Mail” and select “Send Test E-mail” which will open up a small form where you have to feed email “TO address” and “Subject” and “Body” for email.
Once that is done click on send email and you will see confirmation that email is sent … but this doesn’t guarantee that email will actually be sent by SMTP server. I mean that message just informs user that message is in queue for processing, but it could fail to deliver due to reasons like invalid email, wrong SMTP server settings or just network timeout …. However there is a log available for database mail which keeps tack of each message sent and response received in MSDB. There are system views sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems and sysmail_event_log can be used to view all, sent, unsent (in queue), failed mails and event log respectively. If you are interested you can read more about it in official BOL.
Database Mail is really handy feature if you want to send email using Database Server like some kind of notification system.
That’s it for now …
It’s Just A Thought … 
