Developing with the SQL Server Service Broker

As you saw in the first part of this chapter, the SQL Server Service Broker is a subsystem that enables the development of database-oriented messaging applications. While the first part of this chapter provided you with an overview of the primary components of the SQL Server Service Broker subsystem and gave you an idea of the functions and interactions of those components, this section of the chapter will dive in a little deeper and will provide you with a rundown showing how you develop applications using the SQL Server Service Broker.

Programming Model

The SQL Server Service Broker utilizes a set of metadata to describe the kinds of messages that an application can receive, which directions the messages are sent, and how the messages are related. Essentially, the SQL Server Service Broker’s metadata describes the messaging infrastructure used by an application. Additionally, the metadata has associated permissions that can be used to secure the messaging application. Figure 6-4 illustrates the SQL Server Service Broker’s metadata model.

Figure 6-4: SQL Server Service Broker metadata

The message type is the most basic object in the SQL Server Service Broker’s metadata. The message type is used to describe the messages that will be used. For XML messages, the message types can have an optional schema associated with the message. If there is an associated schema, the SQL Server Service Broker will ensure that the message complies with the schema definition. If there is no schema, the messages are treated as binary data.

The next primary object in the SQL Server Service Broker’s metadata is the contract. Message types are grouped into contracts. The contract describes all of the messages that can be received using a particular dialog. For example, if a reservation request and response dialog comprises a simple reservation system, the contract will specify that those two message types are both grouped together and related to the reservation system. The contact essentially specifies which message types are allowed by a given dialog.

Contracts are grouped together to form a service, which essentially represents all of the dialogs that are required to process the messages for that service. A service is associated with one or more queues and is the primary object that’s addressed by the SQL Server Service Broker application. The application opens a dialog to a service. The application isn’t concerned with the physical details of how the service is implemented, as the physical implementation details are defined by the metadata. The queue contains the messages that are received by a SQL Server Service Broker application.

T-SQL DDL and DML

T-SQL has been enhanced with several new statements that enable the native integration of SQL Server Service Broker messaging with traditional database procedures. Table 6-1 summarizes the new T-SQL DDL statements that are used to create SQL Server Service Broker objects.

Table 6-1: T-SQL Statements for SQL Server Service Broker Objects

T-SQL DDL	Description
CREATE MESSAGE TYPE	Creates a new message type
CREATE CONTRACT	Creates a new contract in a database
CREATE QUEUE	Creates a new queue in a database
CREATE ROUTE	Creates a new route in a database
CREATE SERVICE	Creates a new service in a database
ALTER MESSAGE TYPE	Changes a message type
ALTER CONTRACT	Changes a contract
ALTER QUEUE	Changes a queue
ALTER ROUTE	Changes a route
ALTER SERVICE	Changes a service
DROP MESSAGE TYPE	Deletes a message type from a database
DROP CONTRACT	Deletes a contract from a database
DROP QUEUE	Deletes an queue from a database
DROP ROUTE	Deletes a route from a database
DROP SERVICE	Deletes a service from a database

In addition to the new T-SQL DDL statements that are used to create the new SQL Server Service Broker objects, there are also a group of new T-SQL statements that work with the messages in a SQL Server Service Broker application. Table 6-2 lists the new SQL Server Service Broker–related T-SQL DML statements.

Table 6-2: T-SQL Statements for SQL Server Service Broker Messages

T-SQL DML	Description
BEGIN DIALOG	Opens a new dialog
END CONVERSATION	Ends a conversation used by a dialog
MOVE CONVERSATION	Moves a conversation to a new dialog
GET SERVICE INSTANCE	Retrieves a service instance ID from a queue
RECEIVE	Receives a message from a queue
SEND	Sends a message to a queue
BEGIN CONVERSATION TIMER	Starts a timer. When the timer expires, Service Broker puts a message on the queue

SQL Server Service Broker Example Application

In the first part of this section, you got a high-level overview of how you use the SQL Server Service Broker to develop asynchronous messaging applications. This section will now dive in deeper and demonstrate how you use T-SQL to create the required SQL Server Service Broker objects and go on to show how you use them in a simple application. The sample application is a simple reservation system that accepts a simple reservation request on an input queue and then responds with a message on a response queue.

Creating the SQL Server Service Broker Objects

The code that’s used to create the required SQL Server Service Broker objects is shown in the following listing:

CREATE MESSAGE TYPE ResRequest        ENCODING varbinary CREATE MESSAGE TYPE ResResponse        ENCODING varbinary CREATE CONTRACT ResContract  (ResRequest SENT BY any,   ResResponse SENT BY any) CREATE QUEUE ResRequestQueue WITH ACTIVATION  (STATUS = ON,   PROCEDURE_NAME = ResRequestProc,   MAX_QUEUE_READERS = 5,   EXECUTE AS SELF) CREATE QUEUE ResResponseQueue CREATE SERVICE ResRequestService ON QUEUE    ResRequestQueue(ResContract) CREATE SERVICE ResResponseService ON QUEUE    ResResponseQueue(ResContract)

The first step to creating a SQL Server Service Broker application is the creation of message types, which describe the messages that will be sent. The first two statements create two simple message types. The first parameter is used to name the message type, and the ENCODING keyword indicates whether the message will be binary or XML. In this example both message types, ResRequest and ResResponse, are created as binary messages, meaning they will accept any type of data.

Next, a contract is created. The contract describes all of messages that can be received using a particular dialog. The first argument is used to name the contract. The SENT BY clause is used to designate which messages are associated with the contract and where those messages come from.

Then queues must be created. This example shows the creation of two queues: the ResRequestQueue and the ResResponseQueue. The ResRequestQueue uses the ACTIVATION keyword to automatically fire off a stored procedure that will read the contents of the queue. This stored procedure must exist at the time the queue is created; otherwise, an error will be generated. The ResRequest stored procedure is shown a bit later in this section. The MAX_QUEUE_READERS keyword specifies the maximum number of readers that the SQL Server Service Broker will automatically activate. The EXECUTE AS option allows you to execute the activated procedure under a different user context.

After the queues are created, you can display the contents of the queues by using the SELECT statement exactly as if the queue were a standard database table. The following line of code shows how you can display the contents of the Request queue:

SELECT * FROM ResRequestQueue.

Just after the queues are created, they are empty. However, running SELECT statements on the queue is a great way to check out functionality of the SQL Server Service Broker applications you are developing.

After the queues have been created, the next step is to create a service. You create a service using the CREATE SERVICE statement. The first parameter names the service. The ON QUEUE clause identifies the queue associated with the service, and then the contracts that are associated with the service are listed.

If one of the services were located on a remote system, you would also need to create a route. The CREATE ROUTE statement basically supplies the SQL Server Service Broker with the system address where the remote service is found, which tells SQL server how to deliver the message to the remote system.

Sending Messages to a Queue

After the necessary SQL Server Service Broker objects have been created, you’re ready to use them in your queuing applications. The following code listing shows how you can add a message to the ResRequestQueue queue:

DECLARE @ResRequestDialog UNIQUEIDENTIFIER BEGIN TRANSACTION BEGIN DIALOG @ResRequestDialog   FROM SERVICE ResResponseService   TO SERVICE 'ResRequestService'   ON CONTRACT ResContract   WITH LIFETIME = 1000; SEND ON CONVERSATION @ResRequestDialog   MESSAGE TYPE ResRequest (N'My Request Message'); SEND ON CONVERSATION @ResRequestDialog  MESSAGE TYPE ResRequest (N'My Request Message2'); COMMIT TRANSACTION

At the start of this listing, you can see that a variable named ResRequestDialog is created; it contains a unique identifier that will be assigned by a SQL Server Service Broker dialog. Next, a transaction is started. It’s a good idea to wrap all of the actions that are performed by the SQL Server Service Broker in a transaction. This enables you to commit and optionally roll back any changes to the queues. Then, the BEGIN DIALOG statement is used to open up a new SQL Server Service Broker dialog. When you declare a dialog, you always need to specify two endpoints. The FROM SERVICE identifies the initiator of the messages, while the TO SERVICE keyword identifies the target endpoint. Here, the sender is named ResResponseService and the target is named ResResquestService. While this example uses simple names, Microsoft recommends that you use a URL name to uniquely identify the SQL Server Service Broker objects. For example, to ensure uniqueness in the network Microsoft recommends using names like [http://MyCompany.com/MyApp/ResResponseService]. The ON CONTRACT keyword specifies the contract that’s used for the dialog. The contract specifies which messages this dialog is able to send or receive.

Then, two SEND operations are executed. These statements send two messages to the target service, which will receive those messages and add them to the queue that is associated with that service. Finally, the transaction is committed.

Retrieving Messages from a Queue

Now that you’ve seen how to add a message to a queue, the next example will illustrate how to create a stored procedure that will read the messages off the queue. As you may recall from the earlier SQL Server Service Broker object creation examples, the target queue, ResRequestQueue, was created with activation. This means that an associated stored procedure named ResRequestProc will be automatically started when a message arrives on the queue. You can see the code for that stored procedure in the following listing:

CREATE PROC ResRequestProc AS DECLARE @ResRequestDialog UNIQUEIDENTIFIER DECLARE @message_type_id int DECLARE @message_body NVARCHAR(1000) DECLARE @message NVARCHAR(1000) while(1=1) BEGIN     BEGIN TRANSACTION         WAITFOR   (RECEIVE top(1)        @message_type_id = message_type_id,        @message_body = message_body,        @ResRequestDialog = conversation_handle         FROM ResRequestQueue), TIMEOUT 200;        if (@@ROWCOUNT = 0)        BEGIN           COMMIT TRANSACTION           BREAK        END        IF (@message_type_id = 2) -- End dialog message            BEGIN                PRINT ' Dialog ended '              + cast(@ResRequestDialog as nvarchar(40))            END        ELSE            BEGIN                BEGIN TRANSACTION                    BEGIN DIALOG @ResRequestDialog                    FROM SERVICE ResRequestService                        TO SERVICE 'ResResponseService                        ON CONTRACT ResContract                        WITH LIFETIME = 1000;                    SELECT @message = 'Received:' + @message_body;                     SEND ON CONVERSATION @ResRequestDialog                     MESSAGE TYPE ResResponse (@message);                     PRINT CONVERT(varchar(30), @message)                COMMIT TRANSACTION            END CONVERSATION @ResRequestDialog     END     COMMIT TRANSACTION END

A variable that will contain the response dialog identification is declared at the top of this stored procedure, followed by three variables that will be used to pull back information from the queue that’s being read. Then a loop is initiated to read all of the entries from the queue. Within the loop, a transaction is started and the RECEIVE statement is used to receive a message. In this example, the TOP(1) clause is used to limit the procedure to receiving only a single message at a time. If the TOP clause were omitted, you could receive all of the messages that were present on the queue. The RECEIVE statement populates the three variables. The message_type_id identifies the type of message, which is typically either a user-defined message or an End Dialog message. The @message_body variable contains the contents of the actual message, while the @ResRequestDialog variable contains a handle that identifies the sending dialog.

Then, the result set is checked to ensure that a message was actually received. If no rows were received, then the last transaction is committed and the procedure is ended. Otherwise, if rows were received, the message typeid is checked to see if the message is a user message or an End Dialog message. If it’s a user message, the contents will be processed. First, a dialog is opened to the ResResponseService. This dialog will be used to send the modified message to the ResResponse Queue. Again, the ResContract is specified, which restricts the message types that will be allowed.

After the dialog is opened, the received message is modified by concatenating the string “Received:” with the contents of the message that was received, and then the SEND statement is used to send the modified message to the ResResponseQueue. Finally, the dialog conversation is ended and the transaction is committed.