浅聊一下数据监控（针对 MSSQL）

2022 年 6 月 21 日
本文字数：2244 字
阅读完需：约 7 分钟

背景

最近一直在赶工期，没时间总结，所以好长时间不来更文了。正好前几天遇到了一个小问题，在解决掉的同时，觉得这个问题不算大，值的小小的总结一下，而且也不会花太长时间，来给我的主页吹吹土吧~~真的好想认真的总结沉淀一下，一直写业务感觉自己快废了。。

场景

好了，话不多说，来看下我遇到的场景。

我们的系统在和数据库交互的时候，用到了 ORM 框架，而在一次具体的业务里，我发现对某一个模型的数据写入一直失败，查询原因发现是因为数据表使用了触发器造成的。

因为我们的系统是一次针对老系统的全面升级，也因为涉及到的业务面非常广，因此底层数据模型是基本成型的，只有小修小改，没有大规模的改动，而这个系统的运营时间已经超过 10 年了，是个老当益壮的家伙。因此，收到时代制约，当时为了满足一些特定的需求，比如监控对表数据的写操作，就使用了数据库提供的触发器功能。

随着时代的发展，技术领域已经出现了诸多更新换代，比如要对数据表里的数据实现监控，完全可以通过一种更灵活方便的手段，而不再需要使用触发器。注意，我并不是说触发器不好，只是现在系统的建设很多都是高度集成，代码，数据库，缓存等这些基础设施都是分开部署和管理，而触发器作为和 sqlserver 高度耦合的产品，可能会对业务的重构造成一定影响。

因此基于实际情况，我就禁用了表的触发器机制。那么替代方案是什么？

SqlDependency

其实微软也提供了官方的解决方案就是“SqlDependency”，它是 sqlserver 提供的一种消息机制，当监听到指定数据表的初始数据与当前数据不一直时，可以触发对应的操作。整体的流程，可以参照官方的文档。我这里简单罗列一下：

启动通向服务器的 SqlDependency 连接。
创建 SqlConnection 和 SqlCommand 对象以连接到服务器并定义 Transact-SQL 语句。
创建新的 SqlDependency 对象或使用现有对象，然后将其绑定到 SqlCommand 对象。在内部，这将创建一个 SqlNotificationRequest 对象，并根据需要将其绑定到命令对象。此通知请求包含唯一标识此 SqlDependency 对象的内部标识符。如果客户端侦听器尚未处于活动状态，它还会启动它。
向 SqlDependency 对象的 OnChange 事件订阅事件处理程序。
使用 SqlCommand 对象的任何 Execute 方法执行该命令。因为该命令绑定到通知对象，所以服务器认识到它必须生成一个通知，并且队列信息将指向依赖项队列。
停止与服务器的 SqlDependency 连接。

需要注意的是，SqlDependency 是微软提供的关于数据监控的高级别封装类，要想使用它，数据库需要开启“Service Broker”。

开启脚本如下

USE [数据库名称]ALTER DATABASE 数据库名称 SET NEW_BROKER WITH ROLLBACK IMMEDIATE;GOALTER DATABASE 数据库名称 SET ENABLE_BROKER;GO

复制代码

案例

好了，简单看一下实际的案例（我这里对业务代码进行了一些删减，便于分享）

建立一个控制台程序（或者其他类型都可以）
启动监听，编写监听对象

string connectionStr = "xxx连接串";SqlDependency.Start(connectionStr);//传入连接字符串,启动基于数据库的监听DataMonitor.TargetTable(connectionStr);Console.Read();SqlDependency.Stop(connectionStr);//传入连接字符串,启动基于数据库的监听

复制代码

TargetTable 方法（实际监听的表）

public static void TargetTable(string connectionStr){  using (SqlConnection connection = new SqlConnection(connectionStr))  {    connection.Open();    //依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]    using (SqlCommand command = new SqlCommand("SELECT [UPLinkID],[UserID],[TypeID],[StatusID] ,[ChannelType],[ProvinceID],[UserName],[RealName]  FROM [dbo].[User_ProgramTypeLink]", connection))    {      command.CommandType = CommandType.Text;      SqlDependency dependency = new SqlDependency(command);      dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);      using (SqlDataReader sdr = command.ExecuteReader())      {        Console.WriteLine();
        AnsiConsole.Markup($"[bold blue]正在监听中...{DateTime.Now}[/]");        var table = new Table().Centered();        table.AddColumn("被授权人ID");        table.AddColumn("被授权赛项ID");        table.AddColumn("授权人");        while (sdr.Read())        {          string column1 = sdr["UserID"] == null ? "无" : Convert.ToString(sdr["UserID"]);          string column2 = sdr["TypeID"] == null ? "无" : Convert.ToString(sdr["TypeID"]);          string column3 = sdr["UserName"] == null ? "无" : Convert.ToString(sdr["UserName"]);          table.AddRow(column1, column2, column3);
        }        AnsiConsole.Write(table);        sdr.Close();      }    }  }}

复制代码

SqlDependcy 订阅的事件

private static void dependency_OnChange(object sender, SqlNotificationEventArgs e){  if (e.Type == SqlNotificationType.Change) //只有数据发生变化时,才重新获取并数据  { 
    AnsiConsole.MarkupLine($"[red]数据发生了变化!有人执行了【{e.Info}】操作[/]，");    //执行一些通知操作，如写日志，发送邮件，站内信等    TargetTable(connectionStr);  }}

复制代码

好了到此，整个流程就结束了，看一下执行效果的截图