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.