0

I have a requirement broadly similar to this question:

I need to integrate two third-party applications (i.e. I don't have access to the code of either application).

  • App2 needs to know about a subset of updates on App1 (user did "x" on App1 - not interested; user did "y" - invoke a chain of actions on App2). At least to begin with, the interaction is likely just to be notifications from App1 to App2.

  • App1 is tied to SQL Server (can't assume Enterprise); App2 might be using any d/b platform but can be configured to reach out to a SQL Server table or to a log or other non-db file.

  • When both ends are online the solution needs to be real-time or close to; if the receiving end (App2) is down it will need to catch up when it becomes available.

  • Our customers range from a few hundred users to tens of thousands, so the solution needs to scale (in practice likely to mean multiple instances of App1 notifying a single instance of App2).

  • The solution needs to be limited to configuration, not code.

After a bit of digging I came up with the following:

D/b trigger on appropriate table -> Service Broker -> activate stored proc in intermediate database -> table in intermediate database (from which App2 can pull the data it needs)

However, there are dark hints in the related question (and the one referred to there) about the disadvantages of Service Broker for this kind of thing but no explanation of what those might be. Can anyone point out potential problems with this kind of solution (including any really obvious ones, because this is not a technology I'm familiar with and I don't have a DBA background - it took me two weeks to realise that I couldn't have a SQL Express d/b at both ends).

Thanks in advance...

Edit 18th April 2013 to add:

I have zero errors from SSBDiagnose and the SB call executes successfully from a test proc, but nothing arrives at the other end. If I try to query sys.transmission_queue the query hangs. Anyone know what the problem is likely to be?

Community
  • 1
  • 1
mike1880
  • 1
  • 1
  • Based on your mentioned first item " I don't have access to the code of either application" and last item: "The solution needs to be limited to configuration, not code." I'm not sure if a community specialized in coding as SO can be the best place to look for advice – emecas Apr 10 '13 at 14:44
  • Perhaps if I was asking for a solution, but given that I've already identified SB as appearing to do what I want the question is: what problems am I going to run into if I go down this road? – mike1880 Apr 10 '13 at 15:46
  • Sorry, I see I've edited out my proposed solution along the way, which is: d/b trigger -> Service Broker -> activate stored proc in intermediate database -> table in intermediate databse, from which App2 can pull the data it needs. Can anyone indicate what problems I might have with this? – mike1880 Apr 10 '13 at 15:53

0 Answers0