After missing for over a year I decided to start blogging again. Much has changed. I no longer work in the SQL Server Engine team on Service Broker. I moved to the Zune team to work on web-services that power the Zune marketplace and have been quite busy shipping the newest line of products and services. Keep tuned!

After receiving several requests, I have put together simple documentation on how to use the External Activator sample here.

If you are wondering if the disappearance of images from my blog had anything to do with moving it back to blogs.msdn.com, you are on the right track. The reason I moved the blog was that my computer which was hosting the blog had a RAID-0 failure causing a lot of data to be lost. While all my articles were backed up using SQL Server backup, the images in the filesystem were not being correctly backed up. The images are lost for good. (There are thumbnails still lingering in Live Search and Google's cache, but I haven't figured out if they store full-sized images and whether those can be recovered). So until I recreate all my artwork, you will have to use the text-only mode.

One of the prime use cases of the CLR integration in SQL Server 2005 is to make outbound HTTP requests to web applications or web services. But how do you do that reliably given the large number of failures that need to be handled ranging from network unavailability, server timeouts, 404 errors, 500 errors, etc. While some of these errors may be transient and we could retry the requests, some may necessitate giving up altogether. This seems like a lot of effort for a user, who just wants to perform a simple web-request.

One solution is to use Service Broker to add reliability. The user can simply begin a dialog and send the request over to a proxy service that handles the task of actually performing the web-request and sending the response back over the dialog. The service could perform all the magic of determining transient errors and retrying requests.

Starting with this idea, I built a sample web proxy service that can be downloaded from the SSB CodeGallery. The sample comprises of a Service Broker service called WebProxyService which exposes a request/response contract. The contract defines custom message types that marshall HTTP requests and responses into XML. Services like the WebClient service can initiate a dialog to the WebProxyService using this custom contract and send HTTP requests over the dialog.

The WebProxyService itself is implemented as an internally activated stored procedure that uses CLR. Its behavior is controlled by two tables – the RequestFilter table and the ResponseFilter table.

Each row in the RequestFilter table (i.e. a request rule) maps a class of incoming requests (based on method and/or URL pattern) to the action to be taken (deny or accept). If the request is to be accepted, additional columns indicate how the service is to behave in case of failures. The columns of the table are as follows:

The service maps incoming requests to rules in the RequestFilter. Exact matches (i.e. both Method and UrlPattern match) are preferred to partial matches. If no rules match or if the rule specifies ‘deny’ action, the service ends the conversation on which the request was received with an error declining the request. If there is a matching ‘accept’ rule, the service makes an outbound HTTP request to the Web server with the timeout specified in the rule. If a response is received, it is matched against the ResponseFilter table.

Each row in the ResponseFilter table maps a class of responses (based on the HTTP status code) to the action to be taken (respond, retry or error). The columns of the table are as follows:

If the matching rule indicates ‘respond’, the service marshalls the incoming HTTP response into the XML format and sends it as a message back on the conversation that had sent the request. If the matching rule indicates ‘error’, the service ends the conversation with an error that wraps the status code and message returned by the Web server. If the matching rule indicates ‘retry’, the service saves the original HTTP request to a table indexed by the conversation handle and begins a timer on that conversation with an appropriate interval (calculated as ‘RetryDelay X BackoffFactor^NumberOfRetries’).

When the timer fires (i.e. sends a dialog timer message to the service), the service loads the saved request from the table and tries it again after incrementing the number of retries. Once the request completes (i.e. either a valid response is returned or the number of retries reach maximum), the conversation is ended and the saved request is deleted from the table.

Security Consideration: SQL allows CLR threads to impersonate the caller (eg> the user that invokes a CLR stored procedure) in order to allow external access under the security context of that user instead of the security context of the service account running sqlservr.exe. However, in the context of internal activation, there is no concept of a caller. There may be no user interactively logged on to the server and hence there is no way to obtain the security context of any appropriate user. Hence CLR impersonation does not work in internally activated stored procedures. Our HTTP requests will have to be made in the security context of the service account running sqlservr.exe (eg> NETWORK SERVICE or LOCALSYSTEM). If the firewall does not allow the service account user to access the network (or the Internet) for making the HTTP requests, this is bound to fail.

Apart from firewall issues, it may not be such a good idea to use a backend database server for making HTTP requests. First, this requires putting the backend in the DMZ thus exposing it to security threats. Second, making a web-request uses up a database thread that could be doing potentially more important data access work. Hence the recommendation is to use a dedicated instance (even SQL express) to host the WebProxyService. This instance can be placed in the DMZ and it could be running using a Windows account with permission to penetrate the firewall.

Following up on my previous post regarding how to talk to partitioned services with data dependent routing, here is another mechanism -- redirection. One of the main drawbacks of having the routing service forward messages to the final target service was that you need to delegate trust to the routing service. The target cannot authenticate the initiators directly and so you lose out on the built-in security model.

The other major drawback is if the system is under sufficient load, the routing service could become a bottleneck since all conversations are routed through it and each message in the system must pass through this service.

Using redirection instead of forwarding solves both these problems. I have uploaded a sample to the CodeGallery to show how to build a routing service that redirects inbound conversations, rather than forwarding incoming messages.

The above diagram illustrates how the redirecting routing service works. The initiator S1 begins a dialog and sends a message to the routing service. The routing service receives the message (1) and applies the classifier function to obtain the service name and broker instance of the service where this conversation must be redirected to (2). It replies on the same conversation D1 with a redirect message containing the service name and broker instance found (3) and ends the dialog on its end. When the initiator S1 receives the reply, it will end the dialog and begin a new dialog D2 targetting the service name, broker instance specified by the redirect message (4). Finally it will resend the original message to the final destination S2.

It is obvious that since S1 is directly talking to S2, S2 can authenticate S1 and apply the right level of access control. Also once the conversation has been bound to final destination, all subsequent messages will be sent directly, thus freeing the routing service from processing them.

The only limitations of this approach are that the initiators must have routes to the targets and vice versa. Also the contracts need to be extended to include the special 'Redirect' message type and the services should be able to handle them appropriately.

One strategy to achieve scale-out is replicating data. Another is partitioning. Each has its own advantages and limitations; and you’d use one over the other depending on the nature of the data itself as well as the access pattern. While Service Broker naturally provides a mechanism for achieving scale-out when the data is replicated, it is also possible to build highly scalable apps that use partitioned data using Service Broker. The key to achieving this lies in data dependent routing, which is the subject of this post.

But before we go into that topic, let us look at what Service Broker offers for talking to replicated services. The main purpose of routing provided by Service Broker is to decouple the target service specification from its location. When initiating a dialog, the target service is specified using the service name and optionally the broker instance of the target broker (i.e. database). Routes are database objects that help the broker determine where to deliver messages. A route maps a service name and optionally the broker instance with the address of a remote SQL Server instance’s endpoint. So routes enable the user to move services from one database to another without requiring the application to be rewritten. A target service specification may match multiple routes, in which case the broker will chose one of them in a round-robin fashion. This policy can help you to replicate services and distribute load across them.

When the data (and hence the services providing an interface to it) are not replicated but partition, the routing infrastructure will not help. In such a case, you would want your message to be routed based on what is inside it. For example, if there is a table of sales transactions partitioned by region across several databases with a service in each database used for querying the sales information; a request for sales info by region should be routed to the service that is servicing that region. Data dependent routing can be easily implemented as a service that either forwards an incoming conversation or redirects the initiating service to the right destination. In this example, we will look at the design and implementation of a forwarding type data dependent routing service. You can obtain the code from the SSB CodeGallery here.

The above figure illustrates how the routing service routes and forwards messages. Lets say a service S3 begins a dialog with the routing service and sends it a message (1). When the routing service receives the message, it checks if a mapping exists for this conversation D1 in its mapping table. If no mapping exists, it will invoke the classifier function with the message body to obtain the target service name (2) and then begin a dialog D4 with that service (3). It then creates mappings both for the inbound as well as the outbound conversations (4) and finally it forwards the received message on conversation D4 (5).

If the target service S4 was to send a reply back on the same conversation D4, the routing service would now find mapping for D4 to D3 and simply forward the reply message on D3 without having to classify.

If the routing service receives an “End Dialog” message, it checks if an outbound conversation exists for this conversation and ends it. In either case it will end the inbound conversation. If the routing service receives an “Error” message, it checks if an outbound conversation exists for this conversation and ends it with an error that wraps the incoming error message. It will also end the inbound conversation.

The data dependent routing facility is implemented as a service called [urn:rushi.desai.name/RoutingService]. It uses a table [resolved_conversations] to store the mapping of inbound to outbound conversations. The service is internally activated to execute the [route_messages] stored proc. The stored proc implements the logic presented above when it receives an “End Dialog” or “Error” message. When it receives any other type of message, it will invoke a configured stored proc for performing the classification. This stored proc name is stored in the [routing_service_config] table. You can implement your custom logic in this stored proc which must have the following signature:

CREATE PROCEDURE your_custom_classifier (

      @message_body VARBINARY(MAX),

      @to_service_name NVARCHAR(256) OUTPUT)

That is, the classifier stored procedure takes in the message body as input and gives the service name as output. You can write the stored procedure in plain T-SQL as well as any CLR language by using SQLCLR hosting and the in-proc managed provider.

I have provided a sample classifier called [sample_classifier] which basically assumes XML message bodies and looks up the target service in the message itself by running an XQuery on /message/toServiceName/.

1. The first assumption is that the routing service supports the union of all the contracts supported by the target services. Also the routing broker must contain all the associated message types.
2. The routing broker (i.e. database) must contain Service Broker routes for all possible target services. So it is basically dependent on dialog level routing to be already setup.

1. The first limitation is the security model. In this example, you are basically delegating the routing service to talk to the final target on your behalf. Hence you lose out on end-to-end security. The target service cannot authenticate and authorize the initiator. If this is a requirement, then it might be simpler to use a redirection approach, rather than a forwarding approach. That is, instead of the routing service forwarding messages on your behalf, it should simply classify the message and reply to the initiator with the service name. The initiator can then go begin a new dialog with the resolved target service. A more elaborate approach that maintains end-to-end security but provides forwarding involves using proxy identities. (But that’ll be a subject of another posting).
2. The routing service itself can become a bottleneck. Solutions include preferring redirection over forwarding (will discuss this shortly) as well as replicating the routing service in multiple instances and then relying on the round-robin route picking policy to balance the load across the routing services (i.e. ‘m’ initiators talk to ‘n’ replicated routers that talk to ‘l’ partitioned targets).

My teammate Remus Rusanu spawned his blog today with a great article on how to write a simple publish-subscribe service in SQL Server 2005 using Service Broker. While SQL Server 2005 does not natively support a pub-sub architecture and the only type of conversations provided by the Service Broker are peer-to-peer dialogs, Remus shows how dialogs are a sufficient primitive to implement other types of communication such as pub-sub. So for those of you who have been asking how to build pub-sub using Service Broker, this is a good place to start.

Remus has been heavily involved with answering posts on the SSB forums and has been contributing to the SSB CodeGallery as well. I'm sure his blog will soon be a useful resource for SSB programmers and his articles will fill the void the Write Ahead Blog has been suffering from due to lack of new ideas :-)

The official documentation for SQL Server 2005 (called 'Books Online') is now available on MSDN. You can read the Service Broker section by clicking on the following link:

    http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx

You can now download the demos used in Gerald and Roger's PDC talk on the Service Broker from SSB CodeGallery. The demos include:

1. Basics - Creating metadata, beginning a dialog, sending a message and receiving a message.
2. CLR Services - Writing a CLR stored proc and activating it internally, writing a CLR application and activating it externally (using the External Activator).
3. CG Locking - Maintaining consistency when concurrent queue readers receive messages from the queue.
4. WCF Channel - Layering the Windows Communication Foundation ("Indigo") on top of Service Broker by implementing  Request/Reply custom transport channels. [This is work in progress and hence we are not releasing this yet].
5. Remote - Setting up endpoints and exchanging service listings so that services hosted in different SQL Server instances can talk to each other securely and reliably.

You implemented a Service Broker service and now want others to be able to talk to it. But they will have to first create your message types and contracts to be able to begin a dialog and send messages to your target service. If their service does not reside in the same SQL Server instance, they will have to setup a broker endpoint and setup adjacent layer security to talk to your endpoint. Next they have to setup routing and dialog level security on their side and hand you the public key certificates and reverse route information so that you can setup routing and dialog level security on your side. All this can be quite tedious and prone to errors as some of you may have experienced with BrokerChallege 0.

To ease this process, we are releasing sample stored procs that can extract all the information needed to talk to your service into an XML file and then on the other side import the service listing XML file into the database. This service listing XML is to Service Broker what WSDL is to Web Services.

The package contributed by my teammate Remus Rusanu below contains two SQL files:

• ServiceListing.Master.sql: Run this in the 'master' database.
• ServiceListing.Database.sql: Run this in the database to export/import service listing.

Example of using the Service Broker Service Listing procedures

Before starting this example, the Service Broker endpoint has to be created and configured before in order for this example to work. If there is no Service Broker endpoint configured and started, one has to be created. Use select * from sys.service_broker_endpoints to verify if an Service Broker endpoint already exists. If no Service Broker exists, use these steps to create an Service Broker endpoint. Our example will use certificate based Service Broker Transport security. For this, a database master key is required in the master database. Here is a script that creates the Service Broker endpoint:

-- 
-- replace <computer_name> with the actual machine name on which this script is run 
-- 
use Master; 
go 

create certificate [<computer_name>] 
	with subject = N'<computer_name>'; 
go 

create endpoint [ServiceBroker]
	state = started as tcp (listener_port = 4022) for service_broker 
	(authentication = certificate [<computer_name>]); 
go

First, the target service host instance administrator creates a database that will host the target service. A database master key is required for dialog security:

create database SampleServiceDb;
go

use SampleServiceDb;
create master key encryption by password = '....';

After that, the target administrator creates the SampleService and prepares it for dialog security:

use database SampleServiceDb;
go

create message type [Sample/Request] validation = well_formed_xml;
create message type [Sample/Reply] validation = well_formed_xml; 
create contract [SampleContract] 
	([Sample/Request] sent by initiator, 
	[Sample/Reply] sent by target);
create queue [SampleQueue]; 
create service [SampleService] on queue [SampleQueue] ([SampleContract]); 
go

exec sp_secure_service [SampleService];

use database SampleServiceDb;
go

declare @service_listing xml;
exec sp_export_service_listing [SampleService], @service_listing output;
exec sp_save_service_listing @service_listing, N'c:\SampleService.ServiceListing.xml';
go

<definition xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" author="REDMOND\rushid" timestamp="2005-09-13T18:35:08.397" version="1.0">
   <message xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" name="Sample/Reply" validation="XML"/>
   <message xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" name="Sample/Request" validation="XML"/>
   <contract xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" name="SampleContract">
      <message xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" name="Sample/Request" sent-by="INITIATOR"/>
      <message xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" name="Sample/Reply" sent-by="TARGET"/>
   </contract>
   <service xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" name="SampleService" broker-instance="FDF5116A-F129-48E6-91BB-C9902ECD4601" public-access="No">
      <contract xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" name="SampleContract"/>
      <certificate xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" issuer-name="SampleService" serial-number="b2 4d 4b 02 93 a7 a8 93 4e c8 24 bd 05 74 6d c0">
         <blob>MIIBszCCARygAwIBAgIQsk1LApOnqJNOyCS9BXRtwDANBgkqhkiG9w0BAQUFADAYMRYwFAYDVQQDEw1TYW1wbGVTZXJ2aWNlMB4XDTA1MDkxMzE4MzQzOVoXDTA2MDkxMzE4MzQzOVowGDEWMBQGA1UEAxMNU2FtcGxlU2VydmljZTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAoEOMkpKAICM168qG5JWXruYnKvEa2saUIxm+OmkE8f5VIthQ4cVaV5adnPKNKjJ/oWYf0v99o82amRNSJqFUCoyxwyNH5A7LCHjNTmIRrZvZb9hxCn66hJhkvZDEWNUFpC4yubokhKaP5gPDE2k8LAVO+KTuqlZi9EPvRcA0Il0CAwEAATANBgkqhkiG9w0BAQUFAAOBgQBFdCGeawEHJg0QD5lLiQ/nzFJfCaRIyc14aZfmr5ywXokMhtmWSzoE7Ty/GfvSXL86JLZsbJ87mAhFhF7px2WGWS0PUCpdE+y84NeZfvq98scnt4XTcjNuhJ46arjKlmX4c4cikp/gfMKmQc+YnzWBtgFolOMo+e7x1HHbTKf6pg==</blob>
      </certificate>
   </service>
   <endpoint xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" machinename="rushi.desai.name" tcp-port="4023" authentication="CERTIFICATE" encryption="NONE, AES" public-access="No">
      <certificate xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/ServiceListing" issuer-name="rushid01 IDENTITY" serial-number="3e e2 04 5d 08 0b f2 b9 44 67 a0 06 ee aa f7 5d">
         <blob>MIIBuzCCASSgAwIBAgIQPuIEXQgL8rlEZ6AG7qr3XTANBgkqhkiG9w0BAQUFADAcMRowGAYDVQQDExFydXNoaWQwMSBJREVOVElUWTAeFw0wNTA5MTMxNjA0MDJaFw0wNjA5MTMxNjA0MDJaMBwxGjAYBgNVBAMTEXJ1c2hpZDAxIElERU5USVRZMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQD6U4JDzBDCPLiGHQ3VFEghiw5Rl72HyHODw6+9Y6JtMdhuB3KDf3liRRV5/YZCK2R97cRJXqnTraR/xW9Jx2H7HSmr2nB5NoEF3k/bDeekcrfOHLCggYiyDdVZQXkp7g9Ak7elDjYhjyaYN6CBTGJI+OLlvxSObjXgNWjuD/OD9wIDAQABMA0GCSqGSIb3DQEBBQUAA4GBAN1xjIRYv3sPLX1M91OofdMwG5yQiqOoxIfbdqPwgGwAR4hlJ7zWXncT/UEeBddg6QeNsR2PaMzWW/ZhfI78ji+IMMmmthV9avvZu8AkdaO2fSjiYB54wguksi4SyZAMo5O9gAxOsQs51zPtBYiEz0FQJ+q01GzKhlGpJ/0cyqzk</blob>
      </certificate>
   </endpoint>
</definition>

create database InitiatorServiceDb;
go

use InitiatorServiceDb;
create master key encryption by password = '....';
go

The initiator admin can now import the service listing received from the target service administrator:

use InitiatorServiceDb;
go

declare @service_listing xml;
exec sp_load_service_listing N'c:\SampleService.ServiceListing.xml', @service_listing output;
exec sp_import_target_service_listing_at_initiator @service_listing, [SampleServiceOwner];
go

use InitiatorServiceDb;
go
	
select * from sys.service_message_types;
select * from sys.service_contracts;
select * from sys.routes;
select * from sys.database_principals;
select * from sys.certificates;
select * from sys.remote_service_bindings;
go

In addition, if this is the first service listing received from the SQL instance that hosts the target service, the Service Broker transport security also has to be imported:

--
-- replace <target-service-computer-name> with the actual name of the machine hosting the target service
--

use InitiatorServiceDb;
go

declare @service_listing xml;
exec sp_load_service_listing N'c:\SampleService.ServiceListing.xml', @service_listing output;
exec master..sp_import_service_listing_grant_connect_on_endpoint @service_listing, '....', [<target-service-computer-name>];
go

The initiator administrator can now create a service that will be used to begin the dialogs with the target service:

use InitiatorServiceDb;
go

create queue [InitiatorQueue];
create service [InitiatorService] on queue [InitiatorQueue];
go

exec sp_secure_service [InitiatorService];
go

Following up on my article titled "Activating queue reader processes on demand", we are launching the External Activator sample. This sample demonstrates how you can build scalable Service Broker applications as executable processes that may be started on demand. The External Activator runs as an NT service and automatically starts a service program (i.e. an EXE that you configure) to read messages from a queue as messages arrive. If the messages arrive quicker than they are being handled by the service program, the External Activator will launch additional instances of the service program upto the configured limit. Service programs normally terminate when they find there are no messages in the queue. Hence, when the arrival rate drops, the number of active queue readers will automatically go down.

The External Activator relies on the “QUEUE ACTIVATION” event notification. It connects to a database and waits for event notification messages to arrive on its queue. Services that require external activation are setup to deliver queue activation events to the External Activator service. When messages arrive on your service’s queue, the queue activation event is fired and a notification message will be delivered to the External Activator service. The External Activator responds to the message by looking at the max number of processes currently running and launching additional instances if required. The External Activator is also resilient to abrupt process shutdown. If it was to die, it has its own recovery mechanism to come back up and restart activating processes.

I thank Ivan Trindev, my teammate and developer on the Service Broker team, for contributing this sample. You can download the binaries and source from the SSB CodeGallery.

Over the past week I have been planning to launch the first open to public Service Broker. At the same time, my dev team was having a discussion regarding how we can do something cool and interesting to get the community involved in our product. So I decided to setup simple services on my public Service Broker to let people tinker around with it. This is the zeroth in a series of BrokerChalleges; zero since it requires hardly any skill, just the motivation to play with cool technology.

My service broker endpoint is hosted at the location tcp://rushi.desai.name:4022 and uses certificate based authentication with this certificate identity (public key) and supports AES encryption. In this challenge you will have to attempt to setup your own endpoint and then send a message to the CommentService hosted by my broker at this endpoint. If you are successful, your comment will appear here:

Broker Hall of Fame

[I wanted to embed an IFRAME object here but CommunityServer::Blogs would not let me!]

I am not going to provide any T-SQL snippets here and you will have to figure that out yourself by referring to the documentation (Books Online).

1. Install SQL Server 2005 June CTP: While it should work with the April CTP as well, I haven't tested it. If you want to upgrade or if you don't have a SQL Server on your box, you can download the brand new CTP from here.
2. Create a Service Broker endpoint: Next step is to create a service broker endpoint. The endpoint listens for incoming TCP connections on the port you specify (default is 4022). It should use certificate authentication and you will require to create a certifcate for owner dbo in master. If you have a firewall, you may need to punch a hole in that. My endpoint supports only AES encryption, so if you make encryption 'required', make sure to use AES as well.
3. Grant access to my SQL Server instance to connect to yours: Next you will have to grant access to my SQL Server instance to connect to your endpoint. You can do that be granting connect permission on the endpoint to public, or by creating a login and user in master, adding this certificate to that user and granting connect permission on your endpoint to that user.
4. Create a database: Create a new database (or use a pre-existing one with broker enabled). Create a database master key so that you can use secure dialogs in the future. We will not be doing that now.
5. Create service broker objects: You will need to create a queue and then create the initiating service on this queue in your new database. You will use this service to begin a dialog with my 'CommentService' service and send a message. In order for acknowledgments and reply messages to get back, my Service Broker will require a route to your service. But since I do not know that in advance, you should use a fully-qualified URL as your service name (eg> [tcp://myhost.microsoft.com:4022/CommentClient]). The 'CommentService' serves the [http://rushi.desai.name/public/PostCommentContract] which you must use to post your comments. This contract consists of just one message type, namely [http://rushi.desai.name/public/AddCommentMessage] which is sent by initiator. This message type only accepts valid XML that conforms with the following schema: (Don't worry, e-mail address is for my reference and won't be published)

   <?xml version="1.0" encoding="utf-8"?> <xs:schema elementFormDefault="qualified"
   xmlns:xs="http://www.w3.org/2001/XMLSchema">
       <xs:element name="comment">
           <xs:complexType>
               <xs:sequence>
                   <xs:element name="senderName" type="xs:string"
   minOccurs="1" maxOccurs="1" />
                   <xs:element name="emailAddress" type="xs:string"
   maxOccurs="1" minOccurs="1" />
                   <xs:element name="body" type="xs:string"
   maxOccurs="1" minOccurs="0" />
               </xs:sequence>
           </xs:complexType>
       </xs:element>
   </xs:schema>
   

6. Create a route to the 'CommentService': Finally you need a route that tells your broker how to get to 'CommentService'. You know the address of my endpoint so this should be fairly straightforward.
7. Setup dialog security: I'll relieve you off this for now by turning off dialog security.
8. Now lets start talking: At this point you are done with all the DDL steps. It's time to fire off some DML. Begin a dialog from your newly created service to the 'CommentService' on the [http://rushi.desai.name/public/PostCommentContract]. And send a message of type [http://rushi.desai.name/public/AddCommentMessage] with a well-formed valid message body. You can briefly describe yourself in the message body.
   
   For example:
   
   <comment>
       <senderName>Michael Nelson</senderName>
       <emailAddress>micn@invalid.domain</emailAddress>
       <body>Hi! I am a developer in the Service Broker team. Since there was no disclaimer that team members cannot participate in the BrokerChallenges, I took the opportunity of making myself famous by sending this message.</body>
   </comment>

When I get the message, it will activate a stored proc which receives the message, adds the comment to a table and ends the conversation. Entries from the table is displayed in the hall of fame link. If you successfully send me a message and I receive it, you will get an EndDialog message in your initiator queue. If you don't, check sys.transmission_queue to see if the comment message is still stuck there and what is the reason for that. You could also look at ERRORLOG and the NT event log for clues. You can ask questions in the comments below.

[Attachment: ShippingRowsets.zip]

While there are technologies such as log shipping, SQL Server Replication Service and database mirroring available for various scenarios that require maintaining a copy of the data in a secondary source, several customers have asked how they could use Service Broker for securely and reliably sending tables or rowsets from one database to another. Since there were no examples available for demonstrating how this task can be performed I decided to cook up a very simple sample. In this sample, we will use the FOR XML clause to convert a rowset into an XML document, send it from one database to another (which could be possibly residing on a different SQL Server instance and on anoher machine) using Service Broker, receive the XML message at the target and convert it back to a rowset using OPENXML to insert it into a table. What is really cool about this sample is that it is entirely written in T-SQL and does not require any magic from CLR. Also this is a very basic example and does not fully demonstrate the power of nested FOR XML queries and XQuery which are both available in SQL Server 2005.

The database holding the original table is called primary_db and it contains a table called contacts which looks as follows:

We can obtain an XML document of this table by using the FOR XML clause with the SELECT statement as follows:

   SELECT * FROM CONTACTS FOR XML AUTO, TYPE, ROOT('phoneBook')

The result looks as follows:

We can create an XML valued variable in T-SQL and set this to the XML above as follows:

   DECLARE @body AS XML;
   SET @body = (SELECT * 
                FROM CONTACTS 
                FOR XML AUTO, TYPE, ROOT('phoneBook'));

The XML document can be shipped from one database to another database using Service Broker as shown in this schematic diagram:

We can use this XML variable as the message body in the SEND statement to send the XML dataset from the initiating service in the local database to the target service in some remote database as follows:

   DECLARE @dh UNIQUEIDENTIFIER;
   BEGIN TRANSACTION;
   BEGIN DIALOG @dh
      FROM SERVICE replication_client
      TO SERVICE 'replication_service'
      ON CONTRACT replication_contract;
   SEND ON CONVERSATION @dh MESSAGE TYPE insert_rowset
      (@body);
   COMMIT;

On the receiver's side, we can convert the XML message back into a rowset and insert the rows into a table as follows:

   DECLARE @body XML;
   DECLARE @docHandle INT;
   BEGIN TRANSACTION;
   RECEIVE TOP(1) @body=message_body FROM service_queue;
   EXEC sp_xml_preparedocument @docHandle OUTPUT, @body;
   INSERT INTO contacts
      SELECT * FROM OPENXML(@docHandle, N'/phoneBook/contacts')
      WITH CONTACTS;
   EXEC sp_xml_removedocument @docHandle;
   COMMIT;

The attached file (see top of the post) contains T-SQL scripts for creating two databases -- [primary_db], the database containing the original [contacts] table and [secondary_db], the database containing the replicated contacts table. [secondary_db] contains a service called [replication_serivce] that implements the [replication_contract]. The queue bound to the replication_service has an internally activated stored proc that receives messages and if they are of the [insert_rowset] message type then it converts the XML body into a rowset and inserts it into the [contacts] table. The [primary_db] database also contains a stored proc called [sp_ship_new_rows] that looks for new rows added since the last time rows were shipped and sends them to the [replication_service] service from a local service called [replication_client].

Exercise: The internally activated stored proc is not too falt tolerant. For example, if the incoming message contains duplicate rows, the INSERT would fail resulting in the stored proc to be rolledback. Modify the stored proc to handle such exceptions and error the dialog if caught.