Database Mail on a cluster
In our production environment, we have two 4-node clusters. One cluster runs at the primary site; the other cluster runs at our disaster recovery site. Each cluster is running 11 SQL Server 2005 instances.
We setup Database Mail on all of the instances at both sites, so that we could e-mail internal customers the results of various ad-hoc queries. It was soon realized that Database Mail was not working properly at the primary site on any of the 11 instances. We got our Exchange, server, and network administrators involved, but we were unable to figure out what was wrong. We never received any errors but yet no e-mail was ever received. Since we were unable to resolve the issue, we decided to open a case with Microsoft. One of the things that they had me to do was increase the logging level of Database Mail so that it was more verbose. After the configuration change was made, I attempted to send an e-mail with Database Mail. As usual, no error was returned but also the e-mail never made it out of SQL Server. Microsoft wanted me to send them the Application Log in Event Viewer. While preparing to do this, I noticed the following error in it:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Since remote connections was enabled on all of the instances, I decided to add an alias for one of the instances on all 4 nodes of the cluster. This worked, so I reported my findings back to Microsoft. After a bit of time, they reported back to me that the SQL Browser service was the culprit.
They don't have an exact reason as to why the SQL Browser service is unable to connect to a local instance (meaning an instance that is on the node where the service is running), but they did provide possible reasons:
Clustering
SQL Server Browser is not a clustered resource and does not support failover from one cluster node to the other. Therefore, in the case of a cluster, SQL Server Browser should be installed and turned on for each node of the cluster. On clusters, SQL Server Browser listens on IP_ANY.
Note: When listening on IP_ANY, when you enable listening on specific IPs, the user must configure the same TCP port on each IP, because SQL Server Browser returns the first IP/port pair that it encounters.
Using a Firewall
To communicate with the SQL Server Browser service on a server behind a firewall, open UDP port 1434, in addition to the TCP port used by SQL Server (e.g. 1433). For information about working with a firewall, see "How to: Configure a Firewall for SQL Server Access" in SQL Server Books Online.
If you are unable to get Database Mail to send e-mails when the SQL Server 2005 instance is on a cluster, try adding an alias for the virtual server. You can easily add aliases using the SQL Server Configuration Manager tool.