How can I send an event from SQL Server to Mule?

0
votes

What I want to do:

  • Update a Salesforce object when a row is changed in the database.
  • I know how to update Salesforce with a Mule flow once Mule receives notification that an object needs updated.
  • The main problem I need to solve is, how can I notify Mule that the database has changed.

What I can't do:

  • Change any of the dependent software or versions.
  • Change the application that updates the database.

What I want to avoid:

  • Polling the database for changes. The Mule Connector for SQL Server uses JDBC and polls for changes.

What I have to work with (cannot be changed):

  • MS SQL Server 2012
  • Mule ESB 3.7
  • Salesforce

What I have done so far:

  1. Created a flow in Mule using the SQL Server Mule Connector to poll the database for changes.
  2. The next attempt to make the process event driven was to create a CLR function that would put a message on a RabbitMQ to be consumed by a Mule RabbitMQ Connector. That failed because the RabbitMQ assembly couldn’t be included in SQL Server runtime.
  3. The third attempt is to use a Service Broker to make a REST callout to a Mule HTTP endpoint component when a row is modified. Mule would then transform and modify the Salesforce object using the Mule Salesforce connector.

What I’m expecting:

  • Given the inherent incompatibilities between Java and .Net/SQL Server, I’m thinking that using a Service Broker to make a REST call to Mule is the best solution I am going to get.

Is there a better solution?

2
sql-servermule
How does the event work on MS SQL server . Does it send any updates through the Web service ? – Naveen Raj
@NaveenRaj Thanks. Sending an event out of SQL Server is the part that I am trying to figure out. How can I send an event out of SQL Server? Currently, I am trying to making a REST call out of SQL Server using a Service Broker but it feels hack-y. – coryd

2 Answers

0
votes

There a few ways to do this

http://www.codeproject.com/Articles/428200/How-to-invoke-a-Web-Service-from-a-Stored-Procedur

Best Way to call a Web Service from TSQL

but in my opinion its not good idea to do.

Mule is a middle ware layer to control interaction to different systems and your Database cant to do that.In future if you want to share the same data with another system then it will be problem for you.

If your case is specific to only interaction between Salesforce and Database again you need to end up with maintaining Database stored procedures or triggers to do the work.

So let the Mule take control what needs to do with your data.

0
votes

Have you looked at Database Change Notification concepts in Oracle. It is designed for notifying middle tier applications. I am not a database expert hence couldn't advise much of internals but I know oracle has that. if you have a DBA team (or an expert) in your team talk to him.