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,
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