Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
3  
If you are considering creating 100-500 unique tables, one per endpoint, then why not consider having your table partitioned by endpoint? Eliminates the hassle of dynamic SQL but has roughly the same effect as separate tables. (This assumes Enterprise Edition, of course.) –  Aaron Bertrand Dec 12 '12 at 3:48
5  
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
    
@AaronBertrand -- I will have to look into partitioned tables. I was planning to implement the queue tables as ring buffers with pre-allocated slots to avoid the constant churn of inserted and deleted messages. I'm not sure if a partitioned table can do that. –  Dan Dec 12 '12 at 12:32
    
@mrdenny -- I have looked into SSSB. Unfortunately, I need to use the message queue tables in ways that SSSB does not permit. –  Dan Dec 12 '12 at 12:35
    
@AaronBertrand -- 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. That's very helpful to know, but I doubt I'm going to be able to sell the requirement for Enterprise Edition. –  Dan Dec 12 '12 at 12:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.