Strange issue with database mail in clustered environment - not working tried everything

Scenario: We have a 4 node clustered environment having windows server 2008 64bit with SQL server 2005 SP3 enterprise edition.

Problem: The issue was that out of 4 servers in a clustered environment, on only one server the database mail was not working.

Approach:

Step1: Check that the Database mail is enabled using Surface Area Configuration. Then I rebuilt the Profiles and Accounts within the SQL Database Mail. I sent test messages after the rebuild and didn't see anything logged. I used the troubleshoot option on the test message send page and found some stored procedures which allow you to check to see if the queue is active and to start it. I both confirmed it was active, and sent it another start command, but still don't see email getting sent. I did see that the DB Mail start command was logged in the event logs, but nothing other than that.

Below is what was done:

--The following query will show database mail is working correctly
SELECT * FROM msdb.dbo.sysmail_sentitems order by sent_date desc

No results are seen after running the above command.


SELECT * FROM msdb.dbo.sysmail_allitems order by sent_date desc

WHEN I RUN above COMMAND I SEE THAT ALL THE MESSAGES ARE IN AN "failed" send_status

--The following query will show Database mail is started and running
SELECT * FROM msdb.dbo.sysmail_event_log order by log_date desc

WHEN I RUN THIS COMMAND I CAN SEE DB MAIL IS STARTED, BUT I DON'T SEE ANY SUCCESSFULLY SENT MESSAGES LIKE I DO WHEN I RUN IT ON ANY OF THE OTHER DB SERVERS

--If Database mail was having problems, we would see failed items in this view.
SELECT * FROM msdb.dbo.sysmail_unsentitems

WHEN I RUN THIS COMMAND I SEE THAT ALL THE MESSAGES ARE IN AN "unsent" send_status

Then the FIREWALL and SMTP relay were working fine or not were checked and they were all working fine !!

Then I GOOGLED and found "http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/fd210efa-9c01-439e-8573-4f25a4177b08", but this did not work !!

Then the decision was taken to open a ticket with MS... spent 2.5 hours on the phone with Microsoft trying to figure out what's going on with DB Mail.

At this point they took some traces, other diagnostic data ... and yes they were able to figure the REAL problem ....

When you select MSDB and run the following to show DB Mail config:

------------------------------------------------------------------

USE msdb

select * from sysmail_configuration

------------------------------------------------------------------

...We can see that the following is set (which MS says would have been done manually as the default is to NOT use a config file):

------------------------------------------------------------------
ReadFromConfigurationFile 1 Send mail from mail server in configuration file
------------------------------------------------------------------

With this option enabled there MUST be a configuration file that tells the DB Mail exe what Server\Instance to send the mail to. This file existed (I don't know if it has existed from the beginning, who created it, etc...), but it was not in the correct location.

The file was in:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn

...and needed to be in (MSSQL.3 since registry shows [server_name] being the 3rd instance):

C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn

Once this file was moved into the proper location, we were able to define the correct server name:

------------------------------------------------------------------
Contents of config file: "DatabaseMail90.exe.config"
------------------------------------------------------------------

configuration
appSettings
add key="DatabaseServerName" value ="[server_name]" /
add key="DatabaseName" value ="msdb" /
/appSettings
/configuration

------------------------------------------------------------------
Once this had been properly configured and the file put into the correct location we were able to successfully send mail

Since this is a 4-node SQL Cluster we also had to copy the "DatabaseMail90.exe.config" file over to the other 3 nodes to:

\\[server_name]\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn
\\[server_name]\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn
\\[server_name]\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn

...so that if this instance fails over to any other node it will continue to function.


This is how the Strange database mail issue was fixed ... Ah.. it was with the config file .... and lastly got resolved !!!