problem with service broker in always-on

by winter_sql   Last Updated August 05, 2018 08:06 AM

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.



Related Questions


SQL Service Broker and AlwaysOn Issue

Updated April 23, 2015 21:02 PM

Permission for user to enable a Queue

Updated August 15, 2018 19:06 PM

Service Broker repeating messages

Updated March 11, 2017 20:06 PM

Service Broker - Conversation Lifetime?

Updated October 03, 2017 22:06 PM