I'm trying to enable Service Broker on a SQL Server 2012 database that is part of an availability group and keep getting this error message:
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.
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 1468)
Even attempting to enable Trustworthy on the database generates the same error. According to this article, it seems that Service Broker could be enabled on databases configured for AG. What could I be missing?
The listener has been configured, endpoints created with public permission, targets reference the listener name. The error occurs when I try to enable service broker or set Trustworthy via management console or T-SQL.
I wanted to add that the database is a restore from a SQL Server 2005 version with a existing Service Broker implementation.
Best Answer
When you restore a database the Service Broker in it gets disabled and must be explicitly enabled (there are reasons why this is necessary, but I won't go into them). If you restored the DB and then set up the AG w/o enabling broker first, you have set up the AG w/o the Service Broker enabled and now you cannot run the
ALTER DATABASE SET ENABLE_BROKER. Unfortunately to set it up correctly you must enable broker before adding it to the AG, which means you must tear apart the AG, enable broker, then set set up the AG again.