I have a database with Broker_Enabled set via the following command:
ALTER DATABASE 'xxxx' SET ENABLE_BROKER
All works well, I made a backup and want to move to my development machine. When I run I still get an error saying I need to enable broker service again.
"The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications."
How can I move the database and get this setup Where are the BrokerID's Stored
Thanks,
~ Steve
Moving DB one server to another with Broker Enabled
basementjack
Nothing like being "that guy" and answering my own post. I just figured out a solution.
alter database xxxx set NEW_BROKER
Thanks,
~ Steve
Michael Drüing
I did ALTER DATABASE 'xxxx' SET ENABLE_BROKER , and it worked on machine A
I moved my database to machine B, and upgraded the dB with SQL2005 sp2.
Now I neither of ENABLE_BROKER and NEW_BROKER work.
Please help.
Any help will be much appreciated
Clifford
ItsIdefix
Did the query finish when you ran it I've run into issues where there are active connections to the DB and you cannot enable the broker without exclusive access.
Try restarting your instance then run the set new_broker and see if that works.
~ Steve
TGunthorpe
The broker is automatically disabled after a restore or attach operation. So even if you enabled the broker, it will be disabled after the restore on the development machine. You'd have to run again the ALTER .. SET ENABLE_BROKEr on the newly restored database.
But indeed, as you found out, is better to use NEW_BROKER in your scenario. It would be a bad thing to have two copies of the same broker_instance in your environment, so is necessary to create a new one after the restore on the dev machine.
HTH,
~ Remus