What is Database Mail and How To Configure it

We all get some SMS from our internet banks whenever we logging to an Internet bank. Many developers think that they have to send e-mails (or sms) from their own applications but it’s not a good idea to do such a work. SQL Server has a functionality to do such a thing. In fact whenever a transaction Commits or Rollbacks after that SQL Server can send SMS or e-Mail to anybody with any limitation.

Database Mail Architecture

What is Database Mail? In a short answer, Database Mail is a feature of SQL Server which let Database sends Mail or SMS to anybody after a transaction.

Database Mail run outside of SQL Server so it is not pressure on your SQL Server Engine. It also is support clustered environment and can use SMTP Servers. On the other hand it sends mails asynchronously with Service broker so there will be no waste time and it has some security maintenance which let you filter messages.

So with this background it is necessary to know that when we have to use this functionality. The best example to send SMS is after a Backup procedure it doesn’t matter that it is commit or rollback, you will receive SMS (mail) after the procedure and you can remotely run the backup procedure after a rollback. It is very good for DBAs. Another good example is bank Transactions. Those are important and customers have to know about the result of transaction no matter what.

How to Configure Database Mail

Database Mail is disabled by default and you have to enable it. To enabling it you have three options.

1 –  First one is to use Database Mail Configuration Wizard.

To use Database Mail Configuration Wizard, connect to an instance of SQL Server, and then from the Object Explorer expand the Management key. Then right click on the Database Mail and select Configure Database Mail.

Connect to an Instance of SQL Server > from the Object Explorer expand Management > right click on Configure database Mail

At the first time you have to select Setup database Mail and create a new one. Then you can choose between – Manage Database Mail accounts and profiles – Manage profile security – View or change system parameters.

The important note is that Service Broker should be enabled first. SQL Server Service broker is an extension mechanism that allows you to queue events for asynchronous processing. There is no intrinsic harm in enabling the broker, if it’s not used it will just be idle. In fact Service Broker is a messaging system built into the SQL server db engine.

So enabling SQL Server Service Broker in any database requires a database lock. If Service Broker was deactivated in msdb, to enable Database Mail, first stop SQL Server Agent so that Service Broker can obtain the necessary lock.

2 –  The Second solution to configure Database Mail, is to use Store procedures.

Mail & SMS

It has a long story and you can read about the store procedures here in MSDN.

3 –  The third solution is using the Surface Area Configuration facet of Policy-Based Management.

With above information you can guess that what the Database Mail requirements are.

1.   SQL Server (2005 – 2008) – SQL Express don’t have Database Mail.
2.   Enabling Service Broker
3.   Enabling Database Mail
4.   A Mail Server (Usually Exchange Server) to send Mails.
5.   Configuring Database Mail to use it.

I also recommand you to take a look at these articles 1 – 2 – 3 – 4