Warning: mentioning SQLMail to some DBAs may cause undesirable reactions. The notoriously bad press that surrounded the SQLMail feature in previous versions of SQL Server can now come to an end. So can the hassles of configuring MAPI profiles, installing the Outlook client on your production server, and living with the risk of a disruptive xp_sendmail call taking down the SQL Server process.
Database Mail is an SMTP-based e-mail solution for SQL Server. This mail feature provides users with the following:
Before we dive into the Database Mail internals, let’s define a few basic concepts in the Database Mail world. An account is name, description, and authentication information for a specific SMTP account. A group of these accounts is referred to as a profile. Having a group of accounts is useful, since this adds a layer of reliability, because the mail would still be sent by a different account in the profile if one particular SMTP server was down. If you’re a visual person, you may see things more clearly by looking.
Pictoral representation of a Database Mail profile
Figure 8-24 shows one profile defined as MyProfile. This profile has two accounts created within it: Rob_DBA and Tammie_DBA.
When users send mail using Database Mail, they use the new stored procedure sp_send_dbmail, as in this example:sp_send_dbmail @profile_name='MyProfile',
One of the parameters in this stored procedure is @profile_name. When the user specifies a profile, Database Mail looks at the first account that is defined for the particular profile and attempts to send the e-mail using this account. If for whatever reason the send fails, such as when the SMTP server is offline, Database Mail will wait 100 seconds (the default Account Retry Delay setting), and then proceed down the account list and attempt to send the mail using the second account. If this account fails, it will continue to the next account, and so on. When it has reached the end of the account list, the send will fail. If you wanted Database Mail to loop back around and attempt the first account again, you could set the Account Retry Attempts setting to the number of times Database Mail will “round-robin” the account list. These global settings will be described in detail.
So are profiles just a way to group accounts? Well, sort of. There are two types of profiles: public and private. Public profiles can be accessed by any valid msdb database user. With private profiles, administrators can limit which users can use a specific profile.
Database Mail installs both configuration and mail item information in the msdb database.
Configuring Database Mail
The Database Mail Wizard can be accessed from the Configure Database Mail context menu off the Database Mail node. This wizard is used to manage accounts, profiles, and Database Mail global settings. Clicking Next on the welcome screen will bring you to the Select Configuration Task page. This page will determine which function the wizard should perform. By default, the option to configure the mail host database is selected, so you can simply click the Next button.
Select Configuration Task page
You need to have at least one profile configured in order to use Database Mail. Add a profile through the New Profile page. The profile name is what users will use when sending e-mail, so make sure it’s easy to remember. The description is optional and is used to provide more information about the profile.
New Profile page
At the bottom of this page, you can define one or more accounts that this profile will use. Clicking Add will pop up a dialog box that allows you to select an existing account or create a new account. Once an account is defined, it’s available for all profiles.
Clicking Next now brings you to one of the more interesting user interface conglomerations without SQL Server. The Manage Profile Security page.
Manage Profile Security page
The first thing to notice about this page is it has two tabs: Public Profiles and Private Profiles. When a new profile is created, it’s neither public nor private; basically only sysadmins can use this profile. If the sysadmin marks a profile as public, then any database user in the msdb database can send mail using this profile.
If you click the Private Profiles tab, you’ll notice a combo box that allows you to specify a database user. From the User Name combo box, you can select any username and check the profiles to which to grant them access. Once you finish the wizard, the private profile won’t generally show up unless you have the username selected in the combo box.
Manage Profile Security, Private Profiles tab
The last significant property that needs to be set on a profile is the default profile. If there are no profiles that are set for the default, users will always need to specify the @profile_name parameter in the sp_send_dbmail stored procedure call. If the sysadmin has a default profile defined, users can omit the profile, and Database Mail will use the default profile that is defined.
Clicking Next in the wizard will bring you to the last configuration page, called Configure System Parameters. This page allows you to set the global settings of Database Mail. Table describes each of these parameters.
Configure System Parameters page
Database Mail System Parameters
The next page in the wizard is the summary page describing the actions that will be performed. Following that page is a progress page that will show you the live status of the actions being performed on your database.
Depending on which options you selected, you’ll have a variable number of actions to be performed. In the event of a failure, the status would read “Failed,” and there would be an error message hyperlink in the message column. Clicking this link may provide you with additional information about this error, provided you have an active Internet connection. This online error help feature can be seen throughout all of the SQL Server Management Studio dialog boxes and wizards.
The Database Mail feature isn’t enabled by default on SQL Server. This feature can be enabled via the wizard. If you’re planning on configuring Database Mail entirely through scripts and want to enable Database Mail, simply enable the Database Mail XPs parameter through the sp_configure stored procedure:SP_CONFIGURE 'show advanced', 1
After you’ve configured Database Mail, you’re ready to send mail.
For purposes of this discussion, consider an example where the administrator has created a single profile called MyDatabaseMailProfile. This profile contains two accounts: Rob_DBA using SMTP server SMTP_SVR1, and Tammie_DBA using SMTP server SMTP_SVR2. At this point, the administrator issues the following T-SQL command:Use msdb
The stored procedure will first validate that the required parameters are entered, and then write the message in the sysmail_mailitems table. Having an immediate copy of the mail before it’s “sent” allows the administrator to easily troubleshoot mail problems. After the mailitems entry is created, the stored procedure will call another stored procedure to create an XML document that wraps the mail. At this point, the XML document is sent to a predefined Service Broker queue. Service Broker provides queuing and reliable messaging. Using this feature allows Database Mail to asynchronously send large amounts of e-mail without disrupting other server operations.
When messages are placed on this queue, a SQL Server event notification is raised. At this point, another Service Broker queue is listening for this event, and upon reception, it will spawn the DatabaseMail90.exe process. When this process is instantiated, it obtains configuration information from msdb, pulls messages from the queue, and sends the mail. Upon completion, it will update the sysmail_mailitems table with delivery status and update the sysmail_log table. Since spawning a process is an expensive operation, the process itself will stay alive after processing all the mail items for a time period that is defined in the Database Mail global configuration settings as Database Mail Executable Minimum Lifetime. The default value is 600 seconds, or 10 minutes, and can be changed through the Database Mail Configuration Wizard, as discussed in the previous section.
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.