Friday, January 28, 2011

SQL SERVER – Difference Between Database Mail and SQLMail


In recent user group meeting in my city Ahmedabad, I have found that not every user knows difference between these two features of SQL Server. I do not blame any user for not knowing difference between Database Mail and SQLMail as this is very confusing sometime. I will try to explain this concept here.

Database mail is newly introduced concept in SQL Server 2005 and it is replacement of SQLMail of SQL Server earlier version. Database Mail has many enhancement over SQLMail. Database Mail is based on SMTP (Simple Mail Transfer Protocol) and also very fast and reliable where as SQLMail is based on MAPI (Messaging Application Programming Interface). Database mail depends on Service Broker so this service must be enabled for Database Mail. Database Mail can be encrypted for additional security. SQLMail is lesser secure as it can encrypt the message as well anybody can use SMTP to send email. Additionally, for MAPI to be enabled for SQLMail it will require Outlook to be installed. All this leads to potential security threat to database server.

In summary, I suggest if you are using SQLMail, it is right time to upgrade to Database Mail.

If you still want to use SQLMail you will have to enable it with specific commands.

EXEC sp_configure 'SQL Mail XPs', 1
GO
RECONFIGURE
GO

Again, I suggest upgrade to Database Mail and start to stop using SQLMail.


SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database



Today in this article I would discuss about the Database Mail which is used to send the Email using SQL Server.  Previously I had discussed about SQL SERVER – Difference Between Database Mail and SQLMail. Database mail is the replacement of the SQLMail with many enhancements. So one should stop using the SQL Mail and upgrade to the Database Mail. Special thanks to Software Developer Monica, who helped with all the images and extensive testing of subject matter of this article.

In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out. 1) Create Profile and Account 2) Configure Email 3) Send Email.

Step 1) Create Profile and Account:

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:

Step 2) Configure Email:

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Step 3) Send Email:

After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USE msdb
GO
EXEC sp_send_dbmail @profile_name='PinalProfile',
@recipients='test@Example.com',
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'

After all validations of the parameters entered are done, certain stored procedures are executed and the mail is queued by Service Broker, read more at SQL SERVER – Introduction to Service Broker.

Database Mail keeps copies of outgoing e-mail messages and displays them in the sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems . The status of the mail sent can be seen in sysmail_mailitems table, when the mail is sent successfully the sent_status field of the sysmail_mailitems table is set to 1 which can again be seen in sysmail_sentitems table. The mails that are failed will have the sent_status field  value to 2 and those are unsent will have value 3.
The log can be checked in sysmail_log table as shown below:

SELECT *
FROM sysmail_mailitems
GO
SELECT *
FROM sysmail_log
GO

Status can be verified using sysmail_sentitems table.

After sending mail you can check the mail received in your inbox, just as I received as shown below.



Database Mail set up in SQL Server 2008


Introduction

This article discusses about setting up and configuring SQL Server 2008 Database Mail feature within SQL Server Instance. Database mail was introduced in SQL server 2005 and it was a new feature that Microsoft has added in SQL Server 2005. Database mail is also available in SQL Server 2008. I did not find any difference in Database mail in 2005 and in 2008. Database mail in SQL Server 2008 is an enterprise solution that is used to send emails from Database Engine component of SQL Server. Basic function of SQL Server 2008 Database Mail is to send alert messages to database administrators with issues related to performance, disk space, SQL Jobs, Backup plans, and other changes in the database schema. This is a very neat feature that helps database administrators to keep track of all activities in SQL Server Instances across all servers in the network. Database mail has better performance, easy to set up and more reliable than SQL Mail in SQL Server 2000.  SQL Mail uses MAPI where as Database Mail uses SMTP. Another main point to remember on database mail is, it uses service broker service and this service need to be enabled in order to use Database Mail in SQL Server 2008. By default, database mail will not be enabled; there are different ways to configure it. We can enable it using system stored procedure, configuration manager or from the Database Mail Wizard during the set up. I am using database mail set up wizard to enable it which will be shown below (Figure 3: Enable Database Mail Feature). In setting up database mail, profile creation, account set up, configuring the account and sending test email are the main steps. Each of these steps are explained below.

Note: Database mail feature in SQL Server 2008 is not available in Express Edition.

Create Mail Profile and Account:

First step in Database Mail set up is creating a profile. Mail profile is the main element of Database Mail.  A profile can have multiple email accounts added in that profile. Profile can be of two types, and they are:

Public Profile: A public profile is a mail profile set up in the database mail which can be accessed by any users and these users will have the ability to send emails.

Private Profile:  A private profile is that profile in which users who are granted access to this profile can use to send emails. 

Steps:

  1. Connect to the SQL Server Management Studio.
  2. In the Object Explorer, Go to Management Node,  expand Management Node, and Point to Database Mail option as shown in the figure below:  

Figure 1: Database Mail Folder Screenshot

  1. Right Click on Database Mail, and then Click on Configure Database Mail as shown below.

 

Figure 2: Configure Database Mail Screenshot

  1. Once Configure Database Mail option is clicked, Database Mail Configuration Wizard  appears.  This wizard helps to set up the database mail in SQL Server Instance.  
  2. Click Next on Database Mail Configuration Wizard as shown above. 
  3. Select Configuration Task window comes when Next button is clicked as shown above. Since am setting up Database Mail for the first time therefore am selecting the Set up option as shown in the figure below.  In this set step, new profile can be created, SMTP account will be added, security in the mail profile can be set up and system parameters can be configured.  Click Next after select the set up option.

Note:  In case, if Database Mail is already setup, we can choose to manage database mail and changing system parameters.  

 

Figure 4: Select Configuration Task

  1. When you click Next, Following window comes since Database Mail is not enable on my instance so am going to enable it from here instead of using system stored procedure or configuration manager as I mentioned earlier.

 

 Figure 5: Enable Database Mail Feature

  1. Click Yes as marked in Red to enable the Database Mail and the next comes a window where we can create New Profile for the Database mail and then add new SMTP accounts for this profile.  Profile name is used by different users to send out email notifications.  As shown in the figure below, Profile name and Description of the Profile are created. The name of the Profile that I used is:  SQLServer2008 Database Mail Profile. Description is an optional but it is good to add description about the profile. I have added a short description as shown in the figure.

 

9. After profile name and description of the profile is added, we can add the SMTP Accounts by Clicking on Add…. button as shown in New Database Mail Account Window figure below.

Account name is Database Mail, Description is an optional.  I have used my Gmail account as an Outgoing SMTP since I do not have mail server set up on my machine. 

Under Outgoing Mail Server SMTP:

E-Mail Address:   abi.chapagai@gmail.com

Display Name: SQL Server

Reply Email:  It can be blank or we can use same email as above.

Server Name:  smtp.gmail. com, this is SMTP server. 

Port Number:  Gmail smtp server port number to be used is 587 but default port number is 25.

Secure Connection: We have to select SSL connection as shown in the picture for gmail.

Basic Authentication:  Provide gmail account and password for this account.

 

 Figure 6: New Database Mail Account Screenshot

10.  Click OK  to go back to New Profile window as shown below.

 

Figure 7: Profile Window with SMTP Accounts Screenshot

11.  Now, SMTP account details are added in the profile.  Click Next to  Go to Manage Security Window as shown below.

 

Figure 8: Manage Profile Security Screenshot

In this profile security management window, there are two options for selecting profiles which users are going to have access. They are public and private as shown in the figure 6. Public profile can be accessed by all users, whereas private profile is accessed by only specific users.  

I have used Public profile where I have chosen Public profile. Also have to make this profile default, so have to select Yes option from the drop down menu from Default profile as shown in Figure 6.  

12.  Click Next button to go to Configure System Parameters window as shown below.

 

Figure 9: Configure System Parameters Screenshot

            In this window, we can see the information about how many times we can retry to get the mail, how long to wait when we retry the mail, maximum email size, what are the prohibited attachment extensions, Minimum life time for Database Mail and the logging level.  Default prohibited attachment extensions are: exec, dll, vbs, and js.  I have added two more extensions that I do not want to get the attachment from and they are; com and bat, which are shown in the picture above. 

To add more prohibited attachment file extensions, click on … on the right side of Configure System Parameters Window on Prohibited Attachment file Extensions under Systems Parameters column which is shown in Figure 7.  Once you add more extension values, Click OK to go back to Configure System Parameters Window as shown below.  There are three levels of logging namely normal, verbose and extended.

I am using extended logging level as is the default one.

 

Figure 10: Configure System Parameters Screenshot with Logging Level Configuration

13.  Click Next to go to the Confirmation Window for Database Mail Set up and is shown below.  Click Finish to complete the Database Mail set up steps.

 

Figure 11: Completing Database Mail Set up Screenshot

Once finished button is clicked following Configuring window comes where we can see the Action, status and messages. If there are errors, there will be error messages.  Click Close button to close the Database Mail Configuration Wizard.

 

Figure 12: Configuring Database Mail screenshot

Send Test Email

To send the test email, go to Database Mail as shown in (Figure 1: Database Mail Folder Screenshot)

Right click on Database Mail folder and the Click on Test Email … as shown in figure below.

 

Figure 13: Send Test Email Screenshot

After Send Test E-Mail is clicked, following screen comes where Database Mail Profile, to which email address this message will be sent, subject of the message and body of the test email. I am using my email address, abi.chapagai@gmail.com where the email will be sent. Click on Send Test E-Mail and this email will go to my Gmail inbox.

 

Figure 14: Sent Test Email Screenshot

Here is the email that I got after clicking the Test E-Mail in my Gmail inbox.

Figure 15: Screenshot of Gmail Inbox with Message from DB Mail

Conclusion

Setting up database mail in SQL Server 2008 is very straightforward and easy to set up.


SQL Server Database Mail to send email using your Windows Live Mail Account or your GMail Account


This post comes as a response to requests from many of our customers, who want to know the step by step process on how to configure SQL Server Database Mail to send emails using their Windows Live Mail Account or their Gmail Account.

If you are using SQL Server 2005 or higher, you might have noticed that there is now a "Database Mail" (DB Mail) option under "Management". This is very different from the "SQL Mail" that we had on previous versions (it still exists under Management\Legacy). Using DB Mail, you no longer have to configure a mailbox on your machine, and you certainly do not need to run the SQL Server or the SQL Server Agent under the account you want to send emails from. Moreover, you can have multiple mail profiles and you can decide which account to use under various conditions.

So, here we go on the steps to configure DB Mail:

  1. Right-Click on Database Mail and choose Configure Database Mail.

    DBMail01
  2. This starts the Database Mail Configuration Wizard. Click Next.
  3. In the Select Configuration Task screen, choose "Set up Database Mail by performing the following tasks:" and click "Next".

    DBMail02
  4. Now, you will be required to enter a Profile Name. This can be any Arbitrary Name that will help you identify the Profile. You might also want to add a Description. Now, click on Add.

    DBMail03
  5. Now, you are prompted to create a New Database Mail Account. Enter any Account Name and Description. The other parameters are as follows:
    • For configuring Windows Live Mail:

      Email address: Your Live e-mail ID
      Display name: Your name
      Reply e-mail: Any reply-to email account
      Server name: smtp.live.com
      Port number: 25
      This server requires a secure connection (SSL): Checked ON

      In the next section, choose Basic Authentication and enter the following information:
      User name: Your Live e-mail ID
      Password: Password for your Live e-mail ID
      Confirm password: Password for your Live e-mail ID

      The configuration should look like the screenshot below. Now, click OK.

      DBMail04
    • For configuring Google Mail (Gmail):

      Email address: Your Gmail ID
      Display name: Your name
      Reply e-mail: Any reply-to email account
      Server name: smtp.gmail.com
      Port number: 587
      This server requires a secure connection (SSL): Checked ON
      (Settings looked up from Gmail Help)

      In the next section, choose Basic Authentication and enter the following information:
      User name: Your gmail ID
      Password: Password for your gmail ID
      Confirm password: Password for your gmail ID

      The configuration should look like the screenshot below. Now, click OK.

      DBMail05

  6. Back on the New Profile screen, click Next. The next Manage Profile Security screen allows you to set the Public Profiles, the Private Profiles and the Default Profiles for each of the Public and Private Profiles.

    DBMail06
  7. Click Next. This will move us to the Configure System Parameters screen. You may want to tweak the parameters; however, for demonstration purposes, we will keep these as default. Now, click Next.

    DBMail07
  8. In the Complete the Wizard screen, review the parameters and click Finish. Ensure that all the 5 Actions succeed and then click Close.

    DBMail08
  9. Your DB Mail should now be configured successfully and you should be all setup to send emails using DB Mail. But before we confirm success, we will like to send a test email and confirm receipt. To do that, right-click on Database Mail and choose Send Test E-Mail…

    DBMail09
  10. Enter a To: email id and click Send Test E-Mail.

    DBMail10
  11. You should receive the email in a few seconds. If you have received the email, you have been able to configure DB Mail successfully, and you can now start using DB Mail.

Hope this post will be helpful.


Thursday, January 27, 2011

IMAP vs. POP3 E-mail Accounts


IMAP vs. POP3 Email Accounts

Basically both IMAP and POP are different protocols for handling e-mail. Each has its own unique function and purpose. Protocol allows IMAP to deal with e-mail in a different way than POP does. POP is basically a flow through entity—it just passes on the information to you at your e-mail program. IMAP on the other hand, is interactive with your e-mail program. Let's take a look at the uniqueness of both.

POP

POP stands for "Post Office Protocol". It works very simply. When the POP e-mail server receives e-mail it stores it on the server until you to request it. By simply opening your email program (e.g., Outlook) you request the e-mail from the server by pressing the "Send" or "Receive" button. The e-mail program in essence asks the server if there is any mail waiting. If there is, it tells the server to send it to you.

When the POP server receives your request for mail, it sends the entire message to your e-mail program. Once you receive the email, the message is no longer stored on the server unless you specifically tell it to keep a copy.

IMAP

IMAP stands for "Internet Message Access Protocol". It allows you to download e-mails from the server to your e-mail program the same as POP does. However, the difference is that when you request your e-mail from the server it sends a copy rather than sending the entire e-mail. It keeps a copy of the e-mail on the server while simultaneously keeping a copy on your computer.

You may be wondering what happens if you have certain messages on your local computer and IMAP has different messages on the IMAP mail server? IMAP has built in intelligence to handle this task. When you connect the IMAP mail server with your local computer, it senses that there are differences between the local computer and the mail server. It then synchronizes both so that they have the same information.

For example, if you delete messages, compose more and have sent others, this information will be synced up with the IMAP server so that the IMAP server will delete the copies of the messages that were deleted. By the time you log off the IMAP server you have two complete copies of all of the e-mail tasks performed: one on the IMAP server and one on your local computer.

Advantages

POP

IMAP

Relatively straightforward

Slower but more redundant

Faster (sends your e-mails and gets e-mails from you)

Keeps a copy of everything you do on the server

The downside is that if you lose e-mails on your computer you have lost them forever (unless you have saved a copy on the server)

The downside is that it takes up more space



IMAP (4)

POP3

Stands for 'Internet Message Access Protocol' Stands for 'Post Office Protocol 3'
Uses port TCP:143 Uses port TCP:110
RFC 1730 RFC 1939
Allows email client to access/ manipulate messages on a server Allows email client to download emails locally
Saves bandwidth as only email headers are displayed in client. Consumes bandwidth as entire email message gets downloaded
Since messages are stored in server, email messages are accessible from anywhere Email message can be viewed from the machine where email was downloaded.



SQL Server 2005 Installation - Maintenance Plan


Maintenance plans can be created using the Maintenance Plan Wizard or using the design surface. The Wizard is useful if the DBA wants to create a basic maintenance plan. If he intends to create enhanced work flow then, it is advisable to use the design surface. Maintenance Plans are displayed only to users connected using the Windows Authentication.

Invoking The Maintenance Plan Wizard from Add/Remove Programs.

One method of accessing the Maintenance Plan Wizard is by navigating to the Add/Remove programs panel of the Operating system. Each option of SQL Server is listed in the panel.


1. In Control Panel, double-click Add or Remove Programs.



2. Under Currently installed programs, click the instance of SQL Server to configure, and then click Change.



3. On the Change or Remove Instance page, click Change Installed Components.



4. On the Feature Selection page, select or clear features.


5. Click Next, and then follow the instructions on subsequent wizard pages.



Invoking The Maintenance Plan Wizard from SQL Server Management Studio.

The Maintenance Plan Wizard helps set up the core maintenance tasks for optimum performance of the SQL Server components.


To start the Maintenance Plan Wizard


1. Expand the server.



2. Expand the Management folder.



3. Right-click Maintenance Plans and select Maintenance Plan Wizard. This launches the wizard and you can now step through and create a plan customized to meet your maintenance requirements.



The first step is to give a name to the Maintenance plan along with a description. Enter the name, description, the server details and authentication mode and click Next.



The next step is to specify the areas where the DBA wants to set up Maintenance options.



The Execute SQL Server Agent job cannot be selected unless jobs have been assigned.



The Next screen prompts the Administrator select the order in which he wants the maintenance tasks performed. He can move the items up and down the list until he is satisfied with the order using the Move up.. and move down… buttons.



The next task is to specify the details for each task. The DBA is taken through Wizard screens for each of the tasks he has selected. The first is the Shrink Database task as per the list above. The DBA now specifies the databases in the window that pops up(illustrated below) when he clicks on the Databases combo box. After specifying the databases he returns to the Wizard screen and specifies the event at which the Shrinking should happen and the amount of free space that should remain after the shrink. He has also to specify whether the space should be retained or returned to the operating system.




The Next task is the definition of the Database check integrity task. By default this is the first task. The user has to select the databases and specify whether indexes have to be included or not. The window that pops up is similar to the one above.



The Next task is to enter the specifications for the Reorganize Index task. Select the tables from the popup window and specify the tables and views required. The Administrator has to also specify whether he wants to compact large objects or not.



Rebuilding the Index involves selection of the databases, objects and setting the space options. The DBA can also set Advanced options such as Pad Index, Sort results to tempdb, Ignore duplicate keys and Keep index online while reindexing.



The Update statistics task prompts the user to select the database, objects and the update options.



The Cleanup job specifies what needs to be backed up before cleaning.



The Next task is the definition of Back up options. The first is for Full task. There are a number of options which the user has to specify.



The next backup options are for differential backup. The user has to specify which databases have to be backed up, where it has to be backed up and so on…The user can also set up the option for a back up verification check by checking the check box provided for the purpose.



The Next options required to be set are the Transaction log backup options. Again the user has to specify what to backup, where to backup and so on.. He can also specify a backup integrity verification check.



The Next screen prompts for Plan properties. The user has to specify whether he wants to retain default properties or he wants to change them. If he needs to change them, he has to click on the Change button and navigate through the screens setting the options he is prompted to give as illustrated in the screen shots below.




If the DBA has not unblocked access to dbo.sp_get_sqlagent_properties by turning on the Ágent XPs for the server, he will get an error message prompting him to turn on the Agent XPs by using sp_configure.



Keeping in mind the security requirements, SQL server 2005 selectively installs and activates key features and services. The intention is to reduce the area of exposure of the system. The default configuration has many of the features turned off. The System Administrator can change these features later using the Microsoft SQL Server Surface Area Configuration tool. This tool helps the Administrator enable or disable features, services and network protocols for remote connections. We shall be touching upon how to configure internal settings a little later in this lesson. For the present the DBA needs to understand that an error will be thrown up if the Agent XPs have not been enabled. To enable the Server Agent XP double click on the item in the object explorer and start the service from the SQL Server Configuration manager.



If the SQL Server Agent is enabled, the Wizard will go through the following screens to complete the task of creating the maintenance plan.