SQLSERVER监控复制并使用数据库邮件功能发告警邮件
- WBOY原创
- 2016-06-07 15:20:231866浏览
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自带的数据库邮件来发告警邮件
当然，如果需要同时满足实时、多个点监控、成本足够可以考虑购买成熟的解决方案
例如：微软的System Center 2012 R2
又或者
自己公司开发监控程序，支持短信告警更加及时
需求
（1）当遇到复制出错的时候发邮件到我的邮箱
（2）每天间隔一定时间发邮件告诉我当前复制的情况
测试环境：Windows7 64位 、发布库SQL2005 SP4 、订阅库SQL2012 SP1、发布库和订阅库都在我的笔记本上
复制所用登录用户：[ReplicationUser]
在进行实验之前，需要测试一下smtp.163.com，端口为25，这个地址是否可以访问。如果不通有可能是你机器防火墙的问题
还有可能需要检查杀毒软件有没有屏蔽了端口，否则会发送邮件失败
呈上完整脚本
<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><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></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><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></table></span><span>'</span><span>;
</span><span>SET</span> <span>@SQL</span><span>=</span><span>@replcountersSQL</span><span>+</span><span>'</span><span></br></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><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></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><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></table></span><span>''</span><span>;
SET @SQL=@replcountersSQL+</span><span>''</span><span></br></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>+</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></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会不断重试，然后不断发邮件到你的邮箱提醒你~
把权限勾上后，没有报错了，也没有再发邮件了
分发代理默认是每隔一分钟重试4次
你会看到每隔一分钟会收到4封邮件,其中有一封邮件是空的
而且大家可以看一下[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>
暂时还不清楚可以在哪里修改每分钟的重试次数，还有为什么4封邮件中有一封是空的~
原理
（1）对[distribution].[dbo].[MSrepl_errors]表创建了insert触发器，当有错误的时候，SQLSERVER会向这个表插入错误记录
（2）利用job获取下面的两个存储过程的结果监视复制发送邮件，这两个存储过程都在分发数据库里
sp_replcounters --为每个发布数据库返回有关滞后时间、吞吐量和事务计数的复制统计信息。 此存储过程在发布服务器的任何数据库中执行。
sp_replmonitorsubscriptionpendingcmds -- 返回有关对事务发布的订阅的等待命令数以及处理这些命令的粗略估计时间的信息。
此存储过程针对每个返回的订阅返回一行。 在分发服务器的分发数据库上执行此存储过程，用于监视复制。
感谢群里面的复制大牛：何文通、高文佳、菠萝的帮助
SQLServer Replication 常见错误
Replication--复制延迟的诊断和解决
Replication--复制Token
Replication的犄角旮旯（四）--关于事务复制的监控
SQL Server 创建数据库邮件
如有不对的地方，欢迎大家拍砖o(∩_∩)o
声明：
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn