Database MailDatabase Mail

適用於: 是SQL Server 是Azure SQL Database (僅限受控執行個體) 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure SQL Data Warehouse noParallel Data Warehouse

Database Mail 是從 SQL Server Database EngineSQL Server Database EngineAzure SQL Database 受控執行個體傳送電子郵件訊息的企業解決方案。Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database EngineSQL Server Database Engine or Azure SQL Database Managed Instance. 使用 Database Mail,資料庫應用程式就能夠將電子郵件訊息傳送給使用者。Using Database Mail, 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 適用於 SQL Server Database EngineSQL Server Database EngineAzure SQL Database 受控執行個體,但不適用 Azure SQL 資料庫的單一與彈性集區。Database mail is available in SQL Server Database EngineSQL Server Database Engine and Azure SQL Database Managed Instance, but not in Azure SQL database Singleton and Elastic pools.

使用 Database Mail 的優點Benefits of using Database Mail

Database Mail 具有可靠性、延展性、安全性及可支援性。Database Mail is designed for reliability, scalability, security, and supportability.

可靠性Reliability

  • Database Mail 會使用標準的 Simple Mail Transfer Protocol (SMTP) 來傳送郵件。Database Mail uses the standard Simple Mail Transfer Protocol (SMTP) to send mail. 您不需要在執行 SQL ServerSQL Server的電腦上安裝「擴充 MAPI」用戶端,就可以使用 Database Mail。You can use Database Mail without installing an Extended MAPI client on the computer that runs SQL ServerSQL Server.

  • 處理序隔離。Process isolation. 為了將對 SQL ServerSQL Server的影響減到最小,傳遞電子郵件的元件必須在 SQL ServerSQL Server外部的個別處理序中執行。To minimize the impact on SQL ServerSQL Server, the component that delivers e-mail runs outside of SQL ServerSQL Server, in a separate process. 即使外部處理序停止或失敗,SQL ServerSQL Server 仍會繼續將電子郵件訊息排入佇列中。SQL ServerSQL Server will continue to queue e-mail messages even if the external process stops or fails. 佇列的訊息會在外部處理序或 SMTP 伺服器恢復連線後傳送。The queued messages will be sent once the outside process or SMTP server comes online.

  • 容錯移轉帳戶。Failover accounts. 您可以使用 Database Mail 設定檔,指定多個 SMTP 伺服器。A Database Mail profile allows you to specify more than one SMTP server. 萬一 SMTP 伺服器無法使用時,還是可以將郵件傳遞到另一個 SMTP 伺服器。Should an SMTP server be unavailable, mail can still be delivered to another SMTP server.

  • 叢集支援。Cluster support. Database Mail 可感知叢集,而且在叢集上完全受到支援。Database Mail is cluster-aware and is fully supported on a cluster.

延展性Scalability

  • 背景傳遞:Database Mail 提供背景或非同步傳遞功能。Background Delivery: Database Mail provides background, or asynchronous, delivery. 呼叫 sp_send_dbmail 以傳送訊息時,Database Mail 會將要求加入 Service BrokerService Broker 佇列。When you call sp_send_dbmail to send a message, Database Mail adds a request to a Service BrokerService Broker queue. 此舉會立即傳回預存程序。The stored procedure returns immediately. 外部電子郵件元件就會收到該要求,並傳遞電子郵件。The external e-mail component receives the request and delivers the e-mail.

  • 多個設定檔:Database Mail 可讓您在 SQL ServerSQL Server 執行個體內建立多個設定檔。Multiple profiles: Database Mail allows you to create multiple profiles within a SQL ServerSQL Server instance. 選擇性地傳送訊息時,您可以選擇 Database Mail 使用的設定檔。Optionally, you can choose the profile that Database Mail uses when you send a message.

  • 多個帳戶:每個設定檔都可包含多個容錯移轉帳戶。Multiple accounts: Each profile can contain multiple failover accounts. 您可以設定不同的設定檔使用不同的帳戶,在多個電子郵件伺服器散發電子郵件。You can configure different profiles with different accounts to distribute e-mail across multiple e-mail servers.

  • 64 位元相容性:SQL ServerSQL Server 的 64 位元安裝完全支援 Database Mail。64-bit compatibility: Database Mail is fully supported on 64-bit installations of SQL ServerSQL Server.

SecuritySecurity

  • 預設關閉:為了縮小 SQL ServerSQL Server 的介面區,預設會停用 Database Mail 預存程序。Off by default: To reduce the surface area of SQL ServerSQL Server, Database Mail stored procedures are disabled by default.

  • 郵件安全性:若要傳送 Database Mail,您必須是 msdb 資料庫中 DatabaseMailUserRole 資料庫角色的成員。Mail Security:To send Database Mail, you must be a member of the DatabaseMailUserRole database role in the msdb database.

  • 設定檔安全性:Database Mail 會強制執行郵件設定檔的安全性。Profile security: Database Mail enforces security for mail profiles. 您要選擇擁有 Database Mail 設定檔存取權的 msdb 資料庫使用者或群組。You choose the msdb database users or groups that have access to a Database Mail profile. 您可以將存取權授與給 msdb中的特定使用者或所有使用者。You can grant access to either specific users, or all users in msdb. 私人設定檔限制清單上指定的使用者才有存取權。A private profile restricts access to a specified list of users. 公用設定檔可供資料庫的所有使用者使用。A public profile is available to all users in a database.

  • 附件大小管理員:Database Mail 會強制設定附件檔案大小的限制。Attachment size governor: Database Mail enforces a configurable limit on the attachment file size. 您可以使用 sysmail_configure_sp 預存程序來變更這項限制。You can change this limit by using the sysmail_configure_sp stored procedure.

  • 禁止的副檔名:Database Mail 會維護一份禁止的副檔名清單。Prohibited file extensions: Database Mail maintains a list of prohibited file extensions. 使用者無法附加副檔名出現在清單中的檔案。Users cannot attach files with an extension that appears in the list. 您可以使用 sysmail_configure_sp 來變更此清單。You can change this list by using sysmail_configure_sp.

  • Database Mail 會以 SQL ServerSQL Server 引擎服務帳戶執行。Database Mail runs under the SQL ServerSQL Server Engine service account. 若要從資料夾將檔案附加至電子郵件,則 SQL ServerSQL Server 引擎帳戶應具備存取包含該檔案之資料夾的權限。To attach a file from a folder to an email, the SQL ServerSQL Server engine account should have permissions to access the folder with the file.

可支援性Supportability

  • 整合式設定:Database Mail 會在 SQL Server Database EngineSQL Server Database Engine 內維護電子郵件帳戶的資訊。Integrated configuration: Database Mail maintains the information for e-mail accounts within SQL Server Database EngineSQL Server Database Engine. 毋須在外部用戶端應用程式管理郵件設定檔。There is no need to manage a mail profile in an external client application. 「Database Mail 組態精靈」提供方便的介面,供設定 Database Mail 使用。Database Mail Configuration Wizard provides a convenient interface for configuring Database Mail. 您也可以使用 Transact-SQLTransact-SQL,來建立並維護 Database Mail 組態。You can also create and maintain Database Mail configurations using Transact-SQLTransact-SQL.

  • 記錄。Logging. Database Mail 會將電子郵件活動記錄到 SQL ServerSQL Server、Microsoft Windows 應用程式事件記錄檔,以及 msdb 資料庫中的資料表。Database Mail logs e-mail activity to SQL ServerSQL Server, the Microsoft Windows application event log, and to tables in the msdb database.

  • 稽核:Database Mail 會在 msdb 資料庫中保留所傳送的郵件與附件副本。Auditing: Database Mail keeps copies of messages and attachments sent in the msdb database. 您可以輕鬆稽核 Database Mail 的使用狀況,並檢閱所保留的郵件。You can easily audit Database Mail usage and review the retained messages.

  • 支援 HTML:Database Mail 可讓您傳送 HTML 格式的電子郵件。Support for HTML: Database Mail allows you to send e-mail formatted as HTML.

Database Mail 架構Database Mail Architecture

Database Mail 是根據使用 Service Broker 技術的佇列架構而設計。Database Mail is designed on a queued architecture that uses service broker technologies. 當使用者執行 sp_send_dbmail時,預存程序會在郵件佇列中插入項目,並建立包含該電子郵件訊息的記錄。When users execute sp_send_dbmail, the stored procedure inserts an item into the mail queue and creates a record that contains the e-mail message. 在郵件佇列中插入新項目會啟動外部 Database Mail 處理序 (DatabaseMail.exe)。Inserting the new entry in the mail queue starts the external Database Mail process (DatabaseMail.exe). 外部處理序會讀取電子郵件資訊,並傳送電子郵件訊息到適當的電子郵件伺服器。The external process reads the e-mail information and sends the e-mail message to the appropriate e-mail server or servers. 外部處理序會在「狀態」佇列中插入項目,表示傳送作業的結果。The external process inserts an item in the Status queue for the outcome of the send operation. 在狀態佇列中插入新記錄會啟動內部預存程序,此預存程序會更新電子郵件訊息的狀態。Inserting the new entry in the status queue starts an internal stored procedure that updates the status of the e-mail message. 除了儲存已傳送 (或未傳送) 的電子郵件訊息之外,Database Mail 也會在系統資料表中記錄任何電子郵件附加檔案。Besides storing the sent, or unsent, e-mail message, Database Mail also records any e-mail attachments in the system tables. Database Mail 檢視提供可用於進行疑難排解的訊息狀態,並提供可用來管理 Database Mail 佇列的預存程序。Database Mail views provide the status of messages for troubleshooting, and stored procedures allow for administration of the Database Mail queue.

MSDB 傳送訊息到 SMTP 郵件伺服器msdb sends messages to an SMTP mail server

Database Mail 元件簡介Introduction to Database Mail Components

Database Mail 是由下列主要元件所組成:Database Mail consists of the following main components:

  • 組態與安全性元件Configuration and security components

    Database Mail 會將組態與安全性資訊儲存在 msdb 資料庫中。Database Mail stores configuration and security information in the msdb database. 組態與安全性物件會建立用於 Database Mail 的設定檔與帳戶。Configuration and security objects create profiles and accounts used by Database Mail.

  • 訊息元件Messaging components

    msdb 資料庫會充當郵件主機資料庫,其中會保存 Database Mail 用來傳送電子郵件的訊息物件。The msdb database acts as the mail-host database that holds the messaging objects that Database Mail uses to send e-mail. 這些物件包括 sp_send_dbmail 預存程序,以及保存訊息相關資訊的資料結構。These objects include the sp_send_dbmail stored procedure and the data structures that hold information about messages.

  • Database Mail 可執行檔Database Mail executable

    Database Mail 可執行檔是一個外部程式,它會從 msdb 資料庫中的佇列讀取,並傳送訊息到電子郵件伺服器。The Database Mail executable is an external program that reads from a queue in the msdb database and sends messages to e-mail servers.

  • 記錄與稽核元件Logging and auditing components

    Database Mail 會將記錄資訊記錄在 msdb 資料庫與 MicrosoftMicrosoft Windows 應用程式事件記錄檔中。Database Mail records logging information in the msdb database and the MicrosoftMicrosoft Windows application event log.

設定 Agent 使用 Database Mail:Configuring Agent to use Database Mail:

SQL Server Agent 可以設定成使用 Database Mail。SQL Server Agent can be configured to use Database Mail. 警示通知和完成作業時的自動通知都需要 Database Mail。This is required for alert notifications and automatic notification when a job completes.

警告

未將 SQL ServerSQL Server Agent 設定成使用 Database Mail,作業內的個別作業步驟也可以傳送電子郵件。Individual job steps within a job can also send e-mail without configuring SQL ServerSQL Server Agent to use Database Mail. 例如, Transact-SQLTransact-SQL 作業步驟可以使用 Database Mail,將查詢結果傳送到收件者清單。For example, a Transact-SQLTransact-SQL job step can use Database Mail to send the results of a query to a list of recipients.

您可以設定 SQL ServerSQL Server Agent,在下列時機將電子郵件訊息傳送給預先定義的操作員:You can configure SQL ServerSQL Server Agent to send e-mail messages to predefined operators when:

  • 觸發警示時。An alert is triggered. 經過設定後,警示可以為發生的特定事件傳送電子郵件通知。Alerts can be configured to send e-mail notification of specific events that occur. 例如,將警示設成在發生必須立即處理的資料庫事件或作業系統狀況時通知操作員。For example, alerts can be configured to notify an operator of a particular database event or operating system condition that may need immediate action. 如需設定警示的詳細資訊,請參閱 警示For more information about configuring alerts, see Alerts.

  • 排程工作 (如資料庫備份或複寫事件) 成功或失敗。A scheduled task, such as a database backup or replication event, succeeds or fails. 例如,可以使用 SQL ServerSQL Server Agent Mail,通知操作員在該月結束時的處理期間,是否發生錯誤。For example, you can use SQL ServerSQL Server Agent Mail to notify operators if an error occurs during processing at the end of a month.

另請參閱See also