problem with service broker in always-on

Question

one of my clients has a database that works with service broker, last week we tried to move the database to an always on environment without knowing the application uses the service broker. after there were complaints that it didn't work(scheduled meetings were not added to the calendar and did not close when old ones were done (its a clinic application)) we found Microsoft reference to service broker with always on and tried to use it: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/service-broker-with-always-on-availability-groups-sql-server?view=sql-server-2017

but it still didnt work. at first we tried

alter database [databasename] set enable_broker with rollback immediate

alter database [databasename] set new_broker with rollback immediate

and we received an error

Msg 1468, Level 16, State 1, Line 2 The operation cannot be performed on database "dbname" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group. Msg 5069, Level 16, State 1

we also tried these commands:

 CREATE ENDPOINT [SSBEndpoint] 

    STATE = STARTED 

    AS TCP  (LISTENER_PORT = 4022, LISTENER_IP = ALL ) 

    FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS)


     GRANT CONNECT ON ENDPOINT::[SSBEndpoint] TO [PUBLIC] 


ALTER ROUTE AutoCreatedLocal

   WITH ADDRESS = 'TCP://[server]:4022'  ;

at the end we didn't have a choice and we took it out of the always on group and after executing this command it worked:

   ALTER ROUTE AutoCreatedLocal
   WITH ADDRESS = 'LOCAL'  ;

does anyone have advise of what we are missing here on the configuration? thanks.

Answer 1

In order to have Service Broker work in combination with Availability Groups, you have to enable Service Broker for the database before adding it to an Availability Group.

Your question suggests that you created the AG, then tried to configure Service Broker afterwards.

Also, be aware there are certain patch level requirements for Service Broker Conversations using Availability Groups.

Answer 2

You can

1. Remove DB from AO

2. run

   ALTER DATABASE '' SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE -- on primary
   

3. ADD DB to AO only

4. Check it:

   USE master
   SELECT name, is_broker_enabled
   FROM sys.databases
   order by is_broker_enabled desc