Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2005 Service Broker Infrastructure Objects


By:   |   Last Updated: 2007-03-21   |   Comments   |   Related Tips: More > 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:

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
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
-- Must be the same on both databases
USE UserDefinedDatabase
GO
create message type mt_OrdersData
validation
= well_formed_xml;
GO

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

-- 4 - Create the route between the databases
-- Must be different on both databases
USE UserDefinedDatabase
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
-- Must be different on both databases
USE UserDefinedDatabase
GO
create queue dbo.qu_OrdersData_Initiator;
GO

-- 6 - Create service
-- Must be different on both databases
USE UserDefinedDatabase
GO
create service sv_Orders_Initiator
on queue dbo.qu_OrdersData_Initiator
(ct_Orders);
GO

-- 7 - Enable Service Broker at the database level
ALTER DATABASE UserDefinedDatabase SET ENABLE_BROKER;
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.


Last Updated: 2007-03-21


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools