Troubleshooting dialogs, the sequel
November 28th, 2007Almost 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.
Syntax
SSBDIAGNOSE
[<GeneralOptions>] {<Configuration>}
|
[-?]
<GeneralOptions> ::=
[-XML]
[-LEVEL {ERROR | WARNING | INFO}]
[<ConnectionOptions>]
< 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
window.
-LEVEL ERROR Display only error messages.
WARNING Display error and warning messages (default).
INFO Display error, warning, and informational
messages.
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
configured.
ANONYMOUS Requests verification that anonymous dialog
security is configured.
FROM SERVICE service_name Specifies the name of the service that initiates
conversations.
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
database.
-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
Authentication.
-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:
|
BEGIN DIALOG |
ssbdiagnose.exe |
BEGIN DIALOG @handle FROM SERVICE [initiator]
TO SERVICE 'target'
ON CONTRACT [mycontract]
WITH ENCRYPTION = OFF
|
ssbdiagnose configuration from service initiator - 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
ervice
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.