tailieunhanh - The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P111

The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P111: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | 532 Chapter 12 Service Broker 2. The procedure takes one second to process each message due to the complex business logic within the procedure. 3. The queue MyApplication ReceivingQueue1 receives four messages per second at the peak load and one message every two seconds at the lowest load. As you can see from the following sample code we have configured the queue to run a maximum of two copies of the stored procedure at any one time. When the first message arrives in the queue the SQL SERVICE BROKER automatically starts our stored procedure . When the second message arrives in the queue the first thread is still running so SQL SERVICE BROKER spawns a new thread and starts another copy of the stored procedure. When the third message arrives in the queue the SQL SERVICE BROKER already has two copies of the procedure running so it does not start a third one. Once one of the procedures finishes processing the data the loop within the stored procedure tells it to check for another message. Since the third message has arrived in the queue that message is received and the stored procedure begins processing the data within that message. As you can see you need to find a balance between processing all the messages as quickly as possible and keeping system resources free to complete other tasks. CREATE QUEUE MyApplication ReceivingQueue1 WITH STATUS ON RETENTION OFF ACTIVATION STATUS ON PROCEDURE_NAME MAX_QUEUE_READERS 2 EXECUTE AS dbo ON YourFileGroup Test Day Tip WThe process of setting up an activation procedure can get confusing. Be sure to remember that you need to create the queue then the w procedure then set the procedure as an activation procedure. Service Broker Chapter 12 533 Each queue you create to receive messages should have a queue that is used as the source of the message. After each conversation is closed the receiving queue will send a message back to the source queue telling it that .