Service Broker with Always On Availability Groups (SQL Server)

Artikel
04/26/2023

Applies to: SQL Server

This topic contains information about configuring Service Broker to work with Always On availability groups in SQL Server.

Receive remote messages from other Service Broker instances

Ensure that the availability group possesses a listener.

For more information, see Create or Configure an Availability Group Listener (SQL Server).
Ensure that the Service Broker endpoint exists and is correctly configured.

On every instance of SQL Server that hosts an availability replica for the availability group, configure the Service Broker endpoint, as follows:
- Set LISTENER_IP to 'ALL'. This setting enables connections on any valid IP address that is bound to the availability group listener.
- Set the Service Broker PORT to the same port number on all the host server instances.
  
  Tip
  
  To view the port number of the Service Broker endpoint on a given server instance, query the port column of the sys.tcp_endpoints catalog view, where type_desc = 'SERVICE_BROKER'.
The following example creates a Windows authenticated Service Broker endpoint that uses the default Service Broker port (4022) and listens to all valid IP addresses.
```
CREATE ENDPOINT [SSBEndpoint]  
    STATE = STARTED  
    AS TCP  (LISTENER_PORT = 4022, LISTENER_IP = ALL )  
    FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS)  
```
For more information, see CREATE ENDPOINT (Transact-SQL).

Note

SQL Server Service Broker is not MultiSubnet aware. Set RegisterAllProvidersIP to 0 and verify that the cluster has required permission in DNS to use static IP addresses. See configure availability group listener to learn more. Service Broker may delay message with status "CONVERSING" trying to use a disabled IP address.
Grant CONNECT permission on the endpoint.

Grant CONNECT permission on the Service Broker endpoint either to PUBLIC or to a login.

The following example grants the connection on a Service Broker endpoint named SSBEndpoint to PUBLIC.
```
GRANT CONNECT ON ENDPOINT::[SSBEndpoint] TO [PUBLIC]  
```
For more information, see GRANT (Transact-SQL).
Ensure that msdb contains either an AutoCreatedLocal route or a route to the specific service.

Note

By default, each user database, including msdb, contains the route AutoCreatedLocal. This route matches any service name and broker instance and specifies that the message should be delivered within the current instance. AutoCreatedLocal has lower priority than routes that explicitly specify a specific service that communicates with a remote instance.

For information about creating routes, see Service Broker Routing Examples (in the SQL Server 2008 R2 (10.50.x) version of Books Online) and CREATE ROUTE (Transact-SQL).

Requirements for Sending Messages to a Remote Service in an Availability Group

Create a route to the target service.

Configure the route as follows:
- Set ADDRESS to the listener IP address of availability group that hosts the service database.
- Set PORT to the port that you specified in the Service Broker endpoint of each of the remote SQL Server instances.
The following example creates a route named RouteToTargetService for the ISBNLookupRequestService service. The route targets the availability group listener, MyAgListener, which uses port 4022.
```
CREATE ROUTE [RouteToTargetService] WITH   
SERVICE_NAME = 'ISBNLookupRequestService',   
ADDRESS = 'TCP://MyAgListener:4022';  
```
For more information, see CREATE ROUTE (Transact-SQL).
Ensure that msdb contains either an AutoCreatedLocal route or a route to the specific service. (For more information, see Requirements for a Service in an Availability Group to Receive Remote Messages, earlier in this topic.)

Dela via

Service Broker with Always On Availability Groups (SQL Server)

Receive remote messages from other Service Broker instances

Requirements for Sending Messages to a Remote Service in an Availability Group

Related Tasks

See Also

Feedback

Ytterligare resurser