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


Enable Broker Service


Enable Broker Service

Author
Message
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144187 Visits: 18651
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
Learn Extended Events

Trey Staker
Trey Staker
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2254 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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4630 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
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5556 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
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145498 Visits: 13350
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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4630 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
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1217 Visits: 237
Good Question Didn't know anything about NEW Broker until now.
Tom Thomson
Tom Thomson
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50595 Visits: 13157
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1104 Visits: 263
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