Transmission_status is empty but the messages are still there.

Question

I used the example within same domain with two different machines by using certificate based authentication and it works perfect. And now I am trying to apply exactly same example to different locations.My transmission_status is empty but everything is getting stuck in sys.transmission_queue.I never get message in receiver queue. I have tried many many ways : TCP/IP, Aliases,(in SQL Configuration Manager),FIrewall (exceptions),Pings and etc. It took me a month and still no results. I am not a networking guy, is there any way to ping the remote server with the port that is opened by the endpoint of MS SQL?

The reason I am asking is because my databases are already open through the dedicated port with 1256 for different purposes, not Service broker. And when I ping it through:

SQLCMD -U sa -P password -S 132.186.895.474\SQLEXPRESS,1256 (in cmd)

it gets me in. But when I try to ping the port that I created through endpoint:

SQLCMD -U sa -P password -S 132.186.895.474\SQLEXPRESS,4741 (in cmd)

it's giving me an error. Although the port 4741 is in listening mode on both machines when I check it through:

netstat -a (in cmd)

Is there any way to test the endpoints remotely , before configuring the whole Service broker. I am pretty sure the problem is in networking.

Need your help thanks

 

Answer 1

Please run the ssbdiagnose utility against your service broker application, to check for configuration issues - http://msdn.microsoft.com/en-us/library/bb934450.aspx

This will help you identify the high level issue before we dig into networking details.

Hope this helps !

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 2

Thanks for quick respond,

I tried the ssbdiagnose, here are the results I get from it:

I took ID one of the conversations from sys.transmission_queue 

>>ssbdiagnose -E -d databaseA RUNTIME -ID BA724930-E99D-E111-BCD8-78KEXSK94E8A  -TIMEOUT 20

result:

Microsoft SQL Server 10.50.1600.1
Service Broker Diagnostic Utility
0 Errors, 0 Warnings

another trial:

>>ssbdiagnose -E CONFIGURATION FROM SERVICE SenderService -S Server\SQLEXPRESS -d databaseA TO SERVICE ReceiverService -S

132.186.895.474\SQLEXPRESS -d databaseB
result:
Microsoft SQL Server 10.50.1600.1
Service Broker Diagnostic Utility
D  29997 SERVER\SQLEXPRESS slog            Service Broker GUID is identical to that of database info on serverWEBSERVER\SQLEXPRESS
An exception occurred on a connection to SQL Server 132.186.895.474\SQLEXPRESS:A network-related or instance-spe
cific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.
 Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider:
 SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
is there a way to define the port, since  for service broker i use 4741
thank You

Answer 3

Hi,

The second run of SSBDIAGNOSE gives me the information I was looking for. Do you have the ROUTE set up correctly for your remote service (Target Service).

You can specify a port when you create a ROUTE. Please follow this link for more details on creating ROUTE - http://msdn.microsoft.com/en-us/library/ms186742.aspx

Here is an example of specifying a PORT when creating a ROUTE 

CREATE ROUTE ExpenseRoute
    WITH
    SERVICE_NAME = '//Adventure-Works.com/Expenses',
    BROKER_INSTANCE = 'D8D4D268-00A3-4C62-8F91-634B89C1E315',
    ADDRESS = 'TCP://192.168.10.2:1234' ;

Hope this helps !

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 4

I do use the route on both machines, but I didn't specify BROKER_INSTANCE attribute, and I don't thinks it is required.

Here is the question: Is the address of a route is the actual address (ip address) of the opposite machine(the target) or the local one(itself)?

Thank for collaboration.

Answer 5

Ok, I just learned that it is suppose to be the opposite site, the name itself says it, and that's what I already did before posting here:

sender

Create Route RouteA
WITH
  SERVICE_NAME = 'ReceiverService',
  ADDRESS = 'TCP://132.186.895.474:4741'
GO

target

Create Route RouteB
WITH
  SERVICE_NAME = 'SenderService',
 ADDRESS = 'TCP://172.186.595.413:4741'
GO

Answer 6

There are numerous ways of setting up the route, if you could post the script you have used to create your route, that will help me. It is the recommended best practice to set up the route with BROKER_INSTANCE , unless you are trying to setup message forwarding.

The ROUTE has to be created in the INITIATOR side machine (local machine) and must contain the Address (IP or DNS or NetBIOS name) and port of the TARGET side machine ( remote machine).

 

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 7

Thanks, I got the answer. I will  set the BROKER_INSTANCE attribute right now.

Here is the complete script for sender and target (which functioning perfect within the same domain but two different machines).I just changed the route address.:

sender

USE master;
GO

create Certificate EndPointCertificateA
WITH Subject = 'A.Server.Local',
    START_DATE = '01/01/2012',
    EXPIRY_DATE = '01/01/2013'
ACTIVE FOR BEGIN_DIALOG = ON;
GO
...

target

USE master;
GO

Create Certificate EndPointCertificateB
WITH Subject = 'B.Server.Local',
       START_DATE = '01/01/2012',
       EXPIRY_DATE = '01/01/2013'
ACTIVE FOR BEGIN_DIALOG = ON;
GO
...

Answer 8

Any thing new?

Answer 9

I put  the BROKER_INSTANCE inside the route but still no success

Answer 10

Hi,

Sorry for the lated response. Did not check back on Forums until this morning. Your configuration seems correct. Can you verify if the said port in the target machine is accessible to the initiator machine, via telnet (or any other networking utility that might be better suited ) ?

Does SSBDIAGNOSE run in configuration check mode still return you the same error ?

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 11

Hi,

Thanks fro reply,

I tried SSBDIAGNOSE on the both sides, came up with the same error just the IP's are different.

Also, I used telnet to check the 4741 port of both machines, and it directs to the black screen,which I assume mean that the connection is made.

Maybe, there are some other settings which I didn't touch? i just don't know where. Like I said, I've been playing with it a month, and I configured and read enough places , but still there is something maybe small thing but I can't find.

I even added the port 4741 to each IP(IP1,IP2,IP3 in SQL Configuration Manager in TCP/IP). So now I have two static ports with commas in between, as i mentioned before.

Answer 12

Is there any one else on the whole world know Service Broker ? :)

Answer 13

I think i just found something useful to answer this issue, though its not a soltuion, its definitely an explanation for your issue - http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/3b03a610-8159-4be8-b2d8-e2abb4ba1225

In SQL Express, Service broker has a limitation that when messages have to be exchanged between two separate SQL instances, at least one of the brokers on a route must be running on a paid version of SQL Server. Since both of your instances are free SQL Express versions, hence you are having this issue.

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 14

Ha ha, Sanil Mhatre you made my day today,

Thank You,

I forgot to mention you that, when I was running this script within the domain between two different instances and machines, my target was enterprise edition and sender was express.But right now both of them are free express editions. I hope this is the problem. Let me check that, I will post back.

Answer 15

YES YES YES,

FINALLY, it worked, I used enterprise(trial) as sender and express edition as target.

No even need to SQL SERVER Browser to be enabled. Only firewall, and Port forwarding are Important. 

Again appreciate your help Sanil Mhatre .

Case is closed :)