nagios监控sqlserver复制同步

一主多从

从库为149 154 48 207

分别在主库上创建链接数据库

SQL149 SQL154 SQL48 SQL207

创建发布的时候，自定义一个监控表test_sqlcopy

创建存贮过程，同步正确返回 1，不同步返回0

/****** 对象:  StoredProcedure [dbo].[check_sync]    脚本日期: 06/11/2014 13:51:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[check_sync]
as
begin
declare @result int
declare @num int
declare @num154 varchar(20)
declare @num149 varchar(20)
declare @num48 varchar(20)
declare @num207 varchar(20)
select @num=cast( floor(rand()*10) as int)
update test_sqlcopy set [name]=@num
WAITFOR DELAY '00:00:3'
select @num154=name from sql154.card.dbo.test_sqlcopy
select @num149=name from sql154.card.dbo.test_sqlcopy
select @num48=name from sql154.card.dbo.test_sqlcopy
select @num207=name from sql154.card.dbo.test_sqlcopy
if (@num154=@num and @num149=@num and @num48=@num and @num207=@num)
select @result=1
else 
select @result=0
return @result

end

nagios监控脚本

@sqlcmd  -S SVCTAG-3YGZ43X  -d card -W  -Q "declare @res int exec @res=check_sync select @res"|findstr "^[1-9]" >nul 2>nul
@if %ERRORLEVEL% == 1 (
@echo SYNC IS OK - SYNC is ok !
@exit 0) else (
@echo SYNC Critical - SYNC have a error!!!
@exit 2)
@pause

基本延迟为5秒