The Service Broker

The Service Broker may be the best feature added to this SQL Server release. This feature enables you to build secure, reliable, scalable, distributed, asynchronous functionality to database applications. You can use the Service Broker to build distributed applications, delegating all the system-level messaging details to the Service Broker and concentrating your efforts in the problem domain. You may find that implementing Service Broker application is tedious at first, but once you understand it, we think you will love it.

Service Broker Architecture

The Service Broker is designed around the basic functions of sending and receiving messages. It helps developers build asynchronous, loosely coupled applications, in which independent components work together to accomplish a task. The Service Broker is a framework and extension to T-SQL, and can create and use the components for building reliable and scalable message-based applications. The core of the Service Broker architecture is the concept of a dialog, which is a reliable, persistent, bidirectional, ordered exchange of messages between two endpoints. You will learn more about endpoints later. Figure 8-1 shows the basic architecture of the Service Broker (it includes a lot of terminology that is explained in the following sections).

Figure 8-1

Message Type

A message type is a definition of the format of a message. The message type is an object in a database. Messages are the information exchanged between applications that use the Service Broker. The message type object defines the name of the message and the type of data it contains. The following code shows the syntax for creating a message type:

 CREATE MESSAGE TYPE message_type_name     [ AUTHORIZATION owner_name ]     [ VALIDATION = { NONE                    | EMPTY                    | WELL_FORMED_XML                    | VALID_XML WITH SCHEMA COLLECTION schema_collection_name                   }     ] 

Permission for creating a message type defaults to members of the ddl_admin or db_owner fixed database roles and the sysadmin fixed server role. The REFERENCES permission for a message type defaults to the owner of the message type, members of the db_owner fixed database role, and members of the sysadmin fixed server role. When the CREATE MESSAGE TYPE statement specifies a schema collection, the user executing the statement must have REFERENCES permission on the schema collection specified.

Following are the arguments and their definitions for the CREATE MESSAGE TYPE statement:

• message_type_name: The name of the message type is just a SQL Server identifier. The convention is to use a URL for the name, but any valid name will suffice. By convention, the name has the form //hostname/pathname/name - for example, //www.wrox.com/bookorder/purchaseorder. Although using a URL format is not required, it's generally easier to ensure uniqueness if you use a URL, especially when conversations span distributed systems. The message_type_name may be up to 128 characters.

• AUTHORIZATION owner_name: This argument defines the owner of the message type. The owner can be any valid database user or role. When this clause is omitted, the message type belongs to the user who executed the statement. If you execute the CREATE MESSAGE TYPE statement as either dbo or sa, owner_name may be the name of any valid user or role. Otherwise, owner_name must be the name of the current user, the name of a user for whom the current user has impersonate permissions, or the name of a role to which the current user belongs.

• VALIDATION: This specifies how the Service Broker validates the message body for messages of this type. When this clause is not specified, validation defaults to NONE. The possible values are as follows:

  • NONE: The message is not validated at all. The message body may contain any data, including NULL.

  • Empty: The message body must be NULL.

  • WELL_FORMED_XML: When this clause is specified, the receiving endpoint will load the XML into an XML parser to ensure that it can be parsed. If the message fails the validation, it is discarded and an error message is sent back to the sender.

  • VALID_XML WITH SCHEMA COLLECTION schema_collection_name: This clause validates the message against the schema_collection_name specified. The schema collection must be created in SQL Server before you use it here. A schema collection is a new object in SQL Server 2005 that contains one or more XML schemas. Refer to the CREATE XML SCHEMA COLLECTION command in Books Online for more information. If WITH SCHEMA COLLECTION schema_collection_name is not specified, the message will not be validated; however, the XML will still have to be well formed because anytime you specify WELL_FORMED_XML or VALID_XML, the message of that type is loaded into the XML parser when it is received.

The following is an example of creating a message type:

 CREATE MESSAGE TYPE [//www.wrox.com/order/orderentry] VALIDATE = WELL_FORMED_XML 

The two XML validations load every message of that type into an XML parser when each message is received. If you have thousands of messages coming per second, each message passing through the XML parser will have an effect on performance. Therefore, if your message volume is low, validation may make sense. If your application loads the message into a parser, it will be more efficient to handle parsing or validation in the application, rather than parse the message twice.

You can run the following query to view message types in the database in which you have created them:

 SELECT * FROM sys.service_message_types 

When you run this query, you will see some default message types. Later in the chapter, you will see some examples of using these default message types. These message types are an implicit part of every contract, which we'll discuss shortly, so any endpoint can receive instances of these message types.

You can ALTER or DROP a message type by using the following T-SQL command syntax:

 ALTER MESSAGE TYPE message_type_name    VALIDATION =     {  NONE      | EMPTY      | WELL_FORMED_XML      | VALID_XML WITH SCHEMA COLLECTION schema_collection_name     } 

Changing the validation of a message type does not affect messages that have already been delivered to a queue, which we'll discuss shortly. Permission to alter a message type defaults to the owner of the message type, members of the ddl_admin or db_owner fixed database roles, and members of the sysadmin fixed server role. When the ALTER MESSAGE TYPE statement specifies a schema collection, the user executing the statement must have REFERENCES permission on the schema collection specified.

The following example shows the syntax to drop a message type:

 DROP MESSAGE TYPE message_type_name [,......n] 

Permission for dropping a message type defaults to the owner of the message type, members of the ddl_admin or db_owner fixed database roles, and members of the sysadmin fixed server role. Note that you can drop multiple message types. If you try to drop a message type and a contract is referencing the message type, you will get an error.

Contracts

As described earlier, the Service Broker contracts define which message type can be used in a conversation. The contract is a database object. The following is the syntax for creating a contract:

 CREATE CONTRACT contract_name    [ AUTHORIZATION owner_name ]       (   {   message_type_name SENT BY { INITIATOR | TARGET | ANY } | [ DEFAULT ] }           [ ,...n]       ) 

Permission for creating a contract defaults to members of the ddl_admin or db_owner fixed database roles and the sysadmin fixed server role. The REFERENCES permission for a contract defaults to the owner of the message type, members of the db_owner fixed database role, and members of the sysadmin fixed server role. The user executing the CREATE CONTRACT statement must have REFERENCES permission on all the message types specified unless the user executing the CREATE CONTRACT statement is a member of ddl_admin or db_owner, or the sysadmin role, because members of these roles have REFER-ENCES permission on all the message types by default.

The following list describes the arguments for the CREATE CONTRACT statement:

• contract_name: The name of the contract is just a SQL Server identifier. The convention is to use a URL, but any valid name will suffice. By convention, it has the form of //hostname/pathname/name. So //www.wrox.com/bookorder/ordercontract is an example of a valid contract name. Although using a URL format is not required, it's generally easier to ensure uniqueness if you use a URL, especially when conversations span distributed systems. The contract_name may be up to 128 characters.

• AUTHORIZATION owner_name: This argument is the same as it is in the message type.

• message_type_name: The name of the message type that this contract uses. You can have multiple message types per contract, as you'll see later in the example.

• SENT BY: This clause defines whether the message type can be sent by the initiator of the conversation, sent by the target of the conversation, or either.

  • INITIATOR: The initiator of the conversation can send the defined message type.

  • TARGET: The target of the conversation can send the defined message type.

  • ANY: The defined message type can be sent by either INITIATOR or TARGET.

• [DEFAULT]: If you query the sys.service_message_types catalog view, you will see a DEFAULT message type there.

The following code shows an example of creating a contract:

 CREATE CONTRACT [//www.wrox.com/bookorder/ordercontract] ( [//www.wrox.com/order/orderentry] SENT BY INITIATOR [//www.wrox.com/order/orderentryack] SENT BY TARGET ) 

When a contract is created, at least one message type needs to be marked as SENT BY INITIATOR or SENT BY ANY. Obviously, a message type must exist before you create the contract. In addition, the message type and direction cannot be changed once the contract is defined, so you cannot alter the contract once you create it. You can only change the authorization using the ALTER AUTHORIZATION statement.

Queue

We mentioned earlier that the Service Broker performs asynchronous operations. In asynchronous processing, you send a request to do something and then you start doing something else; the system processes the request you made later. Between the time you make the request and the time the system process acts on it, the request must be stored somewhere. The place where these requests are stored is called the queue.

The Service Broker implements queues with a new feature in SQL Server 2005 called a hidden table. You cannot use normal T-SQL commands to manipulate the data in the queue, and you cannot use INSERT, DELETE, or UPDATE commands on queues. In addition, you cannot change the structure of the queue or create triggers on them. A read-only view is associated with every queue, so you can use a SELECT statement to see what messages are in the queue. We will talk about T-SQL commands that work on queues later. The sender puts the message in the queue, using T-SQL commands that you will look at later. The transport layer moves that message reliably to the destination queue. The receiver application can then pull that message off the queue when it desires. The message from the initiator (first) queue is not deleted until that message has been successfully stored in a destination (second) queue, so there is no chance that the message will be lost in transit. Figure 8-2 shows how this works.

Figure 8-2

Assume that in Figure 8-2, the Service Broker is delivering a message from the database InitiatorDB on InitiatorServer to TargetDB on TargerServer, which means a network is involved in delivering this message. The Service Broker puts the messages that are to be sent over the network in a temporary queue called the transmission_queue on the server where the message is initiated. You can query this transmission_queue with the following select statement:

 SELECT * FROM sys.transmission_queue 

There is one such transmission_queue for each SQL Server instance. After a message is put into the transmission_queue, the Service Broker sends the message over the network and marks that message as waiting for acknowledgement in the transmission_queue. When the Service Broker receives the message from the TargetServer in the TargetDB queue, it sends an acknowledgment back to the InitiatorServer. When the InitiatorServer receives the acknowledgement message, it deletes the message from the transmission_queue. This process is called the dialog. We discuss the dialog and conversation later in this chapter. For now, just keep these terms in mind.

The following is the syntax to create a queue:

 CREATE QUEUE queue_name    [ WITH      [ STATUS = { ON | OFF }  [ , ] ]      [ RETENTION = { ON | OFF } [ , ] ]      [ ACTIVATION         (          [ STATUS = { ON | OFF } , ]            PROCEDURE_NAME = <procedure> ,            MAX_QUEUE_READERS = max_readers ,            EXECUTE AS { SELF | 'user_name' | OWNER }         )      ]    ]      [ ON { filegroup | [ DEFAULT ] } ] 

The arguments to this statement are as follows:

• queue_name: The name of the queue that is created. This must be a SQL Server identifier. Because the queue is never referenced outside the database in which it is created, the URL-like syntax used for other Service Broker object names is not necessary for queue names.

• STATUS: When you create the queue, the STATUS parameter determines whether the queue is available (ON) or unavailable (OFF). If you don't specify the STATUS, the default value is ON. When the queue status is OFF, the queue is unavailable for adding or removing messages. When you deploy the application, it is a good idea to set the queue status to OFF so the application cannot submit the messages to the queue. Once the setup is done correctly, you can change this status to ON with the ALTER QUEUE statement, as you'll see shortly.

• RETENTION: When this parameter is OFF (the default setting), the message from the queue is deleted as soon as the transaction that receives the message commits. If RETENTION is ON, the queue saves all the incoming and outgoing messages until the conversation that owns the messages ends. This will increase the size of the queue, and it may affect the application's performance. You have to decide for your application requirements whether you need to retain the messages in the queue for a longer time or not. Later in the chapter you will look at an example that shows exactly what happens when RETENTION is either ON or OFF and when messages are deleted from the queue.

• ACTIVATION: We will talk about activation in detail later but for now it suffices to understand that this option specifies some information about a stored procedure to be executed when a message arrives in the queue:

  • STATUS: If this parameter is ON, the Service Broker will activate (run) the stored procedure specified in the PROCEDURE_NAME argument. If this parameter is OFF, the queue does not activate the stored procedure. You can use this argument to temporarily stop activation of the stored procedure to troubleshoot a problem with an activation stored procedure. In addition, if you have made some changes to the activated stored procedure, it is good idea to change the STATUS to OFF before you deploy that stored procedure so that the next message in the queue will be processed by the modified stored procedure.

  • PROCEDURE_NAME: This is the name of a stored procedure that is activated when a message arrives in the queue. The procedure has to be in the same database as the queue or be fully qualified. You will learn more about this later.

  • MAX_QUEUE_READERS: This numeric value specifies the maximum number of instances of the stored procedure that the queue starts at the same time. The value of MAX_QUEUE_READERS must be a number between 0 and 32,767. Later you will learn about the effect of setting this value too high or too low.

  • EXECUTE AS: This specifies the database user account under which the activation stored procedure runs. If you specify a domain account, your SQL Server must be connected to the domain because at the time of activation, SQL Server checks for permission for that user. If this is a SQL Server user, SQL Server can check permission locally. If the value is SELF, the activation stored procedure will run under the context of the user who is creating this queue. If the value is 'user_name' , the activation stored procedure will run under the context of the user specified here. The user_name parameter must be a valid SQL Server user. In addition, if the user creating the queue is not in the dbo or sysadmin role, that user needs the IMPERSONATE permission for the user_name specified. See Chapter 9 for more details on impersonation. If the value is OWNER, the stored procedure executes as the owner of the queue. Keep in mind that the owners of the queue can be different users than the one who is running the CREATE QUEUE command.

• ON { filegroup | [ DEFAULT ]: This specifies the SQL Server filegroup on which you want to create the queue. If your application is going to have a high volume of messages coming in, you may want to put your queue on a filegroup with a lot of disk space. If you don't specify this option, the queue will be created on the default filegroup of the database in which you are creating the queue.

You can use the ALTER QUEUE command to change any of theses parameters. One additional parameter you can specify in ALTER QUEUE is to DROP the activation. That will delete all of the activation information associated with the queue. Of course, it will not drop the stored procedure specified in the PROCE-DURE_NAME clause.

Permission for altering a queue defaults to the owner of the queue, members of the ddl_admin or db_owner fixed database roles, and members of the sysadmin fixed server role.

The following code shows the creation of a queue. When you execute this statement, it will fail because the CREATE QUEUE statement requires that the activation procedure dbo.ProcessOrder exists in the database before you run this statement:

 CREATE QUEUE dbo.acceptorder WITH STATUS = ON     ,RETENTION = OFF     ,ACTIVATION (                  STATUS = ON                 ,PROCEDURE_NAME = dbo.ProcessOrder                 ,MAX_QUEUE_READERS = 5                 ,EXECUTE AS SELF                 ) 

Here's the code to alter the queue status:

 ALTER QUEUE dbo.acceptorder WITH  STATUS = OFF, ACTIVATION (STATUS = OFF) 

When you create a queue, you create an object of the type SERVICE_QUEUE. To view what queues exist in a database (keep in mind that the queue is for each database object, while sys.transmission_queue is for each SQL Server instance), you can use a SELECT statement as follows:

 SELECT * FROM sys.service_queues 

If you want to view the contents of the queue, you can issue the following statement:

 SELECT * FROM dbo.acceptorder 

Services

To understand how services work, consider the postal service. When you want to send a letter to your friend, you write his or her address on the envelope before mailing it. In the Service Broker world, that address on the envelope is called a service. A Service Broker service identifies an endpoint of a conversation. That endpoint is a queue in a database. A service is associated with a list of contracts that is accepted by the service. Note that mapping a service to a contract is an optional step on the initiator. On the target, if you do not specify any contract, you won't be able to send any messages to the target.

Here is the syntax to create the service:

 CREATE SERVICE service_name    [ AUTHORIZATION owner_name ]    ON QUEUE [ schema_name. ]queue_name    [ ( contract_name | [DEFAULT] [ ,...n ] ) ] 

The following list describes the arguments for the CREATE SERVICE statement:

• service_name: This is the name of the service to create.

• Authorization: This is the same as discussed earlier in the message type section.

• Queue_name: This is the name of the queue that receives the messages from this service.

• Contract_name: This argument specifies a contract for which this service may be a target. If no contract is specified, the service may only initiate conversation.

The following code demonstrates how you create a service:

 CREATE SERVICE orderentryservice ON QUEUE acceptorder ([//www.wrox.com/bookorder/ordercontract]) 

You can query the catalog view SELECT * FROM sys.services to view the service defined in a database.

Now that you have an understanding of all the pieces of the architecture, Figure 8-3 shows the relationship among the metadata objects used by the Service Broker.

Figure 8-3

Service Broker Examples

Now you have enough information to write a small application. Although we have not covered some of the terms such as activation or conversation, we will do so as you go through the code.

Note

Service Broker metadata names that are sent across the wire (message type, contract, service) are always case-sensitive.

You'll start with a very simple example that involves Service Broker communication on the same server between two databases. You will look at three examples; this first example illustrates how messaging and activation work in the Service Broker. Don't worry about security in the first example. The second example features a Service Broker application that communicates between two instances of SQL Server, and the third involves communication between two SQL Server instances with certificates and message encryption. Security is a major part of these examples.

The first example assumes that you are the system administrator (sa) on the SQL Server. For now, our focus is on how the Service Broker works, and not on which tricks you can do with it yet. Download the code for this chapter from this book's Web site at www.wrox.com. Open the Chapter 8 folder, followed by the Sample1 folder, and double-click the Sample1 solution file. It is assumed that you have SQL Server Management Studio (SSMS) installed where you open this file.

In this example, you will send a message from the Initiator database asking for the customer and product count, and receive that message on the Target database on the same SQL Server instance. The Target database will respond to that message with the customer and product count. The initiator database will store that count in a table and call it good.

You will see the SetupInitiator.sql script in the Queries folder in the Sample1 solution. Open the file and connect to your SQL Server instance. Let's go through the code in that file. First you will set up the InitiatorDB and then the TargetDB. Do not run the script until after you have finished reading the explanation.

 USE Master GO IF EXISTS(SELECT 1 FROM sys.databases WHERE name = 'InitiatorDB') DROP DATABASE InitiatorDB GO CREATE DATABASE InitiatorDB; GO ALTER DATABASE InitiatorDB SET TRUSTWORTHY ON GO USE InitiatorDB GO SELECT Service_Broker_Guid, is_broker_enabled, is_trustworthy_on FROM sys.databases WHERE database_id = DB_ID() GO 

Create a brand-new database called InitiatorDB so that you don't interfere with other databases on your system. Notice that after creating the InitiatorDB, the next statement sets the TRUSTWORTHY bit to 1 for that database. If you are using certificate security with dialog encryption ON (explained later in the chapter), then setting the TRUSTWORTHY bit to ON is not necessary. The SELECT statement from the sys.databases catalog view will give you the Service_Broker_Guid, which is a unique Service Broker identifier for that database. The is_broker_enabled bit is set to 1 when you create a new database (not when you restore or attach from an existing database file). If this bit is 0, then message delivery in this database is not possible because the Service Broker is not enabled in this database.

 ----------------------------------------------------------------------------------- --Create table to log what's happened. ----------------------------------------------------------------------------------- CREATE TABLE dbo.ProcessHistory (  RowID int IDENTITY(1,1) NOT NULL ,Process varchar(60)  NOT NULL ,StatusDate datetime NOT NULL ,Status varchar(50) NULL ,MessageText xml NULL ,SPID int NULL ,UserName varchar(60) NULL  CONSTRAINT PK_ProcessHistory PRIMARY KEY CLUSTERED(RowID) ) 

This table was created to log information so you can view what's happened by looking at the rows in the table.

Now the interesting part starts here. As discussed earlier, the first thing you have to do when you create Service Broker objects is to create message types, which is what happens here:

 ----------------------------------------------------------------------------------- --First we have to create Message Types on InitiatorDB ----------------------------------------------------------------------------------- CREATE MESSAGE TYPE [//www.wrox.com/MTCustomerInfo/RequstCustomersCount] VALIDATION = WELL_FORMED_XML GO CREATE MESSAGE TYPE [//www.wrox.com/MTCustomerInfo/ResponseTotalCustomers] VALIDATION = WELL_FORMED_XML GO CREATE MESSAGE TYPE [//www.wrox.com/MTProductInfo/RequestProductsCount] VALIDATION = WELL_FORMED_XML GO CREATE MESSAGE TYPE [//www.wrox.com/MTProductInfo/ResponseTotalProducts] VALIDATION = WELL_FORMED_XML GO 

This code created four message types. Notice that we have used WELL_FORMED_XML as the validation for the message body for all message types. When the message is received, it will go through an XML parser for validation.

The message type [//www.wrox.com/MTCustomerInfo/RequstCustomersCount] is used when the initiator sends the message to the target to request the number of customers. Similarly, when the target replies with the number of customers, it uses the [//www.wrox.com/MTCustomerInfo/ResponseTotalCustomers] message type.

The message type [//www.wrox.com/MTProductInfo/RequestProductsCount] is used when the initiator sends the message to the target to request the number of products. Similarly, when the target replies with the number of products, it uses the [//www.wrox.com/MTProductInfo/ResponseTotalProducts] message type.

We have not used the AUTHORIZATION clause in the CREATE MESSAGE TYPE statement, so the owner of these message types will be dbo, assuming you are sa on the SQL Server instance. Don't get confused about the message type. It tells the Service Broker what to expect in the message body, including whether or not that message should be validated and how to validate it. In addition, based on the name of the message type, you can take some action.

The next bit of code creates a contract and binds the message types you created earlier to this contract. Later you will see how the contract is used.

 CREATE CONTRACT [//www.wrox.com/CTGeneralInfo/ProductCustomer] (    [//www.wrox.com/MTCustomerInfo/RequstCustomersCount] SENT BY INITIATOR   ,[//www.wrox.com/MTProductInfo/RequestProductsCount] SENT BY INITIATOR   ,[//www.wrox.com/MTCustomerInfo/ResponseTotalCustomers] SENT BY TARGET   ,[//www.wrox.com/MTProductInfo/ResponseTotalProducts] SENT BY TARGET ) 

Notice the SENT BY clause. When SENT BY is set to INITIATOR, it means that only the initiator of the message can send the message of that type. When SENT BY is set to TARGET, only the target can send the message of that type.

You still need to create the stored procedure CheckResponseFromTarget, but we'll ignore that here. For now, focus on the queue:

 ----------------------------------------------------------------------------------- --Create queue to receive the message from target. ----------------------------------------------------------------------------------- CREATE QUEUE dbo.ReceiveAckError WITH STATUS = ON     ,RETENTION = OFF     ,ACTIVATION (                  STATUS = OFF                 ,PROCEDURE_NAME = dbo.CheckResponseFromTarget                 ,MAX_QUEUE_READERS = 5                 ,EXECUTE AS SELF                 ) 

The queue ReceiveAckError will be used to receive the response from the target. When the target sends the reply to the original request from the initiator, that message will go in here. Notice that the queue STATUS is ON. When the status is OFF, the queue is unavailable for adding or removing messages. Also notice that the stored procedure CheckResponseFromTarget will be executed when any message arrives in the queue ReceiveAckError. However, because the status of the ACTIVATION is set to OFF, the stored procedure will not be activated. The code specifies that a maximum of five instances of this stored procedure can be activated if necessary with the MAX_QUEUE_READER option. When you specify a stored procedure for activation, that stored procedure must exist before you create the queue; otherwise, the queue will not be created.

Now you need a service to which you can send or receive the messages. Each service is bound to one and only one queue. When you send the message, you send the message to a service and not to the queue. The service will put the message into the queue:

 CREATE SERVICE InitiatorService ON QUEUE dbo.ReceiveAckError ([//www.wrox.com/CTGeneralInfo/ProductCustomer]) 

Notice that you specify the contract [//www.wrox.com/CTGeneralInfo/ProductCustomer] in the CREATE SERVICE statement. That means that only message types defined by this contract can be sent to this queue. This queue will not receive any other message types. Now run the SetupInitiator.sql script.

Open the SetupTarget.sql script. You will notice that it looks similar to the SetupInitiator.sql script. You have created the same message types and the contract on TargetDB. The queue name is RequestQueue in the TargetDB, and the stored procedure activated when the message arrives is CheckRequestFromInitiator. You will understand the stored procedures CheckRequestFrom Initiator and CheckResponseFromTarget very soon.

Next, run the SetupTarget.sql script on the same SQL Server where you have run the SetupInitiator.sql script.

Your infrastructure is now ready to send your first message. At this point you may have some questions: How do you send the message? What happens when the message is sent? How will it be received?

Open the script SendMessageFromInitiator.sql and connect to the database InitiatorDB, which you created using the script SetupInitiator.sql. The following is the T-SQL code in the SendMessageFromInitiator.sql script:

 USE InitiatorDB GO DECLARE @Conversation_Handle UNIQUEIDENTIFIER        ,@SendMessage xml        ,@MessageText varchar(255) SET @MessageText =  'Request From Server: \\' + @@SERVERNAME                   + ', DATABASE: ' + DB_NAME()                   + ': ** Please send the total number of customers.' SET @SendMessage = N'<message>'+ N'<![CDATA[' + @MessageText + N']]>' + N'</message>' BEGIN TRY       BEGIN DIALOG CONVERSATION @Conversation_Handle       FROM SERVICE [InitiatorService]       TO SERVICE 'TargetService'       ON CONTRACT [//www.wrox.com/CTGeneralInfo/ProductCustomer]       WITH ENCRYPTION = OFF       SELECT * FROM sys.conversation_endpoints       WHERE conversation_handle = @Conversation_Handle;       ----------------------------------------------------------------       SEND ON CONVERSATION @Conversation_Handle       MESSAGE TYPE [//www.wrox.com/MTCustomerInfo/RequstCustomersCount]       (@SendMessage);       --------------------------------------------------------------- END TRY BEGIN CATCH    SELECT ERROR_NUMBER() AS ErrorNumber          ,ERROR_MESSAGE() AS ErrorMessage          ,ERROR_SEVERITY() AS ErrorSeverity          ,ERROR_LINE() AS ErrorLine END CATCH 

Before you can understand the preceding code, you need to know what a conversation is. The core concept of the Service Broker is the conversation. A conversation is a reliable, ordered exchange of messages. Two kinds of conversations are defined in the Service Broker architecture:

• Dialog: This is a two-way conversation between exactly two endpoints. An endpoint is a source or destination for messages associated with a queue; it may receive and send messages. A dialog is established between an initiator and target endpoint (refer to Figure 8-1). The initiator is just an endpoint that issues the BEGIN DIALOG command to start the dialog. After that, the initiator and the target are peers. The first message always goes from the initiator to the target. Once the target receives the first message, any number of messages can be sent in either direction.

• Monolog: This is a one-way conversation between a single publisher endpoint and any number of subscriber endpoints. Monologs are not available in SQL Server 2005, though they will be included in future versions.

Now you can begin to understand the T-SQL code. Before sending a message, you must create a dialog conversation. The command to do that is BEGIN DIALOG CONVERSATION. The arguments of this statement are as follows:

• @Conversation_Handle: When you run the BEGIN DIALOG statement, it returns the value of the type uniqueidentifier. The conversation handle is stored in the @Conversation_Handle variable. It is just a variable, so you can name it anything you like as long as the type of this variable is uniqueidentifier.

• Service: Because a dialog connects exactly two endpoints, you must specify both endpoints in the BEGIN DIALOG command. These endpoints are specified using service names. If a service is analogous to a postal address, then you need a "from" address and a "to" address. That's what you need in BEGIN DIALOG. In this case, the "from" service is [InitiatorService] and the "to" service is 'TargetService'. Note the syntax: The "from" service name is in sqaure brackets, and the "to" service is specified in quotes. The [InitiatorService] service is defined as the database InitiatorDB you created earlier, and the 'TargetService' is defined as the TargetDB you created earlier. In this case, the message will go from database InitiatorDB to TargetDB.

• Contract: The contract defines the content of a dialog. In the CREATE CONTRACT statement, we have defined four message types.

• Encryption: We discuss this clause later in the section "Security Considerations for the Service Broker." For now, set this to OFF.

Right after the BEGIN DIALOG statement is a SQL statement to select from the catalog view sys.conversation_endpoints. The effect of this statement is that every time you run the BEGIN DIALOG statement, you will see a row added to the sys.conversation_endpoints catalog view.

There are other clauses to the BEGIN DIALOG statement, but they are not covered here in order to keep the example simple. The only clause we need to discuss here is LIFETIME. The LIFETIME clause specifies the number of seconds that this dialog is allowed to live. If you don't specify this clause, the dialog lifetime is the maximum value of the int data type. We haven't specified the LIFETIME here because we are looking at how messaging works and we want the dialog to live longer while you examine the system view and the flow of the message. When you specify the LIFETIME to be 600 seconds, for example, if the dialog still exists after the lifetime expires (five minutes in this case), then an error will be returned to any statements that attempt to access it, and error messages are also sent to both endpoints of the dialog. Note that the dialog doesn't go away when its lifetime expires. It remains in the sys.conversation_endpoints table in an error state until it is ended with the END CONVERSATION statement, which we will look at later.

Now take a look at the SEND statement. As described earlier, before sending a message, you must obtain a conversation handle. If you are the initiator of a conversation, you obtain a conversation handle by executing a BEGIN DIALOG command. The target of a conversation obtains a conversation handle from a RECEIVE statement. SEND has three parameters:

• A conversation handle associated with an open conversation

• A message type that describe the contents of the message

• The message body

The message body in this case is contained in the variable @SendMessage. The message body of the Service Broker queue is a varbinary(max) column, so a message body can contain up to two gigabytes of binary data. That means when you send the message using the SEND command, it converts the message body to the varbinary(max) type. In this case, the variable @SendMessage, which is defined as an XML data type, is converted to varbinary(max) before SEND completes. Most SQL Server data types can be cast to or from varbinary(max), so you can send almost anything you want as a Service Broker message. If 2GB of data isn't enough, you can split large messages into 2GB pieces and reassemble then at the destination because the Service Broker ensures that the message arrives in order. You can use the XML data type that converts implicitly to and from varbinary(max), so using the Service Broker with the XML data types is simple and straightforward.

You need to know one last thing before you run the script. The BEGIN DIALOG and SEND statements are inside a while loop to show you how multiple threads of the activation stored procedure start when you have several messages in a queue and one instance of the activation procedure cannot handle all those messages. For now, you will send only one message, so the variable @NumberOfMessages is set to 1.

You are now ready to run the script SendMessageFromInitiator.sql. Press Execute. That will send the message from InitiatorDB database to TargetDB database. Where did it go? You saw earlier that when you send the message it goes to sys.transmission_queue first and then to the target queue, as shown in Figure 8-2. That is true when the message is going over the network, but if you are sending the message on the same instance of the SQL Server, then the message will be directly put into the target queue, which is RequestQueue in this case. If any error happens in that message transmission from initiator to target, you will see a row in the catalog view sys.transmission_queue, even if you are sending the message on the same instance of the SQL Server. So how did the message arrive at Request Queue on the TargetDB? When you sent the message from the InitiatorDB, the BEGIN DIALOG statement specified the "to" service as 'TargetService', and when you created the 'TargetService', you specified the queue (or endpoint) as RequestQueue.

Now take a look at the message in the target queue. Open the script ViewInfoOnTarget.sql and run the following code from the script:

 USE TargetDB GO SELECT Conversation_Handle, CAST(message_body as xml) AS message_body, * FROM dbo.RequestQueue SELECT * FROM sys.conversation_endpoints 

The output from this script is shown in Figure 8-4.

Figure 8-4

Notice that the messages in the queue are of the varbinary data type. The script converts the message into XML so you can read the content. You can also now see a row in the sys.conversation_endpoints catalog view. A SELECT statement is used to view the data in a queue.

Now you can retrieve the message from the RequestQueue. Open the script ReceiveMessageOn Target.sql, which is shown here:

 USE TargetDB GO DECLARE     @message_type_name  nvarchar(256)    ,@xmlmessage_body    xml    ,@MessageFromTarget  varchar(255)    ,@MessageText varchar(255)    ,@conversation_handle UNIQUEIDENTIFIER    ,@ErrorNumber int    ,@ErrorMessage nvarchar(4000)    ,@ErrSeverity int    ,@ErrorLine int    ,@comment nvarchar(4000)    ,@ProcedureName varchar (60)    ,@ResponseMessage xml    ,@CustomerCount int    ,@ProductCount int    SET @ProcedureName = 'ManualMessageReceiveOnTarget' BEGIN TRY BEGIN TRANSACTION -- Receive the next available message WAITFOR (    RECEIVE TOP(1)       @xmlmessage_body = CASE WHEN validation = 'X'                               THEN CAST(message_body AS XML)                               ELSE CAST(N'<none/>' AS XML)                          END      ,@message_type_name = message_type_name      ,@conversation_handle = conversation_handle    FROM dbo.RequestQueue ), TIMEOUT 3000 -------------------------------------------------------------------------------- -- If we didn't get anything or if there is an error, bail out -------------------------------------------------------------------------------- IF (@@ROWCOUNT = 0 OR @@ERROR <> 0) BEGIN    ROLLBACK TRANSACTION    GOTO Done END ELSE BEGIN    IF @message_type_name = '//www.wrox.com/MTCustomerInfo/RequstCustomersCount'    BEGIN       --------------------------------------------------------------------------       --- Your code to process the received message goes here.       --------------------------------------------------------------------------       ---Insert what we got into processhistory table.       INSERT dbo.ProcessHistory(Process, StatusDate,  Status, MessageText, SPID, UserName)       SELECT @ProcedureName, GETDATE(),  'Success', @xmlmessage_body, 'ID, SUSER_NAME()          SELECT @CustomerCount = COUNT(*)          FROM AdventureWorks.Sales.Customer          SET @MessageText = 'Response From Server: \\' + @@SERVERNAME                           + ', Database: ' + DB_NAME() + ' ** Total Customers: '                           + CAST(@CustomerCount AS varchar(15)) + ' **'          SET @ResponseMessage = N'<message>'+ N'<![CDATA[' + @MessageText + N']]>' + N'</message>';          SEND ON CONVERSATION @conversation_handle          MESSAGE TYPE [//www.wrox.com/MTCustomerInfo/ResponseTotalCustomers]          (@ResponseMessage)          END CONVERSATION @conversation_handle       END       ELSE       IF @message_type_name = '//www.wrox.com/MTProductInfo/RequestProductsCount'       BEGIN          -------------------------------------------------------------------------- ------          --- Your code to process the received message goes here.          -------------------------------------------------------------------------- ------          ---Insert what we got into processhistory table.          INSERT dbo.ProcessHistory(Process, StatusDate,  Status, MessageText, SPID, UserName)          SELECT @ProcedureName, GETDATE(),  'Success', @xmlmessage_body, 'ID, SUSER_NAME()          SELECT @ProductCount = COUNT(*)          FROM AdventureWorks.Production.Product          SET @MessageText = 'Response From Server: \\' + @@SERVERNAME                           + ', Database: ' + DB_NAME()                           + ' ** Total Products: ' + CAST(@ProductCount AS varchar(15)) + ' **'          SET @ResponseMessage = N'<message>'+ N'<![CDATA[' + @MessageText + N']]>' + N'</message>';          SEND ON CONVERSATION @conversation_handle          MESSAGE TYPE [//www.wrox.com/MTProductInfo/ResponseTotalProducts]          (@ResponseMessage)          END CONVERSATION @conversation_handle       END       ELSE       IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'           OR @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')          -- If the message_type_name indicates that the message is an error          -- or an end dialog message, end the conversation.       BEGIN          END CONVERSATION @conversation_handle          -------------------------------------------------------------------------- ------          --- Your code to handle the error and do some type of notification          ---(email etc) goes here          -------------------------------------------------------------------------- ------          ---Insert what we got into processhistory table.          INSERT dbo.ProcessHistory(Process, StatusDate,  Status, MessageText, SPID, UserName)          SELECT @ProcedureName, GETDATE(),  'Error', @xmlmessage_body, 'ID, SUSER_NAME()       END    END    COMMIT TRAN END TRY BEGIN CATCH    WHILE (@@TRANCOUNT > 0) ROLLBACK TRAN    SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorMessage = ERROR_MESSAGE()                         ,@ErrSeverity = ERROR_SEVERITY(), @ErrorLine = ERROR_LINE()    SET @comment = 'Error Number: '+ CAST(@ErrorNumber AS varchar(25))                 + ', Error Message: ' + @ErrorMessage                 + ' Error Severity: ' + CAST(@ErrSeverity AS varchar(25))                 + ' Line Number: ' + CAST(@ErrorLine AS varchar(25))    ---Insert what we got into processhistory table.    INSERT dbo.ProcessHistory(Process, StatusDate, Status, MessageText, SPID, UserName)    SELECT @ProcedureName, GETDATE(),  'Error', @comment, 'ID, SUSER_NAME() END CATCH Done: 

This code is exactly the same as the one in the stored procedure CheckRequestFromInitiator you created in the SetupTarget.sql script. You don't want to activate the stored procedure now and pick up the message because you won't know what happened. You need to know some things about the script first.

The command to receive a message from a queue is RECEIVE. This is the only way you can retrieve the message from a queue. The RECEIVE command can pull one or more messages from a queue. In this case, the code is only pulling one message at a time, which is why TOP (1) appears in the RECEIVE statement. In the FROM clause of the RECEIVE statement, you have specified the name of the queue RequestQueue. You can also specify a WHERE clause in the RECEIVE statement, but it is not a full-featured WHERE clause. You can only specify a conversation_handle or conversation_group_id.

The basic RECEIVE command returns the messages available at the time the command executes. If the queue is empty, no rows are returned. In some cases, it might be more efficient to wait for messages to appear on the queue, rather than return immediately when the queue is empty. You can use the WAITFOR command with the RECEIVE command to force the RECEIVE command to wait for a message if the queue is empty. The following code shows the WAITFOR command:

    WAITFOR    (       RECEIVE TOP(1)          @xmlmessage_body = CASE WHEN validation = 'X'                                  THEN CAST(message_body AS XML)                                  ELSE CAST(N'<none/>' AS XML)                              END         ,@message_type_name = message_type_name         ,@conversation_handle = conversation_handle       FROM dbo.RequestQueue    ), TIMEOUT 3000 

The TIMEOUT clause specifies how many milliseconds the RECEIVE will wait for a message to appear on the queue before returning. In this case it will wait for three seconds.

The RECEIVE statement has three columns: message_body, message_type_name, and conversation_handle. The message_body contains the actual message, and the message_type_name contains the type of the message sent. If you look at the SEND statement in the SendMessageFromInitiator.sql script, you will see that you have sent the message of the type [//www.wrox.com/MTCustomerInfo/RequstCustomersCount]. That's what you will see in the message_type_name column. The last column is conversation_handle, which the target will use to send the message reply back to the initiator.

After the RECEIVE statement, the code checks the row count to make sure it has received something, or to see whether an error occurred. If it has not received anything, you can take any action you like there. If it has received a message, you want to take some action on that message. You use message_type_name in this case to decide what to do with the message. Because you are sending the message type [//www.wrox.com/MTCustomerInfo/RequstCustomersCount], you'll want to count the number of customers. If you had received the message of type //www.wrox.com/MTProductInfoRequestProductsCount, you would have the count of the products. Now your application may have some complex things to do instead of the simple count(*) you have here. You can call another stored procedure and pass parameters to that stored procedure to execute some tasks. In this case, the code has counted the number of customers, and you are ready to send the reply back to the initiator.

The next command is the SEND, which we have discussed earlier. Notice that here you don't perform the BEGIN DIALOG again. When you sent the message from the initiator, it created a row in the sys.conversation_endpoint view also. Because you are sending a message in the same conversation, you don't need to issue another BEGIN DIALOG, but you do need the conversation handle to send the message, which is why the target received the @conversation_handle in the RECEIVE command. After the SEND statement, you see END CONVERSATION @Conversation_Handle. The END CONVERSATION command ends the conversation, and sends the message of the message type 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' on both sides of the dialog endpoint. In your application you can decide when you want to end the conversation, but it is very important that you do so. Otherwise, you will see thousands of rows in the sys.conversation_endpoints table after your application has run for a while, and that is not good for application performance.

Once the target has received some information, the code has an INSERT statement to insert some information in the ProcessHistory table so you can later look to see what happened. Notice the BEGIN TRAN statement before the target receives the message and the COMMIT TRAN statement after successfully processing the message. You want to do that to maintain transaction consistency in the database. If something bad happens while you are processing the message, you may lose it. Once the message is received from the queue, you cannot put it back without using BEGIN and COMMIT TRAN. Whether you need that in your application or not is up to you when you design the application. If you cannot tolerate losing any messages, then you should consider using BEGIN and COMMIT TRAN.

Run the script ReceiveMessageOnTarget.sql now. After that, run the script ViewInfoOnTarget.sql in TargetDB. The output is shown in Figure 8-5.

Figure 8-5

You probably noticed that after you ran the ReceiveMessageOnTarget.sql script, the message disappeared from the RequestQueue. You will still see a row in the conversation table with a state_desc of CLOSED. The state_desc is CLOSED because after the message was sent, the script issued END CONVERSATION. In addition, the message is inserted into the ProcessHistory table after it was received, so now the message is back on InitiatorDB with the customer count.

Before we receive the message from the queue ReceiveAckError in InitiatorDB, take a look at what's currently in the queue. Open the script ViewInfoOnInitiator.sql and run it. The output is shown in Figure 8-6.

Figure 8-6

Two rows appear in the queue ReceiveAckError in InititatorDB: One is the customer count sent by the TargetDB, and the other is the Service Broker end dialog message. The end dialog message appears because of the END CONVERSATION command you ran on the target after sending the message. Also notice that the state_desc column in sys.conversation_endpoint has the value DISCONNECTED_INBOUND . That's because the 'TargetService' has closed the conversation. You won't see any rows in the ProcessHistory table yet, because you have not received the message from the queue yet.

Now you can receive the message from the ReceiveAckError queue. Open the script ReceiveMessage OnInitiator.sql. You will see that this script is very similar to ReceiveMessageOnTarget.sql. The difference is that the message_type_names you will see in the ReceiveMessageOnInitiator.sql script are '//www.wrox.com/MTCustomerInfo/ResponseTotalCustomers' and '//www.wrox.com/MTProductInfo/ResponseTotalProducts'. In addition, the RECEIVE statement is receiving the message from the ReceiveAckError queue. Now run the script ReceiveMessageOnInitiator.sql. After that, run the script ViewInfoOnInitiator.sql again. Note that you still see a row in the Receive AckError queue; that's because you have only run the ReceiveMessageOnInitiator.sql script once, and it will receive only one message from the queue because you have set TOP (1) in the RECEIVE statement. Run the ReceiveMessageOnInitiator.sql script again, and then run the ViewInfoOn Initiator.sql script one more time. The output is shown in Figure 8-7.

Figure 8-7

Notice that the queue is now empty and that no row exists in the sys.conversation_endpoints table. You have to make sure that you end the conversation. Whenever you end the conversation, a row for that conversation is deleted from the sys.conversation_endpoints table. You will see two rows in the ProcessHistory table, one is the actual message from TargetDB and the other is the result of END CONVERSATION.

You ended the dialog in the script ReceiveMessageOnInitiator.sql, as shown here:

       IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')          -- an end dialog message, end the conversation.       BEGIN          END CONVERSATION @conversation_handle          ---Let's insert what we got into processhistory table.          INSERT dbo.ProcessHistory(Process, StatusDate,  Status, MessageText, SPID, UserName)          SELECT @ProcedureName, GETDATE(),  'END DIALOG', @xmlmessage_body, 'ID, SUSER_NAME()       END 

Run the ViewInfoOnTarget.sql script again. You will notice that in the TargetDB sys.conversation_endpoints table, you still see a row even though you have ended the conversation. Those rows will be deleted by the Service Broker after about 33 minutes.

You have just seen how the stored procedure is activated when a message gets into the queue. Activation is one of the most important things in a Service Broker application. Of course, it is not required, but activation is a tool to make it easier to write services that execute as stored procedures.

Activation

Service Broker activation is a unique way to ensure that the right resources are available to process Service Broker messages as they arrive on a queue. To use activation, you associate a stored procedure with a queue. The following is a section from the script SetupTarget.sql:

 CREATE QUEUE dbo.RequestQueue WITH STATUS = ON     ,RETENTION = OFF     ,ACTIVATION (                  STATUS = ON                 ,PROCEDURE_NAME = dbo.CheckRequestFromInitiator                 ,MAX_QUEUE_READERS = 5                 ,EXECUTE AS SELF                 ) GO 

Similar code exists in SetupInitiator.sql , with a different queue name and PROCEDURE_NAME. One thing we have changed here is to set the STATUS parameter to ON in ACTIVATION. This is how you associate the stored procedure activation with a queue. This means that whenever a message arrives in a RequestQueue queue, an instance of a stored procedure CheckRequestFromInitiator will be started by the Service Broker only if an instance of the stored procedure isn't already running. The ACTIVATION STATUS must be set to ON to activate a stored procedure when a message arrives in the queue.

When a message is written in the queue, the commit process (which writes the message row into the transaction log) checks to see whether a copy of the activation stored procedure is running. If not, the activation logic starts a stored procedure. When the activation logic finds that a stored procedure is already running, it also checks the number of arriving messages against the number of processed messages to determine whether the activated stored procedure is keeping up with the incoming message rate. If the rate of incoming messages is higher, another copy of the stored procedure is started. This will continue until either the activated stored procedures are able to keep up with incoming messages or until the maximum number of stored procedure instances defined by the MAX_QUEUE_READERS parameter is reached. In this case, MAX_QUEUE_READERS is set to five, which mean that no more than five instances of the stored procedure CheckRequestFromInitiator will be started at any given point in time.

If you have five messages in the RequestQueue, will five instances of CheckRequestFromInitiator be started? It depends. When a stored procedure is configured for activation, it creates a queue monitor for the queue. When messages are added to the queue, and after five seconds of inactivity, the queue monitor determines whether a stored procedure should be started. Suppose you sent four messages to the RequestQueue. The Service Broker will start the first instance of the stored procedure CheckRequestFromInitiator. Now there are still three messages waiting in the queue RequestQueue to be processed but the Queue Monitor will not start another copy of CheckRequestFromInitiator unless the first message takes more than five seconds to process. Assume that the first message is going to take more than five minutes; in that case, the Queue Monitor will start another second copy of CheckRequestFromInitiator. To process the third message, it may or may not start another instance of CheckRequestFromInitiator. When the third message is about to be processed, after waiting for five seconds, the Queue Monitor will check whether any of those instances has finished its work; if one has, the Queue Monitor will use whichever instance completed the work. If not, it will start a third instance of CheckRequestFromInitiator.

When stored procedures are started, they are owned by the Queue Monitor. If one throws an exception, the Queue Monitor will catch it and start a new copy. The Queue Monitor also makes sure that when an activated stored procedure is not receiving any messages, no more copies will be started.

You can see all the Queue Monitors configured in a SQL Server instance by running this query:

 SELECT * FROM sys.dm_broker_queue_monitors 

To find out which activated stored procedures are running in a SQL Server instance, execute the following query:

 SELECT * FROM sys.dm_broker_activated_tasks 

So far in this example, you have received the messages from the queue manually by running the ReceiveMessageOnTarget.sql script on the TargetDB, and ReceiveMessageOnInitiator.sql on the InitiatorDB. Now try to receive messages using activated stored procedures. As you just learned, to activate a stored procedure on a queue, you have to associate the stored procedure with the queue, and the ACTIVATION STATUS must be ON. In the scripts SetupInitiator.sql and SetupTarget.sql, you associated the stored procedure with the queue, but the activation was not on, which is why you were receiving the messages manually. At this point, set the ACTIVATION STATUS to ON for both the initiator and target queues.

Open the ActivateSPOnInitiator.sql script and run it. That will set the ACTIVATION STATUS to ON for the ReceiveAckError queue. The stored procedure CheckResponseFromTarget will be activated when a message arrives in the queue.

Next open the ActivateSPOnTarget.sql script and run it. That will set the ACTIVATION STATUS to ON for the RequestQueue. The stored procedure CheckRequestFromInitiator will be activated when a message arrives in the queue.

Now you are ready to send the message. Open the SendMessageFromInitiator.sql script and run it. What happens?

• The script has sent the message from InitiatorDB to TargetDB in the RequestQueue.

• The stored procedure CheckRequestFromInitiator was activated in TargetDB, which received the message and processed it (i.e., counted the number of customers in AdventureWorks.Sales.Customer), sent the response back to InitiatorDB, and then ended the conversation.

• The message came to the ReceiveAckError queue on InitiatorDB, and the stored procedure CheckResponseFromTarget was activated. The stored procedure received the message, put the result in ProcessHistory, and ended the conversation.

You can see the content in the ProcessHistory table on TargetDB by running the script ViewInfoOnTarget.sql, and on InitiatorDB by running the script ViewInfoOnInitiator.sql.

Now take a look at multiple instances of the activation stored procedure in action. Open the CheckRequestFromInitiator.sql script and run it. This is the same stored procedure you created in the SetupTarget.sql script with one small change: a WAITFOR DELAY of 20 seconds has been added:

          IF @message_type_name = '//www.wrox.com/MTCustomerInfo/RequstCustomersCount'          BEGIN             -----------------------------------------------------------------------             --- Your code to process the received message goes here.             -----------------------------------------------------------------------             ---Insert what we got into the processhistory table.             INSERT dbo.ProcessHistory(Process, StatusDate,  Status, MessageText, SPID, UserName)             SELECT @ProcedureName, GETDATE(),  'Success', @xmlmessage_body, 'ID, SUSER_NAME()             SELECT @CustomerCount = COUNT(*)             FROM AdventureWorks.Sales.Customer             SET @MessageText = 'Response From Server: \\' + @@SERVERNAME                              + ', Database: ' + DB_NAME() + ' ** Total Customers: '                              + CAST(@CustomerCount AS varchar(15)) +  ' **'             SET @ResponseMessage = N'<message>'+ N'<![CDATA[' + @MessageText + N']]>' + N'</message>';             WAITFOR DELAY '00:00:20';             SEND ON CONVERSATION @conversation_handle             MESSAGE TYPE [//www.wrox.com/MTCustomerInfo/ResponseTotalCustomers]             (@ResponseMessage)             END CONVERSATION @conversation_handle          END 

We added the delay to simulate the stored procedure doing some work so that when multiple messages arrive in the queue, the Queue Monitor will activate additional stored procedures.

Now open the scripts CheckResponseFromTarget.sql and CheckRequestFromInitiator.sql and run them both. Next, open the SendManyMessages.sql script and run it. This will send four messages to TargetDB. Figure 8-8 shows the output on TargetDB when you run the ViewInfoOnTarget.sql script after all the messages are processed.

Figure 8-8

Notice that the RequestQueue is empty because all the messages are processed. You can also see that all the conversations are CLOSED, because you ended the conversation with the END CONVERSATION statement. If you look at the SPID column in the third result set from ProcessHistory, you will see four distinct SPIDs there, which means that four instances of the stored procedure ran to process those four messages. The StatusDate column is also interesting: Note the delay of about five seconds before the next instance of CheckRequestFromInitiator was activated. That is what the Queue Monitor does.

You will see similar output on InitiatorDB if you run the ViewInfoOnInitiator.sql script.

Note

Because activation procedures run on background threads, there is no user connection on which they can report errors. Therefore, any error or PRINT output for an activated stored procedure is written to the SQL Server error log. When activation doesn't appear to be working, the first thing you want to look at is the SQL error log file.

Another interesting point to note here is that the reason you will see multiple instances of Check RequestFromInitiator is because you have a distinct conversation_handle for each message (see the second result set in Figure 8-8). The reason you have a different conversation handle is because you have issued the BEGIN DIALOG statement for each new message sent to the target. Take a look at the SendManyMessages.sql script:

 WHILE (@Count < @NumberOfMessages) BEGIN    BEGIN TRY          BEGIN DIALOG CONVERSATION @Conversation_Handle          FROM SERVICE [InitiatorService]          TO SERVICE 'TargetService'          ON CONTRACT [//www.wrox.com/CTGeneralInfo/ProductCustomer]          WITH ENCRYPTION = OFF;          SELECT * FROM sys.conversation_endpoints          WHERE conversation_handle = @Conversation_Handle;          ----------------------------------------------------------------          SEND ON CONVERSATION @Conversation_Handle          MESSAGE TYPE [//www.wrox.com/MTCustomerInfo/RequstCustomersCount]          (@SendMessage);          ---------------------------------------------------------------    END TRY    BEGIN CATCH       SELECT ERROR_NUMBER() AS ErrorNumber             ,ERROR_MESSAGE() AS ErrorMessage             ,ERROR_SEVERITY() AS ErrorSeverity             ,ERROR_LINE() AS ErrorLine       BREAK    END CATCH    SET @Count = @Count + 1 END 

The BEGIN DIALOG statement creates a new conversation handle for each message. If you move the BEGIN DIALOG before the WHILE loop, you will get only one conversation handle. In that case, no matter how many messages you send to the target, only one instance of CheckRequestFromInitiator will be created to process all the messages on the same conversation handle.

Conversation Groups

As we just mentioned, in the previous example the SendManyMessages.sql script created a new conversation handle every time you sent the message with the SEND command. This means that only one message is in each conversation group. If you query the sys.conversation_endpoints view after sending a bunch of messages with SendManyMessages.sql, you will notice that the conversation_handle column has distinct values in it. In this case, even though the messages may have arrived in the order they were sent, they will not necessarily be processed in that order. You have just seen that multiple instances of CheckRequestFromInitiator in TargetDB were processing messages, and there is no guarantee that the messages will be processed in the order in which they were received because you indicated that you don't care about the order by sending each message on a different conversation group.

Many messaging applications require that messages be processed in the exact order in which they are received. Although it is reasonably straightforward to ensure that messages are received in order, it is rather more difficult to ensure that they are processed in order. Consider an example in which an order entry application sends messages to credit card validation, inventory adjustment, shipping, and accounts receivable services on four different dialogs. These services may all respond, and it's possible that response messages for the same order may be processed on different threads simultaneously. That could cause a problem because if two different transactions are updating the order status simultaneously without being aware of each other, status information may be lost. To solve this type of problem, the Service Broker uses a special kind of lock to ensure that only one task (one instance of the activation procedure) can read messages from a particular conversation at a time. This special lock is called a conversation group lock.

To see how this works, open the Sample1 solution. Run the scripts CheckResponseFromTarget.sql and CheckRequestFromInitiator.sql. These are the stored procedures that pick up the messages from the initiator and target queues, as explained earlier. Run ActivateSPOnTarget.sql and ActivateSPOnInitiator.sql, which make sure that both queue status and activation are ON. Open the script SendMessageOnSameConvGroup.sql. The code is as follows:

 USE InitiatorDB GO DECLARE @Conversation_Handle UNIQUEIDENTIFIER        ,@SendMessage xml        ,@MessageText varchar(255)        ,@Another_Conversation_Handle UNIQUEIDENTIFIER        ,@conversation_group_id UNIQUEIDENTIFIER SET @MessageText =  'Request From Server: \\' + @@SERVERNAME                   + ', DATABASE: ' + DB_NAME()                   + ': ** Please send the total number of customers.' SET @SendMessage = N'<message>'+ N'<![CDATA[' + @MessageText + N']]>' + N'</message>' BEGIN DIALOG CONVERSATION @Conversation_Handle FROM SERVICE [InitiatorService] TO SERVICE 'TargetService' ON CONTRACT [//www.wrox.com/CTGeneralInfo/ProductCustomer] WITH ENCRYPTION = OFF; ---------------------------------------------------------------- SEND ON CONVERSATION @Conversation_Handle MESSAGE TYPE [//www.wrox.com/MTCustomerInfo/RequstCustomersCount] (@SendMessage); --------------------------------------------------------------- SELECT @conversation_group_id = conversation_group_id FROM sys.conversation_endpoints WHERE conversation_handle = @Conversation_Handle; BEGIN DIALOG CONVERSATION @Another_Conversation_Handle FROM SERVICE [InitiatorService] TO SERVICE 'TargetService' ON CONTRACT [//www.wrox.com/CTGeneralInfo/ProductCustomer] WITH RELATED_CONVERSATION_GROUP = @conversation_group_id     ,ENCRYPTION = OFF; SEND ON CONVERSATION @Another_Conversation_Handle MESSAGE TYPE [//www.wrox.com/MTCustomerInfo/RequstCustomersCount] (@SendMessage); 

This code sends two messages on the same conversation group. When you send the first message, a conversation group is created by default. Before you send the second message, you get the conversation group of the first message from the sys.conversation_endpoints table with the following script:

 SELECT @conversation_group_id = conversation_group_id FROM sys.conversation_endpoints WHERE conversation_handle = @Conversation_Handle; 

Then you send the second message on the same conversation group by using the RELATED_CONVERSATION_GROUP argument in the BEGIN DIALOG statement.

Run the SendMessageOnSameConvGroup.sql script. Now open the script ViewInfoOnInitiator.sql and run it. You will notice that when you run SELECT * FROM ProcessHistory, the SPID column shows you that only one SPID has worked on both response messages, which indicates that the messages on the same convesation group can only be processed by a single thread.

You've now seen a basic example of how messaging works in the Service Broker. We haven't yet sent a message from one SQL Server instance to another, nor have we sent a message with all the security features the Service Broker provides for a secured application. Next, we will first discuss how security works in the Service Broker. Then we look at an example that sends a message from one SQL Server instance to another, which includes details about how to configure security for safe messaging.