SQLServer数据库之SQLSERVER监控复制并使用数据库邮件功能发告警邮件
小标 2019-03-20 来源 : 阅读 980 评论 0

摘要:本文主要向大家介绍了SQLServer数据库之SQLSERVER监控复制并使用数据库邮件功能发告警邮件,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。

本文主要向大家介绍了SQLServer数据库之SQLSERVER监控复制并使用数据库邮件功能发告警邮件,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。

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

从原来的机房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    )/*********************************************************************/--在发布库和订阅库建立一个同名的登录用户,这两个登录用户都对发布库有访问权限/*********************************************************************/--设置指定数据库的复制选项--存储过程说明//msdn.microsoft.com/zh-tw/library/ms188769.aspxuse [sss]exec sp_replicationdboption @dbname = N'sss', @optname = N'publish', @value = N'true'GO/*********************************************************************/-- 添加事务发布--存储过程说明//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/*********************************************************************/--添加快照代理--存储过程说明//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/*********************************************************************/-- 添加发布项目--存储过程说明//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_DIST',   @srvproduct=N'sqlserver', @provider=N'SQLOLEDB', @datasrc=N'JOE'GOEXEC master.dbo.sp_serveroption @server=N'JOE_DIST', @optname=N'rpc', @optvalue=N'true'GOEXEC master.dbo.sp_serveroption @server=N'JOE_DIST', @optname=N'rpc out', @optvalue=N'true'GOUSE [master]GOEXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'JOE_DIST',     --★Do@locallogin = NULL ,          @useself = N'False',  @rmtuser = N'sa', --要求是对distribution有db_owner权限的 实际应用时最好不要用sa    --★Do@rmtpassword = N'testxxx'    --★DoGO/*********************************************************************/----发送邮件 ,这个步骤只是测试,检查编写的动态SQL是否正确USE [distribution]GODECLARE @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(1000) , [replnextlsn] BINARY(1000))CREATE TABLE #replmonitorsubscriptionpendingcmds ([pendingcmdcount] BIGINT ,[estimatedprocesstime] BIGINT )INSERT  [#replmonitorsubscriptionpendingcmds]EXEC [JOE_DIST].distribution.dbo.SP_replmonitorsubscriptionpendingcmds 'JOE',   --★Do'SSS', 'testpub-sss', 'JOE\SQL2012', 'SSS', 0   --★DoINSERT [#replcounters] EXEC [distribution].[dbo].sp_replcountersSELECT * FROM [#replcounters]SELECT * FROM [#replmonitorsubscriptionpendingcmds]SET @replcountersSQL = N'<H3>数据库滞后时间、吞吐量和事务计数的复制统计信息</H3>'+'<table border="1">' + N'<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>'+ 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]FORXML PATH('tr') ,ELEMENTS-- TYPE ) AS NVARCHAR(MAX)) + N'</table>';SET @replmonitorsubscriptionpendingcmdsSQL = N'<H3>事务发布的订阅的等待命令数以及处理这些命令的粗略估计时间</H3>'+'<table border="1">' + N'<tr><th>[pendingcmdcount]</th><th>[estimatedprocesstime]</th></tr>'+ CAST(( SELECT [pendingcmdcount] AS 'td' , '',[estimatedprocesstime] AS 'td',''FROM [#replmonitorsubscriptionpendingcmds]FORXML PATH('tr') ,ELEMENTS-- TYPE ) AS NVARCHAR(MAX)) + N'</table>';SET @SQL=@replcountersSQL+'</br>'+@replmonitorsubscriptionpendingcmdsSQLEXEC [msdb].[dbo].[sp_send_dbmail]@profile_name = 'ReplicationErrorProfileLog' ,    --★Do@recipients = 'xxxx@163.com', -- varchar(max) --收件人    --★Do@subject = N'数据库复制的相关信息', -- nvarchar(255) 标题    --★Do@body_format = 'HTML', -- varchar(20) 正文格式可选值:text html@body = @SQL DROP TABLE [#replcounters]DROP TABLE [#replmonitorsubscriptionpendingcmds]/*********************************************************************/--创建作业  作业命名规则:数据库名_ReplicationInfoDECLARE @job_name SYSNAMESET @job_name='SSS_ReplicationInfo'     --★DoEXEC 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 SYSNAMEDECLARE @DBNAME NVARCHAR(100)DECLARE @SQL NVARCHAR(MAX)SET @DBNAME='distribution'SET @job_name='SSS_ReplicationInfo'     --★DoSET @SQL = N'USE [distribution]GODECLARE @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(1000) , [replnextlsn] BINARY(1000))CREATE TABLE #replmonitorsubscriptionpendingcmds ([pendingcmdcount] BIGINT ,[estimatedprocesstime] BIGINT )INSERT  [#replmonitorsubscriptionpendingcmds]EXEC [JOE_DIST].distribution.dbo.SP_replmonitorsubscriptionpendingcmds ''JOE'',   --★Do''SSS'', ''testpub-sss'', ''JOE\SQL2012'', ''SSS'', 0   --★DoINSERT [#replcounters] EXEC [distribution].[dbo].sp_replcountersSELECT * FROM [#replcounters]SELECT * FROM [#replmonitorsubscriptionpendingcmds]SET @replcountersSQL = N''<H3>数据库滞后时间、吞吐量和事务计数的复制统计信息</H3>''+''<table border="1">'' + N''<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>''+ 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]FORXML PATH(''tr'') ,ELEMENTS-- TYPE ) AS NVARCHAR(MAX)) + N''</table>'';SET @replmonitorsubscriptionpendingcmdsSQL = N''<H3>事务发布的订阅的等待命令数以及处理这些命令的粗略估计时间</H3>''+''<table border="1">'' + N''<tr><th>[pendingcmdcount]</th><th>[estimatedprocesstime]</th></tr>''+ CAST(( SELECT [pendingcmdcount] AS ''td'' , '''',[estimatedprocesstime] AS ''td'',''''FROM [#replmonitorsubscriptionpendingcmds]FORXML PATH(''tr'') ,ELEMENTS-- TYPE ) AS NVARCHAR(MAX)) + N''</table>'';SET @SQL=@replcountersSQL+''</br>''+@replmonitorsubscriptionpendingcmdsSQLEXEC [msdb].[dbo].[sp_send_dbmail]@profile_name = ''ReplicationErrorProfileLog'' ,@recipients = ''xxx@163.com'', -- varchar(max) --收件人   --★Do@subject = N''数据库复制的相关信息'', -- nvarchar(255) 标题    --★Do@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 SYSNAMEDECLARE @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'     --★Do--修改作业的执行时间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=235959EXEC  msdb.dbo.sp_add_jobserver  @job_name = @job_name, @server_name = N'(local)'/*********************************************************************/--创建对于[distribution].[dbo].[MSrepl_errors]表的insert触发器,当有错误的时候就发邮件USE [distribution]GOIF ( OBJECT_ID('tgr_MSrepl_errors', 'tr') IS NOT NULL )    DROP TRIGGER tgr_MSrepl_errorsGOCREATE TRIGGER tgr_MSrepl_errors ON [distribution].[dbo].[MSrepl_errors]    FOR INSERT --插入触发AS
begin    DECLARE @SQL NVARCHAR(MAX)    SET @SQL = N'<H3>数据库复制出错信息</H3>' + '<table border="1">'        + N'<tr><th>[xact_seqno]</th><th>[id]</th><th>[time]</th><th>[source_name]</th><th>[error_code]</th><th>[session_id]</th></tr>' + 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'</table>';    EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = 'ReplicationErrorProfileLog',        @recipients = 'xxxx@163.com', -- varchar(max) --收件人        @subject = N'数据库复制出错信息', -- nvarchar(255) 标题        @body_format = 'HTML', -- varchar(20) 正文格式可选值:text html        @body = @SQL
end
GO


测试结果

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


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

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


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


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


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


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


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

USE [distribution]GOSELECT * FROM [distribution].[dbo].[MSrepl_errors] ORDER BY [time]


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


原理

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

本文由职坐标整理并发布,希望对同学们学习SQL Server有所帮助,更多内容请关注职坐标数据库SQL Server数据库频道!

本文由 @小标 发布于职坐标。未经许可,禁止转载。
喜欢 | 0 不喜欢 | 0
看完这篇文章有何感觉?已经有0人表态,0%的人喜欢 快给朋友分享吧~
评论(0)
后参与评论

您输入的评论内容中包含违禁敏感词

我知道了

助您圆梦职场 匹配合适岗位
验证码手机号,获得海同独家IT培训资料
选择就业方向:
人工智能物联网
大数据开发/分析
人工智能Python
Java全栈开发
WEB前端+H5

请输入正确的手机号码

请输入正确的验证码

获取验证码

您今天的短信下发次数太多了,明天再试试吧!

提交

我们会在第一时间安排职业规划师联系您!

您也可以联系我们的职业规划师咨询:

小职老师的微信号:z_zhizuobiao
小职老师的微信号:z_zhizuobiao

版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
 沪公网安备 31011502005948号    

©2015 www.zhizuobiao.com All Rights Reserved

208小时内训课程