Create route in cross-server SSB

Create route in cross-server SSB

 

A ROUTE is used by Service Broker to determine where a service is located, be local or remote. A route is created using the CREATE ROUTE command.

 

CREATE ROUTE route_name

[ AUTHORIZATION owner_name ]

WITH

[ SERVICE_NAME = 'service_name', ]

[ BROKER_INSTANCE = 'broker_instance_identifier' , ]

[ LIFETIME = route_lifetime , ]

ADDRESS = 'next_hop_address'

[ , MIRROR_ADDRESS = 'next_hop_mirror_address' ]

 

For example:

CREATE ROUTE Route_LocalServer

WITH SERVICE_NAME = '//RemoteServer/RemoteSSBService',

BROKER_INSTANCE = N'CFA8070A-9E72-498A-A427-32F950B3C6E7',

ADDRESS = 'TCP://192.168.0.10:4020'

GO

 

The BROKER_INSTANCE parameter can be retrieved from sys.databases:

Select service_broker_guid from sys.databases where name=’YOUR_DB_NAME’

 

A REMOTE service binding is used to map the security credentials used to open a conversation with a remote SSB service. A remote service binding is created using the CREATE REMOTE SERVICE BINDING command.

 

CREATE REMOTE SERVICE BINDING BINDING_NAME

  TO SERICE '//RemoteServer/RemoteSSBService',

  WITH USER = [USER NAME],

  ANONYMOUS = OFF

 

[USER NAME] should be mapped to the public certificate of the associated DATABASE in the other SQL SERVER instance.

 

CREATE CERTIFICATE LocalCert

AUTHORIZATION [USER NAME]

FROM FILE = 'C:\Temp\RemoteCert.cer'

GO

The [USER NAME] user is given authorization to the public certificate of the remote server.