SQL Server 2008 - Using Message Forwarding

I’ve shown several scenarios where a Service Broker forwarding service would make sense and provide you with greater flexibility. Now I want to show you how to activate and configure message forwarding. It’s then completely up to you which scenario you want to support with message forwarding, because the required configuration steps are always the same:

1. Activate message forwarding.
2. Set up transport security.
3. Route configuration.

Let’s have a detailed look at each of these three steps.



Activate Message Forwarding
In this message-forwarding example, I want to implement a scenario where ClientService communicates through a forwarding service with OrderService. First, you have to set up the message-forwarding service. In general, you configure message forwarding at a Service Broker endpoint. Because you’re hosting the message-forwarding service on a separate instance of SQL Server 2008 (this could even be a SQL Server Express instance), you have to create a new Service Broker endpoint and configure it for message forwarding.


Setting Up Message Forwarding
USE master
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password1!'
GO

CREATE CERTIFICATE MessageForwardingServiceCertPrivate
WITH SUBJECT =
'For Service Broker authentication - MessageForwardingServiceCertPrivate',
START_DATE = '01/01/2007'
GO

CREATE ENDPOINT ForwardingServiceEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4740
)
FOR SERVICE_BROKER
(
AUTHENTICATION = CERTIFICATE MessageForwardingServiceCertPrivate,
MESSAGE_FORWARDING = ENABLED
)
GO

BACKUP CERTIFICATE MessageForwardingServiceCertPrivate
TO FILE = 'c:\MessageForwardingServiceCertPublic.cert'
GO

The complete infrastructure needed for message forwarding is configured inside the master database. You don’t need to create another database, because you don’t have to host a Service Broker service. The only thing needed for message forwarding is a Service Broker endpoint. Message forwarding is activated on the Service Broker endpoint with the MESSAGE_FORWARDING parameter.


Parameters for Message Forwarding
MESSAGE_FORWARDING = { ENABLED | DISABLED }
ENABLED specifies that message forwarding
is activated on this Service Broker endpoint.

MESSAGE_FORWARDING_SIZE = forward_size
forward_size specifies the maximum
amount of memory (in megabytes) to be
used by the endpoint when storing forwarded
messages.



Setting Up Transport Security
Because the forwarding endpoint establishes a TCP connection with both the initiator service and the target service, you must also configure at least Service Broker transport security for message forwarding to function. Because of this, the public key portion of the associated certificate of the Service Broker endpoint is dumped to the file system. You must import this public key certificate at both the initiator service and target service, and you must associate it with a SQL Server user. You configure the target side in the same way. Please refer to the enclosed T-SQL script in the source code for more information about the target side’s configuration.

Security Configuration on the Initiator’s Side
USE master
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password1!'
GO

CREATE CERTIFICATE ClientServiceCertPrivate
WITH SUBJECT = 'For Service Broker authentication - ClientServiceCertPrivate',
START_DATE = '01/01/2007'
GO

BACKUP CERTIFICATE ClientServiceCertPrivate
TO FILE = 'c:\ClientServiceCertPublic.cert'
GO

CREATE LOGIN MessageForwardingServiceLogin WITH PASSWORD = 'password1!'
GO

CREATE USER MessageForwardingServiceUser FOR LOGIN MessageForwardingServiceLogin
GO

CREATE CERTIFICATE MessageForwardingServiceCertPublic
AUTHORIZATION MessageForwardingServiceUser
FROM FILE = 'c:\MessageForwardingServiceCertPublic.cert'
GO

GRANT CONNECT ON ENDPOINT::ClientServiceEndpoint TO MessageForwardingServiceLogin
GO

As you can see, you’re just creating a new certificate and dumping the public key portion of the certificate to the file system. Also, you’re creating a new user and associating the public key certificate of the message-forwarding endpoint to that user. These are just the normal steps needed to set up transport security for Service Broker. Further, the Service Broker endpoint must also import the public key certificates of the initiator service and the target service, so that transport security will function.

Security Configuration at the Message Forwarder
CREATE LOGIN ClientServiceLogin WITH PASSWORD = 'password1!'
GO

CREATE USER ClientServiceUser FOR LOGIN ClientServiceLogin
GO

CREATE CERTIFICATE ClientServiceCertPublic
AUTHORIZATION ClientServiceUser
FROM FILE = 'c:\ClientServiceCertPublic.cert'
GO

GRANT CONNECT ON ENDPOINT::ForwardingServiceEndpoint TO ClientServiceLogin
GO

CREATE LOGIN OrderServiceLogin WITH PASSWORD = 'password1!'
GO

CREATE USER OrderServiceUser FOR LOGIN OrderServiceLogin
GO

CREATE CERTIFICATE OrderServiceCertPublic
AUTHORIZATION OrderServiceUser
FROM FILE = 'c:\OrderServiceCertPublic.cert'
GO

GRANT CONNECT ON ENDPOINT::ForwardingServiceEndpoint TO OrderServiceLogin
GO

You map each public key certificate to a database user who has a CONNECT permission on the ForwardingServiceEndpoint.



Route Configuration
You’re missing the routes on the initiator’s side, the target side, and the message forwarding endpoint.

The Route from the Initiator’s Service to the Message Forwarder

CREATE ROUTE MessageForwardingServiceRoute
WITH SERVICE_NAME = 'OrderService',
ADDRESS = 'TCP://MessageForwardingInstance:4740'
GO

All messages targeted to OrderService are sent directly through this route to the message forwarder. Additionally, you need a route from OrderService back to the message forwarder. This route is used when OrderService sends a response message or an acknowledgment message back to ClientService.

The Route from the Target Service Back to the Message Forwarder
CREATE ROUTE MessageForwardingServiceRoute
WITH SERVICE_NAME = 'ClientService',
ADDRESS = 'TCP://MessageForwardingInstance:4740'
GO

All messages dedicated for the ClientService are forwarded through this route to the message-forwarding service at TCP://MessageForwardingInstance:4740. Finally, you have to deploy the necessary routes at the message forwarder. Here you need the following two routes:
• A route from the message forwarder to OrderService
• A route from the message forwarder back to ClientService

Creating the Routes at the Message Forwarder
CREATE ROUTE OrderServiceRoute
WITH SERVICE_NAME = 'OrderService',
ADDRESS = 'TCP://OrderServiceInstance:4742'
GO

CREATE ROUTE ClientServiceRoute
WITH SERVICE_NAME = 'ClientService',
ADDRESS = 'TCP://ClientServiceInstance:4741'
GO

As soon as you set up all the required routes, you can send a request message from ClientService to OrderService. This message will be sent first to the message forwarder, and after passing this intermediary, it will be forwarded to the final destination, OrderService.

Source of Information : Apress Pro SQL Server 2008 Service Broker