Troubleshooting dialogs, the sequel

November 28th, 2007

Almost two years ago I have posted the original Troubleshooting dialogs post in this blog (back in the day when it was hosted at MSDN). I have often referenced this post when I was asked to help investigate some Service Broker issue, and I have seen others referencing it. Although it lacked a lot of details it was a good starting point for anybody who was asking itself ‘The messages don’t get through, wonder what’s wrong…’

It is time for an updated entry on this topic because the bar was raised to a whole new level: the Service Broker Diagnostics Tool was shipped in the November CTP of SQL Server 2008!

So what is this new Diagnostics Tool? It is a command line utility that will investigate the configuration of a service deployment and will check if all the necessary requirement to exchange messages with another service are met. The tool basically contains all the know-how on how to troubleshoot Service Broker issues and has the means to collect configuration data, analyze the configuration and offer a diagnostic on the spot. Although everything the tool does could also be done by an experienced dba by looking at the same configuration data the tool is looking at, the fact that tool does it automatically makes it much easier. And besides, not everybody is an expert dba and even less an expert Service Broker configuration dba. Note that although the tool is part of the SQL Server 2008 tools, it is perfectly capable of diagnosing SQL Server 2005 deployments.


        [<GeneralOptions>] {<Configuration>}
<GeneralOptions> ::=
< Configuration> ::=
        CONFIGURATION  {<FromService> <ToService> | <FromService> | <ToService>}
                       { ON CONTRACT contract_name }
                       [ ENCRYPTION {ON | OFF | ANONYMOUS} ]
<FromService> ::=
        FROM SERVICE service_name [<ConnectionOptions>] [<MirrorOptions>]
<ToService> ::=
        TO SERVICE service_name [,broker_id]
        [<ConnectionOptions>] [<MirrorOptions>]
<MirrorOptions> ::=
        MIRROR <ConnectionOptions>
<ConnectionOptions> ::=
        {-E |   {-U login_id [-P password]}}
        [-S server_name[\instance_name]]
        [-d database_name]
        [-l login_timeout]
SSBDIAGNOSE analyzes the configuration between two SQL Server Service Broker
services, or for a single service. Any errors found are reported either in the
command prompt window in human-readable text, or in formatted XML that can be
redirected to a file or another application.
-XML                            Specifies generating the output as an XML file
                                that can be redirected to a file or another
                                application. If -XML is not specified, the
                                output is displayed in the command prompt
-LEVEL  ERROR                   Display only error messages.
        WARNING                 Display error and warning messages (default).
        INFO                    Display error, warning, and informational
CONFIGURATION                   Requests a report of any configuration errors
                                between two Service Broker services, or for a
                                single service.
ON CONTRACT contract_name       Requests that only configurations that use the
                                specified contract be analyzed. If ON CONTRACT
                                is not specified, ssbdiagnose reports on the
                                contract named DEFAULT.
ENCRYPTION ON                   Requests verification that full dialog security
                                is configured (default).
           OFF                  Requests verification that no dialog security is
           ANONYMOUS            Requests verification that anonymous dialog
                                security is configured.
FROM SERVICE service_name       Specifies the name of the service that initiates
TO SERVICE service_name         Specifies the name of the target service.
           [, broker_id]        Specifies the Service Broker identifier for the
                                target database; broker_ID is a GUID.
MIRROR                          Specifies that the associated Service Broker
                                service is hosted in a mirrored database, and
                                gives the connection information to the mirror
-E                              Opens a Windows Authentication connection to an
                                instance of the SQL Server Database Engine using
                                your Windows account. By default, if neither -E
                                or -U are specified, ssbdiagnose uses Windows
-U login_id                     Opens a SQL Server Authentication connection to
                                an instance of the Database Engine using the
                                specified login ID.
-P password                     Specifies the password for the login that is
                                specified in -U.
-S server_name[\instance_name]  Specifies the instance of the Database Engine
                                holding the Service Broker service to be
                                analyzed. Specify server_name to connect to the
                                default instance on the specified computer.
                                Specify server_name\instance_name to connect to
                                a named instance. If -S is not specified,
                                ssbdiagnose connects to the default instance on
                                the local computer.
-d database_name                Specifies the name of the database that is
                                holding the service to be analyzed. If -d is
                                not specified, the default is your login's
                                default-database property.
-l login_timeout                Specifies the number of seconds before an
                                attempt to connect times out. The value must be
                                between 1 and 65534. Not specifying -l or
                                setting login_timeout 0 specifies the time-out
                                to be infinite.
-?                              Displays command-line help.

Now that’s one complex command line set of options! Actually, it is much easier to think in terms of the BEGIN DIALOG verb options. Lets compare the two side by side when establishing a dialog between a service named ‘initiator’ hosted in the database db_init on ServerA and a service named ‘target’ hosted in database db_target on ServerB:



    FROM SERVICE [initiator]
    TO SERVICE 'target'
    ON CONTRACT [mycontract]
ssbdiagnose configuration
   from service initiator -S ServerA -d db_init
   to service target -S Server B -d db_target
   on contract mycontract
   encryption off

When the tool is run it will analyze the configuration of your services and endpoints and will report any problem it finds that would prevent the two services from being able to exchange messages. I have set up two services for test on my SQL Server instances and ran the tool and here is a sample diagnostic output:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn
>SSBDiagnose.exe configuration from service "//MyInitiatorService" -S . -d diagn
ose_init to service "//MyTargetService" -S .\katmai -d diagnose_target on contra
ct mycontract encryption off
 29931 REMUSR10        diagnose_init   There is no route for service //MyTargetS
 29931 REMUSR10\katmai diagnose_target There is no route for service //MyInitiat
orService and broker instance 05D10DB7-8123-4F9F-AE7E-C69AF799410A
 29975 //MyTargetService diagnose_target User public does not have SEND permissi
on on service //MyTargetService
 29950 REMUSR10\katmai                 The Service Broker endpoint was not found
4 Errors, 0 Warnings

In this case the tool has reported that the routes are missing or incorrect, the target service does not have the necessary SEND permission needed for dialogs started without encryption and finally the instance REMUSR10\Katmai does not have a Service Broker endpoint. The output contains an error code, the SQL Server instance to which the error applies, the database to which the error applies and the error message. The database name is optional and if missing it means the error message is relevant to the instance, not to a specific database. Optionally the output can be formatted as XML so that it can be processed by other automation tools. This is achieved by specifying the /XML command line option.

Comments are closed.