Building a Distributed Service Broker Application

In this article, we will discuss about the advanced service broker objects used
for building a Distributed Service Broker Application. We will see how messages
will be transferred between two databases existing in two different servers.

Advanced Service Broker Objects:

For Service Broker Applications which use the same database in the same server,
we dont need to use the Advanced Service Broker Objects.

The following are the advanced Service Broker Objects used by Distributed
Service Broker Application.

End Point:
End Points will accept incoming and outgoing TCP/IP connections on a Specific
port. We can have only one End Point for instance which can be shared between
all services in the instance.

Routes:
Route is used to locate a service that it is sending message to. When no route
is explicitly associated with a service then Service Broker will deliver the
message within the current instance.

Remote Service Binding: Remote Service Binding (RSB) is used to
establish security credentials which will be used by Initiating service to
authenticate itself with the Remote Service. RSB uses a Certificate associated
with the specified database user account to connect to Remote Instance

For more details on Service Broker Objects, refer to SQL Server Books Online.

Security:

Before proceeding further, we should know how Service Broker Security allows
services to communicate securely, even if they are located on different
computers.

Service Broker security relies on certificates that are shared among remote
databases, however no other information is shared. Service Broker allows two
types of security.

Dialog Security:
It provides remote authorization for conversations to specific services and
encrypts individual messages when the message leaves the sending instance until
the messages reaches the destination instance (end-to-end encryption).

Transport Security: It prevents unauthorized network connections from
sending Service Broker messages to databases in the local instance. It controls
which instances can communicate and provides encryption between the two
instances, but it doesnt secure the contents of individual messages

Steps to create a Distributed Service Broker Application:

Create certificate and End Point that support certificate based authentication.
(i.e. creating a Private Key for the Server)

Take a backup of the certificate created and install it into the remote
instance.

Create certificate from the certificate backup file copied from the other
server. (i.e. creating a Public Key of the Remote Server in current
server)

Create login from the certificate created in Step 4.

Grant the login, connect permissions on the end point.

Note: Steps 1 6 should be performed in both the servers

Set up Dialog Security:

Create a master key in the local database i.e. the database we are going to use
for our application.

Create a user certificate. (i.e. creating a Private Key for the Server)

Take a backup of the user certificate created and install it into the remote
instance.

Create a user with the same name as the user who has access rights on the other
Database.

Create a user certificate from the user certificate backup file copied from the
other server, allowing authorization to the user created in Step 4. (i.e.
creating a Public Key of the Remote Server in current server)

Grant connect permissions to the user.

Grant send permissions to the user on the local service

Create a Remote Service Binding with the user created.

Note: Steps 1 8 should be performed in both the servers

Send Messages & Receive Messages

Example:

In this example well first send message from one Server to another Server and
the server that received the message processes the message and sends a message
back to sender server. Ill be using two servers, Server A and Server B. And
the databases used are DatabaseA (in Server A) and DatabaseB (in Server B).

Create Contract SampleContract
(
SenderMessageType SENT BY INITIATOR,
ReceiverMessageType SENT BY TARGET
)

3)Create an Target queue

Create Queue TargetQueue WITH status= ON

4)Create a Service on the queue and the contract

Create Service ReceiverService ON QUEUE TargetQueue (SampleContract)

Note: In the above code snippets we have created identical Message types
and Contracts in both the servers. We need to create identical Message
Types and Contracts in each database that participates in the conversation.

II. Create a Route:

Once the Services are created on both the servers we need to create routes in
each database and associate it with a remote service to which it is sending
message to.

In the above Route, ReceiverService is the service in DatabaseB of Server B. If
we dont specify the broker_instance then the service with a similar name will
be randomly picked by the server B from any database. But if you want to
specifically mention that we need to map to the ReceiverService of DatabaseB,
then we need to get the Service_broker_guid from sys.databases for DatabaseB
using the following query.

select service_broker_guid
from sys.databases
where name = 'DatabaseB'

The address field tells us that we need to connect to 4022 port of Server B and
IPAddress of ServerB in 157.57.100.9.

In DatabaseB in Server B,

Create a Route in the same manner. (We need to create this route in our example,
as we are sending a message back to Server A, once we process the message sent
by Server A in Server B)

The above snippet opens a Transaction and begins a Dialog on the specified
contract with no encryption. It then sends a message on the conversation using
the ConversationHandle created and then commits the Transaction. While begining
a Dialog we also specify the services we are going to use to send and
receive the messages.

The above snippet opens a Transaction and Receives the first message from
the TargetQueue. After receiving the message, We can perform some extra
logic but in our example to make it simple we are just sending a
message back to the sender that we have received a message.

Now check for records in TargetQueue of DatabaseB (Server B). The record will be
removed as it has been processed successfully. Now check the records in
InitiatorQueue of DatabaseA (Server A). Two new records will be inserted one
related to conversation and the other related to end Dialog as we have used End
Conversation.

select cast(message_body as xml) from InitiatorQueue

Conclusions:

This article does not talk about the basic concepts of Service Broker. It
deals with building a distributed service broker application.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.