启用SQL数据库邮箱发送邮件

数据库邮件的设计基于使用 Service Broker 技术的排队体系结构。

当用户执行 sp_send_dbmail 时，存储过程将向邮件队列中插入一项，并创建一条包含该电子邮件信息的记录。

在邮件队列中插入新项将启动数据库邮件外部进程 (DatabaseMail.exe)。该外部进程会读取电子邮件的信息并将电子邮件发送到相应的一台或多台电子邮件服务器。发送后，该外部进程还会在状态队列中插入一项，来指示发送操作的结果。在状态队列中插入新项将启动内部存储过程，该过程将更新电子邮件信息的状态。

除存储已发送（或未发送）的电子邮件信息外，数据库邮件还在系统表中记录所有电子邮件的附件。

只有 msdb 数据库中的 DatabaseMailUserRole 的成员可以执行 sp_send_dbmail。

如下图所示：

 

 

一、启用数据库邮件

手动启用数据库邮件功能，需执行以下脚本：

exec sp_configure 'show advanced options',1
RECONFIGURE

exec sp_configure 'Database Mail XPs',1
RECONFIGURE With Override

二、邮件服务器设置

1.邮箱启用设置-POP3/IMAP/SMTP/Exchange/CardDAV/CalDAV服务，开启时有提示有授权码，记录下，后面配置需要录入

QQ邮箱为例：

 

 

 

 

三、配置数据库邮件向导设置

1.连接上数据库，管理-数据库邮件-右键-配置数据库邮件

 

 

 2.在选择配置任务中，如果是新增选择【通过执行以下任务来安装数据库邮件】，如果是修改，可选择【管理数据库邮件账户和配置文件】

 

 3.配置文件名就是对应系统存储过程-——msdb.dbo.sp_send_dbmail  的 @profile_name，配置SMTP账户，如果已有SMTP账户，可在下方列表中看到；如果没有，点击添加即可。

 

4.点击添加,新增SMT邮件账户（QQ邮箱可以登陆邮箱官网进行配置）

　　　　　　a. 账户名：可根据实际情况设置，对应的是发送邮件的发件人名称

　　　　　　b. 电子邮件地址：填写发件人的邮箱地址，且需要是开通POP3/IMAP/SMTP/Exchange/CardDAV/CalDAV服务的邮箱

　　　　　　c. 显示名称：可根据实际情况新增

　　　　　　d. 服务器名称：如果电子邮件地址是QQ邮箱，服务器名称为：smtp.qq.com  端口分为两种： 勾选SSL—— 465，不勾选SSL—— 25

                                               如果电子邮件地址是139邮箱，服务器名称为：smtp.139.com 端口分为两种： 勾选SSL—— 465，不勾选SSL—— 25

　　　　　　f. 基本身份验证：用户名为发件人电子邮箱地址；密码为授权码。

 

 

5.接下来需要选择安全公共配置，选中刚才配置的文件名，然后后面默认为配置文件，建议选“否”，可根据实际情况配置。<然后点下一步完成配置>

 

 6.测试数据库邮件。

　6.1 在数据库界面管理-数据库邮件-右键-发送测试电子邮件，如下图

 

 

 

 6.2 使用T-SQL发送邮件(详细语法见sp_send_dbmail 存储过程说明)。　

Exec msdb.dbo.sp_send_dbmail @profile_name='YJFS',               --配置文件名

@recipients='XX@qq.com',                                                              --收件email地址

@subject='项目执行进度表',                                                             --邮件主题

@body='这是从 数据库邮件发出的测试电子邮件！' ,                       --邮件正文内容

@file_attachments ='F:/xxx.doc'                                                       --用于在电子邮件中发送附件

@query = 'SELECT * FROM [Test].[dbo].[employee]',                    --要执行有效的 Transact-SQL 语句（也可是存储过程 @query = ' EXEC [数据库名称].[dbo].[存储过程名称] ''参数'',''参数'',''...'',''...'' ',

@attach_query_result_as_file = 1 ;                                                --把 @query 的执行语句所返回消息写入.doc文档（或者txt文档）中以附件形式发送

@query_attachment_filename= 'my_test.doc'                                --设置附件文件的名称和类型为doc文档，如是文本则是.txt格式

 

*其他参数：

[ @query_result_header= ] query_result_header

指定查询结果是否包含列标题。query_result_header 值的数据类型为 bit。如果该值为 1，则查询结果包含列标题。如果该值为 0，则查询结果不包含列标题。该参数的默认值为 1。只有在指定 @query 时，此参数才适用。

[ @query_result_width = ] query_result_width

用于设置查询结果的格式的线条宽度（字符）。query_result_width 的数据类型为 int，默认值为 256。提供的值必须介于 10 和 32767 之间。只有在指定 @query 时，此参数才适用。

实现JOB任务运行状态的检测　　

使用SQL Server时，很多情况下都需要自定义Job进行部分功能的实现，而大部分时间是采取凌晨或者非业务期进行工作。

因而Job的运行结果的检测便形成了一个需要跟踪的问题，比如有时候N个Job的运行，只有几个出现问题，并且不确定的此Job发生在那个机器上，所以自动化运维的重要性就不言而喻了。

对于上面问题的解决，通过SQL Server的警报配置便可以实现检测JOB工作是否运行成功，如果SQL Server 代理的作业任务没有执行成功则通过该作业里的警报配置触发数据库邮件发送到对应的操作员的邮箱。大体配置流程如下：

一、设置SQL Server 代理的警报邮件发送配置，SQL Server 代理 -- 右键 -- 属性

 

警报系统  a.邮件系统 选择 数据库邮件

                 b.邮件配置文件 选择对应的数据库邮件的 @profile_name 名称

 

 

 

一、定义邮件的接收人员，SQL Server 代理 -- 操作员 -- 右键 -- 新建操作员

 

常规：　a. 姓名：操作员的姓名，可以是别名。（在警报里的响应需要配置）

　　　　b. 电子邮件名称：邮箱的地址。

　　　　c. 寻呼值班计划：可根据实际情况设置时间

 

 通知:选择对应 的定义警报内容（需要先定义警报后些处才能选择到）

 

 

二、定义警报属性等，SQL Server 代理 -- 警报 -- 右键 -- 新建警报

 

 常规：a. 名称：在JOB属性设置时会用到。

　　　b. 数据库名称

　　这里面的严重性选项其实是一个很重要的功能，一些简单的问题警告有时候是不需要及时关注的，或者说不需要暂时处理的。但是有些问题则需要里面去解决，比如服务器宕等。

响应：a. 执行作业：需选择对应的具体作业 (需要新建好作业后这里才能选择到）

　　　b. 通知操作员：选择需通知的操作员，可多个。

 

三、设置SQL Server 代理的警报邮件发送配置，SQL Server 代理 -- 右键 -- 属性

警报系统  --勾选启用邮件配置文件

                 a.邮件系统 选择 数据库邮件

                 b.邮件配置文件 选择对应的数据库邮件的 @profile_name 名称

                 --勾选 启用防故障操作员

                c.操作员 选择对应新建的操作员，勾选电子邮件

 

 

 

 

 

一、SQL Server 代理 -- 作业 -- 右键 -- 新建作业

步骤：新建打开作业步骤属性，选择对应的数据库名称 ，在命令处输入执行存储过程检测，

 

 

计划：定义该作业的执行周期与时间点

 

 警报：这里显示的内容对应是定义警报里的响应选择作业内容

 

 

 通知： 勾选 电子邮件，选择对应 操作员及触发通知条件