In the real-world implementation of SQL Server of an enterprise system, it always needs to implement data in multiple databases. In most of the cases, a single transaction will span across multiple databases. If there are multiple databases, there will be performance issues and implementation issues during the cross-database transactions. However, with the use of SQL Service Broker that was introduced in SQL Server 2005, you can implement asynchronous transactions between databases.
The Service broker is similar to other message queuing technologies such as MSMQ. In the service broker, data can be processed automatically or on-demand. Data in the SQL service broker is consumed by SEND and RECEIVE, which is typically an XML format.
Though there are a few other implementations of Service Broker, we will be looking at the usage of Service Broker for the Distributed Transaction System. Let us assume that there is an Order System. When the order is received, inventory has to be processed. To maintain scalability and security concerns, these are handled by two processes. We will start by enabling the service broker from the following code.
