Working with Service Broker on Microsoft SQL Server

Hello guys,
All right ?

Today I got to know Microsoft SQL Server Service Broker and learned a lot by reading the blog Junior Galvao, where I extracted this post from. I don't think it would be worth writing much about this topic, if it has already (in a very didactic way) approached it extensively. So let's get to know this important feature of MS SQL Server, which was made available from the 2005 release.

Introduction

Launched in conjunction with Microsoft SQL Server 2005, Service Broker was a major highlight of this release of SQL Server, and was treated as one of the major product innovations in 2005.

Since then, much has been said about this feature that has been maintained and evolved in later versions and editions of Microsoft SQL Server. Existing as a Database Engine feature and functionality, Service Broker provides developers and database professionals to build reliable queuing applications and data exchange messages in SQL Server. Basically through this service, it is possible for an application connected to a single instance of SQL Server to be able to distribute work and messages across multiple other database instances.

Through this information exchange capability, Service Broker has established an asynchronous form of communication between database applications that make use of this functionality, so the response time for information exchange is much shorter, more interactive and simplified, which makes using this feature more reliable.

In addition, Service Broker provides reliable messaging between distributed database instances within an enterprise, helping developers compose applications that are structured or recognized as standalone services using the same communication instance.

By default, Service Broker uses the TCP / IP protocol and its existing set of protocols for message exchange, containing features that can prevent unauthorized access to a network at any time, setting standards for encrypting messages sent by applications between the instances of SQL Server.

How Service Broker Works

Because its goal is to create a simplified, secure, and decoupled form of message exchange, Service Broker has a set of tasks required to perform this messaging process between Database Server applications and instances. Service Broker architecture consists of the following components, tasks, and characteristics:

• Conversations;
• Message ordering and coordination;
• Transactional asynchronous programming;
• Support for flexibly coupled applications; and
• Service Broker Components.

The following describes this architecture, briefly highlighting each task.

Conversation

Designed as a basic feature for sending and receiving messages, Service Broker presents a task called “Conversation” performed during the message exchange process. Each conversation task is recognized and treated as a reliable and persistent communication channel, the messages feature a specific type of conversation that Service Broker handles individually that makes it possible to reinforce and ensure the safe recording of data exchanged by applications.

During this message exchange, the "Conversation" task allows the applications involved in this process to establish this communication channel within a "queue" that represents a display of an internal table related to the database that Service Broker is making. use.

For each conversation handled by the Service Broker, a sequence and sorting of messages is established through the task “Sorting and coordinating messages”, this ensures that each message is part of a single and exclusive conversation, ie the same message exchanged will be handled by same task and conversation.

A simple way to represent and illustrate how Service Broker works is to think about how a postal service works. To keep a conversation with a distant colleague, you can communicate by sending letters through the postal service.

The postal service classifies and delivers the letters. You and your colleague then retrieve letters from mailboxes, read, write replies, and send new letters until the conversation is over. The delivery of the letter happens asynchronously while you and your colleague deal with other tasks.

A Figure 1 introduces the process of message exchange based on a Postal Service.

Figure 1: Representation of the message exchange process performed by Service Broker.

By analyzing the operation of the Service Broker, we can understand that the programs involved in this scenario, should understand that this service will be similar to the Postal Service or Post Office, giving full support to asynchronous messages exchanged between applications.

Service Broker messages function like letters. The Service Broker service is treated as the address where the post office delivers the letters. Queues are the mailboxes that hold the letters after they are delivered. Applications receive messages, act on messages, and send replies.

Observation: As long as there is a message exchange process through Service Broker, the queue process is being maintained and fed until the receiving data application is able to handle these messages, while this does not occur to the message queue it will have its size increased and your expanded queuing process.

Message Ordering and Coordination

All process of control and maintenance of message queues is performed by Service Broker directly in the Database Engine, adopting a traditional treatment through two aspects:

• Service Broker managed queues integrate directly with the database to which the service is involved.
• Queues are responsible for coordinating and sorting the messages that are in transit.

Through the strong control of sending and receiving messages, the task of “Ordering and Coordinating Messages” establishes to Service Broker guarantees in the message exchange flow, establishing two sides in the communication process, namely:

• Beginning side called initiator; and
• Destination side of message called receiver.

A basic Service Broker message exchange process consists of:

Below is an illustration of message exchange in a typical dialog:

• In the initiator:
  • A program begins the conversation.
  • The program creates a message containing the data required to perform a task.
  • The program sends the message to the destination service.
• At the receiver:
  • The message is placed in the queue associated with the destination service.
  • A program receives the message from the queue and performs the work.
  • The program responds by sending a message to the initiator service.
• In the initiator:
  • The reply message is placed in the queue associated with the initiator service.
  • A program receives the response and processes it.

Until the end of sending and receiving messages this cycle is repeated cyclically and continues.

This cycle repeats until the initiator ends the conversation because there are no more requests to send to the destination.

Transactional Asynchronous Programming

The “Transactional Asynchronous Programming” component is totally related to the Service Broker operating infrastructure, serving as a message transfer area, handling each process in a transactional manner, allowing it to be reversed at any time.

In this component, Service Broker controls the data being exchanged, establishing a scalability process, which ensures that the service can be automated and grow according to the situation. One of them is the ability to automatically start the data. processes that process a queue, so it is possible for the application using the send / receive process to identify when the message is running or in the processing queue.

Asynchronous programming allows developers to design software solutions that can write data to message queues by making use of the database itself through one or more tables as internal message queue repositories.

Support for flexibly coupled applications

Considered as a feature rather than a component or task, “Support for tightly coupled applications” ensures and enables Service Broker to work with a very distinct set of standalone applications that can be identified as potential message exchange components. These applications must internally contain the same structure and mechanism for messaging that exists in Service Broker, which in some situations allows this component to be coupled with the messaging service.

Service Agent Components

Service Broker consists of three basic components that exist according to the task you are performing:

• Conversation Components: Known as dialog, it is any conversation held through Service Broker during the message exchange process, allowing conversion groups, conversations, and messages to be handled by your applications by establishing participants.
• Service Definition Components: Responsible for establishing the flow of conversation, storing data in a database, this component defines the basic structure of conversation performed between Service Broker and the application.
• Network Components and Security: Through this component, Service Broker enables database administrators to manage their environments without impacting application source code, focusing on scenario changes or scenarios, and establishing a secure and reliable messaging channel for users. applications that are consuming Service Broker.

Observation: Service definition components, network components, and security components are part of the SQL Server instance and database metadata. Chat groups, conversations, and messages are part of the data that the database contains.

Well, after this long walk, I will put some pepper in this sauce, starting the creation of our study environment, through the code 1 presented below:

Note that after we create the database, we use the command Alter Databasethrough the directive Set Enable_Broker to enable and indicate to SQL Server that this database should allow the use of the messaging service.

Remarks:

• To run ENABLE_BROKER SQL Server requests a unique database lock. If other sessions have locked resources in the database, ENABLE_BROKER will wait until the other sessions release the locks.
• To enable Service Broker on a user database, ensure that no other sessions are using the database before executing the ALTER DATABASE SET ENABLE_BROKER statement, for example, by placing the database in single user mode.

Message Types

To be able to communicate, applications that use Service Broker use the concept of messages through the send and receive function, something very similar to e-mail. Those involved in the message exchange process are responsible for agreeing to the content exchange, as well as in recognizing the name of each message.

The message type object defines a name for the message type and the data type that the message should contain. Message types persist in the databases in which they are created. You can create an identical message type in each database that participates in a conversation.

Each message type is responsible for specifying the validation that SQL Server should perform for the messages according to the data type. SQL Server can verify that the message contains valid XML, contains XML that conforms to a given schema, or simply contains no data.

Validation is performed when the target service receives the message, if the message content does not match the specified validation, Service Broker returns an error message to the service that sent the message.

Well, our scenario will consist basically of two types of messages:

1. mtSendMessage; and
2. mtReceivingMessage.

To create our Message Types, let's use the 1 Code Block, shown below:

Note that I am specifying in the argument Validation, the option =Well_FORMED_XML, this will indicate to SQL Server that ours should contain in its body a well formatted XML file as a message validation mechanism.

A Tabela 1 below illustrates the types of messages that exist in Service Broker:

1 Table - List of arguments in the Create Message Type command.

We can observe the Figure 1 which illustrates the structure of our database after creating the two types of messages:

If you would like to see the list of messages created at this time, use the System Table: Sys.Service_Message_Types

Our next step is to create the Contracts, so let's get to know a little about the Contracts and then perform the creation of this feature.

Contracts

A contract defines what type of message an application uses to accomplish a particular task. A contract is an agreement between two services about which messages each service aims to accomplish a particular task.

The contract specifies which message types can be used to accomplish the desired work. The contract also specifies which participant in the conversation can use each type of message.

Service Broker also includes an internal agreement called DEFAULT. This only contains the message type SENT BY ANY. If no contract is specified in the statement, Service Broker uses the DEFAULT contract.

To accomplish the creation of a new contract, we will use the 2 Code Blockpresented below, note that we will use the Create Contract command, specifying the Message Type that will be Target and which will be Initiator.

Note that we define as initiator(initializer) o mtShippingMessage It is likeTarget(target) the mtReceivingMessageThis way, from the moment we start the dialogue process, Service Broker will be able to understand which Message Type will send and which will receive the message.

A Figure 2 The following illustrates the structure of our MyDatabaseServiceBroker Database after the creation of the contract. cProcessesMessages:

Figure 2 - Database Structure MyDatabaseServiceBroker, showing the previously created contract.

If you would like to consult the list of contracts created at this time, use the System Table: Sys.Service_Contracts

At this moment, our database has in its structure the following components:

• Message Types: mtSendMessage and mtReceiveMessage; and
• Contract: cProcessMessages.

We will evolve a little more in the structure of our environment, now creating another very important feature to be able to make use of Service Broker, and of great value so that messages can be exchanged, this referring me to QUEUE (Queues)In keeping with the rule, I will introduce the concepts about Queue and then the code we should use to create our queues.

QUEUE (Queues)

Queues, or queues, have the function of providing a communication channel between those involved in the message exchange process, creating the so-called Message Queuing.

When Service Broker receives a message from a service, it inserts it into that service's queue, so to receive messages sent to the service, an application receives messages from the queue, enabling Service Broker to manage and control the message flow that is being processed in each queue.

During message processing, queues are filled by creating a sequence of lines representing the messages being used.

The line has the message content and information about the message type, the service intended by the message, the contract that the message follows, the validation performed on the message, the conversation of which the message is part, and the internal information for the queue.

Queues do not return messages in a strict first-in, first-out order; instead, queues return messages for each conversation in the order in which the messages were sent. Thus, an application does not need to include code to retrieve the original order of messages.

Moving forward, we will create the queues that we will use in our environment, for this we will create two daughters, called: qOrigem and qDestination, used as communication channels and queued in the processing of messages, for this, we will use the 3 Code Block, shown below:

If we look at the process of creating a queue is very simple and without any secrets, quite the opposite, one of the most peculiar features of Service Broker is related to its configuration process, performed very clearly, smoothly and very simplified.

We now have one more component created in our database, as shown in Figure 3, which is how Management Studio presents queues created for our Service Broker:

Figure 3: Database Structure, showing the previously created qOrigin and qDestination queues.

If you would like to see the list of queues created at this time, use the System Table:Sys.Service_Queues

Our last component that will be created to compose the structure of our database. MyDatabaseServiceBroker, known as the Service, and as its own description shows, the Service will be the element involved in the service broker's message exchange process.

Services

Element that makes up the architecture of a Service Broker, services are defined and treated as tasks that have a specific purpose, enabling the occurrence of conversations (dialogues) in Service Broker. Through services, Service Broker was able to correctly deliver messages to queues within a database, route messages, enforce the contract for a conversation, and determine the remote security of a new conversation.

Each service specifies a queue to contain incoming messages, making use of the service-associated contracts that define specific tasks for which the service accepts new conversations.

Continuing along this long road, we will create our services, just as we do for Queues, we will be creating two services called sOrigem and sDestination, as presented in 4 Code Block Next:

Importantly, that the service sDestination is linked with the queue Destination and for this same service we specify that the contract will be usedcProcessMessagesTherefore, when using this service, SQL Server must call the contract cProcessMessages as an element of validation and guarantee of the type of message being exchanged.

A Figure 4, illustrates the structure of the MyDatabaseServiceBroker database after the creation of our services:

Message Submission Process

As presented in Part I, the process of communication (conversation) between the service broker and the applications that make use of it is very similar to the operation of the post office, where there is the data exchange (messages) involving the sender and its recipient.

In Service Broker this communication process occurs through the use of commandsBegin Dialog Conversation e Send, where the process starts using Begin Dialog and Send will occur via Send.

Begin Dialog Conversation command

The Begin Dialog Conversation command basically guides SQL Server to initiate a conversation block between one or more services, which typically occurs by exchanging messages with at least two services.

The information specified in the BEGIN DIALOG CONVERSATION statement is similar to the address in a letter; Service Broker uses the information to deliver the messages to the correct service. This command presents a unique set of arguments to best determine the conversation process. The following is the list of arguments:

• @dialog_handle: Variable used to store the generated dialog identifier returned by the BEGIN DIALOG CONVERSATION statement. This variable must be of type Uniqueidentifier.
• From Service initiator_service_name: Determines the service that starts the dialog. The specified name must be the name of a service in the current database. The queue specified for the initiator service receives messages returned by the target service and messages created by Service Broker for this conversation.
• To Service 'target_service_name': Specifies the destination service with which to initiate the dialog. Target_service_name is of type nvarchar (256). Service Broker uses a byte-by-byte comparison of the character set in the message. This comparison is case sensitive and does not take the current grouping into account.
• Service_broker_guid: Informs the database that hosts the target service. The service_broker_guid is of type nvarchar (128). Through the System Table Sys.Databases you can get service_broker_guid.
• 'CURRENT DATABASE': Displays that the conversation uses service_broker_guid in the current database.
• ON CONTRACT contract name: Specifies the contract that this conversation should use and involve in the exchange process. When this argument is omitted, the conversation will use the contract called DEFAULT.
• RELATED_CONVERSATION = related_conversation_handle: Specifies the existing conversation group to which a dialog is added. When this clause is present, the dialog belongs to the same conversation group involved in the dialog specified in the related_conversation_handle arguments.
• RELATED_CONVERSATION_GROUP =related_conversation_group_id: Determines the existing conversation group to which a new dialog is added. When this clause is present, the new dialog box will be added to the conversation group specified by related_conversation_group_id.
• LIFETIME = dialog_lifetime: Declares the maximum time limit that the dialog will remain open. For the dialog to complete successfully, endpoints must explicitly terminate the dialog before its lifetime expires. The dialog_lifetime value must be expressed in the 2nd. Life time is like int. When no clause LIFETIME is specified, the lifetime of the dialog is the maximum value of the data type int.
• ENCRYPTION: Specifies whether or not messages sent and received in the dialog should be encrypted when sent to an instance of Microsoft SQL Server. When ENCRYPTION = ON and the certificates required to support encryption are not configured, Service Broker returns an error message in the conversation. If ENCRYPTION = OFF, encryption is used if a remote service association is set to target_service_name; otherwise, the messages will be sent decrypted. If this clause is not present, the default value is ON.

Important: All messages are part of a conversation. Therefore, a starter service should start a conversation with the target service before sending you a message.

The service specified in the TO SERVICE clause is the address to which messages are sent. The service specified in the FROM SERVICE clause is the return address used for response messages. Starting a dialog creates a database conversation endpoint for the initiator service, but does not create a network connection to the instance hosting the target service. Service Broker does not communicate with the dialog destination until the first message is sent.

After knowing a little about this command, let's work on our first code block, called Code 1, where we will be starting our dialogue process for further message exchange.

Where
- @MyConversationHandle: Variable used to store the Handle_id created by SQL Server for use in this dialog;
- From Service: we inform sOrigem Service as our home message exchange service;
- To Service: Declared the sDestination Service as the destination service involved in our message exchange;
- On Contract: We declare our Agreement that we will use in this Dialogue for the services involved;
- Encryption = Off: We are informing SQL Server that our message that will be exchanged in this dialog does not need to be encrypted; and
- Lifetime = 600: Informs Service Broker that this dialog will have a maximum lifetime of 600 seconds, when this timeout expires and the dialog still exists it must be terminated.

Note: When executing this block of code you will notice that Microsoft SQL Server is hardly performing any procedure or message exchange. The purpose of Code 1 is to illustrate and present how we can create a new dialog, and this same block will be used in later.

Cool our dialogue has started and now Service Broker starts working waiting later the data that will be added to be part of the message that is sent.

Let us know a little about Send Command, which is directly responsible for Service Broker's process of sending messages.

Send command

Like the Begin Dialog Conversation command, the Send command is also involved in the process of exchanging messages between services that are bound to Service Broker.

Send's main function is to make it possible to send messages between one or more existing conversations. Through Send SQL Server is oriented to perform the so-called PULL (Push) pushing data stored in the structure that compose the message to be shifted between the source and destination services, passing internally in the structure of the database to which the message. It's even related.

This command also presents a unique set of arguments that can be used to determine a better way to send data, as follows:

• ON CONVERSATION conversation_handle: Specifies the conversations to which the message belongs. The conversation_handle must contain a valid conversation identifier. The same conversation identifier cannot be used more than once.

• MESSAGE TYPE: Reports the type of message sent. This type of message should be included in the service contracts used by these conversations. These contracts should allow the message type to be sent from this side of the conversation. If this clause is omitted, the message is of type DEFAULT.

• message_body_expression: Provides an expression that represents the message body. The message_body_expression is optional. However, if message_body_expression is present, the expression must be of a type that can be converted to varbinary (max). The expression cannot be NULL. If this clause is omitted, the message body is empty.

Now we can perform the process of creating and sending our message, so we will use the Block of 2 Code presented below.

Remarks:

- Note that in order to be able to carry out the entire Dialog and Message Sending process, we are required to execute Code Block 1 again within the 2 Code Block.

- Another important point is the use of the command Commit Transaction In order to force SQL Server to process and commit the statements that make up this block of code, this will ensure that the message was sent and terminated the transaction block.

Ball Show, our message has been sent and now that question can not miss. Where did our message go, is precisely this answer that I will present in the Block of Code 3, as if it were an appetizer that we added to our lunch and will be unveiled in the next part of this article.

Processing message reception

The process for receiving messages handled by applications that make use of Service Broker, or directly in Management Studio, is basically accomplished by using the Receive command, in conjunction with the Top clause.

The main function of the Receive command is to retrieve one or more messages that may exist in the queue that the Service Broker Service is currently using. Normally this command should remove the message from your queue or update its Status, depending on how this setting is set for the queue in use.

Receive command

The Receive command has a simple function to read the queue being processed to identify the set of messages that are inside this repository, passing a status update order or deleting the message from the queue. In order for this simple procedure to be performed, there is a set of arguments that can be used in conjunction with the command, which will be recognized by SQL Server as guiding action and treatment that should be done with the captured message or message set. The following is the list of arguments:

• Waitfor: Guides the RECEIVE statement to wait for a message to arrive in the queue if there are no messages at the moment.
• Top (n): Indicates the maximum number of messages to retrieve and later return. If this clause is not specified, all messages that meet the statement criteria will be returned. For this argument it is allowed to use some options: Column_Name, Expression or Column_Alias, as possible data filters.
• From: Informs the queue to be used and to contain the set of captured messages. In conjunction with this argument, we can use the Database_Name, Schema_Name, and Queue_Name options as possible queue owners.
• Into table_variable: Indicates to SQL Server that the message return should possibly be stored in a table-type variable, where the table must have the same number of columns as the messages. The data type of each column in the table variable must be implicitly convertible to the corresponding column data type in messages. If this option is omitted, SQL Server will return the messages in standard format, identifying each value as a possible result.
• Where: Displays the conversation or conversation group of received messages. If omitted, messages will be returned from the next available conversation group. For this argument, we can use the Conversation_Handle and Conversation_Group_Id options to help Service Broker identify the message handler or conversation group we are capturing within the queue. Note that the values ​​passed to these two options are treated as Unique Identifiers orUniqueIdentifier.
• TimeOut: Determines the amount of time, in milliseconds, that the instruction waits for a message. By default, this clause can only be used with the WAITFOR clause. If this clause is not specified or if the timeout is -1, the waiting time will be unlimited. At the end of the timeout RECEIVE will return an empty result set.

Important: The RECEIVE statement reads messages from a queue and returns a result set. This set consists of zero or more lines, each of which contains a message. If the instruction INTO is not used and　column_specifier　do not assign values ​​to local variables, the instruction will return a result set to the calling program.

The RECEIVE statement removes messages received from the queue unless the queue specifies message retention. When the setting of RETENTION Queue is ON, the RECEIVE statement will update the column status　for　0,　 and leave messages in the queue. When a transaction containing a RECEIVE statement is rolled back, any changes made to the queue in the transaction are also rolled back, returning messages to the queue.

Very cool, we already know a little about the Receive command, its arguments, options and especially the way this command works, we can evolve a little more and start making the process of receiving messages, starting by identifying which messages are in our Queue .

But as we are at the end of this walk, a lot has been presented before and that as memory is currently something that is usually lacking in our heads, I will refresh our memories by presenting again our scenario, with all the resource structure, through Table 1 below:

Table 1 - Structure and Resources - Service Broker.

Now you can not complain, I believe you can remember in our scenario, structure and especially the resources we are using.

An important detail, you can see that Table 1, has a feature of type Route (Route), at no time this feature was addressed in this scenario, but it is present is possible a great importance.

When a Service Broker application is created by default Microsoft SQL Server, it creates a route called AutoCreatedLocal, which addresses it as a Local Route and is responsible for allowing data traffic that Service Broker is processing. If you would like to know and know more about this element and its functionality, refer to the Microsoft SQL Server Books (Books Online), looking for Create Route.

So let's work with our first code block, called Code 1,looking for the list of messages that might exist in our queue, called Destination, as shown below:

Note that after Code 1 is run Management Studio should display our list of messages stored in Queue Destination, where the result should be in XML format, because our messages were created and sent in this format. Figure 1 shown below illustrates this result:

Figure 1 - List of Messages stored in Queue qDestination.

We already know which messages are present in this queue, and now the next step is to read and receive the data that is contained in our message. For this, we will use the code block 2, presented in sequence:

Well, you saw how simple and easy it was to get the data in our Message that was stored in the qDestination Queue, at no time do we need to do any kind of complex configuration or need external resources.

Service Broker is fully capable of returning data, the secret is in using the Receive command in conjunction with the Top clause, where we inform the number of messages that should be returned, then through the Select command we present the contents of the variable. @MyMessage_Body. To further illustrate, Figure 2 shows the message content we just retrieved:

Figure 2 - Message content retrieved through the Receive command.

At this time, if you want to query the content in the qDestination queue, you may notice that this message is no longer present.

After everything has gone smoothly and hopefully, the last step we will take is basically to parse a message that we will query within our queue. Destinationbased on your return interact with our dialogue and return a message to queue qOrigin, finalizing the process of sending and receiving messages. For this purpose we will use the 3 and 4 Code Blocks, presented below:

When we execute these two blocks of code we perform the complete process of sending and receiving messages through the features we set up for Service Broker, our last step is to check the messages in Queue qOriginas a way of ensuring and making sure that the return message has been received we will then make use of the 5 Code Block, presented in sequence.

As you execute this block of code, you will notice that we are not doing anything different simply by using the command Select to query the information in the qOrigin Queue, then receiving the data using the command Receive.

Happy New Year!!!