Wondering how many people out there are running clusters and what your solutions to this problem are?

SQL Clustering doesn't really support the SendMail function very well, which we use for notifications. MS says SendMail shouldn't be used in a clustered environment etc.

I'm curious what solutions others use to do mail/pager notifications from within SQL Server, I've written an SMTP proc but calling through the OLE XP procs isn't exactly smiled upon around here.

We already buffer all of our mail into a table and have a job running SendMail to insure that no procs hang if SendMail is down, what I am leaning towards at the moment is to write a .Net service that reads our table and sends SMTP mail on a regular interval outside SQL Server, but I keep thinking there has to be a more elegant way of solving such a common problem.

John

jtgooding wrote:

what I am leaning towards at the moment is to write a .Net service that reads our table and sends SMTP mail on a regular interval outside SQL Server, but I keep thinking there has to be a more elegant way of solving such a common problem.

That is exactly the way most companies that i worked for do it - donno if they don't know how to use the SQL Server smtp option or weather there is something wrong to do it that way.

I suppose the "real" reason you should not send from a cluster is that it could be hard to determine which sql instance in the cluster is sending the mail.

A service isnt that bad though and using LLBLGen it should be a really quick solution.

The company that I work for now had me write a service to monitor all of our hosted platforms and send mail for various operations. I ended up writing a multi threaded service that would get data from various sql DBs at various time intervals. The service would then take the retreived data and create MSMQ commands that would be parsed by another service and the mail would be sent.

It was a little OTT probably, but its completely configurable and extensible, i.e. the service is controlled from a config file, and it does more than send mail, like kick off batch jobs, do data imports / exports, move data from system to system, expose remote object hooks, etc.

Its just too bad that in the next year I will be phasing out the services and replacing them with biztalk 2004.