Can a SqlServer trigger push to ActiveMQ

Question

can anyone tell me if it is possible to push data from a SqlServer trigger directly to ActiveMQ.

I have hunted through the ActiveMQ docs and don't see any reference to it. We can use a service polling the DB for inserts but the trigger would be the ideal solution.

Answer 1

but the trigger would be the ideal solution

Absolutely not. There are many reasons why this is a bad idea, but I'll give you the main ones:

• added latency on each DML as you need to wait on an RPC call to complete.
• operational failures with random and erratic behavior when the ActiveMQ has availability issues (the RPC call fails, taking with it the DML and the original app transaction)
• correctness in presence of rollbacks. This alone is a complete show stopper. You cannot undo the ActiveMQ operation if the app transaction that run the trigger rolls back, for whatever reason.

Some of these can be solved by enrolling the app transaction in a distributed transaction (2PC) along with the queuing system, ie . XA for ActiveMQ. This solves the last problem but at the cost of adding serious latency and reducing the throughput by orders of magnitude.

The advocated solution is to have the trigger enqueue locally in a table used as a queue and have an external application dequeue from this table and push to ActiveMQ. Do not cut corners and use your application state as the 'queue' (ie. poll the state and mark as 'processed'), this is why 99% of 'queue in the database' attempts fail.

You still won't be able to solve some of the issues that are inherent when you split your data storage from the queue storage (inability to do consistent backup/restores, 'split-brains' on failover for HA/DR as the queueing does not follow the database failover etc) but those can only be solved if you buy into queueing and messaging embeded in the database, like Service Broker.