配置 SQL Server 中的数据库邮件

引言

这是一个企业级解决方案，用于从 SQL Server 数据库引擎向 SMTP 服务器发送邮件。SQL Server 数据库应用程序可以通过电子邮件系统与用户进行通信。它提供了可扩展性、安全性和可靠性等功能。

它使用 SMTP 服务器发送邮件。SQL Server 2000 支持 SQL Mail，它支持 MAPI 配置文件来发送电子邮件，而不是使用 SMTP 服务器。SQL Mail 需要一个符合 MAPI 的邮件服务器（Microsoft Exchange Server）和一个 MAPI 客户端（Microsoft Outlook）。

我们可以发送文本消息、查询结果、附件文件。数据库邮件可用于通知用户或管理员有关 SQL Server 中发生的事件。例如，如果自动化过程（如复制、数据库镜像）失败，或者存在延迟相关问题，SQL Server 可以使用此功能通知管理员或操作员。

注意事项

• 与 SQL Mail 不同，数据库邮件不需要 MAPI – 一个符合 MAPI 的邮件服务器，如 Outlook Express 或扩展的编程接口。
• 更好的性能。SQL Server 发送邮件到 SMTP 服务器的影响减小，因为该任务由 DatabaseMail.exe 文件启动的外部进程实现。
• 在集群环境中运行良好。
• 支持 64 位。
• 数据库邮件配置信息存储在 MSDB 数据库中。
• 默认情况下，只有 MSDB 的 SysAdmin 和 DatabaseMailUserRole 数据库角色的成员才能发送邮件。
• 允许以文本和 HTML 等不同格式发送消息。
• 通过 MSDB 的不同系统表支持日志记录和审计功能。

数据库邮件的主要组件是：

• Sp_send_dbmail

这是一个系统定义的存储过程，SQL Server 使用它通过数据库邮件功能发送电子邮件。此存储过程位于 MSDB 数据库中。

• MSDB 数据库 

包含所有与数据库邮件相关的存储过程、系统表和数据库角色。

• Service Broker

为了在 SQL Server 引擎和数据库邮件引擎之间建立通信，我们需要一个服务代理。它将消息提交给邮件引擎。

• DatabaseMail.exe

此文件位于相应实例的 Binn 文件夹中。它是数据库邮件引擎。

常见问题解答：如何在 MSDB 中启用服务代理？

USE [master]
GO
ALTER DATABASE [MSDB] SET  ENABLE_BROKER WITH NO_WAIT
GO  

与数据库邮件相关的 MSDB 表

1. sysmail_profile：包含所有配置文件信息。
2. sysmail_account：包含 SMTP 服务器帐户信息。
3. Sysmail_server：包含 SMTP 服务器详细信息。
4. sysmail_allitems：邮件发送状态。如果 sent_status 为 1 则成功，否则失败。
5. sysmail_log：用于检查数据库邮件功能引发的错误。
6. sysmail_configuration：包含系统参数详细信息。

配置步骤

1. 在服务器级别启用 db 邮件功能
.

sp_configure 'Database Mail XPs',1
reconfigure

2. 在 MSDB 数据库中启用服务代理。

USE [master]
GO
ALTER DATABASE [MSDB] SET  ENABLE_BROKER WITH NO_WAIT
GO

3. 配置邮件配置文件（配置文件是帐户的集合）。
4. 添加 SMTP 帐户。
5. 将配置文件设为私有或公开。

私有配置文件可供使用

• sysadmin 成员和
• MSDB 的 databasemailuserrole 成员
6. 设置参数。
7. 发送邮件。

示例：配置数据库邮件

1. 转到对象资源管理器
2. 管理
3. 右键单击数据库邮件，然后选择“配置数据库邮件”，如下图所示



4. 下一篇
5. 选择“通过执行以下任务设置数据库邮件”，如下图所示



6. 下一篇
7. 输入配置文件名 =“SQL Profile”并按如下方式填写描述



8. 点击“添加”按钮并输入以下详细信息。始终使用您自己的电子邮件 ID。通常我们需要在这里使用公司的电子邮件 ID。我们需要向邮件服务器管理员团队提交一个请求单以获取以下详细信息。
• SQL Server 的单独电子邮件 ID（这是发件人电子邮件 ID）
• SMTP 服务器名称
• 端口号
• SSL 功能应启用或禁用。

我在这里使用我的个人电子邮件 ID。在“基本身份验证”选项中，输入相同的电子邮件 ID 以及该电子邮件 ID 的有效密码。



9. 好的
10. 下一篇
11. 在“管理配置文件安全性”选项下，通过选择复选框将配置文件设为公开并设置为默认值，如下图所示



12. 下一篇
13. 接受系统参数的默认设置，如下图所示



14. 下一篇
15. 结束



16. 关闭。

观察

Use msdb
Go
--Step1: Varifying the new profile
     select * from sysmail_profile
--Step2: Verifying accounts
     select * from sysmail_account
--Step3: To check the accounts of a profile
     select * from sysmail_profileaccount
     where profile_id=3
--Step4: To display mail server details
select * from sysmail_server

17. 我们已成功配置数据库邮件功能。让我们按如下方式测试邮件功能。
18. 转到对象资源管理器 -> 管理 -> 右键单击数据库邮件 -> 发送测试电子邮件



19.  输入以下详细信息。您可以使用所需的值。
• 选择配置文件名： SQLProfile
• 收件人： ask.sqlserver@gmail.com  （任何电子邮件 ID - 通常应该是管理员组的电子邮件 ID）
• 主题：您好，备份警报
• 正文：master 数据库的备份已成功生成。



20. 发送测试电子邮件 -> 确定
21. 验证邮件是否成功发送。打开新的查询并运行以下命令，并检查您的邮件的 sent_status 列值，如下所示



22. 检查您的邮箱，您会找到来自 SQL Server 的新电子邮件。

使用 SP_SEND_DBMAIL 发送邮件

我们可以使用 sp_send_dbmail 系统过程从任何存储过程、作业或批处理文件中以编程方式发送邮件。在发送邮件之前，我们可以通过查看存储过程的定义来检查所需的参数。

我们需要将以下参数传递给上述存储过程。

• Profile_name（我们可以提及上面我们创建的配置文件名）
• Recipients（我们可以通过用 分隔来提及多个收件人
• Subject
• Body

示例 1：使用 sp_send_dbmail 存储过程发送邮件

步骤 1

步骤 2：使用 sysmail_allitems 进行验证。在此处检查最后一封电子邮件的 sent_status 列。

use msdb
go
select * from sysmail_allitems

示例 2：使用 sp_send_dbmail 存储过程发送邮件，其中包含查询结果作为消息。

步骤 1：发送邮件（我正在使用 Test 数据库中的 emp 表）

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLProfile',
@recipients = 'skm.mcp@gmail.com',
@query = 'SELECT COUNT(*) as No_Empls FROM 
            Test.dbo.emp',
@subject = 'No of Employees Working';

步骤 2：使用 sysmail_allitems 进行验证。在此处检查最后一封电子邮件的 sent_status 列。

use msdb
go
select * from sysmail_allitems   

使用 T-SQL 脚本配置数据库邮件

--Enabling Database Mail
sp_configure 'show advanced options',1
reconfigure										  go											  sp_configure 'Database Mail XPs',1							  reconfigure 

--Creating a Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'SQLProfile',
@description = 'Mail Service for SQL Server' ;

-- Create a Mail account for gmail. We have to use our company mail account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQL_Email_Account',
@email_address = 'youremail@gmail.com',
@mailserver_name = 'smtp.gmail.com',
@port=587,
@enable_ssl=1,
@username='youremail',
@password='Emailid password'

-- Adding the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'SQLProfile',
@account_name = 'SQL_Email_Account',
@sequence_number =1 ;

-- Granting access to the profile to the DatabaseMailUserRole of MSDB
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'SQLProfile',
@principal_id = 0,
@is_default = 1 ;

--Sending Test Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLProfile',
@recipients = 'youremailid@xxxx.com',
@body = 'Database Mail Testing...',
@subject = 'Databas Mail from SQL Server';

--Verifying, check status column
select * from sysmail_allitems 

摘要

数据库邮件功能是在 SQL Server 2005 版本中引入的，可用于通知管理员/操作员。它提供了更好的性能以及集群感知功能。