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?
Best Answer
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
QueueIdandSlotId. 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.