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，这个地址是否可以访问。如果不通有可能是你机器防火墙的问题

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

呈上完整脚本

--测试复制邮件告警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.aspxuse [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.htmuse [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.htmexec 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.htmuse [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 = 5GO/*********************************************************************/--在发布库新建订阅 使用推送订阅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',1RECONFIGURE WITH OVERRIDEGOEXEC sp_configure 'database mail xps',1RECONFIGURE 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 = NULLGO/*********************************************************************/--数据库配置文件IF EXISTS(SELECT name FROM msdb..sysmail_profile WHERE name=N'ReplicationErrorProfileLog')BEGIN    EXEC msdb..sysmail_delete_profile_sp    @profile_name='ReplicationErrorProfileLog'ENDEXEC msdb..sysmail_add_profile_sp    @profile_name = 'ReplicationErrorProfileLog',--profile名称    @description = '数据库邮件配置文件',--profile描述    @profile_id = nullGO/*********************************************************************/--用户和邮件配置文件相关联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]GOEXEC master.dbo.sp_addlinkedserver @server = N'JOE_DI