Back To Basics: Service Broker Routes

Published On: 2008-06-19By:

Routes are only needed when sending service broker messages from one server to another.  They define the SQL Server and TCP Port which the sending SQL Server will connect to in order to deliver the message.  If you are sending the message to a mirrored database, then you can specify the mirror as well as the primary database.

If you needed to you could setup three or more servers in a chain and have them forward the messages from one server to another.  This would be handy if you needed to get messages through firewalls and the source and destination servers were not permitted to talk to each other.  The only requirement to do this is that one server in each conversation must be a paid for edition of SQL Server.  In other words two SQL Express instances can not send messages to each other directly.  Those messages much be forwarded through a Workgroup, Standard, or Enterprise instance.

Before you can create a Service Broker Route, you have to have an endpoint on the remote machine.  For this purpose we’ll assume that the Service Broker endpoint was created on port 1234.  Our local server is SQL1 and the remote machine will be SQL2.  The only other piece of information that you need to know is the Service Broker Instance GUID from SQL2.  This is found in the service_broker_guid column of the sys.databases DMV on server2 (fifth column from the right in SQL 2005).  If the GUID is all 0s then you need to enable the service broker by using the ALTER DATABASE Command.

The CREATE ROUTE syntax is very easy.

WITH SERVICE_NAME = 'ServiceName',
BROKER_INSTANCE = 'ae8505fa-b84d-4503-b91f-3252825ccf09', /*Use your GUID here*/

If your target is using database mirroring set the MIRROR_ADDRESS to the name and port number of the mirror.  This way in the event of a fail over the sending server can continue to deliver messages.

If you need the route to expire on a specific date, for example you are sending data to a partner and you want to automatically stop sending them the messages when the contract ends, add the LIFETIME parameter with the number of seconds until the route expires.  If the LIFETIME is omitted the route will never expire.


Contact the Author | Contact DCAC


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link