![]() ![]() In part 1 of this tip series, I will first start my discussion on using the built-in Send Mail Task and then in part 2 of this tip series, I will discuss using the "Script Task" to overcome the limitations imposed by Send Mail Task. none windows authentication is not allowed) nor does it support specifying a SMTP port number to send emails if your SMTP server does not use the default value. The Send Mail Task is quite simple and straight forward in its configuration and use, but it has some inherent limitations for example, first it supports only sending plain text email (doesn't support HTML formatted mail) and second it doesn't support passing username and password while connecting to SMTP server (it only supports Windows authentication i.e. SSIS provides a built-in "Send Mail Task" to send email in these circumstances. that would take quite some time to build in-house.Sending an email is a frequent requirement to notify a user on the occurrence of certain events, especially if an unexpected event happens (for example sending notification on failure that could be either logical or physical). Uless there’s a really good reason not to use one then an off-the-shelf service like the ones Brent mentioned are likely to be more reliable and may provide a bunch of reporting analytics debugging options etc. – someone else above has done something similar. This was later replaced with a Windows Service that functionally did the same as the Agent job ![]() Yuk! – was very frustrating to have to do a full DR failover of the DB just to get emails flowing againĪs a workaround/improvement used a 3rd-party freely available DLL that could called from within an SP.Ī parameter compatible (with the built in email sending SP) SP would log emails into a “queue” table (which could be replicated to failover/DR servers) and other SPs triggered by a repeat-scheduled SQLAgent job could read from the queue, send the message by calling the DLL, dequeue when appropriate, log any errors etc. This would regularly get jammed up and required a restart of the DB server itself. ![]() In early versions of SQL Server (sadly can’t tell you which) emails got sent by the database server process itself, via an MS Outlook session. However, if your business users are relying on the email getting there, hand the task over to your developers, and have them use a truly modern enterprise solution like SendGrid or Simple Email Service. These things aren’t as much of a big deal for DBA-focused emails like a job failed. They’re going to be shrugging and pointing the finger at you, going, “Well, the rest of our systems are doing email just fine.” When things do go wrong, the logging isn’t detailed, and it’s not the system that your sysadmins and email admins are used to working with. SQL Server’s email troubleshooting is almost nonexistent. You won’t figure it out until it’s too late. It’s been forever since I’ve gotten a failure email.” In reality, there are hundreds or thousands of emails piled up in some SQL Server somewhere with a broken email delivery subsystem, like an old SMTP server address. When email delivery stops, you won’t notice. People think, “Hey, things must be going great these days. Your messages ain’t coming along for the ride. Database Mail lives in the MSDB database and Service Broker. To protect your user databases, you’re likely using log shipping or Always On Availability Groups, and in the future, it’s only getting more complex with SQL Server 2022’s ability to fail over to Azure SQL DB Managed Instances. Sure, it seemed robust when it came out a couple decades ago, but these days, anything involving the word “enterprise” needs to survive a data center failover. SQL Server’s email delivery isn’t very robust. Whoever started the documentation by writing “Database mail is an enterprise solution” had clearly never seen an enterprise solution in real life. I’m talking about using SQL Server to send emails to confirm a customer’s order, alert that a product is low on stock, or other business processes. That’s fine when you can’t get the business to spring for a monitoring system. I’m not talking about Agent alerts when jobs fail. ![]() I would like to unsubscribe from this idea. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |