2

I am migrating a message processing application that was built on MSMQ to SQL Server 2012 (preferably Standard Edition). Messages are received and distributed via TCP/IP endpoints and the typical installation has between 100 and 500 such endpoints. Each endpoint has its own queue to enforce strict FIFO processing of its messages. Dynamically creating these queues in MSMQ was no problem. Dynamically creating individual queue tables in SQL Server isn't really a problem, either, except for the fact that I want the enqueue and dequeue operations to be implemented as stored procedures because the messages are structured and stored in a non-trivial master-detail arrangement. The separate queue tables would each require a separate pair of enqueue/dequeue stored procedures unless I use dynamic SQL to specify the specific queue table within the stored procedure.

Per Erland Sommarskog's very helpful article on dynamic SQL, the creation of multiple identical queue tables is something of an anti-pattern, but I believe my situation warrants it because storing pointers to all messages in a single queue table would result in significant complexity and fragmentation when maintaining a FIFO ordering on a per-endpoint basis.

Should I just bite the bullet on the dynamic SQL approach to the enqueue/dequeue stored procedures or am I overlooking a simpler solution to the multiple queues problem?

  • 6
    Have you looked at SQL Service Broker? Given that it's a queuing system that is built into SQL Server it might be worth looking at. – mrdenny Dec 12 '12 at 4:42
0

Answer originally left in comments by the question author

After reading up on partitioned tables, it seems that I could store multiple independent ring buffers in one since the partitions can be stored in separate filegroups, but I doubt I'm going to be able to sell the requirement for Enterprise Edition.

I am going to try for a poor man's partitioned table by creating pre-allocated queue slots in a single table using a clustered index on QueueId and SlotId. As long as my queues don't grow beyond the number of allocated slots, I should be able to treat them as consecutive ring buffers for multiple logical queues.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.