4

Our developers need a change on a database that is in the Availability group (they need Service broker in one of the databases to be enabled) but when I try to do it I am getting error saying that I can’t make the change since this database is a member of an Availability group….

Is there a way to change it without taking this database out of availability group or I have to take it out (and how to do it if I have to and not to have to re-install the database again on the secondary node).

I see that source database we copied from on our current Live server has this Service broker enabled but after restoring it's backup on Azure it picked up the False state for Enabled Service Broker feature).

5

There is no other way to enable service broker except taking that database out of AlwaysON Availability group, enabling service broker and then joining it back.

If you know that your source database has service broker enabled, then when you restore the database, use RESTORE .. with ENABLE_BROKER, so that you dont have to go with such trouble again.

If you try to enable service broker for a database involved in AG, you will get below error :

Msg 1468, Level 16, State 1, Line 3 The operation cannot be performed on database "AdventureWorks2012" 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, Line 3 ALTER DATABASE statement failed.

  • If I take the database out of AlwaysOn Availability group secondary will remain in restoring state and I will not be able to make a change on it? I would have to drop it and recreate the secondary out of primary again? Why didn't it keep the setting of the original source database that had the Service Broker enabled to begin with? This just sounds like that I would have to recreate the secondary every time we need to make a chenge? – TheNixon Nov 19 '14 at 22:14
  • You have to restore it with ENABLE_BROKER option. – Kin Shah Nov 19 '14 at 22:16
  • If your question is answered, please upvote/mark as answer. – Kin Shah Nov 19 '14 at 23:40
  • Hello Kin, if I may ask another issue. All of this is going on on Azure. We do have a Listener configured and added to the Availability Group. Except adding a Listener to the group, is there anything else to be done? Thanks msdn.microsoft.com/en-us/library/hh710058(v=sql.110).aspx – TheNixon Nov 24 '14 at 9:13

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.