Any reason to avoid SQL Mail on a Production Server?

We are supporting a SQL Server 2000 (sp3) / ASP application.
The application is extremely poorly written (deadlock, timeout, stored
procs longer than my kids Christmas lists...) and I would like to have
SQL Mail installed to get various information emailed to us when one
of these events occur (through alert).
I meet stiff resistance from the Production guys but without proper
reason.
Before I put my head on the line... Is there any reason we should be
scared to install SQL Mail on a Production box?
One developer told me that it used to hang their production SQL
Server... (but that may have been SQL Server 6.5)
Thanks
EricEric,
> The application is extremely poorly written (deadlock, timeout, stored
> procs longer than my kids Christmas lists...) and I would like to have
> SQL Mail installed to get various information emailed to us when one
> of these events occur (through alert).
>
I would not necessarily view long stored proc as a bad thing. If the task
needs a long stored proc, so be it. It will perform better than breaking it
down to shorter ones.
> One developer told me that it used to hang their production SQL
> Server... (but that may have been SQL Server 6.5)
>
Though not scared, we did see some problem with Outlook:
1. When a process involves Outlook mail, and the mail server or client is
down, the process hangs (at least we saw this on SQL7).
2. Not whether a general problem or server specific: we saw that a SQL7
server's service be restarted by a too long mail recipient value.
We have the notifications you are considering on most of our servers. Just
mention this for your consideration to be prepared to talk to your
counterparts in other groups.
hth
Quentin|||Well, you'll need to run SQL under a domain user account -- not necessarily
a domain admin if you set all the appropriate permissions, but without a
domain user, there's no way to hook to an Exchange mailbox.
You'll also need to install a MAPI-compliant email program on the server
(preferably Outlook). Yet anothe application on a production server means
another point of hacking attacks.
In my experience, SQL Mail and SQL Agent Mail (they are two different
things) don't crash a server, but there are circumstances where they'll
stop working. If they try to send an email while the Exchange server is
offline or unreachable, that particular MAPI session can get messed up, but
remains in memory. You need to stop and start SQL Mail/SQL Agent Mail to
get it working. There are some ways around this like including a .pst file
in the profile you use for mail, but that too has issues.
Another option you may want to look into is Gert Drapers SMTP-based email
(www.sqldev.net). The only thing installed is an extended stored procedure,
it doesn't run into the problems MAPI can create, and you don't need to set
SQL to run under a specific account. The only limitation is that you can
use it for the integrated alert system.
Hope that helps.
"Eric Mamet" <eric_mamet_test@.yahoo.co.uk> wrote in message
news:11269dcb.0307250628.35979849@.posting.google.com...
> We are supporting a SQL Server 2000 (sp3) / ASP application.
> The application is extremely poorly written (deadlock, timeout, stored
> procs longer than my kids Christmas lists...) and I would like to have
> SQL Mail installed to get various information emailed to us when one
> of these events occur (through alert).
> I meet stiff resistance from the Production guys but without proper
> reason.
> Before I put my head on the line... Is there any reason we should be
> scared to install SQL Mail on a Production box?
> One developer told me that it used to hang their production SQL
> Server... (but that may have been SQL Server 6.5)
>
> Thanks
>
> Eric|||> The only limitation is that you can
> use it for the integrated alert system.
I assume you meant "can't"...
To get around this limitation, I simply add a step to jobs I need alerts
for:
step 1
call some sql
if succeeds, quit with success
if fails, goto step 2
step 2
call xp_smtp_sendmail to send alert
-- so that the failure of step 1 is correctly reflected in sys:
if succeeds, quit with failure
if fails, quit with failure|||I think I'll try to get them to install Outlook.
Our SQL Server and SQL Agent services already run under domain
accounts.
Otherwise I know there are ActiveX objects available to send mail on
our production machine but it would be far less convenient than
xp_sendmail.
Thanks for your comments
Eric