Service Broker Issues in Always-On Availability Groups

availability-groupsservice-brokersql serversql-server-2016

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://docs.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
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.

Best Answer

I'm sure this is sorted now, but you have to enable service broker before creating the availability group. This message suggests that you created the AG, then tried to configure service broker afterwards.