Thursday, May 27, 2010

SQL Mail to be deprecated in Microsoft SQL Server 2008 R2

If your organization is considering upgrading to Microsoft SQL Server 2008 R2 and relies on SQL Mail to generate Business Alerts notifications for Microsoft Dynamics GP, then this is for you.

With the introduction of Microsoft SQL Server 2008 R2, a number of database features will be deprecated, this is, the feature will not be available in a future release of SQL Server -- SQL Mail being one of them. You can find the complete list of features that will be deprecated here:

Deprecated Database Features in SQL Server 2008 R2


A couple things about SQL Mail:

1. SQL Mail runs in-process to SQL Server service. The issue here was, if SQL Mail crashed so did your SQL Server. Certainly, not an optimal situation in a production environment.

2. Using SQL Mail requires installing an extended MAPI client component on your server. Most of you would have typically resorted to install Microsoft Outlook to overcome this situation, as without a MAPI client SQL Mail would not run.

So here comes Database Mail:

1. Unlike SQL Mail, Database Mail runs in a separate process thread. If Database Mail goes down, it will not take down SQL Server with it.

2. Database Mail does not require an extended MAPI client component for it to run.

3. Database Mail can be configued with multiple SMTP accounts and with multiple profiles. This increases the robustness of the system. In a realtime environment, if one mail server goes down, Database Mail will still be able to send emails through the other configured SMTP accounts.

4. In Database Mail, all mail events are logged keeping history of emails.

5. You can configure Database Mail to limit file sizes, disallowing large attachments and filtering of file extensions such as .exe or .bat

To configure Database Mail please check the following Microsoft video:


Video source: Technet (http://technet.microsoft.com/en-us/sqlserver/dd939167.aspx)

Be sure to include Database Mail setup as part of your SQL Server 2008 R2 implementation and to test all your Microsoft Dynamics GP Business Alerts in the process.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

4 comments:

Anonymous said...

Hi,
Does it mean that the Emailing Statement option under Customer Master can now email statement using DB Mail and the requirement for MAPI compliant email client is taken out?

May
GP User

Mariano Gomez said...

Not sure I understand your question. The statements emailing capability in GP does not rely on SQL DB Mail, but rather a MAPI profile as it uses Outlook to send emails.

MG.-

Joe said...

I found this blog while researching SQL Mail vs Database Mail. I'm running Dynamics 2010 GP. This version utilizes "xp_findnextmsg" type in it's stored procedures. I wanted to use Database Mail. I don't think Dynamics supports it. Dynamics would need to be updated to use "sp_processmail", "sysmail_start_sp", and more which are stored procedures from SQL Server. Does this sound correct? I only wanted to enable the "Paging/Emailing" feature in the Field Service Module. I think I'm stuck using SQL Mail which means I need to setup outlook on the SQL Server. I don't like the fact the if SQL Mail crashes, so does MSSQLSERVER...

Mariano Gomez said...

Joe,

I'm not sure I am following. SQL Mail is still supported under SQL Server 2008 R2 (it is no longer supported in SQL Server 2012, however), hence xp_findnextmsg and sp_processmail still work. As you mentioned though, you would need Outlook to be able to send and retrieve messages as SQL Mail uses MAPI.

In fact, if using Database Mail, all stored procs must be updated to use sp_send_dbmail - Database Mail uses SMTP.