Write Ahead Blog

hello again

Rushi Desai — Sat, 17 Nov 2007 00:40:00 GMT

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!

External Activator HOWTO

Rushi Desai — Tue, 26 Sep 2006 01:36:12 GMT

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

Where did all the images go?

Rushi Desai — Mon, 25 Sep 2006 23:01:00 GMT

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.

Reliably making a web-request from the database

Rushi Desai — Wed, 19 Apr 2006 18:22:00 GMT

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:

RequestFilterID	Identifier for the rule
Method	HTTP method used in the request – GET, POST, etc
UrlPattern	Regular expression to match URL with
Timeout	Amount to wait for HTTP response from target server before failing and retrying
NumberOfRetries	Number of times to try before giving up and failing the request
BackoffFactor	Factor to multiple the Timeout with for each retry. (eg> if RetryDelay = 4 seconds and BackoffFactor is 1.5, we will retry after 4, 6, 9, etc seconds)
RetryDelay	Number of seconds to wait before next retry
Action	0 indicates DENY, 1 indicates ACCEPT

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:

ResponseFilterID	Identifier for the rule
StatusCodeLower	Lower bound of the status code interval
StatusCodeUpper	Upper bound of the status code interval
Action	0 indicates RESPOND, 1 indicates RETRY and 2 indicates ERROR

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.

App Paritioning: Data Dependent Routing using Redirection

Rushi Desai — Wed, 28 Dec 2005 09:37:00 GMT

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.

App Partitioning: Data Dependent Routing and Forwarding

Rushi Desai — Thu, 22 Dec 2005 16:32:00 GMT

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.

Design

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.

Implementation

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/.

Assumptions

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.
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.

Limitations

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).
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).

Remus joins the blogosphere

Rushi Desai — Tue, 13 Dec 2005 14:59:00 GMT

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 :-)

Service Broker official documentation on SQL Server 2005 Books Online

Rushi Desai — Sat, 05 Nov 2005 08:42:00 GMT

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

[PDC 2005] DAT303 SQL Server 2005: Building Distributed, Asynchronous Database Applications with the Service Broker

Rushi Desai — Thu, 15 Sep 2005 04:08:00 GMT

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

Basics - Creating metadata, beginning a dialog, sending a message and receiving a message.
CLR Services - Writing a CLR stored proc and activating it internally, writing a CLR application and activating it externally (using the External Activator).
CG Locking - Maintaining consistency when concurrent queue readers receive messages from the queue.
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].
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.

Exporting and importing service listings into a database

Rushi Desai — Wed, 14 Sep 2005 11:52:00 GMT

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 = '....';

The administrator now has to install the Service Listing procedures in the SampleServiceDb. The ServiceListing.Database.sql script has be executed in the SampleServiceDb database.

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];

Then the administrator exports the service listing for this service:

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

The service listing was saved in the file c:\SampleService.ServiceListing.xml and it will contain an XML document similar to the following:

<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>

The file contains all the information needed for a different host to begin dialogs and send requests to this service. Note that there is no secret information in the service listing. The service administrator now sends this information to the administrator of the initiator host. With the service listing received from the target, the initiator admin can start setting up his site for a secure dialog with the target service. First, he creates a database to host the initiator service. A database master key is required for dialog security:


create database InitiatorServiceDb;
go

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

The initiator administrator now has to install the Service Listing procedures in the InitiatorServiceDb. The ServiceListing.Database.sql script has be executed in the InitiatorServiceDb database.

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

The import has created the message types, the contract, a route to the target SampleService, a remote service binding, a user that locally represents the owner of the target SampleService and the target SampleService certificate was extracted from the listing and imported into the database. The administrator can now verify that all these completed succesfully:

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

The initiator service admin can now export the initiator service listing and send it to the target service admin. This step is needed for two reasons:

the target service administrator needs to grant SEND permission on the target service to the initiator service

the target service database must contain a route back to the initiator database, so that acks reply messages can arrive back at the initiator

First, the initiator admin exports the initiator service listing:

use InitiatorServiceDb;
go

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

The resulted listing is saved in the c:\InitiatorService.ServiceListing.xml file. The initiator service administrator sends this listing to the target service administrator. The target service administrator can import this listing:

use SampleServiceDb;
go

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

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

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

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

The two systems are now ready to exchange messages.

Service Broker Service Listing procedures reference

sp_secure_service

sp_secure_service (
	@service_name as SYSNAME)

This utility stored procedure creates a certificate for the service owner. Should be run by both the initiator and target service administrators. It must be run BEFORE exporting the service listing.
Parameters:

@service_name: the name of the service to be secured

Created by: ServiceListing.Database.sql

sp_export_service_listing

sp_export_service_listing (
	@service_name as SYSNAME,
	@service_listing as XML OUTPUT)

Will export all the necessary info, including the required certificates, the routing info and the supported contracts/message types for the exported service. A Service Broker endpoint must be configured in order to export the needed routing info.
Parameters:

@service_name: the name of the service to be exported

@service_listing: output, the service listing XML document

Created by: ServiceListing.Database.sql

sp_import_target_service_listing_at_initiator

sp_import_target_service_listing_at_initiator (
	@service_listing as XML,
	[@proxy_user_name as SYSNAME],
	[@request_anonymous as BIT]),

Imports, at the initiator site, a service listing created for a target service. Should be used by the initiator service administrator. It will create the routing info needed to send messages to the target service. It will import the security info needed for targeting the service (the RSB). It will import the contracts and message types used by the target service.
Parameters:

@service_listing: the target service listing

@proxy_user_name: local name to be used for the target service owner proxy user

@requestanonymous: flag to request an anonymous configuration. It cannot be honored if the target service does not accept public access.

Created by: ServiceListing.Database.sql

sp_import_initiator_service_listing_at_target

sp_import_initiator_service_listing_at_target (
	@service_listing,
	@targeted_service,
	[@proxy_user_name])

Imports, at the target site, a service listing created by an initiator. Should be used by the target service administrator. It will import the routing information needed to send back replies to the initiator and the security information needed to grant SEND permission to the initiator service owner. If the initiator service listing does not contain a certificate, no SEND persmission is granted. If anonymous security is desired, the target service administrator should grant SEND permissions on the targeted service to [Public].
Parameters:

@service_listing: the initator service listing

@targeted_service_name: the service targeted by the initiator. SEND permission will be granted on this service to the proxy user representing the initiator service owner.

@proxy_user_name: name to be used for the proxy user representing the initiator service owner.

Created by: ServiceListing.Database.sql

sp_save_service_listing

sp_save_service_listing sp_save_service_listing (
	@service_listing as xml,
	@filename as nvarchar(256)

Saves a service listing to a file. Size of service listing is limited to 8000 bytes Use sp_export_service_listing to create the service listing The file is overwritten if already exists.
Parameters:

@service_listing: the service listing

@filename: the fully qualified name of the file

Created by: ServiceListing.Database.sql

sp_load_service_listing

sp_load_service_listing (
	@filename as nvarchar(256),
	@service_listing as xml output)

Loads a service listing to a file. Size of service listing is limited to 8000 bytes.
Parameters:

@filename: the fully qualified name of the file

@service_listing: output, the service listing

Created by: ServiceListing.Database.sql

sp_import_service_listing_grant_connect_on_endpoint

sp_import_service_listing_grant_connect_on_endpoint(
	@service_listing as xml,
	@loginpassword nvarchar(max),
	@proxyinstanceuser sysname = NULL,
	@dropexisting as bit = 1)

Imports a proxy user for a service host machine and grants CONNECT permission on the broker endpoint. Both initiator service administrator and target service administrator need to do this step. The initator service listing needs to be imported by the target and vice-versa.This step needs to be done only once for each pair of machines. A proxy login will be created to represent the peer service host machine. This login will be granted CONNECT permission on the broker endpoint. Only CERTIFICATE authentication option is supported by this procedure.
Parameters:

@service_listing: the service listing

@loginpassword: a password is required for the proxy login

@proxyinstanceuser: the local name for the proxy user

@dropexisting: flag to control the dropping of already existing proxy login, user and certificate

Created by: ServiceListing.Master.sql

You can download the scripts from SSB CodeGallery.