SQLMail vs Database Mail - which one to use?
Though SQL Server 2005 has been evolved much having the flexibility of sending emails from SQL Server is a general practice within a database environment.
To just overdue on the concept, SQL Mail uses Extended MAPI client components from an external e-mail application (for example, Microsoft Outlook) to send and receive e-mail. Therefore, to use SQL Mail, you must install an e-mail application that supports Extended MAPI on the computer that runs SQL Server. SQL Server uses the Extended MAPI components provided by the e-mail application to communicate with the e-mail server.
Why SQLMail is not supported on Clustered installation? How about database mail?
SQL Mail is provided in Microsoft SQL Server 2005 for backward compatibility. To send mail from SQL Server, use Database Mail.SQL Mail is not supported on a clustered installation of Microsoft SQL Server. The Extended MAPI interface used by SQL Mail is not cluster-aware, and may not work correctly on a cluster. To resolve this problem, update your application to use Database Mail. Database Mail is supported on a clustered instance of SQL Server.
Database Mail don't need the above specified MAPI or outlook,as it uses the standard Simple Mail Transfer Protocol (SMTP) to send mail. You can use Database Mail without installing an Extended MAPI client on the computer that runs SQL Server. Having this facility the sending email process runs outside of SQL Server, in a separate process. SQL Server will continue to queue e-mail messages even if the external process stops or fails. The queued messages will be sent once the outside process or SMTP server comes online. SO you have to keep an eye on your SMTP server queue in this case.
Due to the nature of SMTP server support on cluster DB mail is supported on Cluster, as DB Mail profile allows you to specify more than one SMTP server. Should an SMTP server be unavailable, mail can still be delivered to another SMTP server.
Keep in mind when you install a SQL Server instance the Database Mail is not active by default. To use Database Mail, you must explicitly enable Database Mail by using either the SQL Server Surface Area Configuration tool or the Database Mail Configuration Wizard. The user must be a member of the DatabaseMailUserRole database role in the msdb database and it is not available in SQL Server 2005 Expression edition.
For ease of conveting SQLMail to DBMail referring the Technet article on information about converting a stored procedure from SQL Mail to Database Mail, see How to: Convert Stored Procedures from SQL Mail to Database Mail (Transact-SQL).