Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Enable Broker Service Expand / Collapse
Author
Message
Posted Tuesday, October 26, 2010 2:44 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, September 21, 2011 4:03 AM
Points: 713, Visits: 100
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
--------------------------------------------------------------------------------
Post #1010603
Posted Tuesday, October 26, 2010 2:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, October 19, 2014 10:29 AM
Points: 1,880, Visits: 3,460
I agree with Christian, the question should have stated whether service broker was enabled or not in db1.
Post #1010608
Posted Tuesday, October 26, 2010 2:56 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:55 AM
Points: 558, Visits: 1,488
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
Post #1010611
Posted Tuesday, October 26, 2010 3:02 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 7, 2013 5:12 AM
Points: 509, Visits: 182
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
Post #1010614
Posted Tuesday, October 26, 2010 3:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 6,048, Visits: 8,328
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1010618
Posted Tuesday, October 26, 2010 3:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 13,295, Visits: 11,086
Hugo Kornelis (10/26/2010)
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.


Ah well, I made the same mistake
BOL could have been a bit more specific on that point.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1010628
Posted Tuesday, October 26, 2010 4:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 4:29 AM
Points: 132, Visits: 226
u r right, the question was not phrased very well.

not mentioned Service broker is enabled or not db1.

when we restore database from any existing DB Backup.

we always need to
1) Enable Broker -- when Backup db is not set broker ON.
2) New Broker -- When backup db broker is ON.
Post #1010643
Posted Tuesday, October 26, 2010 6:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:51 AM
Points: 3,067, Visits: 955
I realized there was probably a 50% risk of getting it wrong which I did.

Interestingly in BOL it wasn't specified whether assigning a new identifier with NEW_BROKER actually enabled the Service Broker.



Just because you're right doesn't mean everybody else is wrong.
Post #1010733
Posted Tuesday, October 26, 2010 7:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:36 PM
Points: 2,818, Visits: 2,565
Good question....got it wrong, but I learned something. Thanks.
Post #1010761
Posted Tuesday, October 26, 2010 9:49 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 9:11 AM
Points: 719, Visits: 695
I guess I'm 'nitpick mode' on as well; backup was to disk 'D:\db1.bak' but the restore was from 'D:\main.bak', technically we have no idea what was restored
Post #1010939
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse