By: Jeremy Kadlec | Comments | Related: > Service Broker
Problem
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?
Solution
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:
- Endpoint
- Message Type
- Contract
- Route
- Queue
- Service
- Remote Binding Service
- Enable Service Broker at a database level
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 GO create endpoint ep_SB_UserDefinedDatabase_tcp state = stopped as tcp (listener_port = 4023, listener_ip = all) for service_broker (authentication = windows, encryption = supported, message_forwarding = disabled); GO -- 2 - Create the messagetype GO create message type mt_OrdersData validation = well_formed_xml; GO -- 3 - Create contract GO create contract ct_Orders (mt_OrdersData sent by initiator); GO -- 4 - Create the route between the databases GO CREATE ROUTE rt_Orders_to_Fulfillment WITH SERVICE_NAME = 'sv_Orders_Target', BROKER_INSTANCE = '77742319-F387-4A34-6182-A58AA2A9830A', ADDRESS = 'TCP://SQLServerName:4023'; GO -- 5 - Create queue GO create queue dbo.qu_OrdersData_Initiator; GO -- 6 - Create service GO create service sv_Orders_Initiator on queue dbo.qu_OrdersData_Initiator (ct_Orders); GO -- 7 - Enable Service Broker at the database level GO ALTER ENDPOINT ep_SB_UserDefinedDatabase_tcp STATE = STARTED; GO ALTER DATABASE UserDefinedDatabase SET TRUSTWORTHY ON; GO |
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.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
