SQL Server 监控系统构建指南

本文旨在指导如何从SQL Server监控一组链接的SQL服务器。在开始之前,需要明确几点:本文将使用链接服务器技术和套接字编程,但不会详细讨论这些主题,因为已有其他文章涉及。不过,欢迎提出任何疑问、错误和建议。虽然对这些主题的先验知识会有所帮助,但并非必须。本文是关于如何执行监控的框架,因此可能会有不同的实现想法。

监控系统的用途

该项目可以用于任何基本的数据库监控。但它是为生产支持监控而设计的,其中数据库表被监控以确保某些数据。例如在银行领域,某些交易应在预定的时间表被移动到下游系统。一旦这些交易被移动,它们在数据库中的状态将更新为已发送到下游系统。在这里,可以设置监控来检查交易是否已发送?如果没有,则向生产支持人员发出警报。

监控架构

对于任何基本监控系统,需要以下对象:

  • 监控系统 - 监控系统并发出警报的对象
  • 警报调度 - 监控系统的周期性调度
  • 警报分发器 - 分发警报的对象
  • 警报接收器 - 接收警报的系统

让看看SQL Server为提供了哪些对象,以及需要开发哪些对象:

  • SQL作业 - 用于警报调度
  • 链接服务器功能 - 用于监控系统

剩余的组件“分发警报”和“接收警报”需要开发:

  • AlertDispatcher - 它是一个简单的基于控制台的套接字客户端,将警报消息发送到监听的套接字服务器。
  • AlertReceive - 它是一个基于MFC对话框的套接字应用程序,显示由AlertDispatcher发送的警报。

设置监控系统

在开始监控任何服务器之前,需要将远程SQL服务器注册为链接服务器:

-- Windows身份验证 EXEC sp_addlinkedserver @server='LinkName', @srvproduct='SQLServer OLEDB Provider', @provider='SQLOLEDB', @datasrc='ServerIP_OR_NAME' EXEC sp_addlinkedsrvlogin 'LinkName', 'true', null, null, null -- SQL身份验证 EXEC sp_addlinkedserver @server='LinkName', @srvproduct='SQLServer OLEDB Provider', @provider='SQLOLEDB', @datasrc='ServerIP_OR_NAME' EXEC sp_addlinkedsrvlogin 'LinkName', 'false', null, 'SQL_UserName', 'SQL_Password'

一旦完成了所有服务器的链接服务器设置,可以使用以下查询检查连接性:

SELECT * FROM OPENQUERY(LinkName, 'SELECT TOP 1 * FROM sysobjects WITH (NOLOCK) ORDER BY 1 DESC')

在上述代码中,需要将以下字符串替换为自己的值:

  • LinkName - 是指链接服务器的任何名称,也可以与链接服务器名称相同!
  • ServerIP_OR_NAME - 是SQL服务器的名称或IP地址。
  • SQL_UserName - 是链接服务器上有效的SQL用户,具有查询数据库的权限。
  • SQL_Password - 猜猜看!是的,猜对了,它是密码。

警报调度

一旦设置了链接服务器,下一步就是创建一个SQL作业。它将查询链接服务器进行监控,并将警报传递给警报分发器。

-- 创建一个表来维护警报接收器的列表 CREATE TABLE MonitorIP ( IPAddress VARCHAR(25), Sent_flag BIT, Port INT ) INSERT INTO MonitorIP VALUES ('IP_WHERE_ALERT_RECEIVER_WILL_BE_RUNNING', 1, 1002)

以下是作业中用于监控表中数据存在的示例SQL逻辑。注意:SQL查询可能与此不同。此查询将在作业触发时在链接服务器上执行。此SQL查询执行两件事:

  • 查询链接服务器并从中获取数据。
  • 检查行数是否大于一。如果发现,则使用AlertDispatcher.exe将警报分发给所有接收者。
DECLARE @Row_Count INT DECLARE @ExePath VARCHAR(255), @varMsg VARCHAR(255), @varCmd VARCHAR(2000) DECLARE @IPAddress VARCHAR(25), @Port VARCHAR(10) SET @ExePath = 'C:\Monitoring\AlertDispatcher.exe' SELECT @Row_Count = Row_Count FROM OpenQuery(LinkName, 'SELECT COUNT(*) AS Row_Count FROM Your_Database..Your_Tables WITH (NOLOCK) WHERE Your_Columns = ''SOMEVALUE''') SET @varMsg = 'Date : ' + CAST(GETDATE() AS VARCHAR(50)) + ', Alert for Your_Database..Your_Tables on Your_Server = ' + CAST(Row_Count AS VARCHAR(10)) IF @Row_Count > 0 BEGIN DECLARE C1 CURSOR FOR SELECT IPAddress, Port FROM MonitorIP WHERE sent_flag = 1 OPEN C1 FETCH NEXT FROM C1 INTO @IPAddress, @Port WHILE @@FETCH_STATUS <> -1 BEGIN SET @varCmd = @ExePath + ' ' + @IPAddress + ' ' + @Port + ' "' + @varMsg + '"' EXEC master..xp_cmdshell @varCmd FETCH NEXT FROM C1 INTO @IPAddress, @Port END CLOSE C1 DEALLOCATE C1 END

注意:不要忘记将上述代码中的@ExePath变量替换为环境中的路径。

警报分发器

它是一个简单的基于控制台的套接字客户端,将警报消息发送到监听的套接字服务器。此实用程序接受以下命令行参数以发送消息:

  • IP地址/机器名称
  • TCP端口
  • 要分发的消息。记住,如果消息包含空格,请在引号(")内包含。

AlertDispatcher.exe可以这样执行:

AlertDispatcher.exe localhost 1002 "DateTime, Test Message from AlertDispatcher"

警报接收器

它是一个基于MFC对话框的套接字应用程序,显示由AlertDispatcher发送的警报。此实用程序接受端口号作为命令行参数来监听来自Alert Dispatcher的传入消息。如果没有提供,默认端口号为1002。它期望警报消息的格式为"DateTime, Alert Message",即DateTime和消息文本由逗号分隔。

沪ICP备2024098111号-1
上海秋旦网络科技中心:上海市奉贤区金大公路8218号1幢 联系电话:17898875485