suchen

Heim >Datenbank >MySQL-Tutorial >SQLSERVER监控复制并使用数据库邮件功能发告警邮件

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

WBOY
WBOYOriginal
2016-06-07 15:20:231867Durchsuche

SQLSERVER 监控 复制 并 使用 数据库 邮件 功能 发 告警 邮件 最近熬出病来了，都说IT行业伤不起，不说了，说回今天的正题 正题 上个月月底的时候因为要搬迁机房，需要将一个数据信息 数据库 先搬到我们的机房，然后将客户的 数据库 从原来的机房A搬到机房B

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

最近熬出病来了，都说IT行业伤不起，不说了，说回今天的正题

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

 

正题

上个月月底的时候因为要搬迁机房，需要将一个数据信息数据库先搬到我们的机房，然后将客户的数据库

从原来的机房A搬到机房B，原来我们的数据信息库（DataInfo）是放在机房A的，但是为了以后方便和防止信息泄露

就放到我们的托管机房，这里叫机房C

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

 

在搬迁机房的时候，尽量减少宕机时间，数据不能丢，搬迁机房真是一门学问。。。

虽然这麽忙，但我还是把写文章的时间腾出来，把干货分享给大家o(∩_∩)o 

 

 

因为很多系统都在读写机房A的数据信息库（DataInfo），我在上个月底的时候用备份文件初始化的方式搭建好复制把机房A的

机房A的数据信息库（DataInfo）新插入的数据实时复制到机房C，先让一部分系统能读取机房C的数据信息库（DataInfo），

等以后搬迁完所有系统之后再统一全部改连接地址

 

当然这篇文章不是讲我这次的搬迁过程，在搭建好复制之后，由于我没有设置订阅库的登录用户的权限为只读，导致前几天开发那边

同时把新数据插入到订阅库，导致复制失败（主键重复），分发命令积压（大概26w+条命令未分发），然后一大堆后续工作。。。。。。

 

复制的坑其实挺多的，因为我们不可能24小时用肉眼盯着复制监视器，所以我们需要一些监控手段，

当遇到复制出错的时候可以尽快知道然后进行修复

 

监控考虑的条件：

（1）单个点监控、多个点监控

（2）购买、自己开发

（3）比较实时、不是很实时

（4）数据库服务器是否负载过高

 

 

我这里只考虑最简单的一种：单个点的，不需要很实时，负载不高，如果服务器负载过高有可能连邮件也发不出了

然后就考虑到使用SQLSERVER自带的数据库邮件来发告警邮件

 

当然，如果需要同时满足实时、多个点监控、成本足够可以考虑购买成熟的解决方案

例如：微软的System Center 2012 R2

又或者

自己公司开发监控程序，支持短信告警更加及时

需求

（1）当遇到复制出错的时候发邮件到我的邮箱

（2）每天间隔一定时间发邮件告诉我当前复制的情况

 

测试环境：Windows7 64位 、发布库SQL2005 SP4 、订阅库SQL2012 SP1、发布库和订阅库都在我的笔记本上

复制所用登录用户：[ReplicationUser]

 

在进行实验之前，需要测试一下smtp.163.com，端口为25，这个地址是否可以访问。如果不通有可能是你机器防火墙的问题

还有可能需要检查杀毒软件有没有屏蔽了端口，否则会发送邮件失败

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

 SQLSERVER监控复制并使用数据库邮件功能发告警邮件

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

 

 

呈上完整脚本

 

<span>--</span><span>测试<strong>复制</strong><strong><strong>邮件</strong></strong><strong>告警</strong></span>

<span>USE</span> <span>[</span><span>sss</span><span>]</span>
<span>GO</span>
<span>--</span><span>建立测试表  发布表一定要有主键</span>
<span>CREATE</span> <span>TABLE</span><span> Repl_Test
    (
      ID </span><span>INT</span> <span>IDENTITY</span>(<span>1</span>, <span>1</span><span>)
             </span><span>NOT</span> <span>NULL</span>
             <span>PRIMARY</span> <span>KEY</span><span> ,
      TestNAME </span><span>VARCHAR</span>(<span>100</span>) <span>NULL</span><span> ,
      CreatDate </span><span>DATETIME</span> <span>NULL</span><span>
    )

</span><span>/*</span><span>*******************************************************************</span><span>*/</span>

<span>--</span><span>在发布库和订阅库建立一个同名的登录用户，这两个登录用户都对发布库有访问权限</span>

<span>/*</span><span>*******************************************************************</span><span>*/</span>


<span>--</span><span>设置指定<strong>数据库</strong>的<strong>复制</strong>选项</span><span>
--</span><span>存储过程说明http://msdn.microsoft.com/zh-tw/library/ms188769.aspx</span>
<span>use</span> <span>[</span><span>sss</span><span>]</span>
<span>exec</span><span> sp_replicationdboption 
</span><span>@dbname</span> <span>=</span> N<span>'</span><span>sss</span><span>'</span><span>, 
</span><span>@optname</span> <span>=</span> N<span>'</span><span>publish</span><span>'</span><span>, 
</span><span>@value</span> <span>=</span> N<span>'</span><span>true</span><span>'</span>
<span>GO</span>


<span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span> 添加事务发布</span><span>
--</span><span>存储过程说明http://www.yesky.com/imagesnew/software/tsql/ts_sp_repl_4s32.htm</span>

<span>use</span> <span>[</span><span>sss</span><span>]</span>
<span>exec</span><span> sp_addpublication 
</span><span>@publication</span> <span>=</span> N<span>'</span><span>testpub-sss</span><span>'</span><span>, 
</span><span>@description</span> <span>=</span> N<span>'</span><span>来自发布服务器“JOE”的<strong>数据库</strong>“sss”的事务发布。</span><span>'</span><span>, 
</span><span>@sync_method</span> <span>=</span> N<span>'</span><span>concurrent</span><span>'</span><span>, 
</span><span>@retention</span> <span>=</span> <span>0</span>, <span>--</span><span>订阅是否过期，0为永不过期</span>
<span>@allow_push</span> <span>=</span> N<span>'</span><span>true</span><span>'</span>, <span>--</span><span>推送订阅</span>
<span>@allow_pull</span> <span>=</span> N<span>'</span><span>true</span><span>'</span>,  <span>--</span><span>请求订阅为</span>
<span>@allow_anonymous</span> <span>=</span> N<span>'</span><span>false</span><span>'</span>,   <span>--</span><span>false则表示不允许在该发布上<strong>使用</strong>匿名订阅</span>
<span>@repl_freq</span> <span>=</span> N<span>'</span><span>continuous</span><span>'</span>,   <span>--</span><span>是<strong>复制</strong>频率的类型。默认设置为 continuous。如果是 continuous，则表示发布服务器提供所有基于日志的事务输出。如果是 Snapshot，则表示发布服务器只生成已调度同步事件</span>
<span>@status</span> <span>=</span> N<span>'</span><span>active</span><span>'</span>,   <span>--</span><span>指定发布数据是否可用</span>
<span>@independent_agent</span> <span>=</span> N<span>'</span><span>true</span><span>'</span>,  <span>--</span><span>指定是否有用于发布的单独的分发代理程序</span>
<span>@immediate_sync</span> <span>=</span> N<span>'</span><span>false</span><span>'</span>,   <span>--</span><span>指定是否每次快照代理程序运行时都创建发布的同步文件</span>
<span>@replicate_ddl</span> <span>=</span> <span>1</span>, <span>--</span><span><strong>复制</strong>DDL语句</span>
<span>@allow_initialize_from_backup</span> <span>=</span> N<span>'</span><span>true</span><span>'</span> <span>--</span><span>是否允许备份初始化</span>
<span>GO</span>

<span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span>添加快照代理</span><span>
--</span><span>存储过程说明http://www.yesky.com/imagesnew/software/tsql/ts_sp_repl_7ecj.htm</span>
<span>exec</span><span> sp_addpublication_snapshot 
</span><span>@publication</span> <span>=</span> N<span>'</span><span>testpub-sss</span><span>'</span><span>, 
</span><span>@frequency_type</span> <span>=</span> <span>1</span><span>, 
</span><span>@frequency_interval</span> <span>=</span> <span>0</span><span>, 
</span><span>@frequency_relative_interval</span> <span>=</span> <span>0</span><span>, 
</span><span>@frequency_recurrence_factor</span> <span>=</span> <span>0</span><span>, 
</span><span>@frequency_subday</span> <span>=</span> <span>0</span><span>, 
</span><span>@frequency_subday_interval</span> <span>=</span> <span>0</span><span>, 
</span><span>@active_start_time_of_day</span> <span>=</span> <span>0</span><span>, 
</span><span>@active_end_time_of_day</span> <span>=</span> <span>235959</span><span>, 
</span><span>@active_start_date</span> <span>=</span> <span>0</span><span>, 
</span><span>@active_end_date</span> <span>=</span> <span>0</span><span>, 
</span><span>@job_login</span> <span>=</span> <span>null</span><span>, 
</span><span>@job_password</span> <span>=</span> <span>null</span><span>, 
</span><span>@publisher_security_mode</span> <span>=</span> <span>1</span>

<span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span> 添加发布项目</span><span>
--</span><span>存储过程说明http://www.yesky.com/imagesnew/software/tsql/ts_sp_repl_27s5.htm</span>
<span>use</span> <span>[</span><span>sss</span><span>]</span>
<span>exec</span><span> sp_addarticle 
</span><span>@publication</span> <span>=</span> N<span>'</span><span>testpub-sss</span><span>'</span><span>, 
</span><span>@article</span> <span>=</span> N<span>'</span><span>Repl_Test</span><span>'</span><span>, 
</span><span>@source_owner</span> <span>=</span> N<span>'</span><span>dbo</span><span>'</span><span>, 
</span><span>@source_object</span> <span>=</span> N<span>'</span><span>Repl_Test</span><span>'</span>, <span>--</span><span>要发布的表</span>
<span>@type</span> <span>=</span> N<span>'</span><span>logbased</span><span>'</span><span>, 
</span><span>@pre_creation_cmd</span> <span>=</span> N<span>'</span><span>drop</span><span>'</span>, <span>--</span><span>当应用该项目的快照时，指定系统在订阅服务器上检测到同名的现有对象时所应采取的操作</span>
<span>@schema_option</span> <span>=</span> <span>0x000000000803509F</span><span>, 
</span><span>@identityrangemanagementoption</span> <span>=</span> N<span>'</span><span>manual</span><span>'</span>, <span>--</span><span>自增列范围管理选项，manual为手动管理</span>
<span>@destination_table</span> <span>=</span> N<span>'</span><span>Repl_Test</span><span>'</span>,  <span>--</span><span>是目的（订阅）表</span>
<span>@destination_owner</span> <span>=</span> N<span>'</span><span>dbo</span><span>'</span><span>,   
</span><span>@ins_cmd</span> <span>=</span> N<span>'</span><span>CALL sp_MSins_dboRepl_Test</span><span>'</span>, <span>--</span><span>是<strong>复制</strong>项目的插入时<strong>使用</strong>的<strong>复制</strong>命令类型</span>
<span>@del_cmd</span> <span>=</span> N<span>'</span><span>CALL sp_MSdel_dboRepl_Test</span><span>'</span>, <span>--</span><span>是<strong>复制</strong>项目的删除时<strong>使用</strong>的<strong>复制</strong>命令类型</span>
<span>@upd_cmd</span> <span>=</span> N<span>'</span><span>SCALL sp_MSupd_dboRepl_Test</span><span>'</span> <span>--</span><span>是<strong>复制</strong>项目的更新时<strong>使用</strong>的<strong>复制</strong>命令类型</span>
<span>GO</span>

<span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span>完整备份发布库</span>
<span>BACKUP</span> <span>DATABASE</span> <span>[</span><span>sss</span><span>]</span> <span>TO</span> <span>DISK</span> <span>=</span><span>'</span><span>C:\SSS_FULLBACKUP2014-4-13.BAK</span><span>'</span> 

<span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span>在订阅库上还原<strong>数据库</strong></span>
<span>USE</span> <span>[</span><span>master</span><span>]</span>
<span>RESTORE</span> <span>DATABASE</span> <span>[</span><span>sss</span><span>]</span> <span>FROM</span>  <span>DISK</span> <span>=</span> N<span>'</span><span>D:\sss_fullbackup2014-4-6.bak</span><span>'</span> <span>WITH</span>  <span>FILE</span> <span>=</span> <span>1</span><span>,  
MOVE N</span><span>'</span><span>sss</span><span>'</span> <span>TO</span> N<span>'</span><span>D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\sss.mdf</span><span>'</span><span>,  
MOVE N</span><span>'</span><span>sss_log</span><span>'</span> <span>TO</span> N<span>'</span><span>D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\sss_log.ldf</span><span>'</span><span>, 
NOUNLOAD,  </span><span>REPLACE</span>,  STATS <span>=</span> <span>5</span>
<span>GO</span>

<span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span>在发布库新建订阅 <strong>使用</strong>推送订阅</span>
<span>use</span> <span>[</span><span>sss</span><span>]</span>
<span>exec</span><span> sp_addsubscription 
</span><span>@publication</span> <span>=</span> N<span>'</span><span>testpub-sss</span><span>'</span><span>, 
</span><span>@subscriber</span> <span>=</span> N<span>'</span><span>JOE\SQL2012</span><span>'</span><span>, 
</span><span>@destination_db</span> <span>=</span> N<span>'</span><span>sss</span><span>'</span><span>, 
</span><span>@subscription_type</span> <span>=</span> N<span>'</span><span>Push</span><span>'</span><span>, 
</span><span>@sync_type</span> <span>=</span> N<span>'</span><span>initialize with backup</span><span>'</span><span>,
</span><span>@article</span> <span>=</span> N<span>'</span><span>all</span><span>'</span><span>, 
</span><span>@update_mode</span> <span>=</span> N<span>'</span><span>read only</span><span>'</span><span>, 
</span><span>@subscriber_type</span> <span>=</span> <span>0</span><span>,
</span><span>@backupdevicetype</span><span>=</span><span>'</span><span>disk</span><span>'</span><span>,
</span><span>@backupdevicename</span><span>=</span><span>'</span><span>C:\SSS_FULLBACKUP2014-4-13.bak</span><span>'</span><span>--</span><span>最后一次备份的备份文件（发布服务器上的存放位置）</span>


<span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span>添加分发代理</span>
<span>exec</span><span> sp_addpushsubscription_agent 
</span><span>@publication</span> <span>=</span> N<span>'</span><span>testpub-sss</span><span>'</span><span>, 
</span><span>@subscriber</span> <span>=</span> N<span>'</span><span>JOE\SQL2012</span><span>'</span><span>, 
</span><span>@subscriber_db</span> <span>=</span> N<span>'</span><span>sss</span><span>'</span><span>, 
</span><span>@job_login</span> <span>=</span> <span>null</span><span>, 
</span><span>@job_password</span> <span>=</span> <span>null</span><span>, 
</span><span>@subscriber_security_mode</span> <span>=</span> <span>0</span><span>, 
</span><span>@subscriber_login</span> <span>=</span> N<span>'</span><span>ReplicationUser</span><span>'</span><span>, 
</span><span>@subscriber_password</span> <span>=</span> N<span>'</span><span>ReplicationForUser</span><span>'</span><span>, 
</span><span>@frequency_type</span> <span>=</span> <span>64</span><span>, 
</span><span>@frequency_interval</span> <span>=</span> <span>0</span><span>, 
</span><span>@frequency_relative_interval</span> <span>=</span> <span>0</span><span>, 
</span><span>@frequency_recurrence_factor</span> <span>=</span> <span>0</span><span>, 
</span><span>@frequency_subday</span> <span>=</span> <span>0</span><span>,
</span><span>@frequency_subday_interval</span> <span>=</span> <span>0</span><span>,
</span><span>@active_start_time_of_day</span> <span>=</span> <span>0</span><span>, 
</span><span>@active_end_time_of_day</span> <span>=</span> <span>235959</span><span>, 
</span><span>@active_start_date</span> <span>=</span> <span>20140408</span><span>, 
</span><span>@active_end_date</span> <span>=</span> <span>99991231</span><span>, 
</span><span>@enabled_for_syncmgr</span> <span>=</span> N<span>'</span><span>False</span><span>'</span><span>, 
</span><span>@dts_package_location</span> <span>=</span> N<span>'</span><span>Distributor</span><span>'</span>
<span>GO</span>


<span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span>脚本创建<strong>数据库</strong><strong><strong>邮件</strong></strong></span><span>
--</span><span>开启<strong>数据库</strong><strong><strong>邮件</strong></strong></span>
<span>EXEC</span> sp_configure <span>'</span><span>show advanced options</span><span>'</span>,<span>1</span>
<span>RECONFIGURE</span> <span>WITH</span><span> OVERRIDE
</span><span>GO</span>
<span>EXEC</span> sp_configure <span>'</span><span>database mail xps</span><span>'</span>,<span>1</span>
<span>RECONFIGURE</span> <span>WITH</span><span> OVERRIDE

</span><span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span>创建<strong><strong>邮件</strong></strong>帐户信息</span>
<span>EXEC</span><span>  msdb..sysmail_add_account_sp
      </span><span>@ACCOUNT_NAME</span> <span>=</span><span>'</span><span>ReplicationErrorMailLog</span><span>'</span>,<span>--</span><span><strong><strong>邮件</strong></strong>帐户名称</span>
      <span>@EMAIL_ADDRESS</span> <span>=</span><span>'</span><span>hiAT163.com</span><span>'</span>,<span>--</span><span>发件人<strong><strong>邮件</strong></strong>地址</span>
      <span>@DISPLAY_NAME</span> <span>=</span><span>'</span><span>系统管理员</span><span>'</span>,<span>--</span><span>发件人姓名</span>
      <span>@REPLYTO_ADDRESS</span> <span>=</span><span>NULL</span><span>,
      </span><span>@DESCRIPTION</span> <span>=</span> <span>NULL</span><span>,
      </span><span>@MAILSERVER_NAME</span> <span>=</span> <span>'</span><span>SMTP.163.COM</span><span>'</span>,<span>--</span><span><strong><strong>邮件</strong></strong>服务器地址</span>
      <span>@MAILSERVER_TYPE</span> <span>=</span> <span>'</span><span>SMTP</span><span>'</span>,<span>--</span><span><strong><strong>邮件</strong></strong>协议</span>
      <span>@PORT</span> <span>=</span><span>25</span>,<span>--</span><span><strong><strong>邮件</strong></strong>服务器端口</span>
      <span>@USERNAME</span> <span>=</span> <span>'</span><span>hiAT163.com</span><span>'</span>,<span>--</span><span>用户名</span>
      <span>@PASSWORD</span> <span>=</span> <span>'</span><span>xxx</span><span>'</span>,<span>--</span><span>密码</span>
      <span>@USE_DEFAULT_CREDENTIALS</span> <span>=</span><span>0</span><span>,
      </span><span>@ENABLE_SSL</span> <span>=</span><span>0</span><span>,
      </span><span>@ACCOUNT_ID</span> <span>=</span> <span>NULL</span>
<span>GO</span>

<span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span><strong>数据库</strong>配置文件</span>
<span>IF</span> <span>EXISTS</span>(<span>SELECT</span> name <span>FROM</span> msdb..sysmail_profile <span>WHERE</span> name<span>=</span>N<span>'</span><span>ReplicationErrorProfileLog</span><span>'</span><span>)
</span><span>BEGIN</span>
    <span>EXEC</span><span> msdb..sysmail_delete_profile_sp
    </span><span>@profile_name</span><span>=</span><span>'</span><span>ReplicationErrorProfileLog</span><span>'</span>
<span>END</span>

<span>EXEC</span><span> msdb..sysmail_add_profile_sp
    </span><span>@profile_name</span> <span>=</span> <span>'</span><span>ReplicationErrorProfileLog</span><span>'</span>,<span>--</span><span>profile名称</span>
    <span>@description</span> <span>=</span> <span>'</span><span><strong>数据库</strong><strong><strong>邮件</strong></strong>配置文件</span><span>'</span>,<span>--</span><span>profile描述</span>
    <span>@profile_id</span> <span>=</span> <span>null</span>
<span>GO</span>

<span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span>用户和<strong><strong>邮件</strong></strong>配置文件相关联</span>
<span>EXEC</span><span> msdb..sysmail_add_profileaccount_sp
    </span><span>@profile_name</span> <span>=</span> <span>'</span><span>ReplicationErrorProfileLog</span><span>'</span>,<span>--</span><span>profile名称</span>
    <span>@account_name</span> <span>=</span> <span>'</span><span>ReplicationErrorMailLog</span><span>'</span>,<span>--</span><span>account名称</span>
    <span>@sequence_number</span> <span>=</span> <span>1</span><span>--</span><span>account 在profile 中顺序</span>
<span>GO</span>


<span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span>发送简单文本的<strong><strong>邮件</strong></strong></span><span>
/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span>创建链接服务器</span><span>
--</span><span>要开启分发服务器上的Distributed Transaction Coordinator（MSDTC服务）</span>
<span>USE</span> <span>[</span><span>master</span><span>]</span>
<span>GO</span>
<span>EXEC</span><span> master.dbo.sp_addlinkedserver 
</span><span>@server</span> <span>=</span> N<span>'</span><span>JOE_DIST</span><span>'</span><span>,   
</span><span>@srvproduct</span><span>=</span>N<span>'</span><span>sqlserver</span><span>'</span><span>, 
</span><span>@provider</span><span>=</span>N<span>'</span><span>SQLOLEDB</span><span>'</span><span>, 
</span><span>@datasrc</span><span>=</span>N<span>'</span><span>JOE</span><span>'</span>
<span>GO</span>



<span>EXEC</span> master.dbo.sp_serveroption <span>@server</span><span>=</span>N<span>'</span><span>JOE_DIST</span><span>'</span>, <span>@optname</span><span>=</span>N<span>'</span><span>rpc</span><span>'</span>, <span>@optvalue</span><span>=</span>N<span>'</span><span>true</span><span>'</span>
<span>GO</span>
<span>EXEC</span> master.dbo.sp_serveroption <span>@server</span><span>=</span>N<span>'</span><span>JOE_DIST</span><span>'</span>, <span>@optname</span><span>=</span>N<span>'</span><span>rpc out</span><span>'</span>, <span>@optvalue</span><span>=</span>N<span>'</span><span>true</span><span>'</span>
<span>GO</span>


<span>USE</span> <span>[</span><span>master</span><span>]</span>
<span>GO</span>
<span>EXEC</span><span> master.dbo.sp_addlinkedsrvlogin 
</span><span>@rmtsrvname</span> <span>=</span> N<span>'</span><span>JOE_DIST</span><span>'</span><span>, 
</span><span>@locallogin</span> <span>=</span> <span>NULL</span><span> , 
</span><span>@useself</span> <span>=</span> N<span>'</span><span>False</span><span>'</span><span>, 
</span><span>@rmtuser</span> <span>=</span> N<span>'</span><span>sa</span><span>'</span>, <span>--</span><span>要求是对distribution有db_owner权限的 实际应用时最好不要用sa</span>
<span>@rmtpassword</span> <span>=</span> N<span>'</span><span>testxxx</span><span>'</span>
<span>GO</span>


<span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span>--发送<strong><strong>邮件</strong></strong> ，这个步骤只是测试，检查编写的动态SQL是否正确</span>
<span>USE</span> <span>[</span><span>distribution</span><span>]</span>
<span>GO</span>

<span>DECLARE</span> <span>@SQL</span> <span>NVARCHAR</span>(<span>MAX</span><span>)
</span><span>DECLARE</span> <span>@replcountersSQL</span> <span>NVARCHAR</span>(<span>MAX</span><span>)
</span><span>DECLARE</span> <span>@replmonitorsubscriptionpendingcmdsSQL</span> <span>NVARCHAR</span>(<span>MAX</span><span>)
 

</span><span>CREATE</span> <span>TABLE</span><span> #replcounters
(
 </span><span>[</span><span>database</span><span>]</span> <span>NVARCHAR</span>(<span>200</span><span>) ,
 </span><span>[</span><span>replicated_transactions</span><span>]</span> <span>BIGINT</span><span> ,
 </span><span>[</span><span>replication_rate_trans_sec</span><span>]</span> <span>DECIMAL</span>(<span>18</span>, <span>2</span><span>) ,
 </span><span>[</span><span>replication_latency</span><span>]</span> <span>DECIMAL</span>(<span>18</span>, <span>2</span><span>) ,
 </span><span>[</span><span>replbeginlsn</span><span>]</span> <span>BINARY</span><span> ,
 </span><span>[</span><span>replnextlsn</span><span>]</span> <span>BINARY</span><span>
)


</span><span>CREATE</span> <span>TABLE</span><span> #replmonitorsubscriptionpendingcmds 
(
</span><span>[</span><span>pendingcmdcount</span><span>]</span> <span>BIGINT</span><span> ,
</span><span>[</span><span>estimatedprocesstime</span><span>]</span> <span>BIGINT</span><span> 
)


</span><span>INSERT</span>  <span>[</span><span>#replmonitorsubscriptionpendingcmds</span><span>]</span>
<span>EXEC</span> <span>[</span><span>JOE_DIST</span><span>]</span>.distribution.dbo.SP_replmonitorsubscriptionpendingcmds <span>'</span><span>JOE</span><span>'</span><span>,
</span><span>'</span><span>SSS</span><span>'</span>, <span>'</span><span>testpub-sss</span><span>'</span>, <span>'</span><span>JOE\SQL2012</span><span>'</span>, <span>'</span><span>SSS</span><span>'</span>, <span>0</span>



<span>INSERT</span> <span>[</span><span>#replcounters</span><span>]</span> <span>EXEC</span> <span>[</span><span>distribution</span><span>]</span>.<span>[</span><span>dbo</span><span>]</span><span>.sp_replcounters

</span><span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>#replcounters</span><span>]</span>
<span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>#replmonitorsubscriptionpendingcmds</span><span>]</span>

<span>SET</span> <span>@replcountersSQL</span> <span>=</span> N<span>'</span><span><h3>
<strong>数据库</strong>滞后时间、吞吐量和事务计数的<strong>复制</strong>统计信息</h3></span><span>'</span><span>+</span>
<span>'</span><span><table border="1">
<span>'</span> <span>+</span><span> 
N</span><span>'</span><span><tr>
<th>[database]</th>
<th>[replicated_transactions]</th>
<th>[replication_rate_trans_sec]</th>
<th>[replication_latency]</th>
<th>[replbeginlsn]</th>
<th>[replnextlsn]</th>
</tr></span><span>'</span>
<span>+</span> <span>CAST</span>(( <span>SELECT</span> 
<span>[</span><span>database</span><span>]</span> <span>AS</span> <span>'</span><span>td</span><span>'</span> , <span>''</span><span>,
</span><span>[</span><span>replicated_transactions</span><span>]</span> <span>AS</span> <span>'</span><span>td</span><span>'</span>,<span>''</span><span>,
</span><span>[</span><span>replication_rate_trans_sec</span><span>]</span> <span>AS</span> <span>'</span><span>td</span><span>'</span>,<span>''</span><span>,
</span><span>[</span><span>replication_latency</span><span>]</span> <span>AS</span> <span>'</span><span>td</span><span>'</span>,<span>''</span><span>,
</span><span>CAST</span>(<span>[</span><span>replbeginlsn</span><span>]</span> <span>AS</span> <span>INT</span>) <span>AS</span> <span>'</span><span>td</span><span>'</span>,<span>''</span><span>,
</span><span>CAST</span>(<span>[</span><span>replnextlsn</span><span>]</span> <span>AS</span> <span>INT</span>) <span>AS</span> <span>'</span><span>td</span><span>'</span>
<span>FROM</span> <span>[</span><span>#replcounters</span><span>]</span>
<span>FOR</span><span>
XML PATH(</span><span>'</span><span>tr</span><span>'</span><span>) ,
ELEMENTS</span><span>--</span><span> TYPE </span>
) <span>AS</span> <span>NVARCHAR</span>(<span>MAX</span>)) <span>+</span> N<span>'</span><span></span>
</table></span><span>'</span><span>;


</span><span>SET</span> <span>@replmonitorsubscriptionpendingcmdsSQL</span> <span>=</span> N<span>'</span><span><h3>事务发布的订阅的等待命令数以及处理这些命令的粗略估计时间</h3></span><span>'</span><span>+</span>
<span>'</span><span><table border="1">
<span>'</span> <span>+</span><span> 
N</span><span>'</span><span><tr>
<th>[pendingcmdcount]</th>
<th>[estimatedprocesstime]</th>
</tr></span><span>'</span>
<span>+</span> <span>CAST</span>(( <span>SELECT</span> 
<span>[</span><span>pendingcmdcount</span><span>]</span> <span>AS</span> <span>'</span><span>td</span><span>'</span> , <span>''</span><span>,
</span><span>[</span><span>estimatedprocesstime</span><span>]</span> <span>AS</span> <span>'</span><span>td</span><span>'</span>,<span>''</span>
<span>FROM</span> <span>[</span><span>#replmonitorsubscriptionpendingcmds</span><span>]</span>
<span>FOR</span><span>
XML PATH(</span><span>'</span><span>tr</span><span>'</span><span>) ,
ELEMENTS</span><span>--</span><span> TYPE </span>
) <span>AS</span> <span>NVARCHAR</span>(<span>MAX</span>)) <span>+</span> N<span>'</span><span></span>
</table></span><span>'</span><span>;

</span><span>SET</span> <span>@SQL</span><span>=</span><span>@replcountersSQL</span><span>+</span><span>'</span><span></span><span>'</span><span>+</span><span>@replmonitorsubscriptionpendingcmdsSQL</span>

<span>EXEC</span> <span>[</span><span>msdb</span><span>]</span>.<span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>sp_send_dbmail</span><span>]</span>
<span>@profile_name</span> <span>=</span> <span>'</span><span>ReplicationErrorProfileLog</span><span>'</span><span> ,
</span><span>@recipients</span> <span>=</span> <span>'</span><span>linyonghua.hi@163.com</span><span>'</span>, <span>--</span><span> varchar(max) --收件人</span>
<span>@subject</span> <span>=</span> N<span>'</span><span><strong>数据库</strong><strong>复制</strong>的相关信息</span><span>'</span>, <span>--</span><span> nvarchar(255) 标题</span>
<span>@body_format</span> <span>=</span> <span>'</span><span>HTML</span><span>'</span>, <span>--</span><span> varchar(20) 正文格式可选值：text html</span>
<span>@body</span> <span>=</span> <span>@SQL</span>

 

<span>DROP</span> <span>TABLE</span> <span>[</span><span>#replcounters</span><span>]</span>
<span>DROP</span> <span>TABLE</span> <span>[</span><span>#replmonitorsubscriptionpendingcmds</span><span>]</span>

<span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span>创建作业  作业命名规则:<strong>数据库</strong>名_ReplicationInfo</span>
<span>DECLARE</span> <span>@job_name</span><span> SYSNAME
</span><span>SET</span> <span>@job_name</span><span>=</span><span>'</span><span>SSS_ReplicationInfo</span><span>'</span> 

<span>EXEC</span> msdb.dbo.sp_add_job <span>@job_name</span><span>=</span><span>@job_name</span><span>, 
</span><span>@enabled</span><span>=</span><span>1</span><span>, 
</span><span>@notify_level_eventlog</span><span>=</span><span>0</span><span>, 
</span><span>@notify_level_email</span><span>=</span><span>0</span><span>, 
</span><span>@notify_level_netsend</span><span>=</span><span>0</span><span>, 
</span><span>@notify_level_page</span><span>=</span><span>0</span><span>, 
</span><span>@delete_level</span><span>=</span><span>0</span><span>, 
</span><span>@description</span><span>=</span>N<span>'</span><span>发送<strong>复制</strong><strong>数据库</strong>情况作业</span><span>'</span><span>, 
</span><span>@category_name</span><span>=</span>N<span>'</span><span>REPL-Checkup</span><span>'</span><span>, 
</span><span>@owner_login_name</span><span>=</span>N<span>'</span><span>sa</span><span>'</span> 
        

<span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span>添加作业步骤</span>
<span>DECLARE</span> <span>@job_name</span><span> SYSNAME
</span><span>DECLARE</span> <span>@DBNAME</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span>DECLARE</span> <span>@SQL</span> <span>NVARCHAR</span>(<span>MAX</span><span>)

</span><span>SET</span> <span>@DBNAME</span><span>=</span><span>'</span><span>distribution</span><span>'</span>
<span>SET</span> <span>@job_name</span><span>=</span><span>'</span><span>SSS_ReplicationInfo</span><span>'</span> 
<span>SET</span> <span>@SQL</span> <span>=</span> N<span>'</span><span>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 </span><span>''</span><span>JOE</span><span>''</span><span>,
</span><span>''</span><span>SSS</span><span>''</span><span>, </span><span>''</span><span>testpub-sss</span><span>''</span><span>, </span><span>''</span><span>JOE\SQL2012</span><span>''</span><span>, </span><span>''</span><span>SSS</span><span>''</span><span>, 0



INSERT [#replcounters] EXEC [distribution].[dbo].sp_replcounters

SELECT * FROM [#replcounters]
SELECT * FROM [#replmonitorsubscriptionpendingcmds]

SET @replcountersSQL = N</span><span>''</span><span><h3>
<strong>数据库</strong>滞后时间、吞吐量和事务计数的<strong>复制</strong>统计信息</h3></span><span>''</span><span>+
</span><span>''</span><span><table border="1">
<span>''</span><span> + 
N</span><span>''</span><span><tr>
<th>[database]</th>
<th>[replicated_transactions]</th>
<th>[replication_rate_trans_sec]</th>
<th>[replication_latency]</th>
<th>[replbeginlsn]</th>
<th>[replnextlsn]</th>
</tr></span><span>''</span><span>
+ CAST(( SELECT 
[database] AS </span><span>''</span><span>td</span><span>''</span><span> , </span><span>''''</span><span>,
[replicated_transactions] AS </span><span>''</span><span>td</span><span>''</span><span>,</span><span>''''</span><span>,
[replication_rate_trans_sec] AS </span><span>''</span><span>td</span><span>''</span><span>,</span><span>''''</span><span>,
[replication_latency] AS </span><span>''</span><span>td</span><span>''</span><span>,</span><span>''''</span><span>,
CAST([replbeginlsn] AS INT) AS </span><span>''</span><span>td</span><span>''</span><span>,</span><span>''''</span><span>,
CAST([replnextlsn] AS INT) AS </span><span>''</span><span>td</span><span>''</span><span>
FROM [#replcounters]
FOR
XML PATH(</span><span>''</span><span>tr</span><span>''</span><span>) ,
ELEMENTS-- TYPE 
) AS NVARCHAR(MAX)) + N</span><span>''</span><span></span>
</table></span><span>''</span><span>;


SET @replmonitorsubscriptionpendingcmdsSQL = N</span><span>''</span><span><h3>事务发布的订阅的等待命令数以及处理这些命令的粗略估计时间</h3></span><span>''</span><span>+
</span><span>''</span><span><table border="1">
<span>''</span><span> + 
N</span><span>''</span><span><tr>
<th>[pendingcmdcount]</th>
<th>[estimatedprocesstime]</th>
</tr></span><span>''</span><span>
+ CAST(( SELECT 
[pendingcmdcount] AS </span><span>''</span><span>td</span><span>''</span><span> , </span><span>''''</span><span>,
[estimatedprocesstime] AS </span><span>''</span><span>td</span><span>''</span><span>,</span><span>''''</span><span>
FROM [#replmonitorsubscriptionpendingcmds]
FOR
XML PATH(</span><span>''</span><span>tr</span><span>''</span><span>) ,
ELEMENTS-- TYPE 
) AS NVARCHAR(MAX)) + N</span><span>''</span><span></span>
</table></span><span>''</span><span>;

SET @SQL=@replcountersSQL+</span><span>''</span><span></span><span>''</span><span>+@replmonitorsubscriptionpendingcmdsSQL

EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = </span><span>''</span><span>ReplicationErrorProfileLog</span><span>''</span><span> ,
@recipients = </span><span>''</span><span>linyonghua.hi@163.com</span><span>''</span><span>, -- varchar(max) --收件人
@subject = N</span><span>''</span><span><strong>数据库</strong><strong>复制</strong>的相关信息</span><span>''</span><span>, -- nvarchar(255) 标题
@body_format = </span><span>''</span><span>HTML</span><span>''</span><span>, -- varchar(20) 正文格式可选值：text html
@body = @SQL

 

DROP TABLE [#replcounters]
DROP TABLE [#replmonitorsubscriptionpendingcmds]
</span><span>'</span>


<span>EXEC</span> msdb.dbo.sp_add_jobstep <span>@job_name</span> <span>=</span> <span>@job_name</span><span>,
</span><span>@step_name</span> <span>=</span> N<span>'</span><span>SendMail</span><span>'</span><span>, 
</span><span>@step_id</span> <span>=</span> <span>1</span><span>,
</span><span>@cmdexec_success_code</span> <span>=</span> <span>0</span><span>, 
</span><span>@on_success_action</span> <span>=</span> <span>3</span><span>,
</span><span>@on_success_step_id</span> <span>=</span> <span>0</span><span>, 
</span><span>@on_fail_action</span> <span>=</span> <span>2</span><span>,
</span><span>@on_fail_step_id</span> <span>=</span> <span>0</span><span>, 
</span><span>@retry_attempts</span> <span>=</span> <span>0</span><span>,
</span><span>@retry_interval</span> <span>=</span> <span>0</span><span>,
</span><span>@os_run_priority</span> <span>=</span> <span>0</span><span>,
</span><span>@subsystem</span> <span>=</span> N<span>'</span><span>TSQL</span><span>'</span><span>, 
</span><span>@command</span> <span>=</span> <span>@SQL</span><span>,
</span><span>@database_name</span> <span>=</span> <span>@DBNAME</span><span>, 
</span><span>@flags</span> <span>=</span> <span>0</span>

         


<span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span>创建作业的调度计划  每天每隔6个小时查看一次</span>
<span>DECLARE</span> <span>@job_name</span><span> SYSNAME
</span><span>DECLARE</span> <span>@Time</span> <span>VARCHAR</span>(<span>100</span><span>)
</span><span>DECLARE</span> <span>@Date</span> <span>DATETIME</span>

<span>--</span><span>修改作业的执行时间</span>
<span>SET</span> <span>@Date</span> <span>=</span> <span>'</span><span>2014-01-08 00:20:00.000</span><span>'</span>
<span>SET</span> <span>@Time</span> <span>=</span> <span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>@Date</span>, <span>8</span> ),<span>'</span><span>:</span><span>'</span>,<span>''</span><span>)

</span><span>SET</span> <span>@job_name</span><span>=</span><span>'</span><span>SSS_ReplicationInfo</span><span>'</span> 

<span>--</span><span>修改作业的执行时间</span>
<span>SET</span> <span>@Time</span> <span>=</span> <span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>@Date</span>, <span>8</span> ),<span>'</span><span>:</span><span>'</span>,<span>''</span><span>)
</span><span>EXEC</span>  msdb.dbo.sp_add_jobschedule  <span>@job_name</span> <span>=</span> <span>@job_name</span>, <span>@name</span><span>=</span>N<span>'</span><span>Plan</span><span>'</span><span>, 
    </span><span>@enabled</span><span>=</span><span>1</span><span>, 
        </span><span>@freq_type</span><span>=</span><span>4</span><span>, 
        </span><span>@freq_interval</span><span>=</span><span>1</span><span>, 
        </span><span>@freq_subday_type</span><span>=</span><span>8</span><span>, 
        </span><span>@freq_subday_interval</span><span>=</span><span>6</span><span>, 
        </span><span>@freq_relative_interval</span><span>=</span><span>0</span><span>, 
        </span><span>@freq_recurrence_factor</span><span>=</span><span>1</span><span>, 
        </span><span>@active_start_date</span><span>=</span><span>20140414</span><span>, 
        </span><span>@active_end_date</span><span>=</span><span>99991231</span><span>, 
        </span><span>@active_start_time</span><span>=</span><span>@Time</span><span>, 
        </span><span>@active_end_time</span><span>=</span><span>235959</span>


<span>EXEC</span>  msdb.dbo.sp_add_jobserver  <span>@job_name</span> <span>=</span> <span>@job_name</span>, <span>@server_name</span> <span>=</span> N<span>'</span><span>(local)</span><span>'</span>




<span>/*</span><span>*******************************************************************</span><span>*/</span>
<span>--</span><span>创建对于[distribution].[dbo].[MSrepl_errors]表的insert触发器，当有错误的时候就发<strong><strong>邮件</strong></strong></span>
<span>USE</span> <span>[</span><span>distribution</span><span>]</span>
<span>GO</span>

<span>IF</span> ( <span>OBJECT_ID</span>(<span>'</span><span>tgr_MSrepl_errors</span><span>'</span>, <span>'</span><span>tr</span><span>'</span>) <span>IS</span> <span>NOT</span> <span>NULL</span><span> )
    </span><span>DROP</span> <span>TRIGGER</span><span> tgr_MSrepl_errors
</span><span>GO</span>

<span>CREATE</span> <span>TRIGGER</span> tgr_MSrepl_errors <span>ON</span> <span>[</span><span>distribution</span><span>]</span>.<span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>MSrepl_errors</span><span>]</span>
    <span>FOR</span> <span>INSERT</span> <span>--</span><span>插入触发</span>
<span>AS</span>
    <span>DECLARE</span> <span>@SQL</span> <span>NVARCHAR</span>(<span>MAX</span><span>)
    </span><span>SET</span> <span>@SQL</span> <span>=</span> N<span>'</span><span><h3>
<strong>数据库</strong><strong>复制</strong>出错信息</h3></span><span>'</span> <span>+</span> <span>'</span><span><table border="1">
<span>'</span>
        <span>+</span> N<span>'</span><span><tr>
<th>[xact_seqno]</th>
<th>[id]</th>
<th>[time]</th>
<th>[source_name]</th>
<th>[error_code]</th>
<th>[session_id]</th>
</tr></span><span>'</span> <span>+</span> <span>CAST</span>(( <span>SELECT</span>  e.<span>[</span><span>xact_seqno</span><span>]</span> <span>AS</span> <span>'</span><span>td</span><span>'</span><span> ,
                        </span><span>''</span><span> ,
                        e.</span><span>[</span><span>id</span><span>]</span> <span>AS</span> <span>'</span><span>td</span><span>'</span><span> ,
                        </span><span>''</span><span> ,
                        e.</span><span>[</span><span>time</span><span>]</span> <span>AS</span> <span>'</span><span>td</span><span>'</span><span> ,
                        </span><span>''</span><span> ,
                        e.</span><span>[</span><span>source_name</span><span>]</span> <span>AS</span> <span>'</span><span>td</span><span>'</span><span> ,
                        </span><span>''</span><span> ,
                        </span><span>CAST</span>(e.<span>[</span><span>error_code</span><span>]</span> <span>AS</span> <span>NVARCHAR</span>(<span>200</span>)) <span>AS</span> <span>'</span><span>td</span><span>'</span><span> ,
                        </span><span>''</span><span> ,
                        </span><span>CAST</span>(e.<span>[</span><span>session_id</span><span>]</span> <span>AS</span> <span>NVARCHAR</span>(<span>200</span>)) <span>AS</span> <span>'</span><span>td</span><span>'</span><span> ,
                        </span><span>''</span>
                <span>FROM</span><span>    dbo.MSdistribution_history h
                        </span><span>JOIN</span> inserted e <span>ON</span> h.error_id <span>=</span><span> e.id
                </span><span>WHERE</span>   comments <span>NOT</span> <span>LIKE</span> <span>'</span><span>%transaction%</span><span>'</span><span>--</span><span>失败的代理</span>
<span>ORDER</span> <span>BY</span>                id <span>DESC</span>
              <span>FOR</span><span>
                XML PATH(</span><span>'</span><span>tr</span><span>'</span><span>) ,
                    ELEMENTS</span><span>--</span><span> TYPE </span>
              ) <span>AS</span> <span>NVARCHAR</span>(<span>MAX</span>)) <span>+</span> N<span>'</span><span></span>
</table></span><span>'</span><span>;

    </span><span>EXEC</span> <span>[</span><span>msdb</span><span>]</span>.<span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>sp_send_dbmail</span><span>]</span> <span>@profile_name</span> <span>=</span> <span>'</span><span>ReplicationErrorProfileLog</span><span>'</span><span>,
        </span><span>@recipients</span> <span>=</span> <span>'</span><span>linyonghua.hi@163.com</span><span>'</span>, <span>--</span><span> varchar(max) --收件人</span>
        <span>@subject</span> <span>=</span> N<span>'</span><span><strong>数据库</strong><strong>复制</strong>出错信息</span><span>'</span>, <span>--</span><span> nvarchar(255) 标题</span>
        <span>@body_format</span> <span>=</span> <span>'</span><span>HTML</span><span>'</span>, <span>--</span><span> varchar(20) 正文格式可选值：text html</span>
        <span>@body</span> <span>=</span> <span>@SQL</span>

<span>GO</span>

 

测试结果

手动启动作业，就可以看到邮件会自动发到我的163邮箱

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

 

 

复制报错的时候也会发邮件

我把复制用户的权限去掉，马上就会报错

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

 SQLSERVER监控复制并使用数据库邮件功能发告警邮件

 

 SQLSERVER监控复制并使用数据库邮件功能发告警邮件

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

SQLSERVER会有一个重试时间，除非你马上停止同步，否则SQLSERVER会不断重试，然后不断发邮件到你的邮箱提醒你~

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

把权限勾上后，没有报错了，也没有再发邮件了

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

 

 

分发代理默认是每隔一分钟重试4次

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

 

你会看到每隔一分钟会收到4封邮件,其中有一封邮件是空的

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

而且大家可以看一下[MSrepl_errors]表，每分钟会插入三条记录到[MSrepl_errors]表这些都是重试记录

<span>USE</span> <span>[</span><span>distribution</span><span>]</span>
<span>GO</span>
<span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>distribution</span><span>]</span>.<span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>MSrepl_errors</span><span>]</span> <span>ORDER</span> <span>BY</span> <span>[</span><span>time</span><span>]</span>

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

暂时还不清楚可以在哪里修改每分钟的重试次数，还有为什么4封邮件中有一封是空的~

原理

（1）对[distribution].[dbo].[MSrepl_errors]表创建了insert触发器，当有错误的时候，SQLSERVER会向这个表插入错误记录

SQLSERVER监控复制并使用数据库邮件功能发告警邮件

（2）利用job获取下面的两个存储过程的结果监视复制发送邮件，这两个存储过程都在分发数据库里

 sp_replcounters    --为每个发布数据库返回有关滞后时间、吞吐量和事务计数的复制统计信息。 此存储过程在发布服务器的任何数据库中执行。

 sp_replmonitorsubscriptionpendingcmds  -- 返回有关对事务发布的订阅的等待命令数以及处理这些命令的粗略估计时间的信息。

此存储过程针对每个返回的订阅返回一行。 在分发服务器的分发数据库上执行此存储过程，用于监视复制。

 SQLSERVER监控复制并使用数据库邮件功能发告警邮件

 

 

感谢群里面的复制大牛：何文通、高文佳、菠萝的帮助

 

SQLServer Replication 常见错误

Replication--复制延迟的诊断和解决

Replication--复制Token

Replication的犄角旮旯（四）--关于事务复制的监控

SQL Server 创建数据库邮件

 

如有不对的地方，欢迎大家拍砖o(∩_∩)o 

Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：自己动手写CPU之第五阶段（2）OpenMIPS对数据相关问题的解决Nächster Artikel：Oracle 基础

In Verbindung stehende Artikel

Mehr sehen