Service Broker Question

  • I'm trying to push data from one server to another. In order to that, I need to create a route. On the Address, what should I put? Since target database resides in different server. Also, do I need to specify port as well? How do I know which port to specify?

    Any help would be appreciated.

    CREATE ROUTE TMS_Route WITH SERVICE_NAME='TMS_Destination_Service',  
    ROKER_INSTANCE='3AF39EB3-7018-4C2C-8DB5-17EF38120A8F', --service_broker_guid

    ADDRESS='LOCAL'

  • You'll need Broker Endpoints on both servers, because Broker conversations are two-way (send-ack). You also need the following four routes:

    1. On Initiator database: Route to target Service and instance IP, with Broker_GUID of target database
    2. On Target instance MSDB database: Route to target Service and database (ADDRESS = 'LOCAL')
    3. On Target instance database: Route to sending Service and instance IP, with BROKER_GUID of sending database (for acknowledgement of received message and Conversation activity)
    4. On Initiator instance MSDB database: route to sending Service and Broker_GUID of sending database (ADDRESS = 'LOCAL')

    Eddie Wuerch
    MCM: SQL

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply