SQL Server 2005 Service Broker Infrastructure Objects

By:   |   Updated: 2007-03-21   |   Comments   |   Related: More > Service Broker


In a recent tip you outlined steps to troubleshoot Service Broker.  I can see how that would be a good tip for someone who is actively working with Service Broker in SQL Server 2005.  Unfortunately, I am new to SQL Server 2005 and Service Broker.  I am not sure where to start with Service Broker.  Can you outline the steps that are needed to setup Service Broker?


Service Broker has a number of infrastructure components that need to be setup prior to sending and receiving messages.  The sending and receiving process can be in the same database, the same SQL Server instance or across 2 SQL Servers in different parts of the world as long as the connectivity and permissions permit the communications.  As such, in this tip we will outline the code to create the following infrastructure objects:

Sample Code

Below is sample Service Broker code that will setup the infrastructure objects from the bullet list in the previous section:

-- Object Name: Service Broker Infrastructure
-- Author: Edgewood Solutions
-- Development Date: 03.19.2007
-- Called By: TBD
-- Description: Service Broker infrastructure example
-- Project: MSSQLTips.com Example
-- Database: User Defined
-- Business Process: MSSQLTips.com Example
-- Num | CRF ID | Date Modified | Developer | Description
-- 001 | N\A | 03.19.2007 | JKadlec | Service Broker infrastructure example

-- 1 - Create the endpoint
-- The transport in this situation is encrypted and Windows authentication is used
-- Can be different on both databases
USE UserDefinedDatabase
create endpoint ep_SB_UserDefinedDatabase_tcp
= stopped
as tcp (listener_port = 4023, listener_ip = all)
for service_broker (authentication = windows, encryption = supported, message_forwarding = disabled);


-- 2 - Create the messagetype
-- Must be the same on both databases
USE UserDefinedDatabase
create message type mt_OrdersData
= well_formed_xml;

-- 3 - Create contract
-- Must be the same on both databases
USE UserDefinedDatabase
create contract ct_Orders
(mt_OrdersData sent by initiator);

-- 4 - Create the route between the databases
-- Must be different on both databases
USE UserDefinedDatabase
CREATE ROUTE rt_Orders_to_Fulfillment
= 'sv_Orders_Target',
BROKER_INSTANCE = '77742319-F387-4A34-6182-A58AA2A9830A',
ADDRESS = 'TCP://SQLServerName:4023';

-- 5 - Create queue
-- Must be different on both databases
USE UserDefinedDatabase
create queue dbo.qu_OrdersData_Initiator;

-- 6 - Create service
-- Must be different on both databases
USE UserDefinedDatabase
create service sv_Orders_Initiator
on queue dbo.qu_OrdersData_Initiator

-- 7 - Enable Service Broker at the database level
ALTER ENDPOINT ep_SB_UserDefinedDatabase_tcp STATE = STARTED;

Additional Notes

In a Service Broker conversation, two sets of the objects above are typically needed to support the conversation.  So be sure to create objects for both the initiator and target SQL Server environments.  There may be exceptions to this rule for some of these objects when conversations are in the same database or instance, but the decision is dependent on the situation.

As you can see, all of these commands involve a CREATE object statement.  All of the Service Broker commands also have corresponding ALTER and DROP statements which can be issued to change or remove the object.  In addition, you can access the objects via Management Studio by navigating to the correct SQL Server instance | Databases folder | database | Service Broker folder to review all of the objects listed above.

Next Steps
  • Service Broker has the means to dramatically change how users and applications interact with SQL Server.  This technology is going to offer a whole new set of opportunities for a variety of applications including order processing, inventory management, etc.
  • As you begin to work with SQL Server 2005, consider Service Broker to address your application needs and consider this technology as viable means of communication between systems.
  • Once you have your Service Broker infrastructure and application setup, reference the tip for troubleshooting the overall application.
  • Stay tuned for future tips on Service Broker to read and write entries to and from a sample Service Broker application based on the infrastructure objects created in this tip.

Last Updated: 2007-03-21

get scripts

next tip button

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at MSSQLTips.com, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips

Comments For This Article


Recommended Reading

Service Broker Troubleshooting

SQL Server Service Broker example on how to configure, send and receive messages

Event Notifications in SQL Server for Tracking Changes

SQL Server Service Broker error database cannot be enabled

SQL Server Service Broker Error Receiving Data - 10054 (An existing connection was forcibly closed by the remote host.)

get free sql tips
agree to terms