Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

SQL Service Broker Managment Studio by 2D9wJO3j

VIEWS: 6 PAGES: 36

									User Guide                                                       SQL Server Service Broker Management Studio 2005




SQL Service Broker Management Studio 2005 - User Guide


                                     Author:                    Saravana Kumar
                                     Version:                   0.2
                                     Issue Date:                11 Apr 2005
                                     Last Saved On:             04 Dec 2011




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk)   Page 1 of 36
Last saved by Sql Server Marketing                                             b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                        SQL Server Service Broker Management Studio 2005




Document Control

Document Author and Contributing Authors
Author                                                   Role                            Team
Saravana Kumar                                           Architect/Developer

Contributing Author(s)                                   Role                            Team



Document Amendment History
Document        Date                 Amended By      Role                      Brief Summary of Changes
Version         Amended
0.1             01-04-2005           Saravana        Architect/Developer       First Flush
                                     Kumar




Document Review History
Document        Date                 Reviewed By         Role              Review Status
Version         Reviewed




Document Approval
Document        Date                 Approved By         Role              Document Status
Version         Approved




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk)    Page 2 of 36
Last saved by Sql Server Marketing                                                b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                       SQL Server Service Broker Management Studio 2005



Background
Service Broker is a new technology in Microsoft SQL Server 2005 that helps database developers build secure,
reliable, and scalable applications, which provides queuing and reliable messaging within SQL Server.
Because Service Broker is part of the Database Engine, administration of these applications is part of the
routine administration of the database.

Microsoft SQL Server 2005 is lacking with an administration tool to support the Service Broker functionality,
which makes developers and administrators to depend on SQL scripts to perform any kind of operation. This
results in lack of productivity to some extend and makes life harder for developers and administrators to do
routine tasks. Also, the loosely coupled Service broker objects like Message Types, Contracts, Queues,
Services etc has some kind of relationship between each other in a Service Broker Application, the current
version of Microsoft SQL Server 2005 doesn’t provide any view on object relationships.

In order to address the above issues and to make life easier for Service Broker application developers and
administrators this GUI tool SQL Service Broker Management Studio 2005 is designed and build.


Objective
This document describes the usage of SQL Service Broker Management studio 2005, GUI application. It is
meant to be a user’s guide on how to use various functionalities of the application. Applications users
(Developers and Administrators) can jump to various sections of guide depending on their requirement. This
guide is meant to be a complete reference for Sql Service Broker Management Studio 2005.


Section by Section Description
Section                                                  Description




Intended Audience
End user, Administrators, Architects, Developers building SQL Service broker applications.


External References
SQL Server 2005 Books online.
SQL Service Broker Beta Preview book – Roger Woulter
An explanation of how Service Broker communicates. - Michael G (dotnetfun.com)




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk)   Page 3 of 36
Last saved by Sql Server Marketing                                             b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                                 SQL Server Service Broker Management Studio 2005




Background .................................................................................................... 3
  Objective ........................................................................................................................................ 3
  Section by Section Description ....................................................................................................... 3
  Intended Audience ......................................................................................................................... 3
  External References ....................................................................................................................... 3
1.      Introduction:............................................................................................ 6
2.      Getting Started:....................................................................................... 7
  2.1     Connecting with Registered Servers and Object Explorer ..................................................... 7
  2.2     Changing the Environment Layout ........................................................................................ 8
  2.3     Showing the Summary Page ................................................................................................. 8
3.      Object Explorer ....................................................................................... 9
4.      Object Properties .................................................................................. 10
5.      Service Broker Artifacts Maintenance: ............................................... 11
  5.1 Message Types................................................................................................................... 11
    5.1.1 Adding new Message Type object ................................................................................ 11
    5.1.2 Editing Message Type object ........................................................................................ 12
    5.1.3 Deleting Message Type Object ..................................................................................... 12
  5.2 Contracts ............................................................................................................................ 13
    5.2.1 Adding new Contract Object ......................................................................................... 13
    5.2.2 Deleting Contract Object............................................................................................... 14
  5.3 Queues ............................................................................................................................... 14
    5.3.1 Adding a new Queue Object ......................................................................................... 14
    5.3.2 Edit Queue Object ........................................................................................................ 15
    5.3.3 Delete Queue Object .................................................................................................... 15
    5.3.4 Starting and Stopping the Queue.................................................................................. 16
    5.3.5 Status of the Queue...................................................................................................... 16
    5.3.6 View Messages in the Queue ....................................................................................... 16
  5.4 Service ................................................................................................................................ 16
    5.4.1 Adding new Service Object ........................................................................................... 17
    5.4.2 Editing Service Object .................................................................................................. 17
    5.4.3 Deleting Service Object ................................................................................................ 17
    5.4.4 Relational Diagram: ...................................................................................................... 18
  5.5 Route .................................................................................................................................. 18
    5.5.1 Adding New Route........................................................................................................ 19
    5.5.2 Editing Route Object..................................................................................................... 19
    5.5.3 Deleting Route Object .................................................................................................. 20
  5.6 Remote Service Binding...................................................................................................... 20
    5.6.1 Adding New Remote Service Binding ........................................................................... 20
    5.6.2 Editing Remote Service Binding Object ........................................................................ 21


 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk)           Page 4 of 36
Last saved by Sql Server Marketing                                                               b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                                SQL Server Service Broker Management Studio 2005


    5.6.3 Deleting Remote Service Binding Object ...................................................................... 21
  5.7 Xml Schema Collection ....................................................................................................... 21
    5.7.1 Adding New Xml Schema Collection............................................................................. 21
    5.7.2 Deleting Xml Schema Collection Object ....................................................................... 22
6.      Security ................................................................................................. 23
  6.1 Server (Instance) Level ....................................................................................................... 23
    6.1.1 Managing Endpoints ..................................................................................................... 23
    6.1.2 Managing Logins .......................................................................................................... 25
  6.2 Database Level ................................................................................................................... 26
    6.2.1 Managing Users ........................................................................................................... 26
    6.2.2 Managing Certificates ................................................................................................... 26
7.      Working with Conversations (Dialogue’s) .......................................... 29
  7.1 Creating New Conversation ................................................................................................ 30
  7.2 Display details of the Conversation ..................................................................................... 30
  7.3 Sending Request Message ................................................................................................. 31
  7.4 Sending Response Message .............................................................................................. 31
  7.5 View Message .................................................................................................................... 32
    Text View .................................................................................................................................. 32
    XML View ................................................................................................................................. 32
  7.6 End Conversation ............................................................................................................... 32
    7.6.1 With Error ..................................................................................................................... 33
    7.6.2 With Cleanup ................................................................................................................ 33
  7.7 Purging all Conversations in Database ............................................................................... 33
8.      Run Time Operations............................................................................ 35
  8.1 Monitoring Service Broker Operations................................................................................. 35
    8.1.1 Activated Tasks ............................................................................................................ 35
    8.1.2 Service Broker Connections ......................................................................................... 35
    8.1.3 Forwarded Messages ................................................................................................... 35
    8.1.4 Queue Monitors ............................................................................................................ 35
    8.1.5 Transmission Queues ................................................................................................... 35




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk)          Page 5 of 36
Last saved by Sql Server Marketing                                                             b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                       SQL Server Service Broker Management Studio 2005



    1. Introduction:
A number of large database applications require asynchronous and distributed execution. Most loosely-coupled
distributed systems provide scale out and resilience by queuing operations. Queues are used extensively in
applications such as order entry systems, stock trading, warehouse querying and travel reservation systems.
Writing queued distributed applications is a challenging task since it involves reliable network communication,
integrity and ordering of messages, tolerance to process and system failures and coordination of transactions
with the database. While most of these tasks can be achieved by using specialized messaging systems such
as MSMQ and MQSeries, the close association between handling data and communicating messages invokes
the need for an integrated solution.

SQL Server 2005 comes with a reliable messaging infrastructure called Service Broker to help organisations
face some of the challenges listed above. With Service Broker, internal or external processes can send and
receive guaranteed, asynchronous messages by using extensions to Transact-SQL Data Definition and
Manipulation Language (DDL & DML). Messages can be sent to a queue in the same database as the sender,
to another database in the same SQL Server instance, or to another SQL Server instance either on the same
server or on a remote server. .

The Service Broker programming model introduces several new concepts and includes new database objects
such as message types, contracts, queues and services. Services are endpoints in a database that can
initiate (or be the target for) message exchanges called conversations (dialogs).

Sql Service Broker Management Studio 2005:
Sql Service Broker Management Studio (SBMS) is new tool for SQL Server administrator and developers
building and administering SQL Service Broker applications. SBMS help creating and managing the new
database objects (message types, contracts, queues, services etc)it also supports creating and maintaining
supporting database objects like Certificates, users, logins etc using the graphical user interface (GUI), making
it one single tool to do all the Service Broker application related tasks.

Service Broker applications normally contain a set of objects like Message Types, Contracts, Queues, Service
etc working together. Most of the objects are connected to one another in one way or the other. Often its the
case, developers and administrators find it hard to group related objects together or find objects dependency
within one another. SBMS gives graphical representations of all the objects linked to each other as shown
below.




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk)   Page 6 of 36
Last saved by Sql Server Marketing                                             b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                       SQL Server Service Broker Management Studio 2005



    2. Getting Started:

    2.1 Connecting with Registered Servers and Object Explorer
As soon as the SBMS application is opened it pre-populates ―Registered servers‖ window with all the SQL
server instances running in the local machine. The user can then right-click on the required server instance and
select ―Connect‖, which bring the Login screen as shown below




User can select appropriate login option and provide required credential and click ―OK‖.

Once the user is successfully authenticated, SBMS will populate ―Object Explorer‖ window asynchronously with
the various objects from the server instance. Once the ―Object Explorer‖ is populated it will look like as shown
in below figure.




User can add as many server instances as required to the ―Object Explorer‖ by following the same procedure
explained above.


 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk)   Page 7 of 36
Last saved by Sql Server Marketing                                             b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                       SQL Server Service Broker Management Studio 2005



    2.2 Changing the Environment Layout
SBMS supports user customization to give developers and administrator a rich working environment. The
general layout of the application has the following parts:

        Registered Servers (Dockable)
        Object Explorer (Dockable)
        Properties Window (Dockable)
        Summary window, and
        Main working area




SBMS is designed in a way it gives similar working experience close to Visual Studio 2005 and Sql
Management Studio with rich set of dockable window functionality. Users will be able to do the following with
all the dockable windows:
        Make windows automatically hide or show.
        Can have a group of them display in a tabbed format.
        Can dock them against the edges of the IDE, or have them free-floating


    2.3 Showing the Summary Page
―Summary window‖ always gives some kind of summary based on the object selected in the ―Object Explorer‖.
Summary window can be viewed any time by selecting ―Summary Window‖ from ―View‖ menu or from the tool
bar.




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk)   Page 8 of 36
Last saved by Sql Server Marketing                                             b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                       SQL Server Service Broker Management Studio 2005



    3. Object Explorer
Object explorer is the key window in SBMS. Most of the operations will be performed from the context menu of
the treeview. Every single node in the treeview has context sensitive menus to perform various operations.

                                                     SQL Server 2005, Service Broker infrastructure has various
                                                     objects both at design time and run time, which forms the
                                                     application working environment. As shown in the picture it has
                                                     various Artifacts like
                                                              Message Types
                                                              Contracts
                                                              Queues
                                                              Routes
                                                              Remote Service bindings

                                                     Object explorer allows the user to view the Service Broker
                                                     artifacts in a formatted treeview structure. All the system objects
                                                     are highlighted in red and user created objects are highlighted
                                                     in black.

                                                     Object explorer acts as the heart of the application, any of the
                                                     object maintenance operations like creating; editing, deleting
                                                     Service Broker objects will be performed from the object
                                                     explorer.

                                                     Any number of server instances can be added to the object
                                                     explorer treeview by connecting from Registered servers.

                                                     Object explorer is a dockable window, so it can be
                                                              Automatically hidden or shown.
                                                              Can be grouped with other windows.
                                                              Can be docked against the edges of the IDE, or have
                                                               them free-floating




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk)   Page 9 of 36
Last saved by Sql Server Marketing                                             b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005



    4. Object Properties
                                                      Every single object displayed in the treeview has
                                                      corresponding properties associated with it, this gives users
                                                      quick access to the most important information required
                                                      frequently. For Clicking on the ―properties‖ context menu on
                                                      ―Server <Instance>‖ node will bring the properties window as
                                                      shown in the picture. This gives information about the server
                                                      environment, product details and type of authentication used to
                                                      logon to the server.

                                                      All the SQL service broker objects like Message Types,
                                                      Queues, Contracts, Services etc will display their
                                                      corresponding properties in the property window.

                                                      Property window is a dockable window, so it can be
                                                              Automatically hidden or shown.
                                                              Can be grouped with other windows.
                                                              Can be docked against the edges of the IDE, or have
                                                               them free-floating

                                                      For the detailed list of objects and their properties see the
                                                      Properties functional testing spread sheet.




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 10 of 36
Last saved by Sql Server Marketing                                              b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005



    5. Service Broker Artifacts Maintenance:
Service Broker Management studio (SBMS), allows the user to manage all the service broker related artifacts
listed below using the graphical user interface.

        Message Types:
        Contracts
        Queues
        Services
        Routes
        Remote Service Bindings
        Xml Schema Collection

SSBM allows users to add new objects, edit existing object, delete objects, script objects, view properties.
    5.1 Message Types
Messages sent to and from Service Broker Services must conform to a particular Message Type and Contract.
Message Types define the type of data the body of a message must contain or not contain. Contracts define
who can send messages (the initiating or target Conversation Endpoint) of a particular Message Type. So,
when two services send messages to each other, they do so by agreeing to communicate via the terms of a
Contract, which contains rules regarding which Message Types can be sent from one Conversation Endpoint
to another.

Message Types are validated against when they're received by a Service Broker Service. Validation, in this
context, refers to validating the type of data a message's body contains or does not contain. If the incoming
message (at the target) does not conform to the contract's allowable message types, an error message is
generated.

Types of Validation:
None - any type of message body is acceptable.
Empty - only an empty message body is acceptable.
Well Formed XML - only a message body with valid XML is acceptable.
Well Formed XML with Schema Collection - only a message with valid XML and XML schema is acceptable.

    5.1.1     Adding new Message Type object
SQL Syntax:
CREATE MESSAGE TYPE message_type_name
     [ AUTHORIZATION owner_name ]
     [ VALIDATION = { NONE
EMPTY
WELL_FORMED_XML
VALID_XML WITH SCHEMA COLLECTION schema_collection_name
} ]


Using SBMS:




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 11 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005




    1. Right-Click on the Message Types node in the object explorer and select ―New‖ from the context
       menu.
    2. Enter a valid name for the object (Mandatory).
    3. All other parameters are optional while creating a Message Type. User can select from the drop down
       Authorization, type of validation and Schema collection.
    4. Clicking the OK button will create the object after performing all the required validation.

Note: User will be able to just view the SQL Script instead of creating the object physically by pressing the view
script button.

    5.1.2     Editing Message Type object
SQL Syntax:
ALTER MESSAGE TYPE message_type_name
   VALIDATION = {      NONE
                             EMPTY
                             WELL_FORMED_XML
                             VALID_XML WITH SCHEMA COLLECTION schema_collection_name }
Using SBMS:
    1. Right click on the specfic Message type object from the object explorer window and select ―Edit‖ from
       the context menu.
    2. User can then modify required values and click OK to commit the changes.


    5.1.3     Deleting Message Type Object
SQL Syntax:
DROP MESSAGE TYPE message_type_name


Using SBMS:
    1. Right click on the specific Message Type object from the object explorer window and select ―Delete‖
       from the context menu
    2. Clicking OK will delete the object permanently.


 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 12 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005


    Note: This operation cannot be undone.


    5.2 Contracts
Contracts are agreements between two Service Broker Services (initiator or target). The initiator is the
Conversation Endpoint that starts a dialog conversation while the target is the Conversation Endpoint that is
the intended recipient of messages sent through that conversation. Each dialog conversation must adhere to
the rules of a Contract. Each contract contains one or more Message Types that can be used during a dialog
conversation between two Services. Also, each Service is defined with one or more Contracts. There are two
places where Contracts are specified:

1) When creating a Service and
2) When initiating a Dialog Conversation.

Once a Service has been created, you no longer have to specify which Contracts that Service can use.
Dialogs, on the other hand, have their associated Contracts specified at the time they're created. Contracts are
specified at each creation of a new dialog conversation.

Message types are added to the contract either as required on Initiator side, or on Target side or on both
Initiator and Target side.




    5.2.1     Adding new Contract Object
SQL Syntax:
CREATE CONTRACT contract_name
   [ AUTHORIZATION owner_name ]
       ( { message_type_name SENT BY { INITIATOR
                                       TARGET
                                              ANY }
Using SBMS:
    1. Right-Click on the Contract node in the object explorer and select ―New‖ from the context menu.
    2. Enter a valid name for the object (Mandatory).




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 13 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005


    3. Select the Message Type from the list and click either Initiator, Target or Initiator and Target to assign
       the accepted message type to the appropriate side of the contract.
    4. Clicking the OK button will create the object after performing all the required validation.

    5.2.2     Deleting Contract Object
SQL Syntax:
DROP CONTRACT contract_name
Using SBMS:
    1. Right click on the specific Contract object from the object explorer window and select ―Delete‖ from the
        context menu
    2. Clicking OK will delete the object permanently.
Note: This operation cannot be undone.
    5.3 Queues
Queues store messages, similar to how tables store data. When Service Broker receives a message for a
service, Service Broker inserts the message into the queue for that service. Each service is associated with
one queue. Each row in a queue is a message with appropriate values. An application uses the information in
the message row to identify each message uniquely and process the message appropriately. A queue may be
associated with a stored procedure, such stored procedures are called Activation Stored Procedures. In this
case, SQL Server activates the stored procedure when there are messages in the queue to be processed. SQL
Server can start more than one instance of the stored procedure up to a configured maximum.




    5.3.1     Adding a new Queue Object
SQL Syntax:
CREATE QUEUE <object>
   [ WITH
      [ STATUS = { ON OFF } [ , ] ]
      [ RETENTION = { ON OFF } [ , ] ]
      [ ACTIVATION ( [ STATUS = { ON OFF } , ]
            PROCEDURE_NAME = <procedure> ,
              MAX_QUEUE_READERS = max_readers ,
              EXECUTE AS { SELF | 'user_name'| OWNER }
               ) ]
     ]



 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 14 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005


      [ ON { filegroup [ DEFAULT ] } ]


Queue Object Options:




Using SBMS:
    1. Right-Click on the Queue node in the object explorer and select ―New‖ from the context menu.
    2. Enter a valid name for the object (Mandatory).
    3. All other fields are optional, user can enable/disable queue, enable/disable retention by selecting
       appropriate check box.
    4. Activation stored procedure can be assigned to the queue by checking Activate check box and
       choosing the appropriate database and stored procedure.
    5. File group where the queue will be residing can be selected from the drop down box.
    6. Clicking the OK button will create the object after performing all the required validation.


    5.3.2      Edit Queue Object
SQL Syntax:
ALTER QUEUE <object> WITH
   [ STATUS = { ON | OFF } [ , ] ]
   [ RETENTION = { ON | OFF } [ , ] ]
   [ ACTIVATION (
       { [ STATUS = { ON | OFF } [ , ] ]
             [ PROCEDURE_NAME = <procedure> [ , ] ]
             [ MAX_QUEUE_READERS = max_readers [ , ] ]
             [ EXECUTE AS { SELF | 'user_name'       | OWNER } ]
         |    DROP }
             ) ]


Using SBMS:
    1. Right-Click on the Message Types node in the object explorer and select ―Edit‖ from the context menu.
    2. User can enable/disable queue, enable/disable retention by selecting appropirate check box.
    3. Activation stored procedure can be assigned/modified to the queue by checking Activate check box
       and choosing the appropriate database and stored procedure.
    4. Clicking the OK button will modify the object after performing all the required validation.


    5.3.3      Delete Queue Object
SQL Syntax:
DROP QUEUE <Queue Name>
Using SBMS:
    1. Right click on the specific Queue object from the object explorer window and select ―Delete‖ from the
       context menu


 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 15 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005


   2. Clicking OK will delete the object permanently.
Note: This operation cannot be undone.

    5.3.4     Starting and Stopping the Queue
Administrators often create queues in an unavailable state. This prevents Service Broker from placing
messages on the queue until all of the objects for a service are created. When the service is ready to receive
messages, the administrator makes the queue available by right clicking on the queue object and selecting
―Start Queue‖ from the context menu. When a queue is unavailable for receive, an application cannot process
the messages in the queue. Because Service Broker guarantees that a message only leaves the queue as part
of a receive operation, there is no way to remove messages from a stopped queue.

Active queue can be stopped by right clicking on the queue object and clicking ―Stop Queue‖ from the context
menu.

This operation can also be performed by editing the queue and changing the status.


    5.3.5     Status of the Queue
Status of either all the queues in the database or individual queues can be viewed by clicing on the ―Queue
Status‖ option in the context menu either at the ―Queues‖ parent node on any of the child Queue object nodes.




    5.3.6     View Messages in the Queue
Messages in the queue can be viewed by Right clicking on the appropriate queue and selecting ―Display
Messages‖ from the context menu.




    5.4 Service
A service represents a related set of business tasks. The name of the service is also used to locate the queue
for the service.

The procedure for creating a service follows the same basic outline regardless of whether your service is an
initiating service, a target service, or both.




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 16 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005


The definition of a service specifies the contracts for which the service can be a target. In contrast, an
application can use a service to initiate a conversation that uses any contract defined in the database. Service
Broker takes this approach to enforce the general rule that a service should only receive messages that the
application can process. To ensure that the application does not receive messages of an arbitrary or unknown
type, Service Broker will accept a new dialog only if the dialog follows a contract specified in the service. An
initiating service specifies the contract to use when the conversation begins, so an initiating service does not
need to include the contract in the service definition.




    5.4.1     Adding new Service Object
SQL Syntax:
CREATE SERVICE service_name
   [ AUTHORIZATION owner_name ]
   ON QUEUE [ schema_name. ]queue_name
   [ ( contract_name | [DEFAULT] [ ,...n ] ) ]
Using SBMS:
    1. Right-Click on the Services node in the object explorer and select ―New‖ from the context menu.
    2. Enter a valid name for the Service
    3. Select the name of the queue from the list
    4. Add appropriate contracts from the list
    5. Clicking OK will create the Service object after all the validation.

    5.4.2     Editing Service Object
SQL Syntax:
ALTER SERVICE service_name
   [ ON QUEUE [ schema_name . ]queue_name ]
       [ ( < opt_arg > [ , ...n ] ) ]
Using SBMS:
    1. Right-Click on the appropriate service object and select ―Edit‖ from the Context Menu
    2. Select the name of the queue from the list
    3. Clicking OK will commit the changes.

    5.4.3     Deleting Service Object
SQL Syntax:

 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 17 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005


DROP SERVICE service_name
Using SBMS:
    1. Right click on the specific Service object from the object explorer window and select ―Delete‖ from the
        context menu
    2. Clicking OK will delete the object permanently.
Note: This operation cannot be undone.

    5.4.4     Relational Diagram:
A service in the service broker infrastructure is connected to different service broker objects like queues and
contracts, which in turn are connected to more objects like message types, activation stored procedures etc.
So, it is always difficult to see the impact of the script after executing them or its difficult to relate object to each
other found in the object explorer. In order to get better understanding of how objects are linked to each other
user can right click ―Service‖ node in the object explorer and select ―Relational Diagram‖. A new window will be
popped up showing all the relationships as shown in the following diagram.




A standard algorithm is used to place object and arrows in the container, if required user can then select the
objects individually or by small groups arrange them in the way they want it.


    5.5 Route
Service Broker uses routes to determine where to deliver messages. When a service sends a message on a
conversation, SQL Server uses routes to locate the service that will receive the message. When that service
responds, SQL Server again uses routes to locate the initiating service. By default, each database contains a
route that specifies that messages for any service which does not have an explicit route are delivered within
the SQL Server instance.

The basic components of a route are a service name, a broker instance identifier and a network address.




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 18 of 36
Last saved by Sql Server Marketing                                             b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005




    5.5.1     Adding New Route
SQL Syntax:
CREATE ROUTE route_name
[ AUTHORIZATION owner_name ]
WITH
   [ SERVICE_NAME = 'service_name', ]
   [ BROKER_INSTANCE = 'broker_instance_identifier' , ]
   [ LIFETIME = route_lifetime , ]
   ADDRESS = 'next_hop_address'
   [ , MIRROR_ADDRESS = 'next_hop_mirror_address' ]
Using SBMS:
    1. Right-Click on the Route parent node and select ―New‖ from the context menu.
    2. Enter a valid name for the Route.
    3. Select the ―To Service‖ from the list(Optional)
    4. Enter value for Broker Instance (Optional)
    5. Enter life time of the route (Optional)
    6. Select the Address type
    7. Clicking OK will create the Route object after all the validation.

    5.5.2     Editing Route Object
SQL Syntax:
ALTER ROUTE route_name
WITH
  [ SERVICE_NAME = 'service_name' [ , ] ]
  [ BROKER_INSTANCE = 'broker_instance' [ , ] ]
  [ LIFETIME = route_lifetime [ , ] ]
  [ ADDRESS = 'next_hop_address' [ , ] ]
  [ MIRROR_ADDRESS = 'next_hop_mirror_address' ]
Using SBMS:
    1. Right-Click on the appropriate route object and select ―Edit‖ from the context menu.
    2. Change required values like service name, broker instance, life time, address or mirror address.

 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 19 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005


    3. Clicking OK will commit changes made to the Route object.

    5.5.3     Deleting Route Object
SQL Syntax:
DROP ROUTE route_name
Using SBMS:
    1. Right click on the specific Route object from the object explorer window and select ―Delete‖ from the
        context menu
    2. Clicking OK will delete the object permanently.
Note: This operation cannot be undone.
    5.6 Remote Service Binding
A remote service binding establishes a relationship between a local database user, the certificate for the user,
and the name of a remote service. Service Broker uses the remote service binding to provide dialog security
for conversations that target the remote service.




    5.6.1     Adding New Remote Service Binding
SQL Syntax:
CREATE REMOTE SERVICE BINDING binding_name
   [ AUTHORIZATION owner_name ]
   TO SERVICE 'service_name'
   WITH     USER = user_name [ , ANONYMOUS = { ON OFF } ]


Using SBMS:
    1. Right-Click on the Remote Service Binding parent node and select ―New‖ from the context menu.
    2. Enter a valid name for the Remote service Binding object.
    3. Select Authorization owner name (Optional)
    4. Select the ―To Service‖ from the list, or enter a valid ―To Service‖ if contacting a remote service.
    5. Select the Certificate user and set anonymous status to true or false.
    6. Clicking OK will create the Remote Service Binding Object



 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 20 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005


    5.6.2     Editing Remote Service Binding Object
SQL Syntax:
ALTER REMOTE SERVICE BINDING binding_name
   WITH [ USER user_name ] [ , ANONYMOUS = { ON | OFF } ]


Using SBMS:
    1. Right-Click on the appropriate Remote Service Binding object (Child node) and select ―Edit‖ from the
       context menu.
    2. Anonymous status can be changed by enabling/disabling the anonymous check box.
    3. User can be changed
    4. Clicking OK will commit the changes to the Remote Service Binding Object.


    5.6.3     Deleting Remote Service Binding Object
SQL Syntax:
DROP REMOTE SERVICE BINDING binding_name


Using SBMS:
    1. Right click on the specific Remote Service Binding object from the object explorer window and select
        ―Delete‖ from the context menu
    2. Clicking OK will delete the object permanently.
Note: This operation cannot be undone.
    5.7 Xml Schema Collection
When creating a Message Type object validation can be set to a Xml Schema. SBMS allows users to create
Xml Schema collection using graphical user interface by browsing to the appropriate schema file or pasting it
on the text box.




    5.7.1     Adding New Xml Schema Collection
SQL Syntax:
CREATE XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier AS Expression


Using SBMS:


 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 21 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005


    1. Right-Click on the Xml Schema Collection parent node and select ―New‖ from the context menu.
    2. Browse to the appropriate schema file.
    3. Clicking OK will create the Xml Schema Collection Object.

    5.7.2     Deleting Xml Schema Collection Object
SQL Syntax:
DROP XML SCHEMA COLLECTION [ relational_schema. ]sql_identifier


Using SBMS:
    1. Right click on the specific Xml Schema object from the object explorer window and select ―Delete‖ from
        the context menu
    2. Clicking OK will delete the object permanently.
Note: This operation cannot be undone.




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 22 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005



6.     Security
Service Broker provides two distinct types of security — dialog security and transport security. Understanding
these two types of security, and how they work together, will help you to design, deploy, and administer
Service Broker applications.

Dialog security — Encrypts messages in an individual dialog conversation and verifies the identities of
participants in the dialog. Dialog security also provides remote authorization and message integrity checking.
Dialog security establishes authenticated and encrypted communication between two services.

Transport security — Prevents unauthorized databases from sending Service Broker messages to databases
in the local instance. Transport security establishes an authenticated network connection between two
databases.
     6.1 Server (Instance) Level
Transport security applies to the instance as a whole. Transport security does not secure the contents of
individual messages, nor does transport security control access to individual services within an instance.

     6.1.1    Managing Endpoints
SQL Server uses Service Broker endpoints for Service Broker communication outside of the SQL Server
instance.

An endpoint is a SQL Server object that represents the capability for SQL Server to communicate over the
network. Each endpoint supports a specific type of communication. A Service Broker endpoint configures SQL
Server to send and receive Service Broker messages over the network.

Service Broker endpoints provide options for transport security and message forwarding. A Service Broker
endpoint listens on a specific TCP port number.

By default, an instance of SQL Server does not contain a Service Broker endpoint. Thus, Service Broker does
not send or receive messages over the network by default. You must create a Service Broker endpoint to send
or receive messages outside the SQL Server instance.




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 23 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005




   6.1.1.1         Adding new Endpoints
SQL Syntax:
CREATE ENDPOINT endPointName [ AUTHORIZATION login ]
STATE = { STARTED | STOPPED | DISABLED }
AS { HTTP | TCP } (
   <protocol_specific_arguments>
        )
FOR { SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING } (
    <language_specific_arguments>
         )

<AS TCP_protocol_specific_arguments> ::=AS TCP (
    LISTENER_PORT = listenerPort
    [ , LISTENER_IP = ALL | (<4-part-ip> | <ip_address_v6> ) ]
)


<FOR SERVICE_BROKER_language_specific_arguments> ::=
FOR SERVICE_BROKER (
    [ AUTHENTICATION = {
             WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
        | CERTIFICATE certificate_name
        | WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificate_name
        | CERTIFICATE certificate_name WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
     } ]
    [ , ENCRYPTION = { DISABLED | SUPPORTED | REQUIRED }
         [ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ]
    ]
    [ , MESSAGE_FORWARDING = { ENABLED | DISABLED* } ]
    [ , MESSAGE_FORWARD_SIZE = forward_size ]
)


Using SBMS:
    1. Right-Click on the EndPoint node and select ―New‖ from the context menu.
    2. Enter a valid name for the EndPoint.
    3. Transport protocol: SQL Service broker supports only TCP transport at the moment.
    4. Set the options for TCP protocol Listener IP and Listener port.
    5. Set up the Service Broker options for EndPoint like Authentication, Encryption, Message forwarding
       etc.
    6. Clicking OK will create the EndPoint.

    6.1.1.2     Starting, Stopping and Disabling Endpoints
Service Broker sends and receives messages over the network while any endpoint for Service Broker is in the
STARTED state. To pause Service Broker networking, alter all Service Broker endpoints to set the state to
STOPPED. Notice that this prevents Service Broker from transmitting messages out of the instance or
receiving messages from outside of the instance, but does not affect message delivery within the instance.




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 24 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005




Start:
Right-Click on the corresponding EndPoint and select ―Start‖ from the context menu.

Stop:
Right-Click on the corresponding EndPoint and select ―Stop‖ from the context menu.

Disable/Pause:
Right-Click on the corresponding EndPoint and select ―Disabled‖ from the context menu.


    6.1.2     Managing Logins




    6.1.2.1    Adding new Logins
Using SBMS:
    1. Right-Click on the Login node and select ―New‖ from the context menu.
    2. Select either Windows authentication or SQL Authentication.
    3. Enter valid Login Name
    4. If SQL, provide valid password and reconfirm the password.
    5. Select the options to force password policy and password expiration
    6. Clicking OK will create the login after all the validation.



 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 25 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005



    6.2 Database Level

    6.2.1     Managing Users




   6.2.1.1         Adding new User
SQL Syntax:
CREATE USER user_name
    [ FOR
         { LOGIN login_name
         | CERTIFICATE cert_name
         | ASYMMETRIC KEY asym_key_name
         }
     ]
     [ WITH DEFAULT_SCHEMA = schema_name ]


Using SBMS:
    1. Right-Click on the User node and select ―New‖ from the context menu.
    2. Enter a valid name for the user.
    3. Select the login from the drop-down box.
    4. Select the Certificate from the drop-down box (Optional).
    5. Clicking OK will create the database user after all the validation.

    6.2.2     Managing Certificates
SQL Server uses certificates to verify the identity of a remote database, and to identify the local database
principal for the operation. Therefore, installing a certificate in SQL Server constitutes a statement of trust in
the database that holds the private key for the certificate. Carefully manage the certificates that you install and
the remote service bindings that you create.




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 26 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005




   6.2.2.1         Adding new Certificate
SQL Syntax:
CREATE CERTIFICATE certificate_name [ AUTHORIZATION user_name ]
     { FROM <existing_keys> | <generate_new_keys> }
     [ ACTIVE FOR BEGIN_DIALOG = { ON | OFF } ]


<existing_keys>::=
     ASSEMBLY assembly_name
     | {
           [ EXECUTEABLE ] FILE = ' path_to_file '
           [ WITH PRIVATE KEY <private_key_options> ]
       }


<generate_new_keys>::=
    [ ENCRYPTION BY PASSWORD = ' password ']
     WITH SUBJECT = ' certificate_subject_name '
     [ , <date_options> [ ,...n ] ]


<private_key_options>::=
     FILE = ' path_to_certificate '
     [ , DECRYPTION BY PASSWORD = ' password ' ]
     [ , ENCRYPTION BY PASSWORD = ' password ' ]


<date_options> :=
     START_DATE = ' mm/dd/yyyy ' | EXPIRY_DATE = ' mm/dd/yyyy '
Using SBMS:
    1. Right click on the Certificate node and select ―New‖ from the context menu.
    2. Enter a valid name for the Certificate.
    3. Select whether the certificate is available for BEGIN DIALOG (Conversation) or not.
    4. Select the appropriate Certificate source option. It can be
             a. Self Signed
             b. From File
             c. From File with Private key.
    5. Fill the appropriate values based on the chosen option.

 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 27 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005


   6. Clicking OK will create the certificate after all the validation.
Note: In order for SQL Server to use a certificate to begin a conversation, the certificate must be marked
ACTIVE FOR BEGIN_DIALOG. Certificates are marked as active for begin dialog by default. However, you can
choose to temporarily deactivate a certificate while updating the security configuration for a service.




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 28 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005



7.     Working with Conversations (Dialogue’s)
Every Service Broker message belongs to a dialog. Every dialog is automatically assigned a conversation
group: a logical grouping of related dialog conversations. Each dialog conversation agrees to a contract that
specifies which service, or conversation endpoint, can send messages to one another as well as what
Message Types can be used with each message. Message Types define what type of data the body of a
message must contain (or not contain).

When Service Broker delivers messages, it does so in an Exactly-Once-In-Order (EOIO) fashion. This means
that exactly one conversation group worth of dialog conversation worth of messages is delivered to a receiving
application at a time. When messages are retrieved from a receiving application, an entire conversation group
worth of dialog conversations is delivered to it. Each message is marked with a message_sequence_number
integer. The first message of each dialog sent has a message_sequence_number of 0. The second has a
message_sequence_number of 1, and so forth. Dialogs, on the other hand, are marked with a send_sequence
and receive_sequence integer. The first dialog of each conversation group has a send_sequence of 0. The
second has a send_sequence of 1, and so forth. receive_sequence pertains to how dialogs are delivered to an
application receiving messages from a queue.

Dialog conversations are made between two Service Broker services, or Conversation Endpoints. The Service
that has begun a dialog conversation with another Service is called the Initiator. The receiving Service for that
dialog conversation is called the Target. The Service is simply the interface through which Service Broker
sends messages to and from different Conversation Endpoints. Initiating and Target Conversation Endpoints
can exist in the same Service (same database) or completely different Services (different databases or even
different instances of SQL Server). Dialog conversations between two services are uniquely tracked by means
of a conversation_id - a UNIQUEIDENTIFIER value. This identifier exists at both Conversation Endpoints of a
Dialog Conversation.

When Services are created, they're assigned one or more Contracts. Contracts define who can send
messages of a particular Message Type: the Initiator or the Target. Message Types, on the other hand, define
what type of data the body of a message must contain (or not contain). When a dialog is created, it's attached
to a Contract. This means that every message sent through that dialog must obey the rules of the Contract,
and since each message is attached to exactly one Message Type, each message much obey the rules of its
assigned Message Type. Each Service Broker Service created is assigned at least one Contract. The Service
ensures that each messages it receives for delivery matches and obeys its assigned list of Contracts.

Each Message is held in a Queue, which is essentially a table in the database. They're held there until they're
retrieved or, if retention is on, the dialog conversations that they belong to are ended explicitly. When an
application retrieves messages, it receives them one conversation group at a time. Every time a message is
sent or received, the conversation group to which it (they) belongs is locked. During the lock, no other services
can send or receive messages to and from the locked conversation group. This feature enforces concurrency.

You start dialog conversations by using a T-SQL BEGIN DIALOG CONVERSATION statement.
You send messages by using a T-SQL SEND statement.
You end dialog conversations by using a T-SQL END CONVERSATION statement.
You retrieve message by using a T-SQL RECEIVE statement.




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 29 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005



    7.1 Creating New Conversation




SQL Syntax:
BEGIN DIALOG [ CONVERSATION ] @dialog_handle
   FROM SERVICE initiator_service_name
   TO SERVICE 'target_service_name'
       [ , { 'service_broker_guid' | 'CURRENT DATABASE' } ]
   [ ON CONTRACT contract_name ]
   [ WITH
   [ { RELATED_CONVERSATION = related_conversation_handle
      | RELATED_CONVERSATION_GROUP = related_conversation_group_id } ]
   [ [ , ] LIFETIME = dialog_lifetime ]
   [ [ , ] ENCRYPTION = { ON | OFF }          ] ]
Using SBMS:
    1. Right-Click on the Conversation node and select ―Begin New Conversation‖ from the context menu
    2. Select the ―From Service‖ from the drop-down box.
    3. Either enter and select ―To Service‖ from the tree view.
    4. Select a Contract from the drop-down box (Optional)
    5. Enable/Disable encryption by selecting the Check box (Optional)
    6. Enter a valid UNIQUE identified and select whether its Releated Conversation handle, or Related
       Group handle from the options (Optional), if ignored new value will be assigned automatically.
    7. Enter the Life Time of the conversation in seconds (Optional)
    8. Clicking OK will create a new conversation.



    7.2 Display details of the Conversation
Once the conversation is create Initiator and Target side of the conversation details can be viewed by right-
clicking on the corresponding conversation and selecting ―Display Details‖ from the context menu.




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 30 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005




    7.3 Sending Request Message
Once the detail of the conversation is displayed, Messages can be send from the initiator side by right clicking
on the messages grid and selecting ―Send Message‖ from the context menu, which will bring the ―Send
Message‖ window.




User can select appropriate message type from the list and enter the message in the text area. Clicking OK will
send the message to the target side.




    7.4 Sending Response Message
On the target side user can follow the same procedure as explained in sending the request message to send
the response message. Right-Click on the messages grid on the target side, select ―Send Message‖ from the


 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 31 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005


context menu, which brings send message window. Select the appropriate message type, enter the message
and click OK.




    7.5 View Message
Messages can be viewed in 2 different formats (only if its of readable text format). Message can be viewed
either as plain text, or it can be viewed in a formatted view as shown in the below pictures.

                  Text View                                            XML View




    7.6 End Conversation
Ending a conversation locks the conversation group that the provided conversation_handle belongs to. When a
conversation ends, Service Broker removes all messages for the conversation from the service queue.




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 32 of 36
Last saved by Sql Server Marketing                                                b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005


After a conversation ends, an application can no longer send or receive messages for that conversation. Both
participants in a conversation must call END CONVERSATION for the conversation to complete. If Service
Broker has not received an end dialog message or an Error message from the other participant in the
conversation, Service Broker notifies the other participant in the conversation that the conversation has ended.
In this case, although the conversation handle for the conversation is no longer valid, the endpoint for the
conversation remains active until the instance that hosts the remote service acknowledges the message.

End Conversation has 3 different options, they are
   1. End Conversation
   2. End Conversation with Error
   3. End Conversation with Clean up.

    7.6.1     With Error
This option will end the conversation with appropriate error message and error code. User can click on either
side of the conversation, select ―End Conversation with Error‖ option from the context menu, which will bring
the following screen.
If the conversation ends with an error and the conversation to the remote service is still active, Service Broker
sends a message of type http://schemas.microsoft.com/SQL/ServiceBroker/Error to the remote service.
Service Broker drops any other messages for this conversation currently in the transmission queue




Enter a valid error code (must be positive numbers) and text description and clicking OK will end the
conversation by sending appropriate error message to the other side.

    7.6.2     With Cleanup
The WITH CLEANUP clause allows a database administrator to remove conversations that cannot complete
normally. This option removes all messages and catalog view entries for the conversation. Notice that, in this
case, the remote side of the conversation receives no indication that the conversation has ended, and may not
receive messages that have been sent by an application but not yet transmitted over the network. Avoid this
option unless the conversation cannot complete normally.
    7.7 Purging all Conversations in Database
This options removes all the active conversations in the selected database. Useful to do clean up operations by
administrators.




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 33 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005




   1. Right-click on the Conversations node and select ―Purge All Conversations in DB‖ option from the
        context menu.
   2. Warning message will be displayed, clicking OK will remove all the conversations in the database
        permanently.
Note: This operation cannot be undone.




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 34 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005



8.     Run Time Operations
     8.1 Monitoring Service Broker Operations




     8.1.1    Activated Tasks
Contains a row for each stored procedure activated by Service Broker.

     8.1.2    Service Broker Connections
Contains a row for each Service Broker network connection.

     8.1.3    Forwarded Messages
Contains a row for each Service Broker message that the SQL Server instance is in the process of forwarding.

     8.1.4    Queue Monitors
Contains a row for each queue monitor in the instance. A queue monitor manages activation for a queue.




     8.1.5    Transmission Queues
Service Broker uses a transmission queue as a holding area for messages. Each database contains a
separate transmission queue.




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 35 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc
User Guide                                                      SQL Server Service Broker Management Studio 2005




 Saravana Kumar (saravana.kumar@digitaldeposit.co.uk) Page 36 of 36
Last saved by Sql Server Marketing                                            b306be44-329c-4081-a9a5-e812977d262d.doc

								
To top