Service Broker - the asynchronous messaging framework in SQL Server 2005

The SQL Server 2005 release consists of several new features and enhancements inside the database engine. One of these new features is Service Broker, an asynchronous messaging framework that is directly integrated into the database engine. In this article I will give you a brief overview of Service Broker and its underlying messaging architecture, and show you how you can write Service Broker applications with the integration of the SQLCLR in a .NET language of your own choice.

Service Broker Architecture

Before I dig into the technical details of combining Service Broker and the SQLCLR, I want to explain the architecture behind a Service Broker application. The core concept here is a conversation, a reliable, ordered exchange of messages between two Service Broker services. The Service Broker architecture defines two kinds of conversations:

• Dialog: A dialog is a two-way conversation between exactly two Service Broker services. There are services on both the sending (the initiator) and the receiving (the target) side of a dialog. The initiator service starts a new dialog and sends the first message to the target service. Both services can then exchange messages in either direction.
• Monolog: A monolog is a one-way conversation between a single publisher service and several subscriber services. This is a reliable version of the popular publish-subscribe paradigm. Currently, monologs are not supported in Service Broker. They may be included in a future version of SQL Server.

Dialogs are bidirectional conversations between two Service Broker services. Each dialog follows a specific contract. A dialog offers a lot of features, like guaranteed delivery, exactly-once delivery, in-order delivery, and persistence. Figure 1 illustrates a Service Broker dialog.

Figure 1: A Service Broker dialog

As you can see from Figure 1, a Service Broker application consists of a Service Broker Service. A Service Broker service is a named endpoint to which messages from other Service Broker services are sent. A Service Broker service itself is a native SQL Server object but has also direct links to other Service Broker objects. A Service Broker service is a composition of the following four objects:

• Message types
• Contracts
• Queues
• Service program

Message Types, contracts and queues are implemented as native SQL Server objects, while service program can be implemented internal (as a stored procedure) or external (as a separate application). Their relationship is shown in Figure 2.

Figure 2: Service Broker Service Anatomy

When you create a new Service Broker service, all of these objects must be created and configured properly and linked together so that a new Service Broker service is created. A Service Broker service is always defined in the context of a SQL Server database. But Service Broker doesn’t restrict where these services are deployed. Service Broker supports the following deployment scenarios:

• Both services are deployed in the same SQL Server database.
• Each service is deployed in a separate SQL Server database located on the same SQL Server instance.
• Each service is deployed in a separate SQL Server database located in another SQL Server instance on a different SQL Server.

The good thing about the Service Broker programming model is that you don’t have to know during the development how your Service Broker services are deployed across your company. To the programming model, it is completely transparent if the service is running in the same SQL Server database or on a SQL Server running in another country connected through the Internet.

Developing Managed Service Broker applications

When you write the core logic of a Service Broker application (the Service Broker service program), you have two choices: you can implement it with pure T-SQL stored procedures or you can use the features of the SQLCLR to develop a managed stored procedure. Microsoft ships a managed assembly with SQL Server 2005 that implements an object model around Service Broker. This managed assembly provides you several classes that encapsulate all well-known Service Broker objects, like message types, queues, etc. These classes generate the needed T-SQL statements for Service Broker.

The managed assembly for Service Broker objects is implemented in a C# Visual Studio 2005 solution called ServiceBrokerInterface, which you can find in the Samples directory within the SQL Server 2005 installation path. One feature of the managed assembly is that it lets you write an activated managed stored procedure for Service Broker. You must deploy the managed stored procedure to SQL Server 2005, to be able to use it in a Service Broker application. It executes automatically when a new message arrives in the specified queue through the SQLCLR mechanism available in SQL Server 2005.

Table 1 lists the Service Broker objects used in this sample. Please make sure to create them with the enclosed T-SQL script.

Table 1: Service Broker objects
Object Type	Name
Message Type	[http://ssb.csharp.at/VSJ/RequestMessage]
Message Type	[http://ssb.csharp.at/VSJ/ResponseMessage]
Contract	[http://ssb.csharp.at/VSJ/HelloWorldContract]
Queue	InitiatorQueue
Queue	TargetQueue
Service	InitiatorService
Service	TargetService

Let’s have a look at the steps you must follow for using this managed assembly for a target service. First of all, you must define your Service Broker service in a class derived from the Service base class:

public class TargetService : Service
{
	public TargetService(
		SqlConnection Connection) :
		base(“TargetService”,
		Connection)
	{
		WaitforTimeout =
			TimeSpan.FromSeconds(1);
	}
}

As you can see from the previous code, you must call the base class constructor in the class constructor and pass the service name as an argument. With the supplied service name the managed assembly is able to retrieve the associated queue object from the database.

After you have created the derived service class, you are able to implement the entry point of your managed stored procedure. The following code snippet shows the basic skeleton of an entry point that you can use in every managed stored procedure written with the managed assembly:

public class TargetService : Service
{
	public static void
		ServiceProcedure()
	{
		Service service = null;
		SqlConnection cnn = null;
		try
		{
// Open the database context connection
			cnn = new SqlConnection(
			“context connection=true;”);
			cnn.Open();
// Instantiate the Service
// Broker service “TargetService”
			service =
				new TargetService(cnn);
			service.FetchSize = 1;
// Run the message loop of the service
			service.Run(true, cnn, nul);
		}
		catch (ServiceException ex)
		{
			if (ex.Transaction != null)
				ex.Transaction.Rollback();
		}
		finally
		{
			if (cnn != null)
				cnn.Close();
		}
	}
}

As you can see, you have to open a new context connection to SQL Server 2005. After you acquire the context connection, you have to create a new instance of your service class – in this case, the TargetService class. After you set the properties of the service class, you call the Run method of the service class to start the message-processing logic.

After you implement the entry point for your managed stored procedure, you have to write the methods that process your different supported Service Broker message types. Because of the design of the managed assembly, you must write a separate method for each message type you want to handle. You must decorate each of these methods with the [BrokerMethod] attribute. This attribute associates a message type with a method. In other words, it ensures that the method is called when the message type – defined with the [BrokerMethod] attribute – is received on the service queue. The following code fragment shows how it is done:

public class TargetService : Service
{
	[BrokerMethod(
		“http://ssb.csharp.at/
		VSJ/RequestMessage”)]
	public void ProcessRequestMessage(
		Message ReceivedMessage,
		SqlConnection Connection,
		SqlTransaction Transaction)
	{
// Create the response message
		MemoryStream body =
			new MemoryStream(
			Encoding.ASCII.GetBytes(
			“<HelloWorldResponse>” +
			“Hello world from a “ +
			“managed stored procedure “+
			“activated by Service “ + 		“Broker.” +
			“</HelloWorldResponse>”));
		Message msgSend = new Message(
			“http://www.csharp.at/” +
			“VSJ/ResponseMessage”,
			body);
// Send the response message back
	ReceivedMessage.Conversation.Send(
	msgSend, Connection, Transaction);
		}
	[BrokerMethod(Message.ErrorType)]
	public void EndConversation(
		Message ReceivedMessage,
		SqlConnection Connection,
		SqlTransaction Transaction)
	{
	ReceivedMessage.Conversation.End(
			Connection, Transaction);
	}
}

After you implement all the necessary methods, you can deploy the assembly to your SQL Server 2005 database. You can start this task automatically from Visual Studio 2005, or start the deployment directly from Microsoft SQL Server Management Studio with the T-SQL statement CREATE ASSEMBLY:

-- Add the assembly file to the database
CREATE ASSEMBLY BackendServiceAssembly
FROM ‘c:\BackendService.dll’
GO

-- Add the debug information to the
-- assembly
ALTER ASSEMBLY BackendServiceAssembly
ADD FILE FROM ‘c:\BackendService.pdb’
GO

When you import your managed assembly to your database, the referenced ServiceBrokerInterface assembly is also deployed to the database automatically. As soon as you register the managed assembly in your database, you can register the managed stored procedure:

-- Register the stored procedure
-- written in managed code
CREATE PROCEDURE
ProcessRequestMessagesManaged
AS
EXTERNAL NAME
BackendServiceAssembly.BackendService.
	TargetService.ServiceProcedure
GO

When you have registered the managed stored procedure in the database, you can change the activation for the TargetQueue to use it for message processing:

-- Use the managed stored
-- procedure for activation
ALTER QUEUE TargetQueue
WITH ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME =
	ProcessRequestMesagesManaged,
MAX_QUEUE_READERS = 5,
EXECUTE AS SELF
)
GO

When you now send a new message from your initiator service to your target service, the message gets automatically processed on the target service by your managed stored procedure, and a response message is sent back to the initiator service:

BEGIN TRY
	BEGIN TRANSACTION;
		DECLARE @ch UNIQUEIDENTIFIER
		DECLARE @msg NVARCHAR(MAX);
		BEGIN DIALOG CONVERSATION @ch
			FROM SERVICE
			[InitiatorService]
			TO SERVICE ‘TargetService’
			ON CONTRACT
			[http://ssb.csharp.at/VSJ/
				HelloWorldContract]
			WITH ENCRYPTION = OFF;
			SET @msg =
				‘<HelloWorldRequest>
					Klaus Aschenbrenner
				</HelloWorldRequest>’;
			SEND ON CONVERSATION @ch
			MESSAGE TYPE
			[http://ssb.csharp.at/VSJ/
				RequestMessage] (@msg);
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO

You might now be wondering if the managed assembly also includes functionality for sending Service Broker messages from the initiator service to a target service (as you have seen in the previous T-SQL code). And yes, you are right, there is support for this scenario. Let’s have a look on the steps you must follow when sending a new Service Broker message from a managed Service Broker client.

In the first step you have to open a new connection to your database, where the client side of your Service Broker application is deployed (the initiator service). After the connection has opened, you have to start a new transaction: public static void Main() { SqlConnection cnn = null; SqlTransaction tran = null; TextReader reader = null; try { // Opening a new database connection cnn = new SqlConnection( “Initial Catalog= VSJ_ManagedServiceBroker; Data Source=localhost; Integrated Security=SSPI;”); cnn.Open(); // Starting a new database connection tran = cnn.BeginTransaction(); ... } } After the database transaction is started, you have to create a new Service class object that represents the initiator service:

// Create a new service object
Service initiatorService = new
	Service(“InitiatorService”,
	cnn, tran);
initiatorService.FetchSize = 1;

In the next step you have to begin a new dialog with the other Service Broker service, in this case with the TargetService:

// Begin a new dialog with the service
// TargetService
Conversation dialog =
	initiatorService.BeginDialog(
	“TargetService”, null,
	“http://ssb.csharp.at/VSJ/
	HelloWorldContract”,
	TimeSpan.FromMinutes(1), false,
	cnn, tran);

Finally you have to send a message over the previous opened dialog, and commit the database transaction:

// Create a new request message
Message request = new Message(
	“http://ssb.csharp.at/VSJ/
	RequestMessage”,
	null);
// Send the message over the newly
// created dialog
dialog.Send(request, cnn, tran);
// Commit the transaction
tran.Commit();

As soon as you have committed the transaction, Service Broker sends the message in a reliable and secure way to the TargetService, where it gets processed by the ProcessRequestMesagesManaged managed stored procedure, you have written previously. Easy, isn’t it?

---

Klaus Aschenbrenner works as a Software Architect for Data Management Solutions vendor ANECON in Vienna, Austria. He currently travels around the world teaching clients the core concepts of SQL Server 2005, and is the author of “Pro SQL Server 2005 Service Broker”, published by Apress (ISBN 1590598423). See his weblog.