Jeff Moden (6/15/2012)
I cannot personally speak as to whether or not Database Mail is a PITA to setup on clusters because, like you, I wasn't the one that set it up on the clustered servers that I'm working on. I can only go by what those that set it up say. I still have a deep dive to do to determine if what they say is true or not. I trust them and I trust you and there's a conflict between what all of you say. For me, the jury is still out until I can find the time to find out for myself. With any luck at all, maybe I can teach "them" a thing or two.
I am not sure I meant to pit my word against theirs. They may have challenges configuring Database Mail in a (or their specific) clustered environment of which I am not aware. I am only saying I have had no trouble using it on a cluster, or configuring it and using it on a stand-alone server.
Shifting gears, you said you had problems "years ago" with CDOSys Mail. Was it actually CDOSys or was it its predecessor (the name eludes me just now but the name still started with "CDO"). I'm wonder if maybe CDOSys fixed some of the problems its predecessor may have had.
Hmmm, were you thinking of CDONTS? I may have used that years ago but I couldn't say for sure. The setup to which I was referring uses sp_OA and CDOSYS to send email from SQL 2005 Standard on Windows 2003 x86 and is in service on one instance in the current shop. I converted everything else to Database Mail in the current shop but had to leave CDOSYS in place on the one instance because of the From-address limitation in SQL 2005 Database Mail (did I mention it is annoying they left that feature out? :-)). This is why I know the From-address limitation is not present on 2008 as that is what the 2005 instance is migrating to (soon). CDOSYS will not be used and the sp_OA procs will be left disabled post-migration to the 2008 environment.
Thanks for the link on setting up Database Mail to use a remote mail server. That'll make it a whole lot easier to do experiments with from home.
Anytime. Happy to assist.
Shifting gears again, I've never had to install CDOSys (or its predecessor) separately. Its always been installed as a part of SQL Server.
I just looked it up (here[/url]) and CDOSYS is included on Windows Server 2003 so your team really doesn't have any setup to do as long as your procs supply the mail server IP/hostname. It is something you have to explicitly add to Windows 2000 which is what I may have been remembering. Sorry for any confusion there.
Last but not least, I very much appreciate and agree with the fact that a lot of functionality defaults to being turned off to "improve" security. But, and I think you agree, I also think that's because of the incredible misuse that some of these features have gone through at the hands of the unaware over the years. xp_CmdShell and OA are both good examples. There are a lot of people who don't know how to properly secure a system (even without enabling such functionality) so that you can use these marvelous tools safely. Even MS flubbed up a bit when they made it possible for non-SA logins/users to use xp_CmdShell directly through the use of a proxy account. I'm a strong believer that non-SA folks should never have the ability to run xp_CmdShell directly but I'm also a strong believer that they should be able to execute a stored procedure that uses it. As you know from a thread long past, there are some pretty simple methods to doing that properly.
No question about it. I may still produce the article we kicked around, showing how to properly secure xp_cmdshell with detail about a real-world implementation of the technique. You said you had lost interest because of similar efforts, or potential holes you thought people would poke in it, but I would take it on from a how-to perspective. If you are still not interested in producing it, if I produced it, would you be willing to do a technical review on it? My first SSC article is scheduled to be published 6/28 so I am very excited about that. My second article, a followup to my first using a third-party tool to accomplish the same task, is in the works.
Since I'm still a bit stuck in the world of 2005 (that's all they currently have at work), I'm really happy to hear that they've made it so you can override the "FROM" address in Database Mail. That's going to make my life a wee bit simpler. Now all I have to do is get the infrastrucure folks at work to install it on the non-production (Dev, Test, etc) clustered servers at work (yep... even those are clustered) so that I don't have to maintain a CDOSys work around for testing procs that send mail.
Not sure how much DB admin work you do on a daily basis versus working with data or development but you may find the sysmail tables in msdb that support Database Mail quite handy for troubleshooting mail issues, or simply tracking email that was sent. Database Mail, given that it is queue driven, also has failed-email retry capabilities and email-send timeout thresholds built-in making it a bit more reliable that CDOSYS out of the box.
As always, I really enjoy these conversations with you, Orlando. They bring out some good points where not only I learn something in the process, but I think others will, as well.
As do I. Our exchanges have taken on quite the adversarial feel to them at times but I have always appreciated and respected your approach, and your patience. You have no doubt rounded down some of the sharp corners on my views and made me a better database professional, and I thank you for that.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato