SQLSERVER 监控 复制 并 使用 数据库 邮件 功能 发 告警 邮件 最近熬出病来了，都说IT行业伤不起，不说了，说回今天的正题 正题 上个月月底的时候因为要搬迁机房，需要将一个数据信息 数据库 先搬到我们的机房，然后将客户的 数据库 从原来的机房A搬到机房B
SQLSERVER 监控 复制 并 使用 数据库 邮件 功能 发 告警 邮件
最近熬出病来了，都说IT行业伤不起，不说了，说回今天的正题
正题
上个月月底的时候因为要搬迁机房，需要将一个数据信息 数据库 先搬到我们的机房，然后将客户的 数据库
从原来的机房A搬到机房B，原来我们的 数据信息库（DataInfo） 是放在机房A的，但是为了以后方便和防止信息泄露
就放到我们的托管机房，这里叫机房C
在搬迁机房的时候，尽量减少宕机时间，数据不能丢，搬迁机房真是一门学问。。。
虽然这麽忙，但我还是把写文章的时间腾出来，把干货分享给大家o(∩_∩)o
因为很多系统都在读写机房A的 数据信息库（DataInfo） ，我在上个月底的时候用备份文件初始化的方式搭建好 复制 把机房A的
机房A的 数据信息库（DataInfo） 新插入的数据实时 复制 到机房C，先让一部分系统能读取机房C的 数据信息库（DataInfo） ，
等以后搬迁完所有系统之后再统一全部改连接地址
当然这篇文章不是讲我这次的搬迁过程，在搭建好 复制 之后，由于我没有设置订阅库的登录用户的权限为只读，导致前几天开发那边
同时把新数据插入到订阅库，导致 复制 失败（主键重复），分发命令积压（大概26w+条命令未分发），然后一大堆后续工作。。。。。。
复制 的坑其实挺多的，因为我们不可能24小时用肉眼盯着 复制 监视器，所以我们需要一些 监控 手段，
当遇到 复制 出错的时候可以尽快知道然后进行修复
监控 考虑的条件：
（1）单个点 监控 、多个点 监控
（2）购买、自己开发
（3）比较实时、不是很实时
（4） 数据库 服务器是否负载过高
我这里只考虑最简单的一种：单个点的，不需要很实时，负载不高，如果服务器负载过高有可能连 邮件 也发不出了
然后就考虑到 使用 SQLSERVER自带的 数据库 邮件 来发 告警 邮件
当然，如果需要同时满足实时、多个点 监控 、成本足够可以考虑购买成熟的解决方案
又或者
自己公司开发 监控 程序，支持短信 告警 更加及时
需求
（1）当遇到 复制 出错的时候发 邮件 到我的邮箱
（2）每天间隔一定时间发 邮件 告诉我当前 复制 的情况
测试环境：Windows7 64位 、发布库SQL2005 SP4 、订阅库SQL2012 SP1、发布库和订阅库都在我的笔记本上
复制 所用登录用户：[ReplicationUser]
在进行实验之前，需要测试一下smtp.163.com，端口为25，这个地址是否可以访问。如果不通有可能是你机器防火墙的问题
还有可能需要检查杀毒软件有没有屏蔽了端口，否则会发送 邮件 失败
呈上完整脚本
-- 测试 复制 邮件 告警
USE [ sss ]
GO
-- 建立测试表 发布表一定要有主键
CREATE TABLE Repl_Test
(
ID INT IDENTITY ( 1 , 1 )
NOT NULL
PRIMARY KEY ,
TestNAME VARCHAR ( 100 ) NULL ,
CreatDate DATETIME NULL
)
/* ******************************************************************* */
-- 在发布库和订阅库建立一个同名的登录用户，这两个登录用户都对发布库有访问权限
/* ******************************************************************* */
-- 设置指定 数据库 的 复制 选项
-- 存储过程说明http://msdn.microsoft.com/zh-tw/library/ms188769.aspx
use [ sss ]
exec sp_replicationdboption
@dbname = N ' sss ' ,
@optname = N ' publish ' ,
@value = N ' true '
GO
/* ******************************************************************* */
-- 添加事务发布
-- 存储过程说明http://www.yesky.com/imagesnew/software/tsql/ts_sp_repl_4s32.htm
use [ sss ]
exec sp_addpublication
@publication = N ' testpub-sss ' ,
@description = N ' 来自发布服务器[JOE]的 数据库 [sss]的事务发布。 ' ,
@sync_method = N ' concurrent ' ,
@retention = 0 , -- 订阅是否过期，0为永不过期
@allow_push = N ' true ' , -- 推送订阅
@allow_pull = N ' true ' , -- 请求订阅为
@allow_anonymous = N ' false ' , -- false则表示不允许在该发布上 使用 匿名订阅
@repl_freq = N ' continuous ' , -- 是 复制 频率的类型。默认设置为 continuous。如果是 continuous，则表示发布服务器提供所有基于日志的事务 输出。如果是 Snapshot，则表示发布服务器只生成已调度同步事件
@status = N ' active ' , -- 指定发布数据是否可用
@independent_agent = N ' true ' , -- 指定是否有用于发布的单独的分发代理程序
@immediate_sync = N ' false ' , -- 指定是否每次快照代理程序运行时都创建发布的同步文件
@replicate_ddl = 1 , -- 复制 DDL语句
@allow_initialize_from_backup = N ' true ' -- 是否允许备份初始化
GO
/* ******************************************************************* */
-- 添加快照代理
-- 存储过程说明http://www.yesky.com/imagesnew/software/tsql/ts_sp_repl_7ecj.htm
exec sp_addpublication_snapshot
@publication = N ' testpub-sss ' ,
@frequency_type = 1 ,
@frequency_interval = 0 ,
@frequency_relative_interval = 0 ,
@frequency_recurrence_factor = 0 ,
@frequency_subday = 0 ,
@frequency_subday_interval = 0 ,
@active_start_time_of_day = 0 ,
@active_end_time_of_day = 235959 ,
@active_start_date = 0 ,
@active_end_date = 0 ,
@job_login = null ,
@job_password = null ,
@publisher_security_mode = 1
/* ******************************************************************* */
-- 添加发布项目
-- 存储过程说明http://www.yesky.com/imagesnew/software/tsql/ts_sp_repl_27s5.htm
use [ sss ]
exec sp_addarticle
@publication = N ' testpub-sss ' ,
@article = N ' Repl_Test ' ,
@source_owner = N ' dbo ' ,
@source_object = N ' Repl_Test ' , -- 要发布的表
@type = N ' logbased ' ,
@pre_creation_cmd = N ' drop ' , -- 当应用该项目的快照时，指定系统在订阅服务器上检测到同名的现有对象时所应采取的操作
@schema_option = 0x000000000803509F ,
@identityrangemanagementoption = N ' manual ' , -- 自增列范围管理选项，manual为手动管理
@destination_table = N ' Repl_Test ' , -- 是目的（订阅）表
@destination_owner = N ' dbo ' ,
@ins_cmd = N ' CALL sp_MSins_dboRepl_Test ' , -- 是 复制 项目的插入时 使用 的 复制 命令类型
@del_cmd = N ' CALL sp_MSdel_dboRepl_Test ' , -- 是 复制 项目的删除时 使用 的 复制 命令类型
@upd_cmd = N ' SCALL sp_MSupd_dboRepl_Test ' -- 是 复制 项目的更新时 使用 的 复制 命令类型
GO
/* ******************************************************************* */
-- 完整备份发布库
BACKUP DATABASE [ sss ] TO DISK = ' C:\SSS_FULLBACKUP2014-4-13.BAK '
/* ******************************************************************* */
-- 在订阅库上还原 数据库
USE [ master ]
RESTORE DATABASE [ sss ] FROM DISK = N ' D:\sss_fullbackup2014-4-6.bak ' WITH FILE = 1 ,
MOVE N ' sss ' TO N ' D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\sss.mdf ' ,
MOVE N ' sss_log ' TO N ' D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\sss_log.ldf ' ,
NOUNLOAD, REPLACE , STATS = 5
GO
/* ******************************************************************* */
-- 在发布库新建订阅 使用 推送订阅
use [ sss ]
exec sp_addsubscription
@publication = N ' testpub-sss ' ,
@subscriber = N ' JOE\SQL2012 ' ,
@destination_db = N ' sss ' ,
@subscription_type = N ' Push ' ,
@sync_type = N ' initialize with backup ' ,
@article = N ' all ' ,
@update_mode = N ' read only ' ,
@subscriber_type = 0 ,
@backupdevicetype = ' disk ' ,
@backupdevicename = ' C:\SSS_FULLBACKUP2014-4-13.bak ' -- 最后一次备份的备份文件（发布服务器上的存放位置）
/* ******************************************************************* */
-- 添加分发代理
exec sp_addpushsubscription_agent
@publication = N ' testpub-sss ' ,
@subscriber = N ' JOE\SQL2012 ' ,
@subscriber_db = N ' sss ' ,
@job_login = null ,
@job_password = null ,
@subscriber_security_mode = 0 ,
@subscriber_login = N ' ReplicationUser ' ,
@subscriber_password = N ' ReplicationForUser ' ,
@frequency_type = 64 ,
@frequency_interval = 0 ,
@frequency_relative_interval = 0 ,
@frequency_recurrence_factor = 0 ,
@frequency_subday = 0 ,
@frequency_subday_interval = 0 ,
@active_start_time_of_day = 0 ,
@active_end_time_of_day = 235959 ,
@active_start_date = 20140408 ,
@active_end_date = 99991231 ,
@enabled_for_syncmgr = N ' False ' ,
@dts_package_location = N ' Distributor '
GO
/* ******************************************************************* */
-- 脚本创建 数据库 邮件
-- 开启 数据库 邮件
EXEC sp_configure ' show advanced options ' , 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure ' database mail xps ' , 1
RECONFIGURE WITH OVERRIDE
/* ******************************************************************* */
-- 创建 邮件 帐户信息
EXEC msdb..sysmail_add_account_sp
@ACCOUNT_NAME = ' ReplicationErrorMailLog ' , -- 邮件 帐户名称
@EMAIL_ADDRESS = ' hiAT163.com ' , -- 发件人 邮件 地址
@DISPLAY_NAME = ' 系统管理员 ' , -- 发件人姓名
@REPLYTO_ADDRESS = NULL ,
@DESCRIPTION = NULL ,
@MAILSERVER_NAME = ' SMTP.163.COM ' , -- 邮件 服务器地址
@MAILSERVER_TYPE = ' SMTP ' , -- 邮件 协议
@PORT = 25 , -- 邮件 服务器端口
@USERNAME = ' hiAT163.com ' , -- 用户名
@PASSWORD = ' xxx ' , -- 密码
@USE_DEFAULT_CREDENTIALS = 0 ,
@ENABLE_SSL = 0 ,
@ACCOUNT_ID = NULL
GO
/* ******************************************************************* */
-- 数据库 配置文件
IF EXISTS ( SELECT name FROM msdb..sysmail_profile WHERE name = N ' ReplicationErrorProfileLog ' )
BEGIN
EXEC msdb..sysmail_delete_profile_sp
@profile_name = ' ReplicationErrorProfileLog '
END
EXEC msdb..sysmail_add_profile_sp
@profile_name = ' ReplicationErrorProfileLog ' , -- profile名称
@description = ' 数据库 邮件 配置文件 ' , -- profile描述
@profile_id = null
GO
/* ******************************************************************* */
-- 用户和 邮件 配置文件相关联
EXEC msdb..sysmail_add_profileaccount_sp
@profile_name = ' ReplicationErrorProfileLog ' , -- profile名称
@account_name = ' ReplicationErrorMailLog ' , -- account名称
@sequence_number = 1 -- account 在profile 中顺序
GO
/* ******************************************************************* */
-- 发送简单文本的 邮件
/* ******************************************************************* */
-- 创建链接服务器
-- 要开启分发服务器上的Distributed Transaction Coordinator（MSDTC服务）
USE [ master ]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N ' JOE_DIST ' ,
@srvproduct = N ' sqlserver ' ,
@provider = N ' SQLOLEDB ' ,
@datasrc = N ' JOE '
GO
EXEC master.dbo.sp_serveroption @server = N ' JOE_DIST ' , @optname = N ' rpc ' , @optvalue = N ' true '
GO
EXEC master.dbo.sp_serveroption @server = N ' JOE_DIST ' , @optname = N ' rpc out ' , @optvalue = N ' true '
GO
USE [ master ]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N ' JOE_DIST ' ,
@locallogin = NULL ,
@useself = N ' False ' ,
@rmtuser = N ' sa ' , -- 要求是对distribution有db_owner权限的 实际应用时最好不要用sa
@rmtpassword = N ' testxxx '
GO
/* ******************************************************************* */
-- --发送 邮件 ，这个步骤只是测试，检查编写的动态SQL是否正确
USE [ distribution ]
GO
DECLARE @SQL NVARCHAR ( MAX )
DECLARE @replcountersSQL NVARCHAR ( MAX )
DECLARE @replmonitorsubscriptionpendingcmdsSQL NVARCHAR ( MAX )
CREATE TABLE #replcounters
(
[ database ] NVARCHAR ( 200 ) ,
[ replicated_transactions ] BIGINT ,
[ replication_rate_trans_sec ] DECIMAL ( 18 , 2 ) ,
[ replication_latency ] DECIMAL ( 18 , 2 ) ,
[ replbeginlsn ] BINARY ,
[ replnextlsn ] BINARY
)
CREATE TABLE #replmonitorsubscriptionpendingcmds
(
[ pendingcmdcount ] BIGINT ,
[ estimatedprocesstime ] BIGINT
)
INSERT [ #replmonitorsubscriptionpendingcmds ]
EXEC [ JOE_DIST ] .distribution.dbo.SP_replmonitorsubscriptionpendingcmds ' JOE ' ,
' SSS ' , ' testpub-sss ' , ' JOE\SQL2012 ' , ' SSS ' , 0
INSERT [ #replcounters ] EXEC [ distribution ] . [ dbo ] .sp_replcounters
SELECT * FROM [ #replcounters ]
SELECT * FROM [ #replmonitorsubscriptionpendingcmds ]
SET @replcountersSQL = N ' 数据库 滞后时间、吞吐量和事务计数的 复制 统计信息 ' +
' ' +
N ' [database]
[replicated_transactions]
[replication_rate_trans_sec]
[replication_latency]
[replbeginlsn]
[replnextlsn]
'
+ CAST (( SELECT
[ database ] AS ' td ' , '' ,
[ replicated_transactions ] AS ' td ' , '' ,
[ replication_rate_trans_sec ] AS ' td ' , '' ,
[ replication_latency ] AS ' td ' , '' ,
CAST ( [ replbeginlsn ] AS INT ) AS ' td ' , '' ,
CAST ( [ replnextlsn ] AS INT ) AS ' td '
FROM [ #replcounters ]
FOR
XML PATH( ' tr ' ) ,
ELEMENTS -- TYPE
) AS NVARCHAR ( MAX )) + N ' ' ;
SET @replmonitorsubscriptionpendingcmdsSQL = N ' 事务发布的订阅的等待命令数以及处理这些命令的粗略估计时间 ' +
' ' +
N ' [pendingcmdcount]
[estimatedprocesstime]
'
+ CAST (( SELECT
[ pendingcmdcount ] AS ' td ' , '' ,
[ estimatedprocesstime ] AS ' td ' , ''
FROM [ #replmonitorsubscriptionpendingcmds ]
FOR
XML PATH( ' tr ' ) ,
ELEMENTS -- TYPE
) AS NVARCHAR ( MAX )) + N ' ' ;
SET @SQL = @replcountersSQL + ' ' + @replmonitorsubscriptionpendingcmdsSQL
EXEC [ msdb ] . [ dbo ] . [ sp_send_dbmail ]
@profile_name = ' ReplicationErrorProfileLog ' ,
@recipients = ' linyonghua.hi@163.com ' , -- varchar(max) --收件人
@subject = N ' 数据库 复制 的相关信息 ' , -- nvarchar(255) 标题
@body_format = ' HTML ' , -- varchar(20) 正文格式可选值：text html
@body = @SQL
DROP TABLE [ #replcounters ]
DROP TABLE [ #replmonitorsubscriptionpendingcmds ]
/* ******************************************************************* */
-- 创建作业 作业命名规则: 数据库 名_ReplicationInfo
DECLARE @job_name SYSNAME
SET @job_name = ' SSS_ReplicationInfo '
EXEC msdb.dbo.sp_add_job @job_name = @job_name ,
@enabled = 1 ,
@notify_level_eventlog = 0 ,
@notify_level_email = 0 ,
@notify_level_netsend = 0 ,
@notify_level_page = 0 ,
@delete_level = 0 ,
@description = N ' 发送 复制 数据库 情况作业 ' ,
@category_name = N ' REPL-Checkup ' ,
@owner_login_name = N ' sa '
/* ******************************************************************* */
-- 添加作业步骤
DECLARE @job_name SYSNAME
DECLARE @DBNAME NVARCHAR ( 100 )
DECLARE @SQL NVARCHAR ( MAX )
SET @DBNAME = ' distribution '
SET @job_name = ' SSS_ReplicationInfo '
SET @SQL = N ' USE [distribution]
GO
DECLARE @SQL NVARCHAR(MAX)
DECLARE @replcountersSQL NVARCHAR(MAX)
DECLARE @replmonitorsubscriptionpendingcmdsSQL NVARCHAR(MAX)
CREATE TABLE #replcounters
(
[database] NVARCHAR(200) ,
[replicated_transactions] BIGINT ,
[replication_rate_trans_sec] DECIMAL(18, 2) ,
[replication_latency] DECIMAL(18, 2) ,
[replbeginlsn] BINARY ,
[replnextlsn] BINARY
)
CREATE TABLE #replmonitorsubscriptionpendingcmds
(
[pendingcmdcount] BIGINT ,
[estimatedprocesstime] BIGINT
)
INSERT [#replmonitorsubscriptionpendingcmds]
EXEC [JOE_DIST].distribution.dbo.SP_replmonitorsubscriptionpendingcmds '' JOE '' ,
'' SSS '' , '' testpub-sss '' , '' JOE\SQL2012 '' , '' SSS '' , 0
INSERT [#replcounters] EXEC [distribution].[dbo].sp_replcounters
SELECT * FROM [#replcounters]
SELECT * FROM [#replmonitorsubscriptionpendingcmds]
SET @replcountersSQL = N '' 数据库 滞后时间、吞吐量和事务计数的 复制 统计信息 '' +
'' '' +
N '' [database]
[replicated_transactions]
[replication_rate_trans_sec]
[replication_latency]
[replbeginlsn]
[replnextlsn]
''
+ CAST(( SELECT
[database] AS '' td '' , '''' ,
[replicated_transactions] AS '' td '' , '''' ,
[replication_rate_trans_sec] AS '' td '' , '''' ,
[replication_latency] AS '' td '' , '''' ,
CAST([replbeginlsn] AS INT) AS '' td '' , '''' ,
CAST([replnextlsn] AS INT) AS '' td ''
FROM [#replcounters]
FOR
XML PATH( '' tr '' ) ,
ELEMENTS-- TYPE
) AS NVARCHAR(MAX)) + N '' '' ;
SET @replmonitorsubscriptionpendingcmdsSQL = N '' 事务发布的订阅的等待命令数以及处理这些命令的粗略估计时间 '' +
'' '' +
N '' [pendingcmdcount]
[estimatedprocesstime]
''
+ CAST(( SELECT
[pendingcmdcount] AS '' td '' , '''' ,
[estimatedprocesstime] AS '' td '' , ''''
FROM [#replmonitorsubscriptionpendingcmds]
FOR
XML PATH( '' tr '' ) ,
ELEMENTS-- TYPE
) AS NVARCHAR(MAX)) + N '' '' ;
SET @SQL=@replcountersSQL+ '' '' +@replmonitorsubscriptionpendingcmdsSQL
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = '' ReplicationErrorProfileLog '' ,
@recipients = '' linyonghua.hi@163.com '' , -- varchar(max) --收件人
@subject = N '' 数据库 复制 的相关信息 '' , -- nvarchar(255) 标题
@body_format = '' HTML '' , -- varchar(20) 正文格式可选值：text html
@body = @SQL
DROP TABLE [#replcounters]
DROP TABLE [#replmonitorsubscriptionpendingcmds]
'
EXEC msdb.dbo.sp_add_jobstep @job_name = @job_name ,
@step_name = N ' SendMail ' ,
@step_id = 1 ,
@cmdexec_success_code = 0 ,
@on_success_action = 3 ,
@on_success_step_id = 0 ,
@on_fail_action = 2 ,
@on_fail_step_id = 0 ,
@retry_attempts = 0 ,
@retry_interval = 0 ,
@os_run_priority = 0 ,
@subsystem = N ' TSQL ' ,
@command = @SQL ,
@database_name = @DBNAME ,
@flags = 0
/* ******************************************************************* */
-- 创建作业的调度计划 每天每隔6个小时查看一次
DECLARE @job_name SYSNAME
DECLARE @Time VARCHAR ( 100 )
DECLARE @Date DATETIME
-- 修改作业的执行时间
SET @Date = ' 2014-01-08 00:20:00.000 '
SET @Time = REPLACE ( CONVERT ( VARCHAR , @Date , 8 ), ' : ' , '' )
SET @job_name = ' SSS_ReplicationInfo '
-- 修改作业的执行时间
SET @Time = REPLACE ( CONVERT ( VARCHAR , @Date , 8 ), ' : ' , '' )
EXEC msdb.dbo.sp_add_jobschedule @job_name = @job_name , @name = N ' Plan ' ,
@enabled = 1 ,
@freq_type = 4 ,
@freq_interval = 1 ,
@freq_subday_type = 8 ,
@freq_subday_interval = 6 ,
@freq_relative_interval = 0 ,
@freq_recurrence_factor = 1 ,
@active_start_date = 20140414 ,
@active_end_date = 99991231 ,
@active_start_time = @Time ,
@active_end_time = 235959
EXEC msdb.dbo.sp_add_jobserver @job_name = @job_name , @server_name = N ' (local) '
/* ******************************************************************* */
-- 创建对于[distribution].[dbo].[MSrepl_errors]表的insert触发器，当有错误的时候就发 邮件
USE [ distribution ]
GO
IF ( OBJECT_ID ( ' tgr_MSrepl_errors ' , ' tr ' ) IS NOT NULL )
DROP TRIGGER tgr_MSrepl_errors
GO
CREATE TRIGGER tgr_MSrepl_errors ON [ distribution ] . [ dbo ] . [ MSrepl_errors ]
FOR INSERT -- 插入触发
AS
DECLARE @SQL NVARCHAR ( MAX )
SET @SQL = N ' 数据库 复制 出错信息 ' + ' '
+ N ' [xact_seqno]
[id]
[time]
[source_name]
[error_code]
[session_id]
' + CAST (( SELECT e. [ xact_seqno ] AS ' td ' ,
'' ,
e. [ id ] AS ' td ' ,
'' ,
e. [ time ] AS ' td ' ,
'' ,
e. [ source_name ] AS ' td ' ,
'' ,
CAST (e. [ error_code ] AS NVARCHAR ( 200 )) AS ' td ' ,
'' ,
CAST (e. [ session_id ] AS NVARCHAR ( 200 )) AS ' td ' ,
''
FROM dbo.MSdistribution_history h
JOIN inserted e ON h.error_id = e.id
WHERE comments NOT LIKE ' %transaction% ' -- 失败的代理
ORDER BY id DESC
FOR
XML PATH( ' tr ' ) ,
ELEMENTS -- TYPE
) AS NVARCHAR ( MAX )) + N ' ' ;
EXEC [ msdb ] . [ dbo ] . [ sp_send_dbmail ] @profile_name = ' ReplicationErrorProfileLog ' ,
@recipients = ' linyonghua.hi@163.com ' , -- varchar(max) --收件人
@subject = N ' 数据库 复制 出错信息 ' , -- nvarchar(255) 标题
@body_format = ' HTML ' , -- varchar(20) 正文格式可选值：text html
@body = @SQL
GO
数据库 滞后时间、吞吐量和事务计数的 复制 统计信息 '' +
'' '' +
N '' [database]
[replicated_transactions]
[replication_rate_trans_sec]
[replication_latency]
[replbeginlsn]
[replnextlsn]
''
+ CAST(( SELECT
[database] AS '' td '' , '''' ,
[replicated_transactions] AS '' td '' , '''' ,
[replication_rate_trans_sec] AS '' td '' , '''' ,
[replication_latency] AS '' td '' , '''' ,
CAST([replbeginlsn] AS INT) AS '' td '' , '''' ,
CAST([replnextlsn] AS INT) AS '' td ''
FROM [#replcounters]
FOR
XML PATH( '' tr '' ) ,
ELEMENTS-- TYPE
) AS NVARCHAR(MAX)) + N '' '' ;
SET @replmonitorsubscriptionpendingcmdsSQL = N '' 事务发布的订阅的等待命令数以及处理这些命令的粗略估计时间 '' +
'' '' +
N '' [pendingcmdcount]
[estimatedprocesstime]
''
+ CAST(( SELECT
[pendingcmdcount] AS '' td '' , '''' ,
[estimatedprocesstime] AS '' td '' , ''''
FROM [#replmonitorsubscriptionpendingcmds]
FOR
XML PATH( '' tr '' ) ,
ELEMENTS-- TYPE
) AS NVARCHAR(MAX)) + N '' '' ;
SET @SQL=@replcountersSQL+ '' '' +@replmonitorsubscriptionpendingcmdsSQL
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = '' ReplicationErrorProfileLog '' ,
@recipients = '' linyonghua.hi@163.com '' , -- varchar(max) --收件人
@subject = N '' 数据库 复制 的相关信息 '' , -- nvarchar(255) 标题
@body_format = '' HTML '' , -- varchar(20) 正文格式可选值：text html
@body = @SQL
DROP TABLE [#replcounters]
DROP TABLE [#replmonitorsubscriptionpendingcmds]
'
EXEC msdb.dbo.sp_add_jobstep @job_name = @job_name ,
@step_name = N ' SendMail ' ,
@step_id = 1 ,
@cmdexec_success_code = 0 ,
@on_success_action = 3 ,
@on_success_step_id = 0 ,
@on_fail_action = 2 ,
@on_fail_step_id = 0 ,
@retry_attempts = 0 ,
@retry_interval = 0 ,
@os_run_priority = 0 ,
@subsystem = N ' TSQL ' ,
@command = @SQL ,
@database_name = @DBNAME ,
@flags = 0
/* ******************************************************************* */
-- 创建作业的调度计划 每天每隔6个小时查看一次
DECLARE @job_name SYSNAME
DECLARE @Time VARCHAR ( 100 )
DECLARE @Date DATETIME
-- 修改作业的执行时间
SET @Date = ' 2014-01-08 00:20:00.000 '
SET @Time = REPLACE ( CONVERT ( VARCHAR , @Date , 8 ), ' : ' , '' )
SET @job_name = ' SSS_ReplicationInfo '
-- 修改作业的执行时间
SET @Time = REPLACE ( CONVERT ( VARCHAR , @Date , 8 ), ' : ' , '' )
EXEC msdb.dbo.sp_add_jobschedule @job_name = @job_name , @name = N ' Plan ' ,
@enabled = 1 ,
@freq_type = 4 ,
@freq_interval = 1 ,
@freq_subday_type = 8 ,
@freq_subday_interval = 6 ,
@freq_relative_interval = 0 ,
@freq_recurrence_factor = 1 ,
@active_start_date = 20140414 ,
@active_end_date = 99991231 ,
@active_start_time = @Time ,
@active_end_time = 235959
EXEC msdb.dbo.sp_add_jobserver @job_name = @job_name , @server_name = N ' (local) '
/* ******************************************************************* */
-- 创建对于[distribution].[dbo].[MSrepl_errors]表的insert触发器，当有错误的时候就发 邮件
USE [ distribution ]
GO
IF ( OBJECT_ID ( ' tgr_MSrepl_errors ' , ' tr ' ) IS NOT NULL )
DROP TRIGGER tgr_MSrepl_errors
GO
CREATE TRIGGER tgr_MSrepl_errors ON [ distribution ] . [ dbo ] . [ MSrepl_errors ]
FOR INSERT -- 插入触发
AS
DECLARE @SQL NVARCHAR ( MAX )
SET @SQL = N ' 数据库 复制 出错信息 ' + ' '
+ N ' [xact_seqno]
[id]
[time]
[source_name]
[error_code]
[session_id]
' + CAST (( SELECT e. [ xact_seqno ] AS ' td ' ,
'' ,
e. [ id ] AS ' td ' ,
'' ,
e. [ time ] AS ' td ' ,
'' ,
e. [ source_name ] AS ' td ' ,
'' ,
CAST (e. [ error_code ] AS NVARCHAR ( 200 )) AS ' td ' ,
'' ,
CAST (e. [ session_id ] AS NVARCHAR ( 200 )) AS ' td ' ,
''
FROM dbo.MSdistribution_history h
JOIN inserted e ON h.error_id = e.id
WHERE comments NOT LIKE ' %transaction% ' -- 失败的代理
ORDER BY id DESC
FOR
XML PATH( ' tr ' ) ,
ELEMENTS -- TYPE
) AS NVARCHAR ( MAX )) + N ' ' ;
EXEC [ msdb ] . [ dbo ] . [ sp_send_dbmail ] @profile_name = ' ReplicationErrorProfileLog ' ,
@recipients = ' linyonghua.hi@163.com ' , -- varchar(max) --收件人
@subject = N ' 数据库 复制 出错信息 ' , -- nvarchar(255) 标题
@body_format = ' HTML ' , -- varchar(20) 正文格式可选值：text html
@body = @SQL
GO
数据库 复制 出错信息 ' + ' ' + N ' [xact_seqno] [id] [time] [source_name] [error_code] [session_id] ' + CAST (( SELECT e. [ xact_seqno ] AS ' td ' , '' , e. [ id ] AS ' td ' , '' , e. [ time ] AS ' td ' , '' , e. [ source_name ] AS ' td ' , '' , CAST (e. [ error_code ] AS NVARCHAR ( 200 )) AS ' td ' , '' , CAST (e. [ session_id ] AS NVARCHAR ( 200 )) AS ' td ' , '' FROM dbo.MSdistribution_history h JOIN inserted e ON h.error_id = e.id WHERE comments NOT LIKE ' %transaction% ' -- 失败的代理 ORDER BY id DESC FOR XML PATH( ' tr ' ) , ELEMENTS -- TYPE ) AS NVARCHAR ( MAX )) + N ' ' ; EXEC [ msdb ] . [ dbo ] . [ sp_send_dbmail ] @profile_name = ' ReplicationErrorProfileLog ' , @recipients = ' linyonghua.hi@163.com ' , -- varchar(max) --收件人 @subject = N ' 数据库 复制 出错信息 ' , -- nvarchar(255) 标题 @body_format = ' HTML ' , -- varchar(20) 正文格式可选值：text html @body = @SQL GO
测试结果
手动启动作业，就可以看到 邮件 会自动发到我的163邮箱
复制 报错的时候也会发 邮件
我把 复制 用户的权限去掉，马上就会报错
SQLSERVER会有一个重试时间，除非你马上停止同步，否则SQLSERVER会不断重试，然后不断发 邮件 到你的邮箱提醒你~
把权限勾上后，没有报错了，也没有再发 邮件 了
分发代理默认是每隔一分钟重试4次
你会看到每隔一分钟会收到4封 邮件 ,其中有一封 邮件 是空的
而且大家可以看一下[MSrepl_errors]表，每分钟会插入三条记录到[MSrepl_errors]表这些都是重试记录
USE [ distribution ] GO SELECT * FROM [ distribution ] . [ dbo ] . [ MSrepl_errors ] ORDER BY [ time ]
暂时还不清楚可以在哪里修改每分钟的重试次数，还有为什么4封 邮件 中有一封是空的~
原理
（1）对[distribution].[dbo].[MSrepl_errors]表创建了insert触发器，当有错误的时候，SQLSERVER会向这个表插入错误记录
（2）利用job获取下面的两个存储过程的结果监视 复制 发送 邮件 ，这两个存储过程都在分发 数据库 里
sp_replcounters --为每个发布 数据库 返回有关滞后时间、吞吐量和事务计数的 复制 统计信息。 此存储过程在发布服务器的任何 数据库 中执行。
sp_replmonitorsubscriptionpendingcmds -- 返回有关对事务发布的订阅的等待命令数以及处理这些命令的粗略估计时间的信息。
此存储过程针对每个返回的订阅返回一行。 在分发服务器的分发 数据库 上执行此存储过程，用于监视 复制 。
感谢群里面的 复制 大牛：何文通、高文佳、菠萝的帮助
SQLServer Replication 常见错误
Replication-- 复制 延迟的诊断和解决
Replication-- 复制 Token
Replication的犄角旮旯（四）--关于事务 复制 的 监控
SQL Server 创建 数据库 邮件
如有不对的地方，欢迎大家拍砖o(∩_∩)o
查看更多关于SQLSERVER监控复制并使用数据库邮件功能发告警邮件的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did98137