Understanding Service Broker to perform asynchronous operations – (Part-1)

Author: Basit A. Farooq

Editor’s note: In this multiple part article series, you learn how to configure Service Broker components. You will create a message type to define the format of a message, a contract to define communication between services, a queue to store and deliver messages, and a service to send and receive messages. You initiated a dialog conversation and used the SEND and RECEIVE statements.

SQL Server includes the Service Broker component, which you can use to build and deploy applications that perform asynchronous operations. An asynchronous operation is one that executes in tandem with other operations. A simple example of how you might use Service Broker follows.

Service Broker example

Outlander Spices wants to build an e-commerce application. When a customer places an order, the application must:

• Check inventory.
• If the product is not in-stock, check to see if it is available from four distributors.
• Debit the customer’s credit card.
• Send a message to the customer.
• Send a notification to the shipping department.

In a synchronous application, the flow of operations would be similar to that shown flow-chart below:

In a well-connected environment, this flow might work well. However, consider the following:

• The Inventory database is in a different office and isn’t always connected to the network.
• Dist1 needs to manually check inventory before responding.
• Dist2 is usually connected, but a power outage has disrupted service.
• Dist3 is overloaded and times out while servicing the request.
• The credit card processing phone number is busy.

If any one of these incidents occurs, the transaction will either be held open or be rolled back. Service Broker can help application developers build solutions that can recover from these scenarios and free resources more quickly. In the following flowchart the same application is implemented asynchronously using Service Broker.

When Service Broker implements the application asynchronously, it can send a message to all five services at the same time.

• 1 Service Broker queues the message for each service.
• 2 When the service responds, Service Broker puts the responses in a queue, which the originating application checks.
• 3 After the application receives messages that the product is available and the credit card is good, the application commits the transaction and sends a message to the customer and to shipping.
• 4 If the application receives a decline on the credit card or if the product is no longer available, the application rolls back the changes and sends a message to the customer.

Service Broker queues return control to the application immediately after the message is placed in the queue. Because the messages are queued, the initial transaction is considered a success as soon as the messages are sent to the queue. Transaction locks aren’t held open through the entire transaction. A message remains in the queue until it can be serviced. If a network problem occurs, the message will be waiting when the service comes back online.

Service Broker architecture

In the Service Broker architecture, an application is a component that takes a message from the queue. A Service Broker application can be:

• A stored procedure
• A CLR user-defined function
• An external application that accesses SQL Server using ADO.NET or another programming interface. ADO.NET is a set of .NET Framework programming objects that you can use to connect an application to, and query, an SQL Server.

A service is an endpoint SQL Server uses as the destination for a message. A queue is a storage area for messages and is structured like a table. You usually have a separate queue for each service, although multiple services can share a queue in the same database.

A Service Broker solution is based around conversations. A conversation is an exchange of messages between applications.

• An application begins a conversation by sending a message to a service. As shown above, the OrderFullfillment application sends a message to the InventoryService. The OrderFullfillment service is known as the initiator.
• The InventoryService then places the message in a queue.
• InventoryApp retrieves the message from the queue and processes it.
• InventoryApp sends a response to the OrderFulfillment service.
• The OrderFulfillment service places the message in a different queue.
• OrderFulfillmentApp retrieves the message from the queue and processes it.

To make this work, Service Broker uses contracts and message types. A message type defines the names and formats of the messages that are sent and received as part of the conversation. For example, the conversation might include the CheckInventory message type and the InventoryResponse message type. You can define a message type as:

• Well-formed XML — Any well-formed XML is accepted.
• Well-formed XML that matches a specific schema — Messages are validated against a schema collection.
• Empty — Message has Null content.
• None — Service Broker doesn’t validate the message.

A contract defines the message types allowed in the conversation and the direction they can travel. In the Outlander Spices example, the contract allows the CheckInventory message type to travel from the initiator (OrderFullfillmentApp) to the target (InventoryService). The contract allows the InventoryResponse message type to travel from the target to the initiator. You can also define contracts that allow a message to travel in either direction. For example, you might create a message type that simply checks whether the other service is running.

Queue activation

When using Service Broker, you must decide how the queue reader is activated. Queue reader activation options include:

• Automatically when a message enters the queue (internal activation)
• In response to an event
• As a scheduled job
• When SQL Server starts up

You choose a startup option based on the business requirements and the queue reader’s implementation. For example, if an application should read the queue once a day, you should create a scheduled job. If the queue reader is a stored procedure that should process the messages as soon as possible, your best choice is internal activation.

Creating a Service Broker application

You create Server Broker objects using Transact-SQL Statements. The objects are database-specific and stored with the database in which they are created. You can view the objects in SQL Server Management Studio’s Object Explorer by expanding the database, then expanding Service Broker and the folder for the specific object type.

Enabling Service Broker in a database

When you create a database, Service Broker is disabled by default. You enable Service Broker using the ALTER DATABASE statement as follows:

ALTER DATABASE Outlander

SET ENABLE_BROKER

Executing this statement sets the is_broker_enabled flag in sys.databases to 1.

You can disable Service Broker by running:

ALTER DATABASE Outlander

SET DISABLE_BROKER

If you have moved the database to a different domain since it was created, you might need to change the database owner. To do so, you execute ALTER AUTHORIZATION on the database.

Defining message types

You create a message type using the CREATE MESSAGE TYPE statement. Its syntax is:

CREATE MESSAGE TYPE message_type_name

[AUTHORIZATION owner_name]

[VALIDATION = NONE | EMPTY | WELL_FORMED_XML |

VALID_XML WITH SCHEMA COLLECTION schema_collection_name]

If you don’t specify a validation type, Service Broker uses NONE as the default.

To create a message type named CheckInventory that accepts only well-formed XML, you execute:

CREATE MESSAGE TYPE CheckInventory

VALIDATION=WELL_FORMED_XML

To create a message type that must be validated against the InventoryRequest schema collection, you execute:

CREATE MESSAGE TYPE CheckInventoryStrict

VALIDATION=VALID_XML WITH SCHEMA COLLECTION InventoryRequest

Documentation on the CREATE MESSAGE TYPE statement and other Service Broker statements are located in the command reference at the end of this course. You can view information about the command in SQL Server Books Online.

Defining contracts

After you have created the message types used in the conversation, you need to create a contract. You create a contract using the CREATE CONTRACT statement. Its syntax is:

CREATE CONTRACT contract_name

[AUTHORIZATION owner_name]

( {message_type_name SENT BY {INITIATOR | TARGET | ANY}

| [DEFAULT]} [, …n])

You can specify multiple message types. Message type names are case-sensitive. The DEFAULT message type is a built-in message type that doesn’t perform any validation. When you choose DEFAULT, the direction is ANY. To create a contract that allows a CheckInventory message type to be sent by the initiator and an InventoryResult message type to be sent by the target, you execute:

CREATE CONTRACT InventoryContract(

CheckInventory SENT BY INITIATOR,

InventoryResult SENT BY TARGET)

Continue to Part-2…