Configuring SQL Servers Mailing Engines


Configuring SQL Server’s Mailing Engines

SQL Mail is the legacy facility that provides a mechanism for the reception of e-mail generated by SQL Server. Database Mail is the new SQL Server process for sending e-mail messages from the database engine. Using either, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network. Database Mail is designed for reliability, scalability, security, and supportability. SQL Mail has been provided for backwards compatibility, while Database Mail provides a more reliable, scalable and securable solution.

Database Mail provides background, or asynchronous, delivery to the platform. It is invoked When you call proc sp_send_dbmail to send a message. This prompts Database Mail to add a request to a Service Broker queue. The external e-mail component receives the request and delivers the e-mail. The proc is non-blocking, which provides scalability.

With Database Mail you can create multiple mailing profiles within a SQL Server instance. This allows you to specify the profile that Database Mail uses when you send a message, keeping the process database or application specific. The profile you create and also be email account specific.

Security and scalabilty is the big deal with Database Mail. Not only is it 64-bit compatible, but the Database Mail stored procedures are secure. Database Mail is disabled by default. The SQL Server Surface Area Configuration tool can be used to enable the Database mail stored procedure. You will also be prompted to enable Database Mail when you first attempt to set up a profile. To configure Database Mail, you must be a member of the DatabaseMailUserRole database role in the msdb database.

Database Mail enforces a configurable limit on the attachment file size. It can also maintain a list of prohibited file extensions preventing a developer from attach files with an extension that is on the list. The system is also audited and keeps messages and attachments that were sent in the msdb database. You can easily audit Database Mail usage and review the retained messages.

Messages can be created that provide you with the status of a job or a warning caused by an alert. The mailers can even include a result set, or data obtained by a collateral query. They let SQL Server send and receive e-mail but they must first be configured to establish and obtain a client connection with a mail server such as Microsoft Exchange. Let’s first discuss SQL Mail.

SQL Mail

SQL Server makes use of two services to handle e-mail. MSSQLServer processes mail for all of the mail stored procedures. SQL Server Agent, however, does not use SQL Mail to send its e-mail. The Agent has its own mail capabilities, configured and operated separately from SQL Mail.

SQL Server Books Online refers to SQL Server Agent mail features as SQLAgentMail to distinguish it from the SQL Mail features provided by MSSQLServer. SQL Mail works by first establishing an extended MAPI connection with a mail host, while SQLAgentMail establishes an extended MAPI connection on its own. Both SQL Mail and SQLAgentMail can connect with Microsoft Exchange Server, or any Post Office Protocol 3 (POP3) server, even a UNIX POP3 server.

To get SQL Mail going you need a post office connection, a mail store (mailbox), a mail profile, and an Active Directory domain user account, which is used to log on to an instance of SQL Server. The SQL Mail facility comprises of a number of stored procedures, which are used by SQL Server to process its e-mail messages. These are then received in the defined SQL Mail account mailbox on the mail server or to reply to e-mail messages generated by the stored procedure xp_sendmail.

By using SQL Mail extended stored procedures, messages can be sent from either a trigger or a stored procedure. SQL Mail stored procedures can also manipulate data, process queries received by e-mail, and return a result set in a reply to an e-mail.

To process e-mail automatically, you must create a regularly scheduled job that uses the stored procedure, sp_processmail, which checks your SQL Mail mail profile and then checks your mailbox for e-mail. The sp_processmail procedure uses xp_sendmail to execute query requests contained in the text of the e-mail and then returns the result set to the original sender and any additional recipients that might be included in the communication.

SQLAgentMail

SQLAgentMail can use its own domain account and mail profile that is different from the one set up for SQL Mail. With SQL Server, you can configure SQLAgentMail e-mail messages to be sent when either of the following takes place:

  • An alert is triggered   The alerts can be configured to send e-mail notification upon the occurrence of specific events. SQL Mail is not required. For example, an alert can be configured to notify an operator per e-mail of a particular database-related event that needs immediate attention.

  • A scheduled task event   This fires when something like a database backup or a replication event succeeds or fails.

Your e-mail can be sent to a list of recipients, informing them of the status of scheduled jobs for possible user action. This is an ideal service if you want to make sure that a backup job is proceeding according to standard operational procedures. This facility is also useful for gathering up a regular log of events that can be later referenced. As mentioned earlier you can send a result set by e-mail to any list of recipients. That result set might be data generated from a call to a system stored procedure, such as sp_who. Mail can be generated on firing of a trigger designed to alert an operator or administrator. An example of such a trigger is the identification of a user or users that try to access a certain database. For example, a report on an unauthorized attempt to access and inventory database could trigger an e-mail sent by SQLAgentMail to the designated operators. A result set containing connection information can also be e-mailed in the alert.

Configuring and Using SQL Mail

SQL Mail must be configured to run in an e-mail profile created in the same domain account that is used to start an instance of SQL Server. Under the Management folder in SQL Server Management Studio, you will find an icon representing the SQL Mail Service, and you can determine from that if the service is running. You can also start SQL Mail automatically by clicking Autostart SQL Mail When SQL Server Starts on the General tab of the SQL Mail Configuration dialog box.

Configuring and Using Database Mail

The Database Mail Configuration Wizard is used to install or uninstall Database Mail, or to create and configure Database Mail accounts and profiles, and the related system parameters. The first screen you arrive at in the configuration lets you name and describe a profile. There is a button on this dialog box that lets you add SMTP accounts. The process is no different from configuring any SMTP mail client to communicate with an SMTP server.

Once you have created the profile and account, you will be able to determine if the profile is for public or private access. If the profile is for private use, you will need to specify the users or groups that have access to the profile. Finally, you will be able to specify the profile parameters that govern the mailing process. You will be able to set retry attempts, attachment specifics, file sizes, logging, and so on.




Microsoft SQL Server 2005. The Complete Reference
Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features
ISBN: 0072261528
EAN: 2147483647
Year: 2006
Pages: 239

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net