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.
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
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.
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’ GOWhen 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 GOWhen 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 ) GOWhen 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 GOYou 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.
Comments