conversation between 2 different instances

Question

I would like to create a conversation between 2 different instances, so, i followed this example:

http://msdn.microsoft.com/en-us/library/bb839483(v=sql.105).aspx

When i begin conversation(lession4), I don't get a message in target queue. On initiator server the message got stock in transmission queue.
SELECT * FROM sys.transmission_queue

I get the following error:
An error occurred while receiving data: '10054(failed to retrieve text for this error. Reason: 1815)'.

What could be wrong?

I guess that it has to do something with creating routes:

DECLARE @Cmd NVARCHAR(4000);

SET @Cmd = N'USE InstInitiatorDB;
CREATE ROUTE InstTargetRoute
WITH SERVICE_NAME =
       N''//TgtDB/2InstSample/TargetService'',
     ADDRESS = N''TCP://x07215:4022'';';

How can I know if this route is working? Should I add this port 4022 to SQL server from configuration manager, since the default port is 1433 or that was done with endpoint?
Do you have some other suggestion what could be wrong?

Second question. If i set encryption=off, I probably don't need to create certificates, master keys and all of this stuff?

BEGIN DIALOG @InitDlgHandle
     FROM SERVICE [//InstDB/2InstSample/InitiatorService]
     TO SERVICE N'//TgtDB/2InstSample/TargetService'
     ON CONTRACT [//BothDB/2InstSample/SimpleContract]
     WITH
         ENCRYPTION = OFF;--(instead of on)

Thank you,
Simon

Answer 1

Hi Simonxy,

When you create a route you specifiy a Service Name and an address. In your case the service name is : //TgtDB/2InstSample/TargetService and the address is the bios name of your target server : x07215 and port 4022. First of all make sure that you got the rigth target server bios name and that the port 4022 is open. Use telnet for checking that.

This route tells the SB to dispatch messages to this service on this server. so you want to make sure that you have a service with this name on your target server.

First, you should ensure that the message was actually sent and committed - check if the message exists in the sys.transmission_queue

If the sender cannot send the message it will show on the transmission_status

If the message reaches the target you can use the SQL Profiler and add the events : ‘Broker/Broker:Conversation’, ‘Broker/Broker:Message Undeliverable’ and ‘Broker/Broker:Remote Message Acknowledgement’. When the message arrives, you will see the event ‘Broker/Broker:Remote Message Acknowledgement‘ with the EventSubClass ‘Message with Acknowledgement Received‘ followed by ‘Broker/Broker:Message Undeliverable‘ event. The TextData of this last event will contain an error message that will point at the problem.

If you don't want to bother with encryption, master keys and so on you need to GRANT SEND ON SERVICE::[<Servicename>] TO PUBLIC

And ensure that the SQL Service user on server 1 is granted connect on server 2 and vice-versa (GRANT CONNECT ON ENDPOINT::Server2Endpoint TO [server1\SQLServiceUser])

Hope that helps.

Cheers

Régis

---

Blog

Answer 2

Message doesn' reaches the target. It is in transmission queue with error status.
In sql profiler on target nothing happens.

With telnet on target computer I can see this:

x07215:4022 TIME_WAIT
devdb:4820 TIME_WAIT
(devdb is initiator server)

So, it is obvious that the problem is on initiator side.

What next? I have exactly the same code as in example and all ports are opened. Are there some other rights?

And thank you for hint about removing encryption. But first I'll try the same as in example. When this will work i'll try to remove encryption.

br,Simon

Answer 3

Hi,

I think the error lies in the security between sender and receiver. You should either implement it as in http://msdn.microsoft.com/en-us/library/bb839483(v=sql.105).aspx or with no encryption as I described.

My best bet is that the remote side accepts the TCP/IP connection but closes the connection before allowing a message to be sent. Most likely it the error is : 

An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.

Have you examined the  ‘Audit Security/Audit Broker Conversation‘  event on the profiler ?

cheers

Régis

---

Blog

Answer 4

I went exactly frpm step 1 to step 4.
Ports are open in both directions, I have checked with telnet.
I examined the ‘Audit Security/Audit Broker Conversation‘  event on the profiler on target server but the event is never fired.

br,
Simon