Nginx不仅可以作为强大的web服务器，而且nginx还可以按多种方式对后端服务器做负载均衡，同时还支持后端服务器的健康检查。 Nginx的负载均衡通过ng

123213

sql server定时任务监控

由于原来一直在用的zabbix监控系统，没有监控业务中数据是否运行（且设备端口有限，没办法进行端口检测），可以监控磁盘，应用（打开情况），所以做了以下设计。

可以进行分组检测，设置检测是否可用

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

USE [checkTheServer] --检测联通性，并获取设备上的特定参数，防止错误，导致定时任务中断。 GO /****** Object:  StoredProcedure [dbo].[sp_checkconnect4]    Script Date: 09/15/2015 19:15:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create proc [dbo].[sp_checkconnect4] @servername varchar(50),@ip varchar(50),@dbuser varchar(50),@passwd varchar(50),@db varchar(50),@tab varchar(50), @local_time datetime output, @folio_time datetime output, @now_time datetime output, @rtn int output as   DECLARE @sqls nvarchar(4000) begin try     set @sqls='select top 1 @a=local_time,@aa=folio_time,@aaa=GETDATE() from openrowset(''SQLOLEDB'','''+@ip+''';'''+@dbuser+''';'''+@passwd+''','+@db+'.dbo.'+@tab+') order by ID desc;'   exec sp_executesql @sqls,N'@a datetime output,@aa datetime output,@aaa datetime output',@local_time output,@folio_time output,@now_time output   set @rtn=1 end try   begin catch   set @rtn=@@ERROR   insert into dbo.willSendMail values(@servername+'连接失败',ERROR_NUMBER()) end catch GO

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87

--版本3 USE checkTheServer GO DECLARE @serverid int,@servername varchar(50),@ip varchar(50),@dbuser varchar(50),@passwd varchar(50),@db varchar(50),@tab varchar(50) DECLARE @time_lag int,@report_time int,@checktime datetime,@checktime_var varchar(20) DECLARE @sqls nvarchar(4000),@rtn int,@folio_time datetime,@local_time datetime,@now_time datetime,@jobs int,@log_updown int,@logfile int,@telblack int DECLARE contact_cursor CURSOR FOR select serverid,servername,ip,dbuser,passwd,db,tab,checktime,time_lag,report_time from dbo.servermessage where enable='1'and servergroup='1' --清空发邮件的表 truncate table dbo.willSendMail     OPEN contact_cursor FETCH NEXT FROM contact_cursor INTO @serverid,@servername,@ip,@dbuser,@passwd,@db,@tab,@checktime,@time_lag,@report_time -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN   exec sp_checkconnect4 @servername,@ip,@dbuser,@passwd,@db,@tab,@local_time output,@folio_time output,@now_time output,@rtn output   if(@rtn=1)   begin     set @checktime_var=convert(varchar(20),@checktime,120)     -- Jobs     set @sqls='select @b=count(*) from openrowset(''SQLOLEDB'','''+@ip+''';'''+@dbuser+''';'''+@passwd+''','+@db+'.dbo.jobs);'     exec sp_executesql @sqls,N'@b int output',@jobs output     -- log_updown     set @sqls='select @c=count(*) from openrowset(''SQLOLEDB'','''+@ip+''';'''+@dbuser+''';'''+@passwd+''','+@db+'.dbo.log_updown) where success<>''1''and fcode<>''chgdate''and fdate>='''+@checktime_var+''';'     exec sp_executesql @sqls,N'@c int output',@log_updown output     -- logfile     set @sqls='select @d=count(*) from openrowset(''SQLOLEDB'','''+@ip+''';'''+@dbuser+''';'''+@passwd+''','+@db+'.dbo.logfile) where fdt>='''+@checktime_var+''' and fmessage like ''%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'';'     exec sp_executesql @sqls,N'@d int output',@logfile output     -- telblack     set @sqls='select @f=count(*) from openrowset(''SQLOLEDB'','''+@ip+''';'''+@dbuser+''';'''+@passwd+''','+@db+'.dbo.telblack) where fdt>='''+@checktime_var+''';'     exec sp_executesql @sqls,N'@f int output',@telblack  output     --     if(@jobs!=0)     begin       insert into dbo.willSendMail values(@servername+'有未处理的命令列表(数量)',@jobs);     end     if(@log_updown!=0)     begin       insert into dbo.willSendMail values(@servername+'有未处理的升降级日志(数量)',@log_updown);     end     if(@telblack!=0)     begin       insert into dbo.willSendMail values(@servername+'有未处理的无码长话(数量)',@telblack);     end     if(@logfile!=0)     begin       insert into dbo.willSendMail values(@servername+'有操作日志(数量)',@logfile);     end     if (abs(DATEDIFF(minute ,@local_time,@now_time))>@time_lag)     begin       insert into dbo.willSendMail values(@servername+'插入时间与系统时间间隔过大(分钟)',DATEDIFF(minute ,@local_time,@now_time));     end     if (abs(DATEDIFF(minute ,@folio_time,@local_time))>@report_time)     begin       insert into dbo.willSendMail values(@servername+'PBX时间错误(分钟)',DATEDIFF(minute ,@folio_time,@local_time));     end     insert into record(serverid,servername,folio_time,local_time,jobs,log_updown,logfile,telblack)values(@serverid,@servername,@folio_time,@local_time,@jobs,@log_updown,@logfile,@telblack);     -- This is executed as long as the previous fetch succeeds.   end   FETCH NEXT FROM contact_cursor   INTO @serverid,@servername,@ip,@dbuser,@passwd,@db,@tab,@checktime,@time_lag,@report_time END CLOSE contact_cursor DEALLOCATE contact_cursor   if((select top 1 1 from dbo.willSendMail)=1 )   begin     insert into dbo.willSendMail values('邮件已发送',0);           --可以再邮件中以html的方式显示表格     set @Sqls=(select '<tr><th>'+CAST(content AS varchar) + '</th><th>'+ CAST(status AS varchar) +'</th></tr>' from dbo.willSendMail FOR XML PATH(''))     set @Sqls='<table border="1" cellspacing="0" bordercolor="black">'+REPLACE(REPLACE(@Sqls,'&gt;','>'),'&lt;','<')+'</table>'           EXEC msdb.dbo.sp_send_dbmail @profile_name='148-alarm',                              @recipients='13662580487@163.com',                                @subject= '例行检查',                                @body = @Sqls,                              @body_format = 'HTML';   end GO