SQLServerCentral Article

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.

Rate

5 (18)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (18)

You rated this post out of 5. Change rating