Transmission_status is empty but the messages are still there.

Stellen Sie eine Frage

Dienstag, 15. Mai 2012 15:06

0
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
- Bearbeitet coverdip Dienstag, 15. Mai 2012 15:08
- Antworten
- Zitieren

Alle Antworten

Dienstag, 15. Mai 2012 15:48

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
- Antworten
- Zitieren

Dienstag, 15. Mai 2012 17:28

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

Bearbeitet coverdip Dienstag, 15. Mai 2012 19:01

Dienstag, 15. Mai 2012 17:44

1
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
- Antworten
- Zitieren
Dienstag, 15. Mai 2012 17:54

0

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.
- Antworten
- Zitieren
Dienstag, 15. Mai 2012 17:59

0
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
```
- Bearbeitet coverdip Dienstag, 15. Mai 2012 18:00
- Antworten
- Zitieren
Dienstag, 15. Mai 2012 18:06

1

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
- Antworten
- Zitieren

Dienstag, 15. Mai 2012 18:17

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
...

Bearbeitet coverdip Dienstag, 15. Mai 2012 20:01

Dienstag, 15. Mai 2012 23:02

0

Any thing new?
- Antworten
- Zitieren
Mittwoch, 16. Mai 2012 13:57

0

I put the BROKER_INSTANCE inside the route but still no success
- Antworten
- Zitieren
Mittwoch, 16. Mai 2012 14:46

1

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
- Antworten
- Zitieren
Mittwoch, 16. Mai 2012 16:06

0

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.
- Antworten
- Zitieren
Donnerstag, 17. Mai 2012 15:31

0

Is there any one else on the whole world know Service Broker ? :)
- Antworten
- Zitieren
Donnerstag, 17. Mai 2012 15:51

1
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
- Bearbeitet Sanil Mhatre Donnerstag, 17. Mai 2012 15:52 added link
- Als Antwort markiert coverdip Donnerstag, 17. Mai 2012 16:05
- Antworten
- Zitieren
Donnerstag, 17. Mai 2012 16:04

0

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.
- Antworten
- Zitieren
Donnerstag, 17. Mai 2012 18:36

0
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 :)
- Bearbeitet coverdip Donnerstag, 17. Mai 2012 18:39
- Antworten
- Zitieren

Transmission_status is empty but the messages are still there.

Alle Antworten

Produkte

Ressourcen

Lösungen

Updates

Testversionen

Verwandte Websites

Training

Zertifizierungen

Weitere Ressourcen

Für Produkte

Weitere Supportlinks