SQL Server Service Broker Newbie Problems - Part III

Here's a really handy command for when you get Service Broker into a bad state, or just have so much junk in transmission_queue that you want to start over:

 

ALTER DATABASE foo SET NEW_BROKER

 

This will end any open conversations with an error and create a new broker in the database. Note, however, that it does not drop any queues or services that you may have defined, although it will empty your queues.

 

I could be missing some important side effect of this command, of course: I'm still trying to figure this stuff out.

SQL Server Service Broker Newbie Problems - Part II

So after figuring out my last problem, I was still unable to do something as simple as send a test message from one simple service to another. Here's how I was setting things up:

 

create queue mysenderqueue with status = ON;

create queue myreceiverqueue with status = ON;

create service mysenderservice on queue mysenderqueue;

create service myreceiverservice on queue myreceiverqueue;

 

declare @dialog_handle uniqueidentifier

begin dialog conversation @dialog_handle from service mysenderservice to service 'myreceiverservice';

send on conversation @dialog_handle ('This is a message');

 

Those of you that know Service Broker will already have spotted the problem. It took me a little longer. :P

 

The symptoms here are what were so confusing to me. After running the above SQL, if I did a

 

select * from mysenderqueue;

select * from myreceiverqueue;

 

I'd see that there were no messages in receiverqueue, but senderqueue had one. WTF? Why didn't my message go to the right queue?

 

With the help of the inestimable Bob Beauchemin, I was able to figure out that what I should have been doing was this instead:

 

select cast(message_body as XML) from mysenderqueue;

 

If I had, I would have seen that the message in senderqueue actually said

 

<Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">

  <Code>-8425</Code>

  <Description>The service contract 'DEFAULT' is not found.</Description>

</Error>

 

So really, it wasn't that my message was going to the wrong queue, it was that Service Broker was bouncing my message and queuing an error back to the originating service.

 

Now, I was still confused, because this seems to imply that the DEFAULT contract isn't defined, when I can clearly see that it is via SQL Server Management Studio. A bit more digging revealed the following line in the docs for CREATE SERVICE:

 

"If no contracts are specified, the service may only initiate conversations."

 

While surprising (why name a contract DEFAULT if it's not the default?) this certainly was consistent with what I was seeing. Once I changed my service definition for the receiver service to be

 

create service myreceiverservice on queue myreceiverqueue ( [DEFAULT] );

 

then everything started working. Cool! Now on to the next newbie problem!

SQL Server Service Broker Newbie Problems - Part I

One of the things I've been doing lately is checking out some of the new features in SQL Server 2005. And in particular, I've been looking into Service Broker. From the little research I've done so far, it seems quite exciting: a real queuing system that's integrated right into the database. One of the problems I've always had with MSMQ is that it tends to smear out your application state between a database and the opaque message store. After all, a queue is basically just a database table with some special "triggers" attached to it that move the row to another table.

 

At any rate, after doing a bunch of reading, I sat down to actually use the thing. I was hoping that it would be slam-dunk simple, but I hit a few roadblocks. I'm not ready yet to say whether that means SB is difficult to use, but I thought I'd document my problems (and the solutions) here in case anyone else runs into them.

 

The first problem I ran into was that the database needs a master encryption key. That is, in the database where my queues were set up, I needed to run this statement:

 

create master key encryption by password = 'my lame-ass password'

 

Before I did that, I wasn't seeing anything at all in either my source or my destination queues when sending a message. I was able to track the problem down with the help of Service Broker guru Dan Sullivan. He told me to run

 

select * from sys.transmission_queue

 

and sure enough, there was an error there that read

 

The session keys for this conversation could not be created or accessed. The database master key is required for this operation.

 

which made it pretty obvious what the problem was.

 

Of course, that wasn't my last problem, but I'll save those for other posts.

Security Training Modules

Yo, what Keith said. Videos are about 10 minutes - short enough to watch at work while the pointy-haired boss is draining his monitor.

Rob Blogs

I see my friend Rob Engberg has a blog. Not only is Rob my oldest friend (we met at age seven), but we "came up" professionally together, working at our first real jobs doing the same thing.

 

It looks like Rob's blog is going to be a mix of technical stuff, adventure racing stuff, and a chronicle of his upcoming move to New Zealand. Given that Rob is one of the guys I go to when I hit a weird problem that I can't Google my way out of, you can definitely count me subscribed. 

VMWare Server Free

This blog has been nearly silent for quite some time now. Why? Frankly, because I'm fairly burnt out at the moment. Like many of you, I've got a lot going on both at home and at work, and it's been wearing on me a bit. Unfortunately, it doesn't look like it's going to let up any time soon.

 

But enough complaining. Here's something interesting: it looks like VMWare Server has gone freeware. Nice. For years I've been hearing my friends say VMWare is superior to Virtual PC. Now I guess I'll get a chance to see for myself.