SQL Server Database Mail 設定與應用實戰指南

澄清

本文實為 SQL Server 的 Database Mail 功能設定（非 MySQL），常用於資料庫層直接發送郵件通知。

適用場景

• 使用者註冊自動寄送隨機密碼
• 論壇回覆通知郵件
• 排程作業完成/失敗通知
• 異常監控告警郵件

架構元件

1. SMTP 伺服器：郵件傳輸代理（如 Exchange、Postfix、第三方 SMTP 服務）
2. Database Mail 設定檔：定義 SMTP 伺服器、認證、發件人資訊
3. msdb 系統資料庫：儲存郵件隊列、記錄、設定檔
4. Service Broker：非同步郵件傳遞機制

逐步設定流程

1. 準備 SMTP 服務

• Windows Server 內建 SMTP 功能（IIS 6.0 管理工具安裝）
• 或第三方：Exchange、SendGrid、Gmail SMTP（需應用程式密碼）
• 測試：Telnet SMTP 埠 25/587/465 確認連通

2. 建立 Database Mail 設定檔

方法一：SSMS 圖形介面

1. 展開「管理」→「Database Mail」右鍵「設定 Database Mail」
2. 選「設定檔」→ 新增設定檔（如 AppMailProfile）
3. 新增帳號：SMTP 伺服器、埠號、SSL 設定、認證方式、寄件者郵件/顯示名稱
4. 設為預設公開設定檔
5. 完成嚚導，系統自動建立 msdb 相關物件

方法二：T-SQL 指令

-- 啟用 Database Mail
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1; RECONFIGURE;

-- 建立帳號
EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = 'SMTP_Account',
    @email_address = 'noreply@company.com',
    @display_name = '系統自動通知',
    @mailserver_name = 'smtp.company.com',
    @port = 587,
    @username = 'smtp_user',
    @password = 'smtp_password',
    @enable_ssl = 1;

-- 建立設定檔
EXEC msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'AppMailProfile',
    @description = '應用程式郵件設定檔';

-- 關聯帳號與設定檔
EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'AppMailProfile',
    @account_name = 'SMTP_Account',
    @sequence_number = 1;

-- 設為公開預設
EXEC msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'AppMailProfile',
    @principal_name = 'public',
    @is_default = 1;

3. 測試發送

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'AppMailProfile',
    @recipients = 'test@company.com',
    @subject = 'Database Mail 測試',
    @body = '這是一封測試郵件，發送時間：' + CONVERT(VARCHAR, GETDATE(), 120),
    @body_format = 'HTML';

進階應用：預存程序整合

CREATE PROCEDURE usp_NotifyUserRegistration
    @Email NVARCHAR(256),
    @UserName NVARCHAR(100),
    @TempPassword NVARCHAR(50)
AS
BEGIN
    DECLARE @Body NVARCHAR(MAX) = 
    N'
歡迎註冊 ' + @UserName + N'
' +
    N'
您的暫時密碼：' + @TempPassword + N'
' +
    N'
請登入後立即修改密碼。
';
    
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AppMailProfile',
        @recipients = @Email,
        @subject = '帳號註冊成功通知',
        @body = @Body,
        @body_format = 'HTML';
END;

權限設定

• 應用程式連線帳號需具備 DatabaseMailUserRole 角色
• EXEC sp_addrolemember 'DatabaseMailUserRole', 'AppUser';
• 或授權執行 sp_send_dbmail：GRANT EXECUTE ON msdb.dbo.sp_send_dbmail TO AppUser;

監控與疑難排解

-- 查詢失敗郵件
SELECT * FROM msdb.dbo.sysmail_mailitems 
WHERE sent_status = 'failed' 
ORDER BY send_request_date DESC;

-- 查詢錯誤詳情
SELECT * FROM msdb.dbo.sysmail_log 
WHERE event_type = 'error' 
ORDER BY log_date DESC;

常見問題

1. 郵件卡在 unsent：檢查 Service Broker 是否啟用 SELECT is_broker_enabled FROM sys.databases WHERE name='msdb';
2. SMTP 認證失敗：確認帳號密碼、SSL/STARTTLS 設定、防火牆
3. 大量郵件延遲：調整 Database Mail 設定檔 → 系統參數 → 最大執行緒數
4. 附件大小限制：預設 1MB，可用 @file_attachments 參數，大檔案建議上傳雲端給連結

最佳實踐

• 非同步發送：資料庫只寫入隊列，由 Database Mail 背景處理，不阻塞業務交易
• HTML 郵件：使用 @body_format = 'HTML'，支援豐富排版
• 重試機制：Database Mail 內建重試，失敗會記錄於 log 供人工介入
• 安全性：SMTP 帳號密碼加密儲存、限制發件網域、啟用 SPF/DKIM/DMARC

Database Mail 將郵件發送能力內建於資料庫層，減少應用層依賴，提升系統內聚性與可靠度。