SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Enable Broker Service


Enable Broker Service

Author
Message
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32591 Visits: 18557
thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Trey Staker
Trey Staker
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1278 Visits: 2788
Thanks for the question, this is something I know nothing about and missed the point. I don't mind missing points when I learn something.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
SanDroid
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1578 Visits: 1046
Mayank (10/26/2010)

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.


The wording of the question and the code was misleading and confusing.

The code created one backup file db1.bak, and then restored from a differant file called main.bak. This means that it did not restore the backup created for db1 in the first script to to db2 in the second script. Your code restored a backup called main.bak. You quoted comment above does not apply since it assumes we all corrected a mistake in your questions code that was not apparent until after you had made responses here.

Did the database in the main.bak backup file have the Broker Service enabled when it was created?
Did you question make that fact clear?

It was completely unclear if any database had Broker Service enabled, disabled, or if it had ever been set-up when the backup file was created.

This could have been a really decent high level question had anyone taken time moment to read it and execute the code with it prior to posting.
Hardy21
Hardy21
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1586 Visits: 1399
SanDroid (10/27/2010)
Mayank (10/26/2010)

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.


The wording of the question and the code was misleading and confusing.

The code created one backup file db1.bak, and then restored from a different file called main.bak. This means that it did not restore the backup created for db1 in the first script to to db2 in the second script. Your code restored a backup called main.bak. You quoted comment above does not apply since it assumes we all corrected a mistake in your questions code that was not apparent until after you had made responses here.

Did the database in the main.bak backup file have the Broker Service enabled when it was created?
Did you question make that fact clear?

It was completely unclear if any database had Broker Service enabled, disabled, or if it had ever been set-up when the backup file was created.

This could have been a really decent high level question had anyone taken time moment to read it and execute the code with it prior to posting.



that's really good observation - I think most of us didn't check the backup file which author has used for restoration.

Thanks
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27537 Visits: 13268
Hardy21 (10/27/2010)
that's really good observation - I think most of us didn't check the backup file which author has used for restoration.


Well I did :-) I just ignored it because it was an obvious typo (and the answers didn't have any error messages)


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

MCSE Business Intelligence - Microsoft Data Platform MVP
SanDroid
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1578 Visits: 1046
da-zero (10/28/2010)
[quote]
Well I did :-) I just ignored because it was an obvious typo (and the answers didn't have any error messages)


As did I, but it explains why some people that don't have "Question ESP", but do know SQL, would have made the wrong answer.

Had the answer been SET ENABLE instead of SET NEW, it also would have been correct given the code in the example.

Just another instance for the QOTD needs a frakin' Moderator pile. Hehe :-P
rafael lenartowicz
rafael lenartowicz
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 271
Mayank P - Clarion, India (10/26/2010)
Yes Phil,

... you restore the back up of database db1 to database db2, and after that if you want to enable broker service on database db2...


there is nothing in this question that indicates if db1 was previously enabled for service broker, which makes the question invalid. I'm with Phil on that one.
Dhruvesh Shah
Dhruvesh Shah
SSChasing Mays
SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)

Group: General Forum Members
Points: 659 Visits: 237
Good Question Didn't know anything about NEW Broker until now.
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14381 Visits: 12212
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.

But surely it is identical? The broker is deactivated, in both cases, because there is an identifier clash. It can't be reactivated using ENABLE, whether the deactivation happened as part of attach or as part of restore, because of that identity clash, so a new identity has to be substituted using NEW.

Tom

zymos
zymos
Mr or Mrs. 500
Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)

Group: General Forum Members
Points: 598 Visits: 259
Great pick on the question. I did have not dealt much with the Service Broker but have now learnt about it.

Thank you.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search