1

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.

  • 1
    The documented configuration steps need to be performed on all instances participating in the AG before adding the database to the AG. Also there's a required patch level SQL 2016 RTM/CU6 & SP1/CU3 to make this work: blogs.msdn.microsoft.com/alwaysonpro/2017/01/26/… – David Browne - Microsoft Aug 5 '18 at 15:06
  • as far as i know that was the way it was done by my team. i will check again thanks for the reply – sql_girl Aug 5 '18 at 15:08
  • 2
    And don't run alter database [databasename] set new_broker with rollback immediate it will delete data. – David Browne - Microsoft Aug 5 '18 at 15:09
  • @DavidBrowne-Microsoft nothing worked we ended up opening a request for help to microsoft, which are still trying to fix it. thanks for the advice – sql_girl Aug 22 '18 at 12:26
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.