问题：

有很多不同的数据库，它是由许多不同的应用填充直接（没有任何共同的应用层）。数据只能通过SP（政策性）来访问

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)

任务：

应用程序需要跟踪变化，这些数据库并在最短的时间内作出反应。

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

可能的解决方案：

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

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

2）通过的SqlDependency关注每个数据库中的每个表。

2) Watch each table in each database through SqlDependency.

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

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

这是最好的方法是什么？

Which is the best way?

推荐答案

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

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

如果您使用的是SQL 2008年的变更数据捕获是首选工具 这一新功能使您能够监控的 SQL 2008这包括内数据库尽一切更改 DDL修改和更改数据。 请介绍here.

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 Server 2008的旧版本，并允许您为修改DDL 数据库的选项3 将是首选之一（一旦你描述）。我不会推荐它，虽然，因为有其他事情要考虑，像什么发生在一个事务回滚或者触发器时，批量插入例如停用？

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?

这将是一个安静的挑战，让您的解决方案正常工作在所有这些情况。

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

另一种方式，你可以去是看事务日志文件。这样，迄今为止最好的，也是最复杂的方式这样做的，因为是在专有的日志格式几乎没有任何文档。此外，它绑定到SQL Server的特定版本。这将导致一个没有影响监测所选择的数据库。

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.

另一种方法是创建数据的复制也就是被监控和定期检查是否有差异。 这具有 NO更改源数据库必须做的好处。除了摆脱交易或批量插入问题。由于最新的下一个监测来看，你将能够发现的变化。

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)