从SQL Server生成事件 [英] Generating events from SQL server

问题描述

我正在寻找最佳实践或示例，以了解如何能够为给定SQL Server 2008 R2数据库上的所有更新事件生成事件。为了更具描述性，我在POC上进行工作，在那里我实质上将更新事件发布到队列（在我的情况下为RabbitMq），然后供各种消费者使用。这将是通过事件源实现CQRS仅查询数据模型的第一部分。通过放置在队列上，任何人都可以订阅这些事件以复制到任意数量的仅查询数据模型中。这部分很清楚，定义也很明确。我遇到的问题是确定从SQL Server生成事件的最佳方法。我得到了一些想法，例如监视事务日志和SSIS。但是，我不确定这些选项是否可行甚至可行。

I am looking for a best practice or example of how I might be able to generate events for all update events on a given SQL Server 2008 R2 db. To be more descriptive, I am working on a POC where I would essentially publish update events to a queue (RabbitMq in my case) that could then be consumed by various consumers. This would be the first part of implementing a CQRS query-only data model via event sourcing. By placing on the que anybody could then subscribe to these events for replication into any number of query-only data models. This part is clear and fairly well-defined. the problem I am having is determining the best approach for generating the events out from SQL server. I have been given a few ideas such as monitoring the transaction log and SSIS. However, I'm not entirely sure if these options are adviseable or even feasible.

有人对这种事情有任何经验或想法吗？关于这样的冒险？任何帮助或指导将不胜感激。

Does anybody have any experience with this sort of thing or have any notions on how to go about such an adventure? any help or guidance would be greatly appreciated.

推荐答案

您无法监视日志，因为即使 if 您将能够理解它，并且有机会在有机会阅读日志之前 回收日志。除非以某种方式将日志标记为不被截断，否则它将被重用。例如，启用事务复制后，日志将被固定，直到复制代理读取该日志，然后仅 then 被截断。

You cannot monitor the log because, even if you would be able to understand it, you have the problem of the log being recycled before you had a chance to read it. Unless the log is somehow marked not to be truncated it will reused. For instance when transactional replication is enabled the log be pinned until is read by the replication agent and only then truncated.

SSIS是一个非常宽泛的概念，并且说使用SSIS来检测变化类似于说我将使用编程语言来解决我的问题。详细信息是如何您将如何使用SSIS？使用或不使用SSIS，都无法可靠地检测任意模式上的数据更改。即使是专门为允许检测更改而设计的数据模型也存在问题，尤其是在检测删除时。

SSIS is a very broad concept and saying that 'using SSIS to detect changes' is akin to saying 'I'll use a programing language to solve my problem'. The details is how would you use SSIS? There is no way, with or without SSIS, to reliably detect data changes on an arbitrary schema. Even data models specifically designed to allow for detecting changes have issues, specially at detecting deletes.

但是有可行的替代方法。您可以部署 Change Data Capture 并将其委派给引擎本身以跟踪更改。消费这些检测到的更改并将它们发布给消费者（如果您愿意，可以通过RabbitMQ发布）是SSIS擅长的。但是您必须了解，SSIS不能很好地适应连续实时任务。它旨在按批处理定期运行，因此，当SSIS作业运行时，您的变更通知消费者将在高峰（很长的延迟（分钟））内得到快速通知。

However there are viable alternatives. You can deploy Change Data Capture and delegate to the engine itself to track the changes. Consuming these detected changes and publishing them to consumers (via RabbitMQ if that's your fancy) is a something SSIS would be good at. But you have to understand that SSIS does not fare well to continuos, real-time tasks. It is designed to run periodically on batches, so your change notification consumers will be notified in spikes, with long delays (minutes), when the SSIS jobs run.

实时方法更好的解决方案是 Service Broker 。一种可能性是 SEND 服务代理来自触发器的消息，但我不建议这样做。更好的设计是让应用程序本身通过 SEND 发布更改，在进行数据修改时显式地显示消息。使用SQL Server 2012可以多播Service Broker消息到其他SQL Server使用者（包括SQL Server Express）。 SSB消息传递完全是事务性的（如果事务回滚，则不会发送任何消息），并且不需要使用消息存储资源管理器进行两阶段提交。但是要通过RabbitMQ广播，您需要桥接通信，即。 RECEIVE SSB消息并进行转换进入RabbitMQ通知。

For a real-time approach a better solution is Service Broker. One possibility is to SEND Service Broker messages from triggers, but I would not recommend it. A better design is to have the application itself publish the changes by SEND-ing the message explicitly, when it does the data modification. With SQL Server 2012 is possible to multicast Service Broker messages to other SQL Server consumers (including SQL Server Express). SSB message delivery is fully transactional (no message gets sent if transaction rolls back) and does not require two-phase-commit with a message store resource manager. But to broadcast via RabbitMQ you would need to bridge the communication, ie. RECEIVE the SSB messages and transform them into RabbitMQ notifications.

这篇关于从SQL Server生成事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！