Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> SQL Service Broker

"certificate not found" for second receiver

I am trying to test load balancing between multiple broker service instances. I have set up one sender and two receivers. When I tried sending a lot of messages from the sender, I noticed that all messages were being received by receiver 1 alone. While I am able to communicate between sender and receiver 1, I am not able to send message to the second receiver (I stopped the first receiver instance to find this out). I receive the "certificate not found" error in the Profiler for the second receiver. The code for my second receiver is very similar to the first one.

I am dumping in the full code down here. I appreciate if someone can figure out what is wrong. Thanks

Sender:

use [master];
go

create master key encryption by password = 'masterhello1';
go

create certificate TrpCertServ1
with subject = 'TrpCertServer1',
start_date = '06/01/2005';
go

--make sure the cert exist
select * from sys.certificates;
go

--dump out the public key of the cert to a file
--this will then be exchanged with the other instance
--make sure that the path you define below can be accessed
--by sql server. The file needs to be copied over to server 2
BACKUP CERTIFICATE TrpCertServ1
TO FILE = 'c:\amit\Official\Service Broker\certs\TrpCertServ1Pub.cer';
go

--you need to create an endpoint in order to enable communication
--outside of this instance
CREATE ENDPOINT SSB1
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4021
)
FOR SERVICE_BROKER
(
AUTHENTICATION = CERTIFICATE TrpCertServ1,
ENCRYPTION = REQUIRED
);
go

USE master ;
GO

ALTER ENDPOINT SSB1
FOR SERVICE_BROKER ( MESSAGE_FORWARDING = ENABLED,
MESSAGE_FORWARD_SIZE = 10 ) ;
GO

--check that the endpoint has been created
select * from sys.endpoints;
go

--create a login and a user which you eventually will assign a public
--key from the cert in the remote master db to
create login remconnlogin1
with password ='remserver@1';
go

create user remconnuser1
from login remconnlogin1;
go

--grant connect to the endpoint to the login
grant connect on endpoint::ssb1 to remconnlogin1
go

--now is time to go over to server 2 and do similar tasks
--but first make sure that the cert you dumped out above can be
--available for server 2

--Step 2
-- you have now done the similar setup in server 2,
--and you should now do the final setup in master
--where you create a certificate from server 2's public
--cert and assigns it to the user created above
--uncomment from here to go and execute
create certificate TrpCertServ2Pub
authorization remconnuser1
from file = 'C:\amit\Official\Service Broker\certs\TrpCertServ2Pub.cer';
go

--adding for new receiver
create certificate TrpCertServ3Pub
authorization remconnuser1
from file = 'C:\amit\Official\Service Broker\certs\TrpCertServ3Pub.cer';
go
-- end of addition

use master;
go

--create the database
create database [rem_ssb1];
go

use [rem_ssb1];
go

--set master key
create master key
encryption by password = 'hellodb1';

create certificate DlgCertServ1Db1
with subject = 'DlgCertServ1Db1',
start_date = '06/01/2005'
active for begin_dialog = on;
go

--make sure the cert exist
select * from sys.certificates;
go

--dump out the public key of the cert to a file
--this will then be exchanged with the other instance
--make sure that the path you define below can be accessed
--by sql server. The file needs to be copied over to server 2
BACKUP CERTIFICATE DlgCertServ1Db1
TO FILE = 'c:\amit\Official\Service Broker\certs\DlgCertServ1Db1Pub.cer';
go

--create a user which you eventually will assign a public
--key from the cert in the remote db to
create user remdlguser1
without login;
go


--Step 2
create certificate DlgCertServ2Db2Pub
authorization remdlguser1
from file = 'c:\amit\Official\Service Broker\certs\DlgCertServ2Db2Pub.cer';
go

-- adding content for new receiver
create certificate DlgCertServ3Db3Pub
authorization remdlguser1
from file = 'c:\amit\Official\Service Broker\certs\DlgCertServ3Db3Pub.cer';
go
-- end of addition

use [rem_ssb1];
go

-- we need two message types
CREATE MESSAGE TYPE [sendmsg]
VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE [recmsg]
VALIDATION = WELL_FORMED_XML;
go

--create the message contract
--and define who sends what
CREATE CONTRACT [Ctract]
(
[sendmsg]
sent by initiator,
[recmsg]
sent by target
);
go

--create the queue, at this stage we do not care
--about activation
CREATE QUEUE q1
with status = ON;
go

--we need a service
CREATE SERVICE [rem_s1]
on queue q1
(
[Ctract]
);
go

--create a route to the remote service, we know it'll be called rem_s2
create route [rem_s2_route]
with
service_name = 'rem_s2',
address = 'TCP://127.0.0.1:4022';
go
--drop route [rem_s2_route]

-- adding for new route
create route [rem_s3_route]
with
service_name = 'rem_s2',
address = 'TCP://127.0.0.1:4023';
go
-- end of addition

select * from sys.routes

--as we'll be doing encrypted dialogs we need a remote service bindin
CREATE REMOTE SERVICE BINDING [myRms]
TO SERVICE 'rem_s2'
WITH USER = remdlguser1,
ANONYMOUS=Off

--give the user send rights on the service
grant send on service::rem_s1 to remdlguser1;
go
use [rem_ssb1];
go

-start the dialog and send a message
-uncomment from here until the following go statement and run
DECLARE @h uniqueidentifier --conversation handle
DECLARE @msg xml; --will hold the message

BEGIN DIALOG CONVERSATION @h
FROM SERVICE rem_s1
TO SERVICE 'rem_s2'
ON CONTRACT [Ctract];

SET @msg = '<hello00/>';
SEND ON CONVERSATION @h
MESSAGE TYPE [sendmsg]
(@msg);

RECEIVER 1:


--Step 1

use [master];
go

create master key encryption by password = 'masterhello2';
go

create certificate TrpCertServ2
with subject = 'Transport Certificate for Server2',
start_date = '06/01/2005';
go

--make sure the cert exist
select * from sys.certificates;
go

--dump out the public key of the cert to a file
--this will then be exchanged with the other instance
--make sure that the path you define below can be accessed
--by sql server. The file needs to be copied over to server 1
BACKUP CERTIFICATE TrpCertServ2
TO FILE = 'c:\amit\Official\Service Broker\certs\2\TrpCertServ2Pub.cer';
go

--you need to create an endpoint in order to enable communication
--outside of this instance

CREATE ENDPOINT SSB2
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER
(
AUTHENTICATION = CERTIFICATE TrpCertServ2,
ENCRYPTION = REQUIRED
);
go

--check that the endpoint has been created
select * from sys.endpoints;
go

--create a login and a user which you eventually will assign a public
--key from the cert in the remote master db to
create login remconnlogin2
with password = 'pass1234$';
go

create user remconnuser2
from login remconnlogin2;
go

--grant connect on the endpoint to the login
grant connect on endpoint::SSB2 to remconnlogin2
go

--copy in the public cert from server 1 to somewhere on this server

--create a certificate from the public cert from server 1
create certificate TrpCertServ1Pub
authorization remconnuser2
from file = 'c:\amit\Official\Service Broker\certs\2\TrpCertServ1Pub.cer';

--go back to server 1 and step 2 in the script 1_setup_sec_master_server1.sql
--make sure the public cert 'TrpCertServ2Pub.cer' is available
--from server 1

use master;
go

--create the database
create database [rem_ssb2];
go

use [rem_ssb2];
go

--set master key
create master key
encryption by password = 'hellodb2';
go

create certificate DlgCertServ2Db2
with subject = 'DlgCertServ2Db2',
start_date = '06/01/2005'
active for begin_dialog = on;
go

--make sure the cert exist
select * from sys.certificates;
go

BACKUP CERTIFICATE DlgCertServ2Db2
TO FILE = 'c:\amit\Official\Service Broker\certs\2\DlgCertServ2Db2Pub.cer';
go

create user remdlguser2
without login;
go

--copy in the public cert from server 1 to somewhere on this server

--create a certificate from the public cert from server 1
create certificate DlgCertServ1Db1Pub
authorization remdlguser2
from file = 'c:\amit\Official\Service Broker\certs\2\DlgCertServ1Db1Pub.cer';
use [rem_ssb2];
go

-- we need two message types
CREATE MESSAGE TYPE [sendmsg]
VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE [recmsg]
VALIDATION = WELL_FORMED_XML;
go

--create the message contract
--and define who sends what
CREATE CONTRACT [Ctract]
(
[sendmsg]
sent by initiator,
[recmsg]
sent by target
);
go

--create the queue, at this stage we do not care
--about activation
CREATE QUEUE q2
with status = ON;
go

--we need a service
CREATE SERVICE [rem_s2]
on queue q2
(
[Ctract]
);
go

--create a route to the remote service, we know it'll be called rem_s1
create route [rem_s1_route]
with
service_name = 'rem_s1',
--broker_instance = 'D8EE8A81-F1B0-46B3-BBEB-70F19EF59083',
address = 'TCP://127.0.0.1:4021';
go

--as we'll be doing encrypted dialogs we need a remote service binding
--and the user is the user we created in the 2_setup_objects_server2_db.sql scripts
CREATE REMOTE SERVICE BINDING [myRms]
TO SERVICE 'rem_s1'
WITH USER = remdlguser2,
ANONYMOUS=Off
go

--give the user send rights on the service
grant send on service::rem_s2 to remdlguser2;
go
SELECT * from q2;

RECEIVER 2:

use [master];
go

--make sure master had master key
create master key encryption by password = 'masterhello2';
go

create certificate TrpCertServ3
with subject = 'Transport Certificate for Server3',
start_date = '06/01/2005';
go

--make sure the cert exist
select * from sys.certificates;
go

BACKUP CERTIFICATE TrpCertServ3
TO FILE = 'c:\amit\Official\Service Broker\certs\3\TrpCertServ3Pub.cer';
go

--you need to create an endpoint in order to enable communication
--outside of this instance

CREATE ENDPOINT SSB3
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4023
)
FOR SERVICE_BROKER
(
AUTHENTICATION = CERTIFICATE TrpCertServ3,
ENCRYPTION = REQUIRED
);
go

--check that the endpoint has been created
select * from sys.endpoints;
go

--create a login and a user which you eventually will assign a public
--key from the cert in the remote master db to
create login remconnlogin3
with password = 'pass1234$';
go

create user remconnuser3
from login remconnlogin3;
go

--grant connect on the endpoint to the login
grant connect on endpoint::SSB3 to remconnlogin3
go

--copy in the public cert from server 1 to somewhere on this server

--create a certificate from the public cert from server 1
create certificate TrpCertServ1Pub
authorization remconnuser3
from file = 'c:\amit\Official\Service Broker\certs\3\TrpCertServ1Pub.cer';
use master;
go

--create the database
create database [rem_ssb3];
go

use [rem_ssb3];
go

--set master key
create master key
encryption by password = 'hellodb3';
go

create certificate DlgCertServ3Db3
with subject = 'DlgCertServ3Db3',
start_date = '06/01/2005'
active for begin_dialog = on;
go

--make sure the cert exist
select * from sys.certificates;
go

BACKUP CERTIFICATE DlgCertServ3Db3
TO FILE = 'c:\amit\Official\Service Broker\certs\3\DlgCertServ3Db3Pub.cer';
go

--create a user which you eventually will assign a public
--key from the cert in the remote db to
create user remdlguser3
without login;
go

--copy in the public cert from server 1 to somewhere on this server

--create a certificate from the public cert from server 1
create certificate DlgCertServ1Db1Pub
authorization remdlguser3
from file = 'c:\amit\Official\Service Broker\certs\3\DlgCertServ1Db1Pub.cer';

--create the message contract
--and define who sends what
CREATE CONTRACT [Ctract]
(
[sendmsg]
sent by initiator,
[recmsg]
sent by target
);
go

--create the queue, at this stage we do not care
--about activation
CREATE QUEUE q2
with status = ON;
go

--we need a service
CREATE SERVICE [rem_s2]
on queue q2
(
[Ctract]
);
go

--create a route to the remote service, we know it'll be called rem_s1
create route [rem_s1_route]
with
service_name = 'rem_s1',
--broker_instance = 'D8EE8A81-F1B0-46B3-BBEB-70F19EF59083',
address = 'TCP://127.0.0.1:4021';
go

--as we'll be doing encrypted dialogs we need a remote service binding
--and the user is the user we created in the 2_setup_objects_server2_db.sql scripts
CREATE REMOTE SERVICE BINDING [myRms]
TO SERVICE 'rem_s1'
WITH USER = remdlguser3,
ANONYMOUS=Off
go

--give the user send rights on the service
grant send on service::rem_s2 to remdlguser3;
go

select * from q2

[14315 byte] By [AmitKapoor] at [2008-2-18]
«« Dynamic Table Columns
»» My.Settings file location
# 1

For load balancing scenario, the two target services have to be identical from the security point of view. That is, they must have the SAME private key. In your example, you are using separate private keys for each instance of the [rem_s2] service. You should export the certificate and private key from receiver1 and import it into receiver2.

Note that this applies only to the dialog certificates, for transport is OK what you're doing.

Another thing you have to account for is the 'broker instance'. The initiator must be able to distinguish between the two instances of the service [rem_s2]. For this, it needs explicit routes containing the broker instance in addition to the service name. The broker instance will be the value of 'service_broker_guid' in the sys.databases catalog. So you have to add two more routes in the initiator database (yes, you'll have 4 routes at the end):

create route [rem_s2_route]
with
service_name = 'rem_s2',
broker_instance = '....', -- service_broker_guid of [rem_ssb2]
address = 'TCP://127.0.0.1:4022';
go

create route [rem_s3_route]
with
service_name = 'rem_s2',
broker_instance = '....', -- service_broker_guid of [rem_ssb3]
address = 'TCP://127.0.0.1:4023';
go

When you begin a dialog w/o specifying an explicit broker isntance, the first message sent will choose randomly between the two route available (the ones w/o a broker instance). The first reply or message acknowledgement received from the target will carry with it the broker instance of the target service. Subsequent messages sent from the initiator now will be stamped with the target broker instance, and the routing mechanism now needs to deterministically choose the correct route (based on the broker instance), hence the need for these two more routes.

To test these two routes that are correct, begin a dialog and specify explicitly the desired broker instance (once for [rem_ssb2] and once for [rem_ssb3]). The message should deterministically go to the desired instance of the [rem_s2] service.

Note that each time you run the ALTER DATABASE ... SET NEW_BROKER the broker instance changes, you must the all route to it as well.

HTH,
~ Remus

RemusRusanu at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 2

Fantastic response !!!

The configuration that I had initially (the ones in which the broker identifiers were not specified for the routes) was working as a failover configuration not really as a load-balancing configuration. Once the first route was chosen, all subsequent messages were sent to the same target service (as you had indicated in your response). The good part was that when I stopped the target service, currently receiving messages, all the other messages got routed to the second receiver service.

I then altered the routes created above, specifying broker identifiers (Note that I had only two routes at this stage, not four). This configuration worked as a load-balancing one. However, if we stopped any one of the receiver services midway, say receiver 1, messages destined for receiver 1 still piled up on the transmission_queue of the sender. This is where we realized to have 4 routes as you suggested.

However, when we created four routes, initially some messages were distributed across the receiver service instances (It may have been because the routes specified with broker identifiers were chosen initially). But, after that all messages still kept going to one particular receiver (It may have been because one of the routes specified with NO broker identifiers were chosen). Basically, we are looking for a load-balancing + failover configuration. We have been able to figure out how to get each of these individually, however we haven't found a way to do both together. Can you suggest how could we achieve this?

Regards,

Amit

AmitKapoor at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 3

An individual dialog cannot failover between different databases. Dialogs are sticky, the database contains persited information about them (sys.conversation_endpoints). Once the first message was acknowledged by one database (instance of a service), subsequent messages can be received only by that database.

But there are good news. When you are looking for a solution for high availability of service [rem_s2] on one of the databases, you are basically looking for a solution for high availability of the database itself. If you can make the database available, you are making the service available too. And SQL Server has solutions for database availability. You can use clustered servers or database mirroring. The later is especially atractive for Service Broker scenarios, since is explicitly supported in Service Broker routing, see http://msdn2.microsoft.com/en-us/library/ms166052.aspx . You would have only one route, with two addresses (ADDRESS and MIRROR_ADDRESS). Service Broker in this case is capable of identifying the current principal and reacts to a mirror failover, redirecting the messages imedetly as the failover happens, to the new principal. The bad news is Database Mirrorirng is not supported in RTM (see http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx)

With clusters you don't need special support from Service Broker, you just put the address of the virtual node the route and it will work.

You might be tempted to look into a solution that uses only one message per dialog. Since every message is the first message, it will look like you achieved some sort of failover capabilities as well as load balancing: messages will be spread between the two databases and on case when one is down messages will all go to the one still running. Perfect solution, isnt' it? Don't fall for it, there are hidden dangers. First, any dialog has at least two messages exchanged, since it must be closed (END DIALOG is also a message). Second, you run the risk of establishing the same dialog twice, once in each database. This later case is a case Service Broker is aware of and eventualy one of the dialogs will be closed with an error, but your application logic must be prepared to deal with this and have compensation logic to undo the effect of that message. And also you loose the order guarantees dialogs provide, so you're back to the old days when you had to implement the order in the application.

For anybody who happens to stumble on this post later on, when Database Mirroring is supported I'd say go for it, mirroring and broker are a perfect match ;-) Until then, the high availability solution remains clustering.

HTH,
~ Remus

RemusRusanu at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 4

Hi Remus,

I was wondering how dialogs failed over from receiver 1 to receiver 2, when I had not specified broker_identifiers during the route creation. When I stopped receiver 1 midway while it was receiving its response from the sender, all other messages that could not reach Receiver 1 were automatically routed to Receiver 2. Right now, since we are only checking for Service Broker's capabilities around load-balancing, I am actually working on a crude solution to this problem rather than going for a cluster or mirror as you have suggested.

I observed that if I initially create routes with broker_ids specified, and stop one of the receivers' endpoint (Receiver1) midway, then messages destined for that that receiver stay in the sender's transmission_queue. Now, if I drop this defunct route from the sender, the messages destined for receiver1 that could not reach it, now go to receiver2. The only catch is that I now have to work out a way, wherein I can automate this process of dropping the original defunct route, so that the failover is automatic and not manual. Any suggestions around this, and also is this solution feasible.

Regards,

Amit

AmitKapoor at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 5

Lets make a distinction between services that are identical from the perspective of security and functionality and services that are mirrored. When you deploy a service to two different brokers (i.e. databases), each with its own broker instance ID, such that they provide the same functionality and use the same private key for decryption and signing data; we will say the services are identical. On the other hand, when you deploy a service to mirrored brokers (i.e. using database mirroring), such that they have the same broker instance IDs but different endpoints, we will say the services are mirrored.

Identical services can be used for load-balancing dialogs (not messages, but dialogs). Load-balancing is useful for throughput not high-availability. Mirrored services on the other hand are used for high-availability and automatic failover. Service Broker routing allows you to configure the system for either scenarios.

When services are mirrored, we can failover dialogs automatically because the switching the target service to the mirror guarantees that the state associated with the dialog's business transaction is also mirrored. We have no such guarantees with identical services. Since identical services are in different brokers (i.e. databases), if we were to switch to a different service mid-stream, the state associated with that dialog will not failover automatically and so it just wouldn't work.

High-availability and load-balancing can also be mixed. Say you have 4 machines A, B, C, D across which you want to balance your load. You can create four databases on each of the machines to be your brokers and deploy identical services in each of these brokers. Next you can mirror each of the databases by pairing two machines together (B is mirror for A, C is mirror for B and so forth in a cycle). Now if you setup your routes as follows:

Route Service_Name Broker_Instance Address Mirror_Address
r1 s - A B
r2 s - B C
r3 s - C D
r4 s - D A

With this setup, dialogs will get automatically partitioned between A, B, C and D. At the same time, each broker is also mirrored, so if one of the machines was to die (say C), dialogs targetting that broker would automatically failover (to D).

Rushi

RushiDesai at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Service Broker...
SQL Server
SQL Service Broker

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified