Introduction
The SQL Server Service Broker helps developers to build scalable as well as secure database applications. The Service Broker is part of the database engine; it provides a message base communication platform used in independent application components to perform as functioning whole. The Service Broker also supports an asynchronous programming model used in single instances as well as for distributed applications.
Service Broker also provides queuing and reliable messaging for SQL Server. Service Broker is used both for applications that use a single SQL Server instance as well as applications that distribute work across multiple instances.
How to Enable Service Broker
Use the following to enable Service Broker:
-- Enable Service Broker:
ALTER DATABASE [Database Name] SET ENABLE_BROKER;
-- Disable Service Broker:
ALTER DATABASE [Database Name] SET DISABLE_BROKER;
-- To know Service Broker is enabled status for a particular database, run following Query
SELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name';
-- is_broker_enabled will be 1 if Service Broker is enabled for the given database, otherwise it will be 0.
Advantages of Service Broker
The Advantages of the Service Broker are:
-
Database integration provides performance enhancement; database administration is also very easy when data, messages and application logic are within the database.
-
The Service Broker handles message order, unique delivery, and conversation identification automatically.
-
The Service Broker provides a loose coupling between the source (initiating) application and the target application.
-
The Service Broker associates related conversations in a conversation group. The Service Broker automatically locks all messages that are in the same conversation group, so that these messages can be received and processed by one application instance.
-
The Service Broker also supports activation. Activation allows an application to dynamically scale itself to match the volume of messages that arrive in the queue.
Please refer to Advantages of Service Broker to understand the advantages in detail.
When to use a Service Broker
The following are the possible uses of the Service Broker:
-
Asynchronous Triggers
-
Data Collection
-
Reliable Query Processing
-
Data Consolidation for Client Applications
-
Distributed Server-Side Processing for Client Applications
-
Large-Scale Batch Processing
Please refer to the Uses of Service Broker to understand the uses in detail.
Conclusion
Service Broker is very useful for asynchronous integration, providing for secure, scalable and reliable messaging. It is also useful when multiple sources can be responsible for initiating the integration of the same data.