1

I have a SQL Server database which is shared between several ASP.NET (VB.NET) websites, the database has a table which stores customer enquiries - I wish to provide email notifications informing of new enquiries to relevent people within the organisation (and possibly put some of the enquiry data into the body of the email). Please could someone outline my options in terms of how this could be implemented?

My thoughts are:

  1. SQLMail
  2. Database Mail
  3. VB.NET Stored Procedure using System.Net.Mail library?
  4. Other options?

Option 3 seems desirable because I know how to do this in VB.NET and it is trivial.

  • What are my other options?
  • What are the caveats?
  • Can I do this in real-time i.e. a trigger (the volume of inserts is low)?
  • or is it vital that this is done in a batch i.e. a job?

Any help very welcome! Thanks.

Chris Cannon
  • 1,157
  • 5
  • 15
  • 36
  • What is the blocker for database mail? Surely this is easier to set up than CLR. – Aaron Bertrand Mar 21 '12 at 23:13
  • I've done some research into Database Mail but I'm unsure as to whether I need to connect to an email server to send the email - if this is the case then I can't do that because it's separate from our web server. I'm familiar with how to send an email from VB.NET as we do this on one ASP.NET (VB.NET) website and effectively the sender is "spoofed" but it's not spam because it's internal and the DNS records do contain the web server IP address! – Chris Cannon Mar 21 '12 at 23:20
  • You can set up a simple SMTP server on any server (including on the SQL Server box or on the web server). If you don't want to use the IIS feature there are other lightweight solutions available. Your VB.NET code still has to connect to an SMTP server ultimately, why can't SQL Server connect to this same SMTP server? – Aaron Bertrand Mar 21 '12 at 23:22
  • I think the ASP.NET (VB.NET) code uses the "localhost" email server - is this available from Database Mail? My knowledge is a bit patchy on email servers... – Chris Cannon Mar 21 '12 at 23:28
  • Well the SQL Server box would have to use the actual internal IP address or DNS name of the web server (not "localhost"). This may mean you need to open up that port on the web server to allow the SQL Server box to use that SMTP server, but this isn't terribly complicated. – Aaron Bertrand Mar 21 '12 at 23:32
  • They're on the same box. – Chris Cannon Mar 21 '12 at 23:38
  • Then you should be able to set up database mail to use localhost. The steps to set up database mail are pretty simple. You didn't specify the version of SQL Server - this tutorial is for 2005 but it is equally valid for newer versions as well: http://www.mssqltips.com/sqlservertip/1100/setting-up-database-mail-for-sql-2005/ – Aaron Bertrand Mar 21 '12 at 23:40

2 Answers2

5

Between 1), 2) and 3) the only one worth considering is 2). 1) is a stillborn, using a deprecated feature notorious for it's problems and issues. 3) is a bad idea because it breaks transactional consistency (the SMTP mail is sent even if the insert rolled back) and is a performance hog as the caller (presumably your web page being rendered) has to wait until the SMTP exchange completion (ie. is synchronous). Only 2) offers transactional consistency and is asynchronous (does not block the caller until the SMTP completes).

Normally though such task is better delegated to a Reporting Services task. You would create a Data-Driven subscription with an email delivery. This works out-of-the-box, no need to reinvent the wheel.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Thanks for your response. It definitely needs to be asynchronous! I'm not so concerned about an email being sent even if the transaction is rolled back - it's undesirable but its not going to cause problems within the company. So option 2 looks like the best option. I have a couple of issue though - I think we only have SQL Server standard edition and I can't connect to an email server to send the email - I have to "spoof" the sender but it's not spam because it's only internal and the DNS email records do have the server IP address. – Chris Cannon Mar 21 '12 at 23:01
  • You say that 3) is bad because the SMTP mail is sent even if the insert is rolled back. How is it any different from 2)? That said, I agree with you, but you make it sound like that's a con for 3) but not any of the other options. – Aaron Bertrand Mar 21 '12 at 23:15
  • @Aaron: Database Mail sends the 'mail' as Service Broker message into the db mail queue in `[msdb]`. If the transaction commits the 'message' is picked up by the Database Mail external agent and delivered. If it rolls back, the SSB message is rolled back and the Database Mail agent never see it. This is described in http://technet.microsoft.com/en-us/library/ms175887.aspx. – Remus Rusanu Mar 21 '12 at 23:26
  • So you're saying that if you do 15 more things in the trigger, the mail waits until the trigger completes successfully? That doesn't sound as asynchronous to me as I've been led to believe... – Aaron Bertrand Mar 21 '12 at 23:28
  • @Chris: Database Mail (ie. option `2`) is supported in Standard Edition, see [Features Supported by the Editions of SQL Server 2008 R2](http://msdn.microsoft.com/en-us/library/cc645993.aspx) – Remus Rusanu Mar 21 '12 at 23:29
  • @Aaron: the 'sp_send_mail' message will sit, uncommitted (hence un-`RECEIVE`-eable), in the queue. Database Mail will not deliver any mail until the transaction commits (will not even see that it needs to deliver anything). – Remus Rusanu Mar 21 '12 at 23:37
  • @RemusRusanu could you please outline the steps (e.g. use this wizard, write this stored procedure) I would need to take to achieve my task? – Chris Cannon Mar 21 '12 at 23:47
1

I have worked with a similar situation to this, and the solution I went with was to have a C# windows service checking a SQL server job queue every minute. This job queue would include jobs such as send an email. You could then use a TRIGGER on your table to insert a new "Email Alert" job, that would get picked up by the next cycle.

Paul Grimshaw
  • 19,894
  • 6
  • 40
  • 59
  • That sounds sophisticated and I would do that if I knew how - is there not a simpler but just as safe option? – Chris Cannon Mar 21 '12 at 22:40
  • Your option 3, using a CLR stored procedure is the only other way I can think of off the top of my head, and CLR procedures are probably just as hard to implement as my suggestion above. A simpler version would be a console app that also checked a "JobQueue" table in SQL Server, and use windows scheduler to run this every minute. – Paul Grimshaw Mar 21 '12 at 22:46
  • Why would a console app be simpler than a simple SQL Server Agent job that called database mail? More moving parts = more points of failure. Relying on task scheduler (which isn't truly clustered until W8) is probably not better or simpler IMHO. – Aaron Bertrand Mar 21 '12 at 23:21