Database Mail In SQL Server

I am here again with another article on SQL Server. But before proceeding, let’s have a look at the following conversation which I had with my SQL Server recently. I’m sure at the end of the conversation; you’ll find it the most interesting conversation ever. So here the conversation starts.

Me: Hey SQL Server, how are you and howzz your databases?

SQL Server: I’m fine! Databases are also good, taking their backups, but one of your databases is very large and giving me trouble while taking its backup.

Me: Ohhh! Why? Any issue?

SQL Server: Come on, you know the answer; you don’t have that much space in your backup drive! I’m telling this for last week but you’re not giving me any response on this.

Me: I already informed our Networking team to add 500+ GB space to the drive. So by this Friday, you’ll have enough space.

SQL Server: Ohh! Awesome. Then why are you looking tense? Any problem?

Me: Yes, this weekend I’m going for a trip for two weeks and no other DBAs are available here to check the daily backups, space issues, and other database activities.

SQL Server: What’sthe  big problem in that?

Me: The problem is who’s going to do all these for next two weeks?? I can’t tell other DBAs to come here on daily basis to check all these for two weeks. It would be very difficult.

SQL Server: Ohh! Why disturb another DBA or to have any tension when I’m here?

SQL Server: You go and enjoy your trip; I’ll do these activities and will keep you updated.

Me: What do you mean? It’s not a Joke OK. It’s really serious.

SQL Server: Are dude! Why having tension, why don’t to use my Database Mail feature for this purpose?

Me: Database Mail?

SQL Server: Now don’t tell me you’re not aware of it?

Me: No, I know this, yes I think so.

SQL Server: I know you. Don’t lie ok, after installation; you didn’t yet enable this feature in me. Thank God, this situation came, now I can use this awesome feature.

Me (Thinking): Are yaar! What awesome feature.

Me: Ok, I accept I don’t know. Could you please just tell me about this? I mean, what it is all about, how to enable it, what are the things I need to know, etc. etc.

SQL Server: Ok, let me take you through this journey. I’ll show you the picture in four parts as listed below:

• Introduction,
• Configuration,
• Sending Test mail
• DB Mail objects.

SQL Server: Let’s begin with introduction part first,

Introduction

Database Mail feature was introduced in SQL Server 2005 asa replacement to SQLMail. This feature enables SQL Server to send email to any user. The message can contain query results, or any other files.

Database email is designed for reliability, scalability, security, etc. The following are some important points regarding Database Mail.

• Database Mail is not active by default; either we can configure it via Configuration Wizard or by using sp_configure stored procedure.
  
  
• To send mail you must be a member of the DatabaseMailUserRole in the msdb database.
  
  
• It uses SMTP protocol to send emails from SQL Server.
  
  
• All details related to Database Mail are stored under msdb database; we’ll explore them later in this conversation.
  
  
• Database Mail requires at least one Database Mail profile which allows database administrators to reconfigure stored procedures and database applications that use e-mail without changing the application code.
  
  
• You also need a Database Mail account which only used for Database Mail. This account does not correspond to SQL Server account or any Windows account.
  
  
• You can also send HTML formatted emails from Database Mail.
  
  
• You can create multiple profiles under a SQL instance and you can choose any one of the profile to send emails to your users.

When we send any email to any person or any party, SQL Server tells MSDB database to connect an executable file stored at location “MSSQL\Binn\” named “DatabaseMail.exe”. With help of this file SQL Server sends all emails using configured SMTP account.

SQL Server: Is this much information enough for you to understand What Database Mail is?

Me: Oh Yes! Sounds good.

SQL Server: Now let’s move to the configuration part. Here I’ll show you how you can configure Database Mail via “Database Mail Configuration Wizard”. Just look and follow the steps.

Configuration:

SQL Server: Connect you SQL Server instance, expand Management and right click on Database Mail as shown below.



Me: Ok Ok!

SQL Server: Don’t interfere in-between, just follow the steps.

Me: Ohh Sorry!

SQL Server: Next you’ll get the below welcome window. This is nothing but a small description about this feature. Click next.



SQL Server: After clicking, you’ll getthe below configuration window where you can setup new mail account, profiles, you can also modify existing accounts. As we’re doing this for the first time, we’ll select first option and Click next.



SQL Server: After clicking, It’ll tell you whether your Database Mail feature is enabled or not, if not it’ll ask you for the same as shown below.



SQL Server: Click yes and you’ll get the below Profile window to setup new profile. Enter profile name and description and click on Add button to add SMTP account as shown below.



SQL Server: After clicking, you’ll get below window to setup new Mail Account. Enter below information:

Account Name:

• Email Address: From which you want to send email.
• Display Name: What name you want to display instead of that email address.
• Reply Email: Email address to use as the reply-to information for the account.
• SMTP Server Name:
• Port No.:
• Select Authentication Type: Basic authentication.
• Username: Username of your SMTP account.
• Password: Password for the account. (If you’ve enabled 2-step verification for your account, use the generated app password under this field).
• Confirm Password: Re-enter your password.

Click OK.



SQL Server: After clicking, you’ll get  the below window with created SMTP account. Click next.



SQL Server: After clicking, you’ll getthe below Profile Security window where you can set public and private profiles. Select your Profile and Click next.



SQL Server: After clicking, you’ll get the below System Parameter window.



Here you’ll setup parameters for your Database mail. Below are the options you’ll find to configure.

• Account Retry Attempts: The number of times your database mail will try to send email from the selected profile. If you set it to 2 it’ll try 2 times.
  
  
• Account Retry Delay (seconds): This will tell Database mail to wait between next tries. As we’ve selected 60 seconds, if first attempt fails, it’ll retry after 60 seconds. If you mention 120 or 150 seconds, it’ll wait for that much time and will go for next try.
  
  
• Maximum File Size (Bytes): Allow you to set file size limit.
  
  
• Prohibited Attachment File Extensions: Database email allows blocking attachment of files with specified extensions. As in above shown setup, it’ll ignore .exe, .dll and other mentioned extensions while sending emails with attachment.
  
  
• Database Mail Executable Minimum Lifetime (seconds): The number of seconds mention here will database email will wait or remains active even if no emails are there in the queue. In my case it’ll remain active for 600 seconds.
  
  
• Logging level: There are 3 levels as shown below.
  
  
  
  
• Normal: Selecting this option will log errors.
• Extended: This will log errors, warning and other message.
• Verbose: Using this will allow you to log error messages, warnings, and other messages. Mostly this is used for troubleshooting purpose.

After setting all the parameters, Click next.

SQL Server: Next, you’ll get summary of your activity. Click next.



SQL Server: After clicking, SQL Server Start the configuration and you’ll get progress window as shown below. Click Close to complete the setup.



SQL Server: After successfully completing the setup, let’s send one test email. It’s very simple, Right click on Database Mail and select “Send Test Mail”.



SQL Server: After clicking, you’ll get the below email type window. Select Database Mail profile from which you want to send email, Enter email address of the person to whom you want to send the mail, Enter Subject and Body and Click on Send Test Mail as shown below.



After sending you’ll get below small popup window.



SQL Server: And you know what, we can also do this via T-SQL as shown below.



SQL Server: Now below is the email user received from SQLAlerts with a message “Database Mail setup completed successfully”. You can see below snap for the same.



Me: WOW! That’s awesome. But what if I want to see those email, logs, etc.? Where do I check those things?? You told me that you’ll also share those details.

SQL Server: Wow!! Wow!! Dude wait, I remember. Don’t be in toomuch of a hurry, I’ll share those details as well. So, there are various tables, views, stored procedures available in msdb database. You can check those tables and views or stored procedures to check your logs. I’ll show some of the names, remaining details you can find in attached script.

To check Profile details:

Select * from sysmail_profile

To check DB MAIL Account details:

Select * from sysmail_account

To check Server Type:

Select * from sysmail_servertype

To check all mail items:

Select * from sysmail_allitems

To check all sent mail items:

Select * from sysmail_sentitems

To check all queued mails:

Select * from sysmail_unsentitems

To check failed mail items:

Select * from sysmail_faileditems

SQL Server: We’re done with Database Mail and now you know how to play with it. Just add a step to get you alert on Success or Failure in your Database maintenance jobs and you’re ready to go for your trip without any trouble. I’ll keep sending the notifications to you on daily basis.

Me: Thanks man! It’s an awesome feature to enable to get alerts.

SQL Server: I hope you like it.

Me: Ohhh Yes. This entire journey was very informative. Now let’s ask our readers how they find our conversation on this topic.

Me: Hello Readers, so this was the conversation based on Database Mail. Please provide your valuable feedback and comments on our conversation. If there's any mistake in this then please let us know.

SQL Server: Hey Wait! I’m not responsible for any mistake. Readers, if you find any mistake, please let him know, not me.

Me: OK OK, Readers, Please let me know. And you SQL Server don’t act too smart Ok; else I’ll stop your services.

SQL Server: Hahahahahaha! Hahahahahah!