Sample Header Ad - 728x90

problem with service broker in always-on

2 votes
2 answers
6879 views
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 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.
Asked by sql_girl (345 rep)
Aug 5, 2018, 07:23 AM
Last activity: Jan 31, 2023, 06:01 PM