다음을 통해 공유

A detailed walk-through of ‘Database Mail’ system in MS SQL Server

  • 아티클

MS SQL Server is a database engine that stores data in multiple tables, and is used at the back-end of several desktop as well as Internet-oriented applications. The data stored in a database table in MS SQL Server can be retrieved through execution of certain queries in predefined formats. MS SQL Server is available in multiple versions and the most recent version is SQL Server 2014.

Database Mail

MS SQL Server 2008 R2 features ‘Database Mail’ - one of the most significant features of MS SQL Server Engine that allows database applications to send email messages to the users and groups; and therefore, is termed the enterprise solution for automated emails. Based on a predefined message format, a message can contain information as an attachment received from any of the resources available on the network.

Features

Database Mail in SQL Server is not active by default, are thus requires manual activation by using one of the Database Mail Configuration Wizard, the sp_configure stored procedure, or Surface Area Configuration feature of ‘Policy-based Management’. Besides, mentioned below are some prominent features of Database Mail:

Scalability

  • Database Mail features immediate, asynchronous, and background delivery of email messages. Sending an email messages requires calling ‘sp_send_dbmail’ that in turn passes a request to ‘Service Broker’ queue. On the immediate return of stored procedure, the request is passed to an external email component, which delivers it to the intended recipient.
  • Multiple accounts and profiles within SQL Server instance can be created to broadcast emails to multiple email servers, while only the profile set as default can send email.
  • The whole setup is also compatible with 64-bit SQL Server installation.

Reliability

  • Database Mail does not require an EMAPI (Extended Message Application Programming Interface), but SMTP (Simple Mail Transfer Protocol) to send emails to the users. In fact, Database Mail is accessible even in the absence of EMAPI on the computer running SQL Server.
  • The email sending component is set up outside the SQL Server to minimize it effects on the Server setup. The SQL Server keeps collecting the messages even if the external setup fails to deliver them to the intended users.
  • Multiple SMTP servers can be specified with one Database Mail profile to send email messages even if the current server fails.

Security

  • Database Mail has its stored procedures disabled by default, in order to reduce the occupied space on the disk. Sending Email through Database Mail requires membership of the ‘DatabaseMailUserRole’ database role in the msdb database.
  • The profiles created on Database Mail are secure. The admin has selects the msdb users or groups who can access a particular profile of Database Mail, where users can be all or only selected ones. However, public profiles are available to all users.
  • Though the size for email attachments is already specified, it can be changed using the ‘sysmail_configure_sp’ stored proceure, if required.
  • The list of the file extensions can be edited to allow or disallow attaching particular types of files in emails using the ‘sysmail_configure_sp’ stored procedure.
  • To attach a file stored in a folder on computer, Database Mail requires access permission, as it runs under service account of SQL Server Engine.

Supportability

  • The information of the Email accounts created in the Database Mail is maintained within SQL Server Database Engine. Besides, a convenient and user-friendly interface is available with the Database Mail Configuration Wizard. However, the same can also be created using Transact-SQL.
  • SQL Server, Microsoft Widows application event log, and the tables in the msdb database store email activities of Database Mail.
  • The duplicates of each email along with attachments (if any) are present in the msdb database. Thus, it is quite easy to audit and review both Database Mail and retained messages.
  • Emails sent are in a predefined HTML format.

Prime Components of Database Mail

A Database Mail automated system is built with many major components, which as a whole operate the proper execution the email system, including the email messages delivery to the correct recipients. Following are the prime components of Database Mail system in SQL Server Engine:

  1. Configuration and Security Components: The configuration and security information of the Database Mail are stored in the msdb database, and are used to create multiple accounts and profiles for email broadcast across multiple servers.
  2. Messaging Components: The msdb database in Database Mail functions just like the mail-host database that is intended to hold all messaging objects that the Database Mail is going to use for sending email messages. These messaging objects incorporate ‘sp_send_dbmail’ stored procedure as well as the data structure, holding the information regarding email messages.
  3. Database Mail Executable: An external program to read email messages from a queue in the msdb database and send them to intended email servers.
  4. Logging and Auditing Components: The logging information is stored in both msdb database and event log in the Microsoft Windows operating system.

In order to configure Database Email service in SQL Server, one does not need to understand the whole architecture of the Database Mail system. However, it is still better to learn how to configure Database Mail service in MS SQL Server 2008 R2.

Configuring Database Mail

MS SQL Server 2008 R2 allows configuring Database through an inbuilt utility called ‘Database Mail Configuration Wizard’, which facilitates managing mail configuration objects to enable Database Mail. In fact, ‘Database Mail Configuration Wizard’ is a convenient way to configure and manage mail objects.

Conditions: 1. User must be a member of the ‘sysadmin’ fixed server role to be able to configure ‘Database Mail Configuration Wizard’. User must be a member of the ‘DatabaseMailUserRole’ database in the msdb database to be able to send Database Mail.

In case both the conditions are satisfied, implement the following steps to configure Database Mail:

  • Connect to an instance of SQL Server from Object Explorer.
  • Click ‘Management’ to expand it and right-click ‘Database Mail’, and then choose ‘Configure Database Mail’.
  • If you are setting up Database Mail for the first time, select the option ‘Set up Database Mail’.

Important: A database lock is required in order to enable the SQL Server Service Broker in a database. In case Service Broker is unavailable or deactivated in msdb, stop SQL Server Agent to make Service Broker get the required database lock.

  • For some particular maintenance tasks, select one the options:
  1. Manage Database Mail accounts and profiles
  2. Manage profile security
  3. View or change system parameters

Once Database Mail is configured the right way, it is able to send email messages to intended recipients in a secure and efficient manner. Since the emails received by end users are system generated, they are not supposed to respond to those messages.