SQL Server 2005 Service Broker - A Scenario

Scenario

ABC, Inc. wants to ensure that addressing and pricing information is synchronized between the central office and the branch offices. At present ABC Inc. requires a Service Broker solution, which will implement a process to address or handle the following events:

• Customer address information changes at the branch office. A stored procedure in the branch office database should write the changes to that database and then send a message (with the updated address information) to the central office database. A stored procedure in the central office database should receive the message, update that database, and then send a message (with the updated address information) to the other branch office databases.

• Item pricing information changes at the central office. A stored procedure in the central office database should write changes to that database and then send a message (with the updated item pricing information) to the branch office databases.

This solution can be designed by using the following steps:

Step-1: Designing a Service Broker Solution Architecture

Firstly, you need to design the architecture for a Service Broker solution for ABC Inc., and while designing the architecture you need to identify the Service Broker instances and conversations or dialogs to support your Service Broker solution.

Moving further, you must have identified the Service Broker instances, a service instance identifies helps to group of related conversations with a unique identifier and helps
to enable changes to customer information to be propagated between branch offices and the ABC, Inc. central office, and to enable changes to item prices to be propagated from the central office to the branch offices.

Then you would require to identify the conversations or dialogs, which provides two-way messaging to propagate changes between the databases at the central office and the branch office. The main purpose of having conversation or dialog is to provide an ordered sequence of events.

Step-2: Designing a Service Broker Solution

While designing Service Broker solution that meets the business requirements of ABC Inc.. You need to identify the services, queues, stored procedures, and data flow necessary to support your solution.

Identify the Service Broker services and queues needed to send data between the branch offices. Service Broker creates an identifier when it creates the service instance and then adds this identifier to subsequent messages that the client application identifies as a related conversation.

Service Broker sends messages to services; services store messages in queues. The service consumer can hold the message in a local queue if the service provider server is currently unavailable. Another solution to address ABC Inc’s problem is to have one dedicated queue for each branch office database, but this approach will only complicate the application. You would have to create a stored procedure that can distinguish between the types of messages and take the necessary action as appropriate. This approach will require extra processing and more complicated coding, both of which are unnecessary as the solution to the ABC Inc’s problem can be resolved by using one local queue.

Once data will be sent to the queue in the form of a message, Stored procedures will be required to update the customer and item information in each database, and keep the databases synchronized.

Step-3: Executing Service Broker Solution

Once solution has been created, you can execute the Script, once application starts running you can test it to check that it is working fine.