Jean-Pierre Paradis's Blog: SQL Server 2005 Database Mail in cluster

Updated in december 2008 (see at the end)
I've been playing with Database Mail in my SQL 2005 Cluster for a few days now. I always get the following errors :

Database Mail Log:
Log Database Mail (Database Mail Log)
Last Modified By sa
Message : Activation failure.
EventViewer Application Log: Event Type: Information
Event ID: 9724
User: N/A
Description: The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'

Last night I found a solution and I though it would be a good idea to share it.
As you know SQL Server use an external program to process mail : DatabaseMail90.exe. As I understand it, when SQL launch DatabaseMail90.exe it connect to the msdb database and look in the system tables for it's configuration and for messages to process.

First Problem : DatabaseMail90.exe connection

Looks like DatabaseMail90.exe is trying to connect to SQL using Localhost or ComputerName as it's SQL Server name. In a cluster you can't do that, you should use the Virtual SQL Server Name as define in your Cluster resources. Can we change that ? Not really.
But there is a workaround. Microsoft added a new feature in SQL SP2 to allow you to send mail from a remote server. We are going to use that feature to make DataBaseMail90.exe connect to the Virtual SQL Server (Cluster).
First we have to create the text file DataBaseMail90.exe.config in the \MSSQL\Binn folder of your SQL Instance (ex: \Program Files\Microsoft SQL Server\MSSQL.1\MSSQLSERVER\MSSQL\Binn). with the following content :

Replace VirtualServerName\InstanceName by the valid names from your environment.
Don't forget your are in a cluster, so you should copy that new file on every node of your cluster.
Next we have to modify the DatabaseMail config to tell SQL Server to use the new file. We'll do that by running the following code in SQL Server Management Studio :

USE msdb;
GO
INSERT INTO [msdb].[dbo].[sysmail_configuration]
   (
   [paramname]
   ,[paramvalue]
   ,[description]
   )
VALUES
   (
   N'ReadFromConfigurationFile'
   ,N'1'
   ,N'Send mail from mail server in configuration file'
   );
GO

Second problem : the 'sp_sysmail_activate' stored procedure

'sp_sysmail_activate' is the procedure that launch the DatabaseMail90.exe process. Normally when you updated your SQL Server to SP2 that procedure got updated to support the new DataBaseMail.exe.confg file. Don't ask me why but when you are in a cluster this is not the case (thanks FreemanRU for the tip).
So you'll have to update it by yourself. I have taken a copy of the 'sp_sysmail_activate' procedure from a working SQL server (non-cluster) SP2 with CU8 (build 3257). You can simple run the code in SQL Server Management Studio :

USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[sp_sysmail_activate]    Script Date: 08/13/2008 11:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- sp_sysmail_activate : Starts the DatabaseMail process if it isn't already running
--
ALTER PROCEDURE [dbo].[sp_sysmail_activate]
AS
BEGIN
    DECLARE @mailDbName sysname
    DECLARE @mailDbId INT
    DECLARE @mailEngineLifeMin INT
    DECLARE @loggingLevel nvarchar(256)
    DECLARE @loggingLevelInt int
    DECLARE @parameter_value nvarchar(256)
    DECLARE @localmessage nvarchar(max)
    DECLARE @readFromConfigFile INT
    DECLARE @rc INT
    SET NOCOUNT ON
    EXEC sp_executesql @statement = N'RECEIVE TOP(0) * FROM msdb.dbo.ExternalMailQueue'
    EXEC @rc = msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'DatabaseMailExeMinimumLifeTime',
                                                        @parameter_value = @parameter_value OUTPUT
    IF(@rc <> 0)
        RETURN (1)
    --ConvertToInt will return the default if @parameter_value is null or config value can't be converted
    --Setting max exe lifetime is 1 week (604800 secs). Can't see a reason for it to ever run longer that this
    SET @mailEngineLifeMin = dbo.ConvertToInt(@parameter_value, 604800, 600)
    EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'ReadFromConfigurationFile',
                                                  @parameter_value = @parameter_value OUTPUT
    --Try to read the optional read from configuration file:
    SET @readFromConfigFile = dbo.ConvertToInt(@parameter_value, 1, 0)
    --Try and get the optional logging level for the DatabaseMail process
    EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'LoggingLevel',
                                                  @parameter_value = @loggingLevel OUTPUT
    --Convert logging level into string value for passing into XP
    SET @loggingLevelInt = dbo.ConvertToInt(@loggingLevel, 3, 2)
    IF @loggingLevelInt = 1
       SET @loggingLevel = 'Normal'
    ELSE IF @loggingLevelInt = 3
       SET @loggingLevel = 'Verbose'
    ELSE -- default
       SET @loggingLevel = 'Extended'
    SET @mailDbName = DB_NAME()
    SET @mailDbId   = DB_ID()
    EXEC @rc = master..xp_sysmail_activate @mailDbId, @mailDbName, @readFromConfigFile,
    @mailEngineLifeMin, @loggingLevel
    IF(@rc <> 0)
    BEGIN
        SET @localmessage = FORMATMESSAGE(14637)
        exec msdb.dbo.sysmail_logmailevent_sp @event_type=3, @description=@localmessage
    END
    ELSE
    BEGIN
        SET @localmessage = FORMATMESSAGE(14638)
        exec msdb.dbo.sysmail_logmailevent_sp @event_type=0, @description=@localmessage
    END
    RETURN @rc
END

There's only one final step, you should restart the mail process with the follwing SQL command :

EXEC msdb.dbo.sysmail_start_sp;

You could always restart the SQL Server if you prefer.
You should now be able to send a test email from the Database Mail object in SQL Server Management Studio from your SQL Server 2005 SP2 in a cluster.

December 2008 update :

I missed something, in the ‘Second problem :’, ‘sys_mail_activate’ is not the only stored procedure that you have to update. There is a few others :

dbo.sp_ExternalMailQueueListener
dbo.sp_RunMailQuery
dbo.sp_send_dbmail
dbo.sp_sysmail_activate
dbo.sysmail_delete_mailitems_sp
dbo.sysmail_delete_profile_sp
dbo.sysmail_update_account_sp
dbo.sp_ProcessResponse
dbo.sp_validate_user

I’ve created a script to update those procedures to the same level as SQL Server 2005 SP2 with Cumulatives Updates 8. You can download my script here.
May I suggest you backup the msdb database before playing with dbmail procs.