Best approach to consume, messaging, and save audit data from Application A in Application B real time

Question

My initial approach to this was to use a stored procedure in database of Application A, triggered on an insert to gather additional data and call a web-service, hosted by Application B to do the necessary mapping and persistence there. Application A and Application B may not be on the same machine. The initial requirement is to support a SQL Server database on Application A's side. A CLR stored procedure came to mind. However, it was felt that calling out to a web-service would have both severe performance implications in the SQL Server engine and also require elevation of permissions for the procedure that DBA's do not like to give.

I am now thinking in terms of the procedure creating some form of reference table on database A and a polling application consuming this data and cleaning up once processed at Application B. However, I cannot but think that there is a better way of doing this beside polling for the data.

Application A is Windows only. Application B could be Windows, UNIX, or LINUX, so Java would be the obvious choice on this side.

Answer 1

You've given us a list of approaches you've considered but you haven't really outlined exactly what it is you are trying to accomplish, what your goals are, etc., other than the one-sentence title of this question. Can you clarify exactly what your requirements are?

The standard answer for asynchronous messaging between applications is to use JMS. Application A places messages onto a queue whenever events that should be audited occur within it, and Application B is written to consume messages from the queue at a certain rate (if you want "realtime", then you can poll the queue very often). Application B can then do whatever it needs to with these messages - write them to a database, sent them to another web service etc.

This way, the actions in Application A - what you want to audit - and the behavior of Application B - how you want to audit the messages - are completely de-coupled from one another. This allows you to change things on either side - audit new types of events, change the payload of the message, output the messages to somewhere else, etc. - without changing the other side.

It also allows you to scale both applications independently of the other - you can add more instances of A without affecting B, A can produce messages at a much higher rate than B consumes them, and A does not wait for B to finish consuming a message before it is able to respond to the user's actions.