0

Please tell me what are the best practice for linking sql 2012 database server with activemq

What i want to achieve is whenever something changes in database my ativemq should get updated with a message that has the change that has happened in the db.

1.So how should i code in my db (like trigger?). 2.How should the data be sent to activemq? Do we need any interfaces like a java app?

shiva
  • 77
  • 7
  • possible duplicate of [Can my sql server send messages to activemq server without any java app in between](http://stackoverflow.com/questions/18592846/can-my-sql-server-send-messages-to-activemq-server-without-any-java-app-in-betwe) – Remus Rusanu Sep 05 '13 at 09:22

1 Answers1

0

Don't do it in the database. Do it in your app.

While SQL Server can host CLR procedures that can connect to your ActiveMQ server and submit messages, doing so is a bad idea:

  • writing SQLCLR code that connects to external resources is tricky (you need to understand SQL scheduling and impact of preemption, read CLR Hosted Environment)
  • You add coupling (your trigger now will fail if the ActiveMQ server is not available)
  • In order to achieve transactional correctness you must enroll ActiveMQ operation into your current transaction, so that the current transaction rolls back after the trigger executed, the ActiveMQ operation is also rolled back. DTC using XA and SQL Server are not for the faint of heart.

A much better solution is to queue locally, into your own DB. Either use a table as a queue or use Service Broker queues (which, btw, do support Activation). Then process this local queue instead, post-commit, from a separate listener process. More availability, less coupling, higher throughput. If ActiveMQ is really needed, at least a local intermediate queue will decouple the availability. Having each DB operation wait for an external ActiveMQ XA enrolled operation will plumed your throughput (DTC is slow) and you have to deal with the availability problem (no ActiveMQ and XA available, no updates in DB possible).

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • I have tried with Service Broker queues, but it's not working for me [Check this link](http://stackoverflow.com/questions/18593248/message-from-sql-server-to-external-application-activemq) – niren Sep 05 '13 at 09:53
  • @remus so with the service broker queues i can make my sql directly communicate with activemq? i am very new to activemq – shiva Sep 05 '13 at 09:53
  • @remus excellent solution to use the service broker to ensure that your transaction completes locally but what would you use to send the message from the service broker to the ActiveMQ? – Peter Larsen Nov 27 '14 at 13:02