Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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, http://msdn.microsoft.com/en-us/library/hh710058.aspx, it seems that Service Broker could be enabled on databases configured for AG. What could I be missing?

Thanks so much for your help.

share|improve this question
    
Have you followed the directions in the MSDN page you listed, including creating an Availability Group Listener? When does the error you quote occur? –  Max Vernon Sep 10 '12 at 19:00
    
Yes, 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 to true on the database, via management console or t-sql. –  Sol Esguerra Sep 10 '12 at 21:22
    
I wanted to add that the database is a restore from a SQL Server 2005 version with a existing Service Broker implementation. Thanks. –  Sol Esguerra Sep 10 '12 at 21:33
    
Availability Groups dont support listeners "in" the databases involved. you must create a listener for each instance. –  Max Vernon Sep 11 '12 at 0:08
    
Thanks Max. I'll work with the DBA on this. I'll post an update after. –  Sol Esguerra Sep 11 '12 at 15:55

1 Answer 1

I wanted to add that the database is a restore from a SQL Server 2005

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.

share|improve this answer
    
Thanks Remus! We'll redo the configuration. I'll update the post after with the results. –  Sol Esguerra Sep 11 '12 at 15:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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