Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
Windows Server

SQL Server 2008 : Database Mail (part 1) - Database Mail Configuration Wizard

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
6/13/2011 5:54:02 PM
The first thing you should do when automating maintenance tasks is to set up Database Mail. This will allow you to receive automatic notifications for things like job failures and alerts. After all, what good is automating tasks if you constantly have to go out to each server and make sure everything looks okay?

There are a couple of ways to configure Database Mail. One is using the Database Mail Configuration Wizard and the other is good old T-SQL. First, we will walk you through the wizard and explain the options available when setting up Database Mail, and then we will provide a script using T-SQL that you can use to perform the same actions.

1. Database Mail Configuration Wizard

Start the Database Mail Configuration Wizard by expanding the Management folder in the SQL Server Management Studio Object Explorer, right-click Database Mail, and then select Configure Database Mail from the context menu. This will bring you to the Welcome screen shown in Figure 1.

Figure 1. Database Mail Configuration Wizard Welcome screen

Select Next to proceed to the Select Configuration Task screen, as shown in Figure 2. (You may also choose the option to skip the Welcome screen in the future.)

Figure 2. Select Configuration Task screen

Since this is the first time we are setting up Database Mail, we will select the first option that will perform all the tasks required to initially set up Database Mail. Select Next to continue. If you have not enabled Database Mail on the server, you will be prompted to do so before continuing to the New Profile screen shown in Figure 3.

Figure 3. New Profile screen

Enter the name and an optional description for the new profile you are creating. A profile is a group of database mail accounts you can prioritize to increase the chances of receiving an email if one of the email servers is unavailable. To add an account to the mail profile, select the Add button to display the New Database Mail Account dialog box, shown in Figure 4.

Figure 4. New Database Mail Account dialog

The New Database Mail Account dialog contains all the information needed for SQL Server to send an email to a Simple Mail Transfer Protocol (SMTP) server. You must create a separate mail account for each SMTP server you would like to use. Enter the appropriate information for your organization. The following list describes the available options when creating a new mail account.

  • Account Name: Name that will be used to identify the database mail account.

  • Description: Optional description used to identify the database mail account.

  • E-mail Address: Email address that the mail is being sent from. This does not have to be an actual email account. You could use something like Severname@company.com, just to indicate the email came from a specific server.

  • Display Name: Optional name that is displayed on email messages to indicate who the email is from.

  • Reply E-mail: Optional email address that will be used when someone replies to an email sent by this account.

  • Server Name: Server name or IP address of the SMTP server that is used to send emails for this account.

  • Port Number: Port number that is used to connect to the SMTP server.

  • This Server Requires a Secure Connection (SSL): This option will encrypt communication between SQL Server and the SMTP server. You must have a certificate installed for SQL Server in order to use this option.

  • Windows Authentication Using Database Engine Service Credentials: This option will use the MSSQLServer service credentials to connect to the SMTP server.

  • Basic Authentication: This option allows you to specify a username and password in order to connect to the SMTP server.

  • Anonymous Authentication: This option connects to SMTP servers without passing any login credentials and is used for SMTP servers that do not require authentication.

Once you have made the proper configurations in the New Database Mail Account dialog box, select OK to return to the New Profile screen. Repeat the process for any additional mail accounts you would like to add to the profile. If you add multiple mail accounts, you can configure their priority by highlighting an account and selecting Move Up or Move Down. Once you have configured all the new mail accounts, select Next to continue to the Manage Profile Security screen, shown in Figure 5.

Figure 5. Public Profiles tab of the Manage Profile Security screen

You can configure Database Mail profiles as public profiles or private profiles. If you configure the profile as public, anyone who can access the msdb is allowed to use the profile to send mail. If you configure the profile as private, the profile will be limited to only specific users. You can also have one default public profile and one default private profile. A default profile allows you to send mail without specifying a profile name. When an account has access to both a default private profile and a default public profile, the default private profile will be used. If you would like to make the profile public, select the Public check box next to the profile name; if you would like to make the profile private, select the Private Profiles tab, as shown in Figure 6.

Figure 6. Private Profiles tab of the Manage Profile Security screen

The key difference between these two tabs is that the Private Profiles tab provides you with a User Name drop-down list that will allow you to grant access to a profile for a specific user. To grant profile access to multiple users, select each username from the list, and then select the Access check box next to the profile name. Once you have configured the profile security, select Next to continue to the Configure System Parameters screen, shown in Figure 7.

Figure 7. Configure System Parameters screen

You can use the Configure System Parameters screen to define the system parameters for an entire instance of SQL Server; any changes you make here will apply to all Database Mail profiles and accounts. The following list describes the available options when configuring system parameters.

  • Account Retry Attempts: Number of times Database Mail will retry to send mail using each account in a profile. For example, if you set the Account Retry Attempts parameter to three and you have two accounts in a profile, each account will retry to send mail three times.

  • Account Retry Delay (Seconds): Number of seconds Database Mail will wait between retries. This delay is not the delay between each account in a profile. Database Mail will attempt to use all accounts in a profile, and then wait the defined number of seconds before trying all accounts again.

  • Maximum File Size (Bytes): Limits the size of an attachment.

  • Prohibited Attachment File Extensions: Comma-delimited list of file extensions that are not permitted as attachments when sending Database Mail.

  • Database Mail Executable Minimum Lifetime (Seconds): The minimum time the external mail process will remain active even if there are no items in the mail queue. If items are in the queue, the external mail process will remain active until all items are processed.

  • Logging Level: There are three logging levels you can use to send events to the Database Mail log: Normal, Extended, and Verbose. The Normal logging level will log errors. The Extended logging level will log errors, warnings, and informational messages. (Extended is the default logging level.) The Verbose logging level will log errors, warnings, informational messages, success messages, and internal messages. You should generally only use the Verbose logging level for troubleshooting purposes.

Once you have the system parameters configured, select Next to continue to the Complete the Wizard screen, shown in Figure 8. Review the configuration options and select Finish to complete the wizard.

Figure 8. Complete the Wizard screen
Other -----------------
- SQL Server 2008 : Monitoring Your Server - Using Server-Side Tracing
- BizTalk 2009 : Host Integration Server 2009 - Security
- Microsoft Dynamics CRM 2011 : Attaching Files to Accounts and Contacts
- Upgrading to SharePoint 2010 : Performing Post-Upgrade Configurations
- Upgrading to SharePoint 2010 : Performing an In-Place Upgrade (part 2) - Configuring the In-Place Upgrade
- Upgrading to SharePoint 2010 : Performing an In-Place Upgrade (part 1) - Installing the In-Place Upgrade
- Upgrading to SharePoint 2010 : Performing a Database Attach Upgrade
- SQL Server 2008 : Monitoring Your Server - Leveraging the SQL Server Profiler
- SQL Server 2008 : Monitoring Your Server - Monitoring Disk IO
- Troubleshooting Exchange Server 2003 Server Migration and Interoperability (part 2) - Using the Netdiag and Dcdiag Command-Line Utilities
 
 
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer