SQL Server Service Broker activation is another unique feature of the SQL Server Service Broker subsystem. Activation enables you to create a stored procedure that is associated with a given input queue. The purpose of the stored procedure is to automatically process messages from that queue. As each new message comes in, the associated stored procedure is automatically executed to handle the incoming messages. If the stored procedure encounters an error, it can throw an exception and be automatically recycled.

Periodically, the SQL Server Service Broker checks the status of the input queue to find out if the stored procedure is keeping up with the incoming messages on the input queue. If the SQL Server Service Broker determines that there are waiting messages on the queue, then it will automatically start up another instance of the queue reader to process the additional messages. This process of automatically starting additional queue readers can continue until the preset MAX_QUEUE_READERS value is reached. Likewise, when the SQL Server Service Broker determines that there are no remaining messages on the queue, it will begin to automatically reduce the number of active queue readers.

SQL Server Service Broker queues don’t necessarily need to be associated with just stored procedures. Messages that require more complex processing can also be associated with external middle-tier procedures. Since these middle-tier processes are external to the database, they need to be activated differently. To enable the automatic activation of external processes, the SQL Server Service Broker also supports firing a SQL Server event. These events can be subscribed to using WMI (Windows Management Instrumentation).

When dialogs are created, they can optionally be secured using the WITH ENCRYPTION clause. When a dialog is created using the WITH ENCRYPTION clause, a session key is created that’s used to encrypt the messages sent using the dialog. One important point about dialog security is the fact that it is an end-toend security. In other words, the message is encrypted when it is first sent from a dialog, and it is not decrypted until the message reaches its endpoint. The message contents remain encrypted as the message is forwarded across any intermediate hops. To implement dialog security, the SQL Service Broker uses certificate-based authentication, where the certificate of the sending user is sent along with the message. Because of the asynchronous nature of SQL Service Broker, the security information is stored in the message headers and retrieved by the receiving service when the message is retrieved. This enables SQL Service Broker applications to avoid the need to establish a connection to authenticate messages.