March 27, 2009 at 4:14 pm
Hi All,
I have tried to enable service broker using:
Alter Database MyDatabase
Set Enable_Broker
The error message reports "Cannot create a new Service Broker in a mirrored database". I tried pausing the mirroring and received the same message.
I don't see anything about this in BOL. Does anyone know of a way to enable service broker without dropping the mirroring?
Regards,
Elliott
March 27, 2009 at 7:01 pm
What do you get in output when u run:
select name,is_broker_enabled from sys.databases
where name='DatabaseName'
MJ
March 28, 2009 at 6:20 am
Elliott Berkihiser (3/27/2009)
The error message reports "Cannot create a new Service Broker in a mirrored database". I tried pausing the mirroring and received the same message.
You should enable Service broker before the mirroring session has been configured.
SELECT is_broker_enabled FROM sys.databases
WHERE name='your database name'
GO
[/code]
March 28, 2009 at 12:22 pm
I think that Service Broker is already on in a Mirrored DB. It probably will not let you make a NEW service broker because that would kill the old one which would mess up the mirror queuing.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 30, 2009 at 10:09 am
Running the query: Select name, is_broker_enabled from sys.databases
yields a 0 for the databases that do not have Server Broker enabled. This is true whether I run the query from the server with the Principal member, or from the serve with the Mirror members (not surprisingly).
So apparently the Alter Database command is not failing because the service is already enabled.
It would be nice to be able to implement the service without breaking the mirroring. But my attempt to Pause the mirroring and then alter the database failed.
March 30, 2009 at 10:21 am
OK, this is straight from Remus Rusanu himself:
You must enable broker before the mirroring session is enabled. You should
first check if the broker is enabled in the first place
(sys.databases.is_broker_enabled). If is not, then you must stop the
mirroring session, enable the broker and re-establsih the mirroring session
from scratch.
HTH,
~ Remus
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply