Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Intro to Database Mail in SQL 2005 Expand / Collapse
Author
Message
Posted Wednesday, December 14, 2005 5:21 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 2:21 PM
Points: 55, Visits: 168
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cBunch/introtodatabasemailinsql2005.asp
Post #244323
Posted Tuesday, January 3, 2006 3:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:45 AM
Points: 79, Visits: 275

Folks,

I cannot check this out until I get back to the office but, is database mail included with the express edition?

If so it really saves my @ss.

Thanks

Mick




Post #247776
Posted Tuesday, January 3, 2006 9:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 23, 2009 9:44 AM
Points: 179, Visits: 22
First let me say that the author did a good job of showing the advantages of the new mail system.

With that said, though, as someone who has fought through SQL 2000 e-mail to the point where there isn't a killer reason to move over (this includes even queing email when the the SQL Server cannot reach the Exchange Server, logging, and auditing), the big issue for me is:

"unless of course for backward compatibility, legacy applications, etc."

Yesterday we heard that complex DTS packages will probably require work and don't hold one's breath for continued DTS support. What are the issues with Mail and what does the future hold?

Sincerely yours,
A late adopter if not skipping 2005 completely




Everett Wilson
ewilson10@yahoo.com
Post #247865
Posted Tuesday, January 3, 2006 9:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 1:52 PM
Points: 8, Visits: 190

Very nice article on initial Database Mail configuration.  I wanted to point out that there are a few more steps required to have SQL Server Agent be able to use Database Mail for jobs, alerts, etc.  This is from SQL Server 2005 BOL, "Using Database Mail with SQL Server Agent":

  1. Enable Database Mail.
  2. Create a Database Mail account for the SQL Server Agent service account to use.
  3. Create a Database Mail profile for the SQL Server Agent service account to use and add the user to the DatabaseMailUserRole in the msdb database.
  4. Set the profile as the default profile for the msdb database.
  5. Choose Database Mail as the mail system for SQL Server Agent. For more information, see How to: Configure SQL Server Agent Mail to Use Database Mail (SQL Server Management Studio).
  6. Restart SQL Server Agent.

(I struck through the steps that were covered in the article, assuming that the account and profile created are Ok to use for SQL Server Agent Mail.)

It's also important to note that SQLMail issues typically fell into a couple of major categories:

  • Reliability problems
  • Configuration & maintenance overhead

With SQLMail you could spend a lot of time installing Outlook, creating operators, assigning notifications to jobs, alerts, etc., on each and every SQL Server (Config overhead)... and even then it may or may not work (Reliability)  

By taking MAPI/SQLMail out of the mix and going to SMTP with failover Microsoft has addressed biggest element of the "reliability" issue.  However, there is still the fact that Database Mail is a distributed system -- if there is some problem with it on one or more SQL Servers, then you may not receive your notifications for those servers.  In other words, although each individual Database Mail instance is inherently more reliable than SQLMail when properly configured and maintained, there is still no outside-looking-in process making objective decisions on the health of Database Mail on all servers, which goes towards "enterprise reliability".

In terms of maintenance overhead, I am sure many of those familiar with setup of SQLMail will look at the Database Mail setup & config process and think, "Well, it sure is different... and easier in some respects... but it's still quite a bit of work".

Database Mail still needs to be configured and tested on each SQL Server, operators need to be created, and the DBA must remember to assign notifications to all jobs & alerts, including new jobs & alerts added after initial setup.  When you start multiplying this by 50, 100, 250 or more SQL Servers as is commonplace now for many organizations, a migration from 2000/SQLMail to 2005/Database Mail starts to get a bit scary.

Microsoft has certainly made a big move in the right direction with Database Mail, but there is still a ways to go before it can truly be considered an "enterprise friendly" notification solution.

Greg Gonzalez
sqlSentry

Post #247867
Posted Tuesday, January 3, 2006 10:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 23, 2009 9:44 AM
Points: 179, Visits: 22
Actually, if SQLMail is set up use a queuing system the reliability issue can be avoided. After playing around with variuos tests the best solution turned out to be rather straight-forward. Once the email is in the queue send the email, if an error is reported then keep the email in the queue and shut down processing of the queue. In three+ years of production no email has been lost despite network/Exchange problems.



Everett Wilson
ewilson10@yahoo.com
Post #247874
Posted Tuesday, January 3, 2006 11:58 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579

I would add #7 to Greg's list:

7. Issue a statement that send an email.

I use Database Mail and for some reason it works. I am using a statement like that:

EXEC dbo.sp_send_dbmail @recipients='yelena.varshal@philips.com',
    @subject = 'Subject of my Email,
    @query ='Select * from ##MyTempTable'
    @profile_name = 'DBAemail',
 @attach_query_result_as_file = 0

I would also add #1.5 if applicable:

1.5 Specify the database.

In my CTP the next screen after you click Setup on the first screen tell you to select a certain database to install Messaging Objects. I do understand that this may change in the release that I am in the process of installing now.

 




Regards,
Yelena Varshal

Post #247907
Posted Tuesday, January 3, 2006 3:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 5, 2006 1:47 PM
Points: 69, Visits: 1

"In my CTP the next screen after you click Setup on the first screen tell you to select a certain database to install Messaging Objects" - I suggest you check out the RTM. One change from early CTPs is that Database Mail is now msdb only. The ability to install multiple Database Mail instances has gone in RTM, and probably before, I don't recall exactly.

 



Darren Green
SQLDTS.com   |   SQLIS.com   |   Konesans Ltd
Post #247947
Posted Tuesday, January 3, 2006 4:23 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579

 

Darren,

Thanks for your explanations. I am in the process of installing RTM.




Regards,
Yelena Varshal

Post #247953
Posted Thursday, January 5, 2006 8:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 13, 2013 2:41 PM
Points: 49, Visits: 42
Thanks for the information.  I look forward to 2005 the more I read.


Post #248455
Posted Saturday, January 21, 2006 4:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 2:21 PM
Points: 55, Visits: 168
Great feedback from ewilson & Greg....clearly the organization's needs must be #1. If a lot of time has been invested in implementing SQL Mail, then clearly sticking with that infrastructure would be best, especially the larger the organization....I had hoped my catch-all "unless of course for backward compatibility, legacy applications, etc" captured that. Also, there are indeed extra steps to ensure that job alerts are using database mail...thanks Greg.
Post #252602
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse