So you are running into a blocking issue, try to find a solution and you gets back a reply like ‘Sorry, I cannot repro your problem’. You go back and try again, and sure the problem is still there. You dig deeper, go to the Advanced search options on Google, but still cannot find the answer. Desperately, you try again and, surprise, the problem has vanished. Sounds familiar?

I know of one such problem that appears again and again when discussing Service Broker, Database Mirroring and, to a lesser extent, SQL Server 2005 Cryptographic facilities. I want to show you what the problem is, what’s causing it and even drill a bit into how something so basic has slipped into the product.

Read the rest of this entry »

I have previously talked here about the queue monitors and the role they play in launching activated procedures. If you recall, I’ve mentioned that the Queue Monitors will enter the NOTIFIED state after they launch a stored procedure and not launch again the procedure until the Queue Monitor notices that RECEIVE statements are being issued against the queue. In an older post I have also talked about how difficult is to get error handling right, and in particular cast and convention errors. This may seem a trivial problem but in the Service Broker programs it is actually a serious problem because of the frequent conversation to and from XML. These two separate issues can actually ‘cooperate’ into a somehow surprising behavior. Namely if the activated procedure hits an error before it completes the RECEIVE statement, the Queue Monitor will stay in NOTIFIED state and won’t activate again the procedure. Although this looks similar to the typical poison message behavior when the queue is automatically disabled, this is a different issue. And unlike the poison message case, in the case when the Queue Monitor is stranded in NOTIFIED state you can not get a notification that the queue is no longer functional.

This post continues with an example showing how a relatively safe activated procedure can end up in this situation.

Read the rest of this entry »

In case you miss it, the SQL Server Service Broker has recently started posting in an MSDN team blog: http://blogs.msdn.com/sql_service_broker. The team members have already made strides and covered some hot topics like shipping near real-time OLTP data to ETL, some advanced discussion of SSB routing, poison message handling, some juicy internals how-it-works details and also revisited that never exhausted topic of setting up your first remote dialog.

Marcin Policht has a series of articles on databasejournal.com covering Service Broker. So far the series has covered an introduction, a presentation of SSB objects and a conversation primer.

These series are part of a SQL Express cover done by Database Journal and they are to continue with more SSB goodies.

A major class of Service Broker applications have nothing to do with the distributed application capabilities SSB was designed for. Instead they leverage SSB’s capability to run a stored procedure outside the context of the user connection. This capability enables to schedule execution and don’t wait for it to finish, or to schedule multiple procedures to execute in paralel. Often the developers that use these capabilities don’t care for the reliable message delivery SSB offers and many see the complex infrastructure of message types, contracts and services as a mere hurdle. Nonetheless sooner or later they have to troubleshoot an issue and then is when many of you find my blog and my articles. Troubleshooting activated procedure is not difficult, but one simply has to know where to look and what to look for.

In this entry I want to shed some light in the soul that drives the activation process: the Queue Monitors.

Read the rest of this entry »

Those who played with the original Terrarium project back in .Net 1.1 now have a chance to revisit this fun project. The Terrarium2 for .Net 2.0 was released on codeplex, including the project sources.
If you never had a chance to see the Terrarium, check it out. Terrarium allows you to create creatures with behavior you program in .Net and set them up into a virtual peer-to-peer world of Terrarium instances. You can track your creature in the wild, how does it do against other creatures. A nice incentive to learn some basic AI stuff and keep your programming skills sharp in a fun virtual fight-to-the-death competition with other programmers.
Read more on the Windows SDK team blog

On my last post I’ve said that the Toad tools from Quest lack the option to configure Dialog Security for Service Broker. I stand corrected, as they actualy do. Not only that, but they support both full security and anonymous security, and the configuration is also done through a wizard. The Dialog Security Configuration Wizard is launched from the context menu of a service.

The wizard is explicit for security and does not combine security and routing, like the Service Listing Manager does. So to fully configure a pair of services you have to run the Service Broker Application Wizard first and then to follow up with the Dialog Security Configuration Wizard.

The Dialog Security Wizard will guide you through the steps of creating the certificates, set up a database master key, create the users needed, exchange the certificates and set up the remote service binding.

Read the rest of this entry »

Technically Service Broker can offer a huge value over any other comparable technology, but every project faces in the early stages the same barrier: the extremely steep learning curve faced by developers and dba in order deploy a Service Broker solution. As the adopters try to set up a simple scenario to ‘play’ with the technology before making a decision they are facing an apparently insurmountable challenge: set up the configuration for that very first message to get through. Not only services, queue, contracts and message types have to be correctly configured, but also the transport security (endpoints and certificates, logins and endpoint connect permissions), service routing and last but not least service security (remote service bindings, again certificates, users without login and service send permission). These are all new concepts, not trivial ones, and they all have to be correct. And I haven’t even mentioned database master keys! So one is faced with the task of configuring more than ten new object types, never encountered before, just to test his first message. No wonder some feel frustrated and may abandon the technology before even seeing what is capable of.

Tools are the biggest missing piece of this puzzle. A wizard that would walk the new developer or dba through the maze of new concepts and objects to help him get that very first test scenario to work would be tremendous help. Unfortunately the set of tools that ship with SQL Server is in dire need of such wonder. For years my recommendation was to give a try to the Service Listing Manager tool. This tool, specially the command line version, takes all the headake out and can do all the work for you, configuring everything needed for two services to be able to exchange messages. But this tool is not an officially supported Microsoft release, and therefore many organizations cannot afford to use it in their environment.

Recently I have learned that 3rd party vendors stepped up to the challenge and have come up with a set of tools designed for Service Broker. Quest Software’s Toad is a well known toolset, and on Oracle environment is the toolset, the one everybody uses. And now it contains the Service Broker Manager, a set of tools that covers everything related to Service Broker in SQL Server 2005. Organizations that are looking for a set of tools for Service Broker deployment, maintenance and administration have now a commercially supported alternative.

Read the rest of this entry »

I have uploaded here the slides I presented today at SQLPASS on Service Broker Administration, Monitoring and Troubleshooting:
SQLPASS 2008 Slides

I’m about to embark on my flight to Germany for this year PASS conference. My 2008 session is Service Broker Administration, Monitoring and Troubleshooting and is scheduled on Tuesday April 15 at 10:45, see
http://www.european-pass-conference.com/agenda.aspx

If you are at this years European PASS Conference stop by and say Hello!