如何跟踪.NET应用程序中许多SQL Server数据库的更改？

Problem:

问题：

There are a lot of different databases, which is populated by many different applications directly (without any common application layer). Data can be accessed only through SP (by policy)

有许多不同的数据库，它们直接由许多不同的应用程序填充（没有任何常见的应用程序层）。只能通过SP访问数据（按政策）

Task:

任务：

Application needs to track changes in these databases and react in minimal time.

应用程序需要跟踪这些数据库中的更改并在最短时间内做出响应

Possible solutions:

可能的解决方案：

1) Create trigger for each table in each database, which will populate one table with events. Application will watch this table through SqlDependency.

1）为每个数据库中的每个表创建触发器，这将使用事件填充一个表。应用程序将通过SqlDependency观察此表。

2) Watch each table in each database through SqlDependency.

2）通过SqlDependency观察每个数据库中的每个表。

3) Create trigger for each table in each database, which will notify application using managed extension.

3）为每个数据库中的每个表创建触发器，这将使用托管扩展通知应用程序。

Which is the best way?

哪种方式最好？

3 个解决方案

#1

This can be an extensive topic. First of all: What is the SQL Server version used?

这可能是一个广泛的主题。首先：使用的SQL Server版本是什么？

if your are using SQL 2008 the Change Data Capture is the tool of choice This new feature enables you to monitor EVERY change made to databases within SQL 2008. This includes DDL changes as well as changes to the data. Check an introduction here.

如果您使用的是SQL 2008，那么Change Data Capture是首选工具。此新功能使您能够监视SQL 2008中对数据库所做的每一次更改。这包括DDL更改以及对数据的更改。在这里查看介绍。

If you are using an older version of SQL 2008 and you are allowed to modify the DDL of the database the option 3 would be the one of choice (of the once you described). I would not recommend it though, since there are other things to consider, like what happens when a transaction rolls back or when triggers are deactivated when bulk inserting for example?

如果您使用的是旧版本的SQL 2008，并且您可以修改数据库的DDL，则选项3将是您选择的选项之一（您所描述的那个）。我不建议这样做，因为还有其他事情需要考虑，例如当事务回滚时或者当批量插入时触发器被停用时会发生什么？

It will be quiet a challenge to make your solution work properly in all of these cases.

在所有这些情况下，让您的解决方案正常运行将是一项挑战。

Another way you could go is to watch the Transaction Log file. This way by far the best, but also most complex way of doing it, since there is almost no documentation on the proprietary log format. Also it's bound to a specific version of SQL Server. This will result in a no impact monitoring of the chosen databases.

另一种方法是观察事务日志文件。这种方式是迄今为止最好的，也是最复杂的方式，因为几乎没有关于专有日志格式的文档。它也绑定到特定版本的SQL Server。这将导致对所选数据库的无影响监视。

Yet another approach is creating a copy of the data that is to be monitored and periodically check if there are differences. This has the benefit that NO changes to the source databases have to be made. As well as get rid of transaction or bulk inserting issues. Since latest at the next monitoring run you will be able to discover the changes.

另一种方法是创建要监视的数据的副本，并定期检查是否存在差异。这样做的好处是不得对源数据库进行任何更改。以及摆脱交易或批量插入问题。从下次监控运行的最新版本开始，您将能够发现这些变化。

The performance impact is rather minimal since it would only require a primary index consecutive read for the tables that are to be monitored. And this is by far the most optimized way of interacting with a database. This approach will however require quite a development effort. I have to know, since this is my prime focus since the last years. Check here ;)

性能影响相当小，因为它只需要对要监视的表进行主索引连续读取。这是迄今为止与数据库交互的最优化方式。然而，这种方法需要相当大的开发工作。我必须知道，因为这是我自去年以来的主要焦点。点击这里;）

(I hope linking is ok, in this case since its on topic otherwise I remove it)

（我希望链接没问题，在这种情况下，因为它的主题，否则我删除它）

#2

Don't you review possibility to use SQL profiler. Using filter you can select only update operations, then write to log.

你不回顾使用SQL分析器的可能性。使用过滤器，您只能选择更新操作，然后写入日志。

#3

You can use an open source realization of the SqlDependency class - SqlDependencyEx. It uses a database trigger and native Service Broker notification to receive events about the table changes. This is an usage example:

您可以使用SqlDependency类的开源实现 - SqlDependencyEx。它使用数据库触发器和本机Service Broker通知来接收有关表更改的事件。这是一个用法示例：

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

With SqlDependecyEx you are able to monitor just INSERT or DELETE, avoiding UPDATE. Hope this helps.

使用SqlDependecyEx，您只能监视INSERT或DELETE，从而避免更新。希望这可以帮助。

#1