Queuing in OneWay WCF Messages using Windows Service and SQL Server

I need to implement a queuing mechanism for WCF service requests. The service will be called by clients in a one-way manner. These request messages should be stored in a SQL Server database and a Windows Service queues the messages. The time at which the requests are processed will be configurable. If there happens error in processing the message, it need to be retried up to 100 times and if still fails it need to be terminated.

Also there should be a mechanism to monitor the number of transaction made on a day and number of failures.

QUESTIONS

1. If I were using MSMQ, clients could have forwarded the message to queue without knowing the service endpoint. But I am using SQL Server to store the request messages. How the clients can put the requests to SQL Server?

2. Is the solution feasible? Do we have any article/book that explains how to implement the above?

3. What are the steps to prevent service and client reaching faulted state in this scenario?

4. What is the best method to store incoming message to database?

5. What is the best method to implement retry mechanism? Anything already exist so that I don't have to reinvent the wheel?

6. Is there any book/article that explains this implementation?

NOTES

1. Content of the message will be complex XML. For example Travel expense items of an employee or a list of employees.

READING

2. Logging WCF Request to Database

3. Guaranteed processing of data in WCF service

4. MSMQ vs. SQL Server Service Broker

5. Is it possible to persist and then forward WCF messages to destination services?

6. WCF 4 Routing Service - protocol bridging issue

7. https://softwareengineering.stackexchange.com/questions/134605/designing-a-scalable-and-robust-retry-mechanism

8. Integrating SQL Service Broker and NServiceBus

9. Can a subscriber also publish/send message in NServiceBus?

Solution 1:

I'm a DBA, so that flavors my my response, but here's what I'd do:

1. If you're using SQL 2005+, use Service Broker to store the messages in the database rather than storing them in a table. You get a queueing mechanism with this, so you can get rid of MSMQ. You'll also have a table, but it's just going to store the conversation handle (essentially, a pointer to the message) along with how many times it attempted this message. Lastly, you'll want some sort of a "dead letter box" where messages that reach your retry threshold go.
2. In your message processing code, do the following:
   • Begin a transaction
   • Receive a message off of the queue
   • If the retry count is greater than the threshold, move it to the dead letter box and commit
   • Increment the counter on the table for this message
   • Process the message
   • If the processing succeeded, commit the transaction
   • If the processing failed, put a new message on the queue with the same contents and then commit the transaction

Notice that there aren't any planned rollbacks. Rollbacks in Service Broker can be bad; if you rollback 5 times without a successful receive, the queue will become disabled for both enqueuing and dequeuing. But you still want to have transactions for the case when your message processor dies in the middle of processing (i.e. the server crashes).

Solution 2:

1. If I were using MSMQ, clients could have forwarded the message to queue without knowing the service endpoint.

Yes - but they would need to know the MSMQ endpoint in order to send their message to the queue.....

But I am using SQL Server to store the request messages. How the clients can put the requests to SQL Server?

The clients won't put their requests into SQL Server - that's what the service on the server will do. The client just call a service method, and the code in there will store the request into the SQL Server table.

2. Is the solution feasible? Do we have any article/book that explains how to implement the above?

Sure, I don't see any big issue. The only point unclear to me right now is: how will the clients know their results?? Do they need to go get results from another service or something??

3. What are the steps to prevent service and client reaching faulted state in this scenario?

As always - just make sure your service code catches all exceptions and either handles them internally, or returns interoperable SOAP faults instead of .NET exceptions.

Solution 3:

It sounds like what you want to do is similar to this:

In this case you can use netMsmqBinding between your service and your service consumers.

The only thing you won't get out of the box is the retrying. However if you make the queue transactional then this functionality can be implemented in your service code.

If there is a failure in your dequeue operation the message will not be removed from the queue. It will therefore be available for further dequeue attempts.

However, you would need to implement retry attempt threshold code which fails a message after a certain number of attempts.

Solution 4:

I would suggest a different approach to the ones suggested here. If you are able to, I would consider the introduction of a messaging framework such as NServiceBus. It satifies many of the requirements that you have right out of the box. Let me try and address this in context of your requirements.

The service will be called by clients in a one-way manner.

All communication between endpoints in NServiceBus is one way. The underlying transport NServiceBus uses is MSMQ, so much like your WCF approach, your client is communicating with queues, rather than specific service endpoints.

These request messages should be stored in a SQL Server database and a Windows Service queues the messages.

If you wanted to store your request messages in a database then you can configure NServiceBus to forward all messages sent to your request processing endpoint to another "audit" queue, which you can use to persist to the database. This has the added benefit of separating your application logic from your auditing implementation.

The time at which the requests are processed will be configurable.

NServiceBus allows you to defer when a mesage is sent. Normally a message is sent via the Send method of a Bus instance - Bus.Send(msg). You can use The Defer method to send the message some time in the future eg. Bus.Defer(DateTime.Now.AddDays(1), msg); There's nothing more you really have to do, NserviceBus will handle the message once the specified time has been reached.

If there happens error in processing the message, it need to be retried up to 100 times and if still fails it need to be terminated.

By default, NServiceBus will enlist your message in a transaction as soon as your message leaves the queue. This ensures that in the event of failure that the message is rolled back to the originating queue. In such an event, NServiceBus will automatically try to reprocess the message a configurable number of times. The default being 5. You can of course set this to whatever you want, although I am not sure why you would want to set this to 100. At any rate, NServiceBus uses this setting to stop an endless loop of automatic retries. Once the limit has been reached the message is sent to an error queue where it sits until you fix whatever issues caused the exception or until you decide to push the message back to the queue for processing. Either way, you are assured that the message is never lost.

Also there should be a mechanism to monitor the number of transaction made on a day and number of failures.

The beauty of using MSMQ as the transport is that performance monitoring can be a achieved at a infrastructure level. How your applications perform, can be measured by how long they sit in the queue. NServiceBus comes with performance monitors that track the length of time a message is in the queue and you can also add perf mons that come built into windows to track other activity. To monitor errors, all you need to do is check the number of messages in the error queue.

One of the main features of NServiceBus is reliability. WCF will only do so much for you, and then you are on your own. That's a lot of code, complexity and frankly hugely error prone. The things I have described here are all standard features of NServiceBus and I have barely scratched the surface with all the other things that you can do with it. I recommend you check it out.