Our software system is split up between on-premises and on the cloud (Azure) services. Due to corporate security concerns, we are not allowed to access on-premises data from the cloud. As a result, we have an Azure SQL Server database on the cloud and another on-premises SQL Server database. In order to connect both worlds, we created an on-premises Windows Service that uses self-hosting and multi-host to host two different endpoints:
• Endpoint 1: SQL transport on Azure to receive requests (messages) from Azure Services.
• Endpoint 2: send-only SQL transport on-premises to publish events that trigger other on-premises services.
Our Windows Service handles Azure messages from the endpoint 1 using a regular IHandleMessage handler implementation, updates the on-premises SQL database according to the message received and, finally, publishes an event through the endpoint 2 using its IMessageSession to trigger other local services (on-premises) that are subscribed to that event.
This response is adapted from the response I gave @marc.cruzarjona in a support case, posted here for the benefit of others in the community.
First of all, it is possible to use Azure Service Bus for on-premises endpoints as well, but non-functional requirements (like the kind that come from corporate IT security) sometimes forbid stuff like that.
But given the configuration described, it might indeed be possible for you to run into a problem. The reason for this is that your transaction is on the incoming Azure Service Bus message. And it’s not much of a transaction, so I use that term loosely. But within that scope you’re doing database work and then sending a message on SQL transport, which you’re doing with what is essentially a send-only endpoint. There’s no real way to make the incoming message, outgoing message, and database work transactional. So you have all the problems described in our blog post about idempotence: What does idempotent mean in software systems? • Particular Software
In a normal message handler you could use the Outbox feature to deal with this, but that feature is for making things consistent within a single message transport. You’re sending your message on a different endpoint, it’s not connected at all, so that won’t work.
In fact, the Outbox works by writing the intent to send an outgoing message in your storage, which for you would be SQL. But your queues are also SQL. So what’s the point of writing a row to one SQL table to say that later you’ll be dispatching a message…by writing a row to a different database table. Doesn’t make a lot of sense.
What you’re probably looking for is our Bridge component, which makes it like your SQL Transport enpdoints can subscribe to Azure Service Bus events, with the Bridge being responsible with moving the correct messages from the ASB side to the SQL side. And in fact, it’s the ONLY component that needs to even know there are 2 “sides”.