SQL Server Service Broker and AlwaysOn
SQL Server Service Broker provides native support for messaging and queuing applications in the SQL Server Database Engine.
The reference documentation for Service Broker is included in the SQL Server 2012 documentation. This reference documentation includes the following sections:
- Data Definition Language (DDL) Statements (Transact-SQL) for CREATE, ALTER, and DROP statements
- Service Broker Statements
- Service Broker Catalog Views (Transact-SQL)
- Service Broker Related Dynamic Management Views (Transact-SQL)\
- ssbdiagnose Utility (Service Broker)
See the previously published documentation for Service Broker concepts and for development and management tasks. This documentation is not reproduced in the SQL Server 2012 documentation due to the small number of changes in Service Broker in SQ Server 2012.
What's new in Service Broker in SQL Server 2012?
- Messages can be sent to multiple target services (multicast): The syntax of the SEND (Transact-SQL) statement has been extended to enable multicast by supporting multiple conversation handles.
- Queues expose the message enqueued time: Queues have a new column, message_enqueue_time, that shows how long a message has been in the queue.
- Poison message handling can be disabled: The CREATE QUEUE (Transact-SQL) and ALTER QUEUE (Transact-SQL) statements now have the ability to enable or disable poison message handling by adding the clause, POISON_MESSAGE_HANDLING (STATUS = ON | OFF). The catalog view sys.service_queues now has the column is_poison_message_handling_enabled to indicate whether poison message is enabled or disabled.
- AlwaysOn support in Service Broker : For more information, see Service Broker with AlwaysOn Availability Groups (SQL Server).
I can suggest also the following links to better understand the concept of queues:
- Architecting Service Broker Applications
- Creating Service Broker Queues
- Queues
I would also like to recommend the following book that provides some of the answers to your questions and also some script examples : https://www.apress.com/9781590599990
The modifications you will have to do are described in the article: Service Broker with AlwaysOn Availability Groups
- Requirements for a Service in an Availability Group to Receive Remote Messages
- Requirements for Sending Messages to a Remote Service in an Availability Group
Please access the following links to better understand the concept of AlwaysOn , how it works and application failover:
- Overview of AlwaysOn Availability Groups
- Availability Group Listeners, Client Connectivity, and Application Failover