一、数据库邮件报警介绍
数据库邮件是从SQL Server数据库引擎发送电子邮件企业解决方案,使用简单传输协议(SMTP)发送邮件。发送邮件进程与数据库的进程隔离,因此可不用担心影响数据库服务器。
数据库邮件发送要求联网,考虑数据库服务器的安全性,不能将所有服务器的外网开启;处理如下图所示:1、监控服务器轮询每个SQL数据库服务器;2、将获取的数据在监控服务器上集中处理,3、然后通过监控服务器的邮件服务发送邮件。
二、邮件模块处理
笔者发送的邮件内容如下所示,可分为个部分:1、发送主体(发生者);2、发送主题;3、邮件内容(报警开头,报警内容,报警解释);4、邮件结尾,每个部分的都需要HTML脚本的实现。
2.1 发送主体
发送主体即为邮件发送者,主体名称在配置邮件服务的时候制定。在执行邮件发送时必须要指定发送,同时可通过msdb.dbo.sysmail_profile系统表查看邮件主体名称;如下邮件测试,指定邮件发送主体SQLmonitor。
2.2 发送主题
发送主题可了解为邮件的分类,现有主题分类与说明如下。
邮件主题 |
分类说明 |
JobError Monitor |
作业失败报警 |
Block Monitor |
阻塞报警 |
SSISError Monitor |
轮询失败报警 |
Identity Monitor |
自增长报警 |
T_Space Monitor |
大表监控报警 |
AG Monitor |
AlwayOn高可用监控报警 |
LoginError Monitor |
登录失败报警 |
Cpu Monitor |
Cpu报警 |
Disk Monitor |
磁盘报警 |
User Connection Monitor |
用户连接数报警 |
AbnormityLogin Monitor |
异常用户登录报警 |
JobTimeout Monitor |
作业超时报警 |
2.3 邮件内容
邮件内容由三部分组成,分别为报警开头,报警内容,报警解释。
此三部分的内容可自定义格式,只要向M_MailFormart表添加如下数据:subject为报警主题,可定位此为哪种邮件;Formart为报警表格的列名,用逗号隔开;Desc为报警解释,若要换行用逗号隔开即可。
2.3.1 报警开头
报警开头包括三大块;"DBA"大字体凸显,邮件主题:通过集中处理时添加主题内容,监控时间:当前监控时间。
2.3.2 报警内容
报警内容主要格式是表格,表格包括列名称和列内容。
列名称:由M_MailFormart表的Formart字段控制。通过逗号分隔每个列名称。
列内容:列内容由报警处理脚本用户自定义插入,需要注意的是,用户自定义是要与列名称统一 。
由于表格是以HTML脚本控制,<tr></tr>表示表格的每行,<td></td>表示行中的列。例如要表示2行3列可书写为:
<tr><td>内容1</td><td>内容2</td><td>内容3</td></tr>
<tr><td>内容1</td><td>内容2</td><td>内容3</td></tr>
为了邮件输出的美观,必须设置表格格式,笔者定义:'<td width=100 valign=top style="border:solid lightgrey 1.0pt;border-top:none;height:18pt;font-size:8.0pt;font-family:微软雅黑;color:black"></td>'
- width=100valign=top: 宽度100;向上对齐
- border:solid lightgrey 1.0pt; border-top:none: 1pt的灰色边框,去除上边框
- height:18pt;font-size:8.0pt;font-family:微软雅黑;color:black : 字体大小8pt,微软雅黑,黑色
2.3.3 报警解释
邮件解释描述了报警机制,报警如何处理等方面,此定义通过M_MailFormart表指定,需要换行通过逗号指定即可。
2.4 邮件结尾
邮件结尾只是申明此邮件,已经写定。
三、邮件发送列表
上述说明了将所有服务器的信息收集到监控服务器,然后统一分析处理,例如阻塞,作业失败等等。将处理完成的信息插入到邮件发送配置表M_SendMailItem中。
表中记录了邮件的各个信息,邮件报警通过获取该表信息,确定是否发送报警和报警内容等等。
字段名称 |
字段解释 |
MessageID |
自增ID |
MessageType |
报警类型Error,Info |
Message |
报警内容 |
ServerName |
服务器名称 |
JobCode |
作业code;若非作业则为主题名称 |
JobName |
作业名称;若非作业则为主题名称 |
Stepid |
作业步骤;若非作业则NULL |
CreateDate |
新建时间 |
SendDate |
发送时间 |
SendStatu |
发送状态:S成功 F未发送 W不发送 |
SubJect |
邮件主题 |
SenderName |
发送主体 |
Recipients |
发送人 |
CopyRecipients |
抄送人 |
BodyFormat |
发送格式HTML |
Importance |
发送等级 |
ServerType |
服务器类型 |
四、报警流程
作业名称为:[报警]JK136009YW 发送报警邮件;每分钟执行一次,通过执行spb_M_SendMial发送报警,该存储过程的处理流程如下所示。
五、如何添加邮件报警
5.1方式一:自定义邮件主题
(1)添加邮件格式M_MailFormart
若需要自定义邮件主题和列名称以及邮件的描述,可向下表插入相关信息。
INSERT INTO [dbo].[M_MailFormart] ([Subject],[Formart],[Desc],[Inserttime])
VALUES (<Subject>,<Formart>,<Desc>,<Inserttime>)
(2)添加邮件内容M_SendMailItem
上述已经说明了邮件内容格式,M_MailFormart邮件格式表中的Subject内容一定要与M_sendmailitem邮件信息表的Subject对应上,且表格列名称要与邮件内容对应上。
如下定义了四列,也要在M_MailFormart的ForMart字段定义四列。添加完后邮件就会发送。
/*邮件内容定义*/
DECLARE @body VARCHAR(MAX)
SET @body=
'<tr>'+
'<td width=80 style="height:18pt"></td>'+
'<td width=100 valign=top style="border:solid lightgrey 1.0pt;border-top:none;height:18pt;font-size:8.0pt;font-family:微软雅黑;color:black">'+列1内容+'</td>'+
'<td width=100 valign=top style="border:solid lightgrey 1.0pt;border-top:none;height:18pt;font-size:8.0pt;font-family:微软雅黑;color:black">'+列2内容+'</td>'+
'<td width=100 valign=top style="border:solid lightgrey 1.0pt;border-top:none;height:18pt;font-size:8.0pt;font-family:微软雅黑;color:black">'+列3内容+'</td>'+
'<td width=100 valign=top style="border:solid lightgrey 1.0pt;border-top:none;height:18pt;font-size:8.0pt;font-family:微软雅黑;color:black">'+列n内容+'</td>'+
'<td width=80 style="height:18pt"></td>'+
'</tr>'
/*添加邮件记录*/
INSERT INTO SQLMONITOR.dbo.m_sendmailitem
(messagetype,
message,
servername,
jobcode,
jobname,
stepid,
createdate,
senddate,
sendstatu,
subject,
sendername,
recipients,
copyrecipients,
bodyformat,
importance,
servertype)
SELECT 'Info',
@body,
'服务器名称',
'主题内容',
'主题内容',
NULL,
GETDATE(),
NULL,
'F',
'主题内容',
'SqlMonitor',
REPLACE(Stuff((SELECT DISTINCT ';' + email
FROM dbdmonitor.dbo.memberlist m WITH (NOLOCK)
WHERE id > 9000AND m.issendmail = 'T'
FOR XML PATH('')),1,1,''),'''','''''' ) AS recipients,
NULL,
'HTML',
'Normoal',
'P'
报警邮件内容脚本
以下为用户异常登录自定义的邮件内容。
5.2方式二:通过作业失败添加邮件
若觉得邮件自动格式添加麻烦,可通过已经有的格式添加邮件信息。以作业失败为主题的报警,可直接调用存储过程spb_AutoGetJobError添加报警信息。
EXECUTE [dbo].[spb_AutoGetJobError]
@jobcode='TEST'--报警作业Jobcode,若没有作业,则可随便填写。
,@Messages='TEST'--报警内容
,@Flag=0 --报警类型,默认0:Error 1:Warning 其他:Info
测试结果如下图所示:报警主题是作业失败,报警格式是作业报警格式,在Error Message中显示了需要报警的信息。