What Is Service Broker? - SQL Server 2008

As intimated in the chapter introduction, Service Broker is message queuing for SQL Server. It provides a means by which you can send an asynchronous, transactional message from within a database to a queue, where it will be picked up and processed by another service. The sending and receiving services could be in separate instances of SQL Server, so the availability of the receiving service can’t be guaranteed. However, because Service Broker places the message in a queue, you know that the message will eventually be picked up, even if the remote service isn’t currently online.

Service Broker Architecture

The Service Broker architecture is essentially a straightforward client/server architecture. A Service Broker application consists of a client service that initiates a conversation and a receiving service that receives and processes the messages. Each service is associated with a particular queue, which it can send messages to, retrieve messages from, or both. The relationship between the services and the queue is defined by a contract, which specifies which type of messages the initiating and target services can send to the queue. The exchange of messages between the two services is called a dialog conversation (or simply a dialog). The fundamental architecture of a Service Broker application is shown in Figure.

The Service Broker architecture

The Service Broker architecture

Services

A service is an endpoint for a Service Broker conversation. A service can be either an initiating service, which starts the conversation by sending a message to the queue, or a target service, which retrieves messages from the queue. A service is associated with a single queue (although queues can be associated with multiple services) and with a contract, which stipulates which type of messages it can send to the queue. Thus, the service is used to enforce the contract for a conversation. SQL Server also uses services to identify the queues to deliver messages to and to route messages through, and to authorize connections to remote services.

Queues

A queue is a depository for messages. Queues can be associated with multiple services, which can send and/or retrieve messages in the queue. A queue can also be associated with a stored procedure, which will execute when a message arrives in the queue. This gives you the option of processing messages as soon as they arrive in the queue, but it is also possible to process the messages by scheduling a SQL Server Agent job to run recurrently or when the CPUs are idle.

If the receiving service and sending service are in the same SQL Server instance, they can share the same queue; otherwise, they will need to be associated with different queues. If the receiving queue is in another instance of SQL Server, or if it isn’t active for message receipt, the message will be placed in the transmission queue for the database that the sending service belongs to until the message can be delivered to the queue. Similarly, incoming messages will be sent to the transmission queue of the receiving database if the target queue isn’t active for message receipt. You can see the messages currently in the transmission queue through the sys.transmission_queue system view. It’s useful to check this queue when debugging Service Broker applications, as unsent messages remain here, and the transmission_status column may contain an error message explaining why the message wasn’t sent.

Messages

Each message is represented by a row in the queue. The format of messages is defined by the message type, which is specified in the contract between two services. The message type can require that messages be empty, well-formed XML or XML that is valid according to a specific schema, or that no validation is to occur—in which case, the messages in a conversation can contain any data, including binary data. There are also special messages that all services receive, regardless of the contract:

  • Error messages: These are defined as messages of type SQL/ServiceBroker/Error, and they are validated as well-formed XML. They consist of a root element called <Error>, with child elements representing the code and description of the error. Once an error has been generated for a conversation, no further messages can be sent on that conversation.
  • End Dialog messages: These are empty messages of type SQL/ServiceBroker/EndDialog and indicate that a service is ending the conversation without error.
  • Dialog Timer messages: These are empty messages of type ServiceBroker/DialogTimer, which are placed in a queue to indicate that a conversation timer has expired.

Dialog Conversations

A dialog conversation represents the exchange of messages between two services. Messages in a dialog are delivered to the queue in the order in which they are sent. When an application processes amessage from a queue, it may send another message to the queue, and it can indicate that this forms part of the same conversation. The conversation continues until one of the participating applications explicitly ends the conversation or sends an error message. However, each participant in the conversation must issue an END CONVERSATION command, or the dialog will remain in the database (you can view the active conversations through the sys.conversation_endpoints system view).

The initiating application can indicate a maximum lifetime for the application, after which each side will place a timeout error in the queue and refuse further messages. Each participant in a conversation can also specify one conversation timer per conversation. When a conversation timer expires, SQL Server places a Dialog Timer message in the queue, as a prompt for the application to perform a specific action (such as executing a stored procedure).

Conversation Groups

Each dialog belongs to a conversation group. If an application accesses information from two or more services, it may need to relate these conversations to each other; it can do this by including the second conversation in the same conversation group as the first (this doesn’t affect the applications at the other end of the conversations, as conversation groups aren’t shared and the other applications may group the conversations as they wish). Service Broker includes the ID for the conversation group in all incoming messages in that conversation, so that the application can determine which messages from the remote services belong together.

For example, suppose you have a service that is used to process employee vacation requests and that retrieves information about the employee making the request from one service and information about the timelines for current projects from another service. It uses the data from these two services to decide whether or not to grant the request for that employee at that time. This application can use the conversation group ID to ensure that it processes the correct employee data together with the related project data.

Conversation groups are locked when a message is being sent or retrieved on that group. This helps to ensure exactly once in order (EOIO) delivery.

Contracts

The message types that each participant in a conversation is permitted to send are specified in the contract for that conversation. The two services in the conversation will be bound by the same contract. If the two services are in different databases, then identical contracts must be created in each database. The contract stipulates which types of messages the initiating service and the receiving service can send, and which types can be sent by either service. The contract must include at least one message type that can be sent by the initiating service (or by either service, which obviously includes the initiating service); otherwise, there is no way for the conversation to be initiated.

The Service Broker Endpoint

If the two services in a conversation are in different instances of SQL Server, you need to create a Service Broker endpoint, which will accept incoming and outgoing TCP/IP connections on a specific port. A SQL Server instance can contain only one Service Broker endpoint, which is shared between all services in the instance.

Remote Service Bindings

Remote Service Bindings are used to establish the security context under which an initiating service connects to a remote service in a different instance of SQL Server. The Remote Service Binding uses a certificate associated with the specified database user account to connect to the remote instance.

Routes

Service Broker uses routes to locate a service to which it is sending a message. If no route is explicitly associated with a service, by default, Service Broker will deliver the message within the current instance. A route contains the name of the service it is used to connect to, the ID of the Service Broker instance that hosts the service, and the network address of the remote Service Broker endpoint.

Service Broker Scenarios

Now that you’ve seen the various parts that make up a Service Broker application and how Service Broker works at a high level, a basic question remains: what do you actually use Service Broker for? Service Broker brings two things to the table that ordinary SQL Server modules don’t possess: guaranteed delivery of messages and asynchronicity. Without Service Broker, if you attempt to connect to a remote instance of SQL Server (for example, using a linked server), and that server isn’t available, you must roll back the transaction and let the operation fail. With Service Broker, you can simply send a message to the queue and go on about your business, safe in the knowledge that when the server comes back online, it will be able to pick up the message and process it. Also, if you want to execute some complex processing within a module without Service Broker, you need to wait until that processing has completed before the module will continue executing. Service Broker allows the module to send a message and continue executing. The message can be processed at a later time, perhaps when the CPU is idle or during out-of-office hours.

Naturally, Service Broker also comes with an overhead, so it isn’t an appropriate solution if you need an immediate response or if an application is time-critical. It wouldn’t make any sense to use Service Broker to perform a simple SELECT query on a remote database. It’s also worth bearing in mind that, if you use Service Broker for database updates, it could result in data conflicts, as the update may not be performed immediately.

The following are some possible scenarios for Service Broker applications:

  • Asynchronous triggers: If a trigger requires resource-intensive processing that isn’t time-critical, it makes sense to use Service Broker. The trigger can simply place a message on the queue and return without needing to wait for the processing to complete.
  • Bulk processing: If a module requires extensive processing, such as bulk-data processing, Service Broker can avoid the potential performance degradation of running this immediately at busy times of the day. Instead, it can send a message to a queue, and a stored procedure can run as a SQL Server Agent job that will process any messages in the queue at a more convenient time.
  • Distributed order processing: The other advantage of Service Broker—the guaranteed delivery of messages—makes it a good option for applications that involve order processing, where the order doesn’t need to be fulfilled immediately, but it’s vital that the order be delivered. This is particularly the case if orders are taken and fulfilled by different systems (for example, orders are taken at a local branch and dispatched from a central warehouse), where the availability of the remote system isn’t guaranteed at the time the order is taken.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics