Service broker can be a tricky beast to get your head around, however if you can build up your skills around identifying issues with the setup and operation of it – this will certainly help add to your DBA skill set.
Here is a common scenario – your developers may have set up a process using service broker, however when it comes to implementation time things may not be working correctly. Here are a list of the things that you can check in an instance to instance service broker configuration. (not all things may apply to your configuration, this is just a list of possible things that could go wrong or be incorrectly configured)
Make sure the routes are pointing to the correct IP address and port number. Check twice, and if you have doubts about connectivity use telnet to connect to the address and port to verify connectivity.
Make sure the queues are not disabled. If they are, renenable and you may need to clear out old conversations first if the queue keeps disabling itself.
You can run this code to clear out conversations.
USE <dbname> declare @counter int declare @conversationHandle uniqueidentifier SET @counter = 1 select @counter=COUNT(1) from sys.conversation_endpoints select top 1 @conversationHandle = conversation_handle -- select * from sys.conversation_endpoints while (@counter<>0 AND @conversationHandle IS NOT NULL) begin print @counter end conversation @conversationHandle with cleanup select top 1 @conversationHandle = conversation_handle from sys.conversation_endpoints SET @counter=@counter-1 end
Check the thumbprints are matching at both ends, if not export and copy the certificate again until they are.
use master select name, thumbprint from sys.certificates
Make sure the users have been granted permission on the service broker endpoint
Tool – SQL Profiler
In SQL Profiler there are a number of service broker options that you can select. Open up profiler and select all events and all columns. Tick all the broker ones as in the below image. You might want to untick everything else especially on a busy database.
Once you have the trace going you can fire through a few messages and view the output. Remember to check the normal SQL Server logs as well.
Tool – ssbdiagnose
ssbdiagnose is a SQL Server command line utility that you can run via a cmd window and switches. This is quite a handy tool and I have used it a couple of times when I was struggling to get to the bottom of a problem. You are best to read about it on BOL. Basically you put in a couple of switches and details and it outputs any errors it can find in the configuration.
As you fix up different implementations of service broker your skills and experience will continue to grow! If you have any tips you would like to share please do so.