May 27, 2010 at 8:19 am
Hey All,
I've been using the db_mail functionality for a while now and would just add in a couple of caveats:
1) You should add in some mechanism to track emails sent (sent to, datetime stamp, etc...). SQL server does track this but I like to have a non-system method to confirm and report against when and email was generated/sent, at least from the SQL perspective. This would allow you to recover/resend without duplicating any already sent items incase there was some issue that stopped the send.
2) You have to keep an eye on the sysmail_mailitems and other sysmail tables in the msdb. The system records all emails sent and this table can grow quite quickly as time passes. You will have to devise a method for periodic backup/cleanup if your sending mass amounts of emails.
just a couple of cents...
Dave
May 27, 2010 at 8:23 am
I think this is an expensive use of RDBMS because there is no DB Mail in Express which is a wrapper on SMTP, you can send the same mail free in Asp.net.
Kind regards,
Gift Peddie
May 27, 2010 at 8:45 am
Aye. There are better things to do with a DB system than use it as a mass mailer and better systems for doing so but sometimes, if you have a job running already generating the info sometimes it's easy to just do it from there.
Also the post is not really about whether you should but rather, if you do, how to batch it up.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
May 27, 2010 at 8:46 am
@Gift Peddie
I would tend to agree but would not suggest an asp.net app. .Net window application/console/service would probably be more intune with what your suggesting. asp.net apps. would not be able to hold a session long enough to do any real mass mailing...
MS did add this feature in so it does do the job and usually, production DB's do have the horsepower to pull it off with minimal drag on a given server BUT I tend to do mailings in off peak times to ensure clients are not inconvienced with any possible processing time...
Good point though...
Dave
May 27, 2010 at 9:01 am
I would tend to agree but would not suggest an asp.net app. .Net window application/console/service would probably be more intune with what your suggesting. asp.net apps. would not be able to hold a session long enough to do any real mass mailing...
The implementation of System.NET mail in Winform, Console and Asp.net uses different Config files required in app.config for Winform, Console and Web.Config in Asp.net. So what you can do with Asp.net depends on skill and the person without skills could buy existing component for a few dollars. In .NET 4.0 SSL can also be defined in the config file and not code and all of the above require .NET 2.0 and up.
Kind regards,
Gift Peddie
May 27, 2010 at 9:09 am
Point taken...
And hopefully that was a kind jab....
Never bothered to build with asp.net interface due to concerns for remote session timeouts...for mass mailing at least...
Dave
May 27, 2010 at 10:39 am
Simple a brilliant solution for limited resources!
May 27, 2010 at 10:54 am
as a former email admin, why not just let MS Exchange handle the work. create some custom receipients that are hidden from the corporate address book and a few distribution lists. then use SQL Server to send the email to the distribution list smtp address and let Exchange handle the work of sending it to each individual user. the email data will be stored on the domain controllers and generally it's more efficient than using SQL to send one email at a time.
May 27, 2010 at 10:59 am
David Atherton (5/27/2010)
Point taken...And hopefully that was a kind jab....
Never bothered to build with asp.net interface due to concerns for remote session timeouts...for mass mailing at least...
Dave
Hi Dave,
My apologies I did not intend to offend, I help developers implement System.NET in all three areas and I find flexible users without Enterprise restrictions can send thousands of mail free without issues. That is the reason SSL was moved to config file in .NET 4.0 because hosting companies still create problems in shared hosting.
The other free option is to send 5000 mails four times with Gmail or Hotmail and no I have not tested the limit but 20,000 is a lot to send with public mail servers.
🙂
Kind regards,
Gift Peddie
May 27, 2010 at 11:05 am
Right now i'm pushing my email campaigns into a secondary mail server. I typically peak the performance of that server and batching seems like a quicker solution that would work in my current system. I like the idea of pushing emails in a AD distribution list and then pushing it out that way.
May 27, 2010 at 11:10 am
None taken Gift,
Constructive criticism often leads to opened eyes...and always good for debate.
Makes me rethink options for my clients but for now, I've got SQL doing the job of about 100K emails once per week.
Thanks
Dave
May 27, 2010 at 11:14 am
Very useful article
The problem is that it is database specific.
We have a similar solution which can connect to any database/file and use it as a source for generating emails.
It can also load emails into the database
Never thought that sending too many emails can be a problem
You made me realise that there is such a thing as perfect software there is always room for improvement.
We will add batch size as option to our next release
Mike
DB Software Laboratory
May 27, 2010 at 12:18 pm
I have had to send many a batch emails for my company and as a .NET developer, I send via .NET. As someone else susggested, I record the date the email was sent and that allows me to batch my emails. My .NET app calls a stored procedure that gets the respective email recipeints (and the email content that I also store as HTML in the db) and then does the sending. But I can then leverage the table containing the dates and recipients and have my proc only return the top X that have not yet receiveed the email. This then runs at a particular interval as a scheduled task and I keep our Exchange Admin from breathing down my neck. NOT to detract at all from the article. Just throwing it out there as another technique that I've used.
Thank you for the article.
May 27, 2010 at 12:51 pm
alen teplitsky (5/27/2010)
as a former email admin, why not just let MS Exchange handle the work. create some custom receipients that are hidden from the corporate address book and a few distribution lists. then use SQL Server to send the email to the distribution list smtp address and let Exchange handle the work of sending it to each individual user. the email data will be stored on the domain controllers and generally it's more efficient than using SQL to send one email at a time.
but what if it is user subscription, that chan change minute to minute, that generates the recipient list so each new user has the email address as part of the user table in sql. What if also you need a query to pick the recipients, for instance all who have signed up for over a year, ot any criteria you want.
You can have a job to check if any citeria match then generate the emails based on matching criteria all within a single SQL job without having to pass the recipient list out to another process.
Keeping it within SQL server then reduces the number of platforms that need to be maintained.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
May 27, 2010 at 1:03 pm
select * from subscribers where subscription_date=getdate()-365
Easy Peasy
Mike
DB Software Laboratory
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply