CODEWITHSUNDEEP
service-broker
pepr
pepr

Reputation: 20794

Service Broker tutorial -- two SQL Server instances on one machine. How to route?

I am following the Completing a Conversation Between Instances tutorial from MSDN. The Lesson 2: Creating the Initiator Database (at the end) shows, how to create routes at the initiator site (shortened):

...
USE InstInitiatorDB;
CREATE ROUTE InstTargetRoute
WITH SERVICE_NAME =
       N'//TgtDB/2InstSample/TargetService',
     ADDRESS = N'TCP://MyTargetComputer:4022';

...

USE msdb;
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME =
       N'//InstDB/2InstSample/InitiatorService',
     ADDRESS = N'LOCAL'

and the Lesson 3: Completing the Target Conversation Objects does the same on the target instance:

USE InstTargetDB;
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME =
       N'//InstDB/2InstSample/InitiatorService',
     ADDRESS = N'TCP://MyInitiatorComputer:4022';

...

USE msdb
CREATE ROUTE InstTargetRoute
WITH SERVICE_NAME =
        N'//TgtDB/2InstSample/TargetService',
     ADDRESS = N'LOCAL';

However, the tutorial assumes that the SQL server instances run on separate hardware. How should I change the routing or whatever if the two SQL server instances run on the same machine?

Upvotes: 1

Views: 3003

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294407

The two instances cannot share the listener port. On Lesson 1 you had this:

...
CREATE ENDPOINT InstTargetEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
...

and on Lesson 2 you had this:

...
CREATE ENDPOINT InstInitiatorEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
...

This will not work as both instances are configured to listen on the same TCP port. One has to be different. Lets make the target listen on 4023:

...
CREATE ENDPOINT InstTargetEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4023 )
...

Then the route from the initiator to the target has to specify port 4023 now:

...
CREATE ROUTE InstTargetRoute
WITH SERVICE_NAME =
       N''//TgtDB/2InstSample/TargetService'',
     ADDRESS = N''TCP://MyTargetComputer:4023'';';
...

Everything else stays the same.

Upvotes: 3

Related Questions