Blog For .NET

My current work involves asyncronous communication between multiple servers. Servers in the web farm submit jobs for processing by various backend server farms. Over time our implementation evolved from a simple stored procedure call that polled a table to a much more scalable and fault tolerant MSMQ implementation.

We moved to MSMQ because polling SQL Server tables leads to locking issues with multiple pollers and larger data sets.  Unfortunately, the move to MSMQ separated messages from data, requiring complicated logic to simulate distrubuted transactions across queues and the database. We moved to SQL Service Broker because it is designed to handle multiple pollers, utilizes t-sql,and calls are made inside the current t-sql transaction.

Unfortunately the documentation for Service Broker isn’t all that intuitive and the Microsoft tools (Sql Management Studio in particular) don’t really make things easier. Learning about Service Broker takes time and a healthy dose of experimentation.  Our implementation has been live now for several months and is working well.

Here are a few things we learned along the way:

• you can see what messages are in a queue by selecting from it - select * from myQueue.
• the system views sys.service_queues and sys.transmission_queue are your friends. If messages are sent but don’t show up in the queue, check the reason column of the transmission queue.  Most of the time it is because the database master key permissions aren’t set correctly, particularly if the database has been restored. 
• permissions need to be granted on queues and services to send and receive from them:

  --Grant the user access to the service
  GRANT SEND ON SERVICE::[Your_Service_Name] TO [domain\john.doe] ;
  –Grant the user send rights
  GRANT RECEIVE ON [Your_Queue_Name] TO [domain\john.doe] ;
  –Grant the user receive rights
  GRANT SEND ON [Your_Queue_Name] TO [domain\john.doe] ;

• when using Activation on queues the procedure actions are included in the current transaction/
• sending/receiving messages are included in the current transaction.
• sql management studio does not script broker objects “naturally” - when you script database objects through the UI, service broker objects aren’t included. When you script service broker objects, permissions aren’t included

• Alter database your_database set new_broker

  will clear all your queues

• messages in queues are encrypted by default. All messages are hex encoded.
• if you use a common data store for each endpoint, keep messages as terse as possible. The actual content of the messages is less important than the message itself. If you need additional data, you can retrieve it from the database.

Service Broker has more features than any standard user is likely to encounter.  Asyncronous symptoms can be complicated; building a secure, scalable, fault tolerant system takes a lot of forethought.  Until Microsoft better integrates its product we have blogs, technical articles, forums, etc. to help augment the existing information. Good luck.

No Comments yet »