Monday, March 7, 2011

SQL Server Service Broker - External Activation

SQL Server Service Broker allows for setting up two types of activation, Internal Activation or External Activation. To handle increased message traffic, in internal activation you specify a stored procedure (multiple instances might be created depending on your setting) to be called and this way you scale up your distributed application, whereas in case of external activation Service Broker sends notification (QUEUE_ACTIVATION event) to an external application/program outside SQL Server to read the message from the queue and process it. This way you actually scale out your distributed application. External activation allows putting heavy weight processing logic outside SQL Server in a separate process than SQL Server which gives better performance and scalability or might run under different credential than the SQL Server service account.

In my last article I talked about setting up internal activation, writing a stored procedure which will be called upon on activation. In this article, I will be talking about external activation in detail. For more details click here.

Service Broker and Poison Message Handling

Have you ever figured out why your service broker queue gets disabled automatically? What causes your service broker queue to get disabled in the first place? What is a poison message with respect to service broker? Is there anything new in SQL Server 2008 R2 for managing poison messages in Service Broker? To explore it more click here.

Understanding SQL Server Net-Libraries

I know that SQL Server and its clients interact with each other using different Net-Libraries or network protocols. But what are these Net-Libraries that SQL Server uses and how do each of these differ from the others. Also, in which scenario is one preferred over the others? To learn about it click here.

SQL Azure - Getting Started With Database Manager

The Database Manager for SQL Azure is a lightweight, easy to use, thin web-based database management tool for connecting to a SQL Azure database and managing different database management tasks like; creating and editing tables, views and stored procedures, editing table data, writing and executing T-SQL queries, etc...for more information click here.

SQL Server Service Broker – Internal Activation

SQL Server Service Broker allows you to create activation stored procedures, which are called automatically (by Service Broker - Queue Monitor) whenever a message arrives in the queue (very much like a trigger but not exactly the same). Not only this, you can specify the number of instances of the stored procedure to be created to process messages from different conversation groups in parallel if the queue is overloaded with the arrival of lots of messages. In this article, I will be talking about setting up internal activation and writing a stored procedure, which will be called on activation, for more information click here.