This article is a continuation of my two articles on using Service Broker as a pure data-push one-way communication channel: Reusing Conversations and Recycling Conversations. I originally did not plan for this third part, but I was more than once asked the same question: if a conversation is in error what happens to the messages that were sent but not yet delivered?

The short answer is that messages that are still pending are in the sender’s database sys.transmission_queue system table so when an Error message is received the sender can scan this table and resend each message that is still pending. An example of such procedure is not difficult to code:

Read the rest of this entry »

The question was asked on the forums at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2320807&SiteID=1: How to enable a WCF based application to benefit from SQL Server 2005 Query Notifications? Basically the front end clients should be notified when data is changed on the back-end server tables as possible with least possible pooling.

Deploying SqlDependency in the WCF service itself to leverage the server’s Query Notification is a relatively easy process, simply follow the SqlDependency usage guidelines for any application: start SqlDependency, then attach a SqlDependency object instance to your SqlCommand objects, use this object’s OnChange event to react to changes, and subscribe again once notified.

Read the rest of this entry »

So how does one deploy Service Broker services in a large enterprise? Hundreds of services that change location every now and then (just enough to create a major outage exactly at the wrong moment!), and each database requiring a route to any service it wishes to interact with. Creating hundreds and thousands of routes and maintaining them as each new service is deployed, is retired or is moved is a difficult task and obviously prone to operational mistakes, pretty much a disaster waiting to happen.

Read the rest of this entry »

In my previous post Reusing Conversations I promised I’ll follow up with a solution to the question about how to end the conversations that are reused for the data-push scenario (logging, auditing, ETL for DW etc).

Read the rest of this entry »

One of the most common deployed patterns of using Service Broker is what I would call ‘data push’, when Service Broker conversations are used to send data one way only (from initiator to target). In this pattern the target never sends any message back to the initiator. Common applications for this pattern are:

· ETL from the transactions system to the data warehouse

· audit and logging

· aggregation of data from multiple sites

Read the rest of this entry »

A question was asked on the newsgroups: how can a C# program be notified when the schema of a table was modified (i.e. a column was added)? The short answer is to use Event Notifications, see http://msdn2.microsoft.com/en-us/library/ms189453.aspx

But an example would help J. First thing, we need to set up a queue and service that will receive the notifications:

Read the rest of this entry »

A fast way to check the count of messages in a queue is to run this query is to look at the row count of the underlying the b-tree that stores the messages:

select p.rows

from sys.objects as o

join sys.partitions as p on p.object_id = o.object_id

join sys.objects as q on o.parent_object_id = q.object_id

where q.name = ‘<queuename>’

and p.index_id = 1

Read the rest of this entry »

A frequent question from customers is ‘How can I change the activation timing?’. The short answer is that you cannot, this is hard coded in the SQL Server engine and cannot be customized. But the reason why most people want the timing changed is to start all the configured max_queue_readers at once. I will show you an unorthodox trick that can be used to achieve this result.

Read the rest of this entry »

It’s been a while since I posted an entry in this blog and this article is long overdue. There were a series of events that prevented me from posting this, not least impacting being the fact that I’ve opened a WoW account…

T-SQL RECEIVE. Fast.

A question often asked is how to write a typical activated procedure? This article will cover the ways to write a performant T-SQL procedure to process messages. I am not going to cover CLR procedures for now.

Read the rest of this entry »

One of the most frequent questions about Service Broker is whether it supports any sort of priority for messages. But having priority within a conversation would conflict directly with the exactly-once-in-order guarantee of a conversation. In a good SOA design the two services involved in a conversation are supposed to be independent: separate developers, separate orgs, separate admins etc. If one service can set the priority of an individual message w/o the consent of the other, this action could wreck havoc on how the messages are processed, since the other service may expect the messages in order. A different story though is to have priority for individual conversations. Conversations are supposed to be independent and atomic; the processing order should not matter so having the possibility of setting a priority for a conversation makes sense. Roger has recently addressed the same problem and he already has a couple of posts on the topic: http://blogs.msdn.com/rogerwolterblog/archive/2006/03/11/549730.aspx and http://blogs.msdn.com/rogerwolterblog/archive/2006/03/17/554134.aspx

Read the rest of this entry »