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.

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.