October 25, 2010 at 10:44 pm
Comments posted to this topic are about the item Enable Broker Service
--------------------------------------------------------------------------------
Mayank Parmar
Software Engineer
Clarion Technologies
SEI CMMI Level 3 Company
8th Floor, 803, GNFC info Tower,
SG Highway, Ahmedabad - 380 054,
Gujarat, India.
www.clariontechnologies.co.in
Email: mayank.parmar@clariontechnologies.co.in
MSN : mayank.parmar@clariontechnologies.co.in
Mobile: +91 9727748789
--------------------------------------------------------------------------------
October 25, 2010 at 11:13 pm
Nice question on something most people probably don't know anything about. Thanks!
October 25, 2010 at 11:55 pm
Nice question, thanks.
Here is also a discussion on this topic: http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/eca47504-9a8c-4c0f-a4a4-3a56fb5a012b
October 26, 2010 at 12:33 am
Another Good question, one which I needed to read up about as we do not use the Service Broker where I work.
October 26, 2010 at 12:45 am
Good question with nice/tricky options.
I was thinking for following options are the candidates:
ALTER DATABASE db2 SET DISABLE_BROKER ; ALTER DATABASE db2 SET ENABLE_BROKER;
ALTER DATABASE db2 SET NEW_BROKER
But after reading some links got the right answer - NEW_BROKER.
Thanks
October 26, 2010 at 1:32 am
Great question!
The Enable broker and new broker were obvious candidates, but it took some Books On line reading to get to NEW_BROKER.
Got to learn something new on Service Broker today. Thank-you!
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
October 26, 2010 at 1:40 am
Nice question about an unknown subject.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 26, 2010 at 2:34 am
I'm sorry, but I do not believe that the question was phrased very well. To 'enable' Service Broker for the database, you use the ENABLE_BROKER option, as stated in the msdn article you posted
ENABLE_BROKER
Specifies that Service Broker is enabled for the specified database. Message delivery is started, and the is_broker_enabled flag is set to true in the sys.databases catalog view. The database retains the existing Service Broker identifier.
To reset/assign a new Service Broker Id for a restored database, you would use the NEW_BROKER option.
NEW_BROKER
Specifies that the database should receive a new broker identifier. Because the database is considered to be a new service broker, all existing conversations in the database are immediately removed without producing end dialog messages. Any route that references the old Service Broker identifier must be re-created with the new identifier.
Phil
Although all answers are replies, not all replies are answers.
Blog: http://philjax.wordpress.com
October 26, 2010 at 2:40 am
<nitpick mode="on" />
I would issue a simple ALTER DATABASE db2 SET ENABLE_BROKER, since noone said that the broker service had already been enabled in db1
<nitpick mode="off" />
Ok, common sense told me that I should assume the service had already been enabled in db1 as well, so I chose the right answer.
Best Regards,
Chris Büttner
October 26, 2010 at 2:42 am
Yes Phil,
Normally to enable the Broker Service, you need to simply use : "ENABLE_BROKER"
But when you restore the back up of database db1 to database db2, and after that if you want to enable broker service on database db2, then what to do? - That was my question.
Regards,
Mayank
--------------------------------------------------------------------------------
Mayank Parmar
Software Engineer
Clarion Technologies
SEI CMMI Level 3 Company
8th Floor, 803, GNFC info Tower,
SG Highway, Ahmedabad - 380 054,
Gujarat, India.
www.clariontechnologies.co.in
Email: mayank.parmar@clariontechnologies.co.in
MSN : mayank.parmar@clariontechnologies.co.in
Mobile: +91 9727748789
--------------------------------------------------------------------------------
October 26, 2010 at 2:44 am
Hi Christian
You are right, I should mention that : "Service had already been enabled in db1"
Regards,
--------------------------------------------------------------------------------
Mayank Parmar
Software Engineer
Clarion Technologies
SEI CMMI Level 3 Company
8th Floor, 803, GNFC info Tower,
SG Highway, Ahmedabad - 380 054,
Gujarat, India.
www.clariontechnologies.co.in
Email: mayank.parmar@clariontechnologies.co.in
MSN : mayank.parmar@clariontechnologies.co.in
Mobile: +91 9727748789
--------------------------------------------------------------------------------
October 26, 2010 at 2:51 am
I agree with Christian, the question should have stated whether service broker was enabled or not in db1.
October 26, 2010 at 2:56 am
Yeah good question. Knew the answer wasn't "enable" so I took a choice between "create" and "new" since I couldn't remember the syntax. I picked wrong 🙁
October 26, 2010 at 3:02 am
To create a new identifier (or as I view it, reset service broker) for a restored database, yes, I would use the NEW_BROKER. However the question could have been phrased a little better; for example, there was no mention that Service Broker was enabled previously; and although the assumption could be made, you specifically asked how would you 'enable' Service Broker.
Phil
Although all answers are replies, not all replies are answers.
Blog: http://philjax.wordpress.com
October 26, 2010 at 3:11 am
Thanks for the question. I missed a point, but learned something new.
The reason I missed a point is because, when doing some research, I found this text:
"If you attach a database with the same service broker identifier as an existing database, SQL Server deactivates Service Broker message delivery in the database being attached."
(This is from http://msdn.microsoft.com/en-us/library/ms166057%28SQL.90%29.aspx).
The quote specifically mentions attaching, but since the entire page is about attach and restore, I assumed that the restore behaviour would be identical. Obviously not.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply