SQL Server Event Generation

Question

I am looking for best practice or an example of how I can generate events for all update events on this SQL Server 2008 R2 db. To be more visual, I am working on a POC where I would essentially post update events to the queue (RabbitMq in my case), which can then be used by various consumers. This will be the first part of implementing a CQRS-only query data model using an event source. By queuing, someone can subscribe to these events for replication to any number of data models for queries only. This part is understandable and quite clearly defined. the problem I am facing is determining the best approach for generating events from the SQL server. They gave me some ideas, such as transaction log monitoring and SSIS. However, I am not entirely sure whether these options are appropriate or even feasible.

Does anyone have experience with this kind of thing or have any idea how to go on such an adventure? Any help or guidance would be greatly appreciated.

Answer 1

You cannot control the magazine, because even if you can understand it, you had a problem processing the magazine before you had the opportunity to read it. If the log is somehow not truncated, it will be reused. For example, when transactional replication is enabled, the journal must be pinned until it is read by the replication agent and only then truncated.

SSIS is a very broad concept and says that "using SSIS to detect changes" is akin to saying "I will use a programming language to solve my problem." Details on how to use SSIS? There is no way, with or without SSIS, to reliably detect data changes on an arbitrary circuit. Even data models specifically designed to detect changes have problems, especially when deleting deletions.

However, viable alternatives exist. You can expand Change data capture and delegate the engine itself to track changes. Consuming these detected changes and publishing them to consumers (via RabbitMQ, if that's your fantasy) is what SSIS would be good for. But you must understand that SSIS is not well suited for continuous, real-time tasks. It is designed to run packages periodically, so your change notification users will be notified of spikes with long delays (in minutes) when SSIS jobs are running.

For a real-time approach, Service Broker is the best solution. One possibility is SEND messages from Service Broker triggers, but I would not recommend it. The best option is that the application itself publishes the SEND changes - explicitly indicating the message when it performs data modification. With SQL Server 2012, multicast messages can be sent to other SQL Server users (including SQL Server Express). SSB message delivery is fully transactional (no message is sent when sending transactions) and does not require a two-phase commit with the message store resource manager. But for broadcasting through RabbitMQ you will need to reduce the connection, i.e. RECEIVE SSB messages and convert them to RabbitMQ notifications.