Database mail was introduced in SQL Server 2005 and it was new feature in SQL Server 2005. Database mail is also available in SQL Server 2008 and 2012.The basic function of SQL Server 2008 Database Mail to send alert message to DBAs with issue related performance, disk space, SQL Jobs, Backup plans, and other changes in the database schema.
In SQL Server, there is another mail component which is called SQLMail. Database Mail is replacement of SQLMail and it is having more security than the SQLMail.
• Database Mail is based on SMTP ( Simple Mail Transfer Protocol)
• Database Mail depends on Service Broker and this service must be enabled
• Databse can be encrypt for additional security
• SQLMail is based on MAPI (Messaging Application Programming Interface)
• For SQLMail, to be enabled MAPI it will require Outlook to be installed
More and More efficient method is Database mail when compared with SQLMail.
This code block will help you to enable Database Mail,
These are the Steps,
1. Connect to the SQL server
2. In Object Explore, Expand the Management Node and Right Click on Database Mail and click on Configure Database Mail
In SQL Server, there is another mail component which is called SQLMail. Database Mail is replacement of SQLMail and it is having more security than the SQLMail.
• Database Mail is based on SMTP ( Simple Mail Transfer Protocol)
• Database Mail depends on Service Broker and this service must be enabled
• Databse can be encrypt for additional security
• SQLMail is based on MAPI (Messaging Application Programming Interface)
• For SQLMail, to be enabled MAPI it will require Outlook to be installed
More and More efficient method is Database mail when compared with SQLMail.
This code block will help you to enable Database Mail,
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO
Create Mail Profile and Account
Ø First you need to create profile. Profile is the main element of Database mail. One profile may contain multiple email accounts. There are 2 types of Profile in SQL Server,
o Public Profile - Can access by any user and these user can send emails
o Private Profile – Need permission to send emails. This is for specific users
These are the Steps,
1. Connect to the SQL server
2. In Object Explore, Expand the Management Node and Right Click on Database Mail and click on Configure Database Mail
3. If you are going to configure Database mail first time you may ask to enable Database mail feature. If it is click yes. Form this window you need to select “ Setup Database Mail by ….” And click Next
4. For this window, you need to give Profile Name and description for it. Description is not a mandatory. This is the place you can configure SMTP server. Click Add button
5. This is place that your SMTP sever configuration need to place
Here, you can use your own mail server configuration. But if you don’t have mail server you can configure with Gmail, Windows Live Mail, Yahoo or any other global mail server provider.
For the demonstration purpose I used Gmail server configuration.
Email address: Your Gmail e-mail ID
Display name: Whatever Name That You want to Display with Your mail ( e.g 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
In the next section, choose Basic Authentication and enter the following information:
User name: Your Gmail e-mail ID
Password: Password for your Gmail ID
Confirm password: Password for your Gmail ID
This link will help you more on SMTP server: http://www.mssqltips.com/sqlservertip/2578/setup-sql-server-database-mail-to-use-a-gmail-hotmail-yahoo-or-aol-account/
Then Click OK button
Then Click Next button to continue
6. From This window you select you profile type ( Public or Private) and set Default Profile as well
After configuring Profile type click Next button
7. Here, You can configure the System Parameter and can specify prohibited attachment extensions
I didn't change other values and click Next button
8. Finally You will get the confirmation window for Database Mail Set up
Click Finish to complete the set up and after set up complete click Close button
To Test Your Mail Configuration,
Go to the Object Explore, Expand the Management Node, Right Click on Database Mail Click Set Test E-Mail
From This window you can sent Test e-mail to your email account, for this you can use any email address
This window will help you to get confirmation on the sent mail (No of sent mail may different)
Check you Inbox and you will see Test mail from your SQL Server
No comments:
Post a Comment