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.

Pre-requisites: Should have basic knowledge of Service Broker Basic

Concepts like Message Types, Contracts, Services, Queues, Conversation, Sending

messages on Conversation and Receiving messages.

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:

1. Create the basic Service Broker Objects (i.e. message types, contracts,

   services, queues etc)

2. Set up Transport Security:
   1. Create a master key for master database.

   2. Create certificate and End Point that support certificate based authentication.

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

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

      instance.

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

   5. Create login from the certificate created in Step 4.

   6. Grant the login, connect permissions on the end point.

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

3. Set up Dialog Security:
   1. Create a master key in the local database i.e. the database we are going to use

      for our application.

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

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

      instance.

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

      Database.

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

   6. Grant connect permissions to the user.

   7. Grant send permissions to the user on the local service

   8. Create a Remote Service Binding with the user created.

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

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

The following are the tasks performed by our Sample Application.

a) Server A sends message to Server B

b) Server B receives the message and sends a message to Server A.

Steps:

I. Create Basic Service Broker Objects:

In DatabaseA in Server A, Lets perform the following Operations

1)Create Message Types

Create Message Type SenderMessageType validation=NONE 
Create Message Type ReceiverMessageType validation=NONE

2)Create Contract on the above message types

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

3) Create an Initiator queue

Create Queue InitiatorQueue
 WITH status = ON

4) Create a Service on the queue and the contract

Create Service SenderService ON QUEUE InitiatorQueue  (SampleContract)

In DatabaseB in Server B, Lets perform the following Operations

1) Create Message Types:

Create Message Type SenderMessageType validation=NONE
Create Message Type ReceiverMessageType validation=NONE

2)Create Contract on the above message types

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 DatabaseA in Server A,

Create Route RouteA
WITH
  SERVICE_NAME = 'ReceiverService',
  BROKER_INSTANCE = '1B9C40BC-7FCF-41F7-9813-61C11A49D0DE',
  ADDRESS = 'TCP://157.57.100.9:4022'
GO

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)

Create Route RouteB
WITH
  SERVICE_NAME = 'SenderService',
  BROKER_INSTANCE='D164787D-590A-47AC-83AB-987F880E3F2A',
 ADDRESS = 'TCP://172.22.26.216:4022'
GO

III. Set up Transport Security:

Note: All actions related to Transport Security will be performed in the master

database of the Servers.

1) Create a master key for master database.

2) Create certificate and End Point that support certificate based

authentication.

Server A:

Use master
Go
--1. Create a master key for master database.
Create Master Key Encryption BY Password = 'gs53&"f"!385'
Go
/*2.Create certificate and End Point that support
     certificate based authentication
*/
Create Certificate EndPointCertificateA
WITH Subject = 'A.Server.Local',
    START_DATE = '01/01/2006',
    EXPIRY_DATE = '01/01/2008'
ACTIVE FOR BEGIN_DIALOG = ON;
GO
CREATE ENDPOINT ServiceBrokerEndPoint
   STATE=STARTED
   AS TCP (LISTENER_PORT = 4022)
   FOR SERVICE_BROKER
   (
     AUTHENTICATION = CERTIFICATE EndPointCertificateA,
     ENCRYPTION = SUPPORTED
   );

Server B:

Use master
Go
--1. Create a master key for master database.
Create Master Key Encryption BY Password = '45Gme*3^&fwu';
Go
--2.Create certificate and End Point that support certificate based authentication.
Create Certificate EndPointCertificateB
WITH Subject = 'B.Server.Local',
       START_DATE = '01/01/2006',
       EXPIRY_DATE = '01/01/2008'
ACTIVE FOR BEGIN_DIALOG = ON;
GO
CREATE ENDPOINT ServiceBrokerEndPoint
      STATE=STARTED
      AS TCP (LISTENER_PORT = 4022)
      FOR SERVICE_BROKER
      (
         AUTHENTICATION = CERTIFICATE EndPointCertificateB,
         ENCRYPTION = SUPPORTED
      );

3) Take a backup of the certificate created and install it into the remote

instance by physically copying this certificate to Other Server.

Server A:

BACKUP CERTIFICATE EndPointCertificateA
 TO FILE =
   'C:\Documents and Settings\Santhi\Desktop\Service Broker\Session\EndPointCertificateA.cer';
GO

Copy the certificate from the above location to the following location

Destination: Server B

Path: C:\Documents and Settings\Santhi\Desktop\ServiceBroker\

Server B:

BACKUP CERTIFICATE EndPointCertificateB TO FILE=
  'C:\Documents and Settings\Santhi\Desktop\ServiceBroker\EndPointCertificateB.cer';
GO
 

Copy the certificate from the above location to the following location

Destination: Server A

Path: C:\Documents and Settings\Santhi\Desktop\ServiceBroker\Session\

4)Create certificate from the certificate backup file copied from the other

server.

Server A:

Create Certificate EndPointCertificateB
 From FILE =
 'C:\Documents and Settings\Santhi\Desktop\Service Broker\Session\EndPointCertificateB.cer';
GO
 

Server B:

Create Certificate EndPointCertificateA
 From FILE =
 'C:\Documents and Settings\Santhi\Desktop\ServiceBroker\EndPointCertificateA.cer';
GO

5)Create login from the certificate in remote server in the current server.

Server A:

CREATE LOGIN sbLogin
 FROM CERTIFICATE EndPointCertificateB;
GO

Server B:

CREATE LOGIN sbLogin
 FROM CERTIFICATE EndPointCertificateA;
GO

6)Grant the login, connect permissions on the end point.

Server A:

GRANT CONNECT ON ENDPOINT::ServiceBrokerEndPoint To sbLogin
GO

Server B:

GRANT CONNECT ON ENDPOINT::ServiceBrokerEndPoint To sbLogin
GO

IV. Set up Dialog Security:

Note: All actions related to Dialog Security will be performed in DatabaseA of

Server A and DatabaseB of Server B, not in master databases.

1) Create a master key in the local database i.e. the database we are going to

use for our application.

Server A:

Use DatabaseA
GO
Create Master Key Encryption BY
Password = 'gs53&"f"!385'
Go

Server B:

Use DatabaseB
GO
Create Master Key Encryption BY
Password = '45Gme*3^&fwu';
Go

2)Create a user certificate.

Server A:

Create Certificate UserCertificateA
 WITH Subject = 'A.Server.Local',
    START_DATE = '01/01/2006',
    EXPIRY_DATE = '01/01/2008'
ACTIVE FOR BEGIN_DIALOG = ON;
GO

Server B:

Create Certificate UserCertificateB
 WITH Subject = 'B.Server.Local',
    START_DATE = '01/01/2006',
    EXPIRY_DATE = '01/01/2008'
ACTIVE FOR BEGIN_DIALOG = ON;
GO

3)Take a backup of the user certificate created and install it into the remote

instance.

Server A:

BACKUP CERTIFICATE UserCertificateA TO FILE=
'C:\Documents and Settings\Santhi\Desktop\Service Broker\Session\UserCertificateA.cer';
GO

Copy the certificate from the above location to the following location

Destination: Server B

Path: C:\Documents and Settings\Santhi\Desktop\ServiceBroker\

Server B:

BACKUP CERTIFICATE UserCertificateB TO
FILE='C:\Documents and Settings\Santhi\Desktop\ServiceBroker\UserCertificateB.cer';
GO

Copy the certificate from the above location to the following location

Destination: Server A

Path: C:\Documents and Settings\Santhi\Desktop\ServiceBroker\Session\

4)Create a user with the same name as the user who has access rights on the

other Database.

Server A:

Create User UserB WITHOUT LOGIN
GO

Server B:

Create User UserA WITHOUT LOGIN
GO

5)Create a user certificate from the user certificate backup file copied from

the other server, with authorization to the user created in Step 4.

Server A:

CREATE CERTIFICATE UserCertificateB
 AUTHORIZATION UserB
 FROM FILE = 'C:\Documents and Settings\Santhi\Desktop\Service Broker\Session\UserCertificateB.cer';
GO

Server B:

CREATE CERTIFICATE UserCertificateA
 AUTHORIZATION UserA
FROM FILE = 'C:\Documents and Settings\Santhi\Desktop\ServiceBroker\UserCertificateA.cer';
GO

6)Grant connect permissions to the user.

Server A:

GRANT CONNECT TO UserB;

Server B:

GRANT CONNECT TO UserA;

7)Grant send permissions to the user on the local service.

Server A:

GRANT SEND ON SERVICE::SenderService To UserB;
GO

Server B:

GRANT SEND ON SERVICE::ReceiverService To UserA;
GO

8)Create a Remote Service Binding with the user created.

Server A:

CREATE REMOTE SERVICE BINDING ServiceBindingB
 TO SERVICE 'ReceiverService'
 WITH USER = UserB

Server B:

CREATE REMOTE SERVICE BINDING ServiceBindingA
 TO SERVICE 'SenderService'
 WITH USER = UserA

V. Send Messages from Server A:

In Database A:

/**********Begin a Dialog and Send a Message******************/
Declare @ConversationHandle uniqueidentifier
Begin Transaction
Begin Dialog @ConversationHandle
 From Service SenderService
 To Service 'ReceiverService'
 On Contract SampleContract
 WITH Encryption=off;
SEND
      ON CONVERSATION @ConversationHandle
      Message Type SenderMessageType
  ('<test>test</test>')
Commit

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.

Now check for this record in TargetQueue of Database B (Server B)

select cast(message_body as xml)  from TargetQueue

VI. Receive Messages from Server A:

In Database B:

/*****Receive the Message and send a message to the ender**********/
Declare @ConversationHandle as uniqueidentifier
Declare @MessageBody as nvarchar(max)
Declare @MessageType as sysname
Begin Transaction
Print 'Started Receiving ';
RECEIVE top (1)
      @MessageType = message_type_name,
      @ConversationHandle = conversation_handle,
    @MessageBody = message_body
FROM TargetQueue;
if @MessageType = 'SenderMessageType'
      Begin
            SEND
                  ON CONVERSATION @ConversationHandle
                  Message Type ReceiverMessageType
                  ('Message is received')
            END Conversation @ConversationHandle
      END
Commit

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.